We help IT Professionals succeed at work.

select data from collection

vishali_vishu
on
5,044 Views
Last Modified: 2013-12-18
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 ?
Comment
Watch Question

Commented:
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;

Author

Commented:
sventhan:

It's good with the for loop

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

Commented:
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 KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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

Production Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 ??????

Author

Commented:
sorry please discard my previous error.....

syntax error.......

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.