We help IT Professionals succeed at work.

Convert table type result into ref cursor

cplusplus030999
on
4,838 Views
Last Modified: 2013-12-19
I have defined a variable which is TABLE TYPE of variable. This variable holds all of results which is of course like a table. Now I want to write store procedue to pull out the results from this variable. Then I want to define ref cursor which holds the results from this variable. Then I will pass OUT the ref cursor. Any idea or suggestions?

This variable defined like:
TYPE cust_tab  IS TABLE OF cust_rec INDEX BY BINARY_INTEGER;
cust_variable cust_tab

Thanks in advance.
Comment
Watch Question

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
try exploring with CURSOR and TABLE operators. Look into the documentation of CURSOR and TABLE operators in the pl/sql oracle online documentation.

I don't have the sample code with me. But i think it is very much possible.

Thanks
SujithData Architect
CERTIFIED EXPERT

Commented:
Its possible - but:
 - you cannot have the declaration of cust_tab inside your package/procedure.
 - cust_tab and cust_rec should be created as types (using the CREATE TYPE command)
 - cust_tab cannot be an index by table, because you cannot created types using the INDEX BY syntax.(Index by tables are pl/sql elements called associative arrays)


See the following simple example how to do it:

create or replace type test_typ as object(
 id number,
 val varchar2(20))
/
create or replace type test_tbl_typ as table of test_typ
/

declare
 l_tbl test_tbl_typ := test_tbl_typ();
begin
 l_tbl.extend(2);
 l_tbl(1) := test_typ(10, 'ten');
 l_tbl(2) := test_typ(20, 'two');

 for rec in (select id, val from table(cast(l_tbl as test_tbl_typ)) ) loop
  dbms_output.put_line(rec.id||' -> '||rec.val);
 end loop;
end;
/
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.