asked on

select data from collection

Hai !

i want to select the data from a collection - something like this.

declare
type tab_col is table of number;
num_table tab_col;
total_count number;

begin
for i in 1..10 loop
num_table(i) := i;
end loop;

select count(*) into total_count from table_x where col_1 in ( select * from table(num_table));

dbms_output.put_line(total_count);

end;

this is giving me the error............ select * from table(num_table) ?

how to do ?

sventhan

DECLARE
eml_dmo_nt email_demo_nt_t := email_demo_nt_t();
BEGIN
-- Some logic that populates the nested table &
eml_dmo_nt.EXTEND(3);
eml_dmo_nt(1) := email_demo_obj_t(45, 3, '23');
eml_dmo_nt(2) := email_demo_obj_t(22, 3, '41');
eml_dmo_nt(3) := email_demo_obj_t(18, 7, 'over_100k');

-- Process the data in assending order of email id.
FOR r IN (SELECT * FROM TABLE(CAST(eml_dmo_nt AS email_demo_nt_t))
ORDER BY 1)
LOOP
dbms_output.put_line(r.email_id || ' ' || r.demo_id);
END LOOP;
END;

sventhan

another one...

http://www.databasejournal.com/img/CAST_and_Table_Functions_Listing_1.html

sventhan

https://www.experts-exchange.com/questions/23202100/Unable-to-use-TYPE-in-a-stored-procedure-SELECT.html?sfQueryTermInfo=1+"select+from+tabl+cast"

vishali_vishu

ASKER

sventhan:

It's good with the for loop

but i need it in sql in clause ( .....list of values from the collection .......)

sventhan

Dude -

If this works here
FOR r IN (SELECT * FROM TABLE(CAST(eml_dmo_nt AS email_demo_nt_t))

It'll work anywhere.

So just chagne your sql to

SELECT * FROM TABLE(CAST(num_table AS tab_col)

Naveen Kumar

i think you are getting the error ...

SQL> @h
( select * from table(CAST(num_table AS tab_col)));
*
ERROR at line 19:
ORA-06550: line 19, column 29:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 19, column 29:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CHAR
ORA-06550: line 15, column 1:
PL/SQL: SQL Statement ignored

i am attaching the code which gives that error :
 
set serveroutput on
 
declare
 
type tab_col is table of number;
num_table tab_col:=tab_col();
total_count number;
 
begin
 
for i in 1..10 loop
num_table.extend;
num_table(i) := i;
end loop;
 
select count(*) 
into total_count 
from t1 
where f2 in
 ( select * from table(CAST(num_table AS tab_col)));
 
dbms_output.put_line(total_count);
 
end;
/

Open in new window

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vishali_vishu

ASKER

I am getting this error:
ORA-06550: line 22, column 8:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored

If i comment out this part

where in
( select column_value from table(CAST(num_table AS tab_col)));

it is running good. where am i going wrong ??????

vishali_vishu

ASKER

sorry please discard my previous error.....

syntax error.......