Home » SQL & PL/SQL » SQL & PL/SQL » Return array values to refcursor (oracle 9.2.0.3)
Return array values to refcursor [message #363376] Sat, 06 December 2008 09:18 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member
Is it possible to return values of an array to refcursor?
Re: Return array values to refcursor [message #363377 is a reply to message #363376] Sat, 06 December 2008 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to do it?

Regards
Michel
Re: Return array values to refcursor [message #363391 is a reply to message #363377] Sat, 06 December 2008 11:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, we will be happy to give you a difinitive answer.

But first you must put some effort into trying something. Post your code that you tried with. Make user you also post the sqlplus output that shows you ran your code. Then we will critique it, give ou pointers, send you in a direction, what ever it takes to get you where you want to be.

Kevin
Re: Return array values to refcursor [message #364162 is a reply to message #363391] Sun, 07 December 2008 11:56 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I tried this way..


We are using jsp as front end application. But finding problem while calling a procedurewith parameter as array type. The reason why we did try to use refcursor as out parameter to access values from array.

create or replace array_1 is table of number;
create or replace procedure test_refcursor( c1 OUT SYS_REFCURSOR)
AS
     A1 array_1;
    begin
      select (dbms_random.value(1,3)) bulk collect into A1
      from dual connect by level <= 10;
      for i in 1..A1.count loop
        dbms_output.put_line('i='||i||' A='||A1(i));
      end loop;
     dbms_output.put_line('-----------------');

--- 
OPEN C1 FOR
SELECT * FROM TABLE(A1);

end;
/



But when I execute it it returns me the error: ORA-22905: cannot access rows from a non-nested table item.

Pipeline can be other option.

Can someone tell me how to solve this in other way?

[Updated on: Sun, 07 December 2008 12:12]

Report message to a moderator

Re: Return array values to refcursor [message #364163 is a reply to message #364162] Sun, 07 December 2008 12:21 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 9.2 you have to cast the array in SQL query:
SQL> create or replace type array_1 is table of number;
  2  /

Type created.

SQL> create or replace procedure test_refcursor( c1 OUT SYS_REFCURSOR)
  2  AS
  3       A1 array_1;
  4      begin
  5        select (dbms_random.value(1,3)) bulk collect into A1
  6        from dual connect by level <= 10;
  7        for i in 1..A1.count loop
  8          dbms_output.put_line('i='||i||' A='||A1(i));
  9        end loop;
 10       dbms_output.put_line('-----------------');
 11  
 12  --- 
 13  OPEN C1 FOR
 14  SELECT * FROM TABLE(A1);
 15  
 16  end;
 17  /

Procedure created.

SQL> var c refcursor;
SQL> exec test_refcursor(:c)
i=1 A=2.9866724608056167375509057402815518194
i=2 A=2.33122816165882225971738795475255263452
i=3 A=1.40906737060024517619956521484555995678
i=4 A=1.51403892912153364589826290309470248474
i=5 A=1.7826190732415017600585063312402799879
i=6 A=2.2157851702554456198564397485469725811
i=7 A=2.76125622275721829173855360564684129884
i=8 A=2.52446652774847297700655494384465890062
i=9 A=1.24352825119343734758556848026324200372
i=10 A=2.54605755029806776634344981484746498288
-----------------
BEGIN test_refcursor(:c); END;

*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at "MICHEL.TEST_REFCURSOR", line 13
ORA-06512: at line 1


SQL> create or replace procedure test_refcursor( c1 OUT SYS_REFCURSOR)
  2  AS
  3       A1 array_1;
  4      begin
  5        select (dbms_random.value(1,3)) bulk collect into A1
  6        from dual connect by level <= 10;
  7        for i in 1..A1.count loop
  8          dbms_output.put_line('i='||i||' A='||A1(i));
  9        end loop;
 10       dbms_output.put_line('-----------------');
 11  
 12  --- 
 13  OPEN C1 FOR
 14  SELECT * FROM TABLE(cast(a1 as array_1));
 15  end;
 16  /

Procedure created.

SQL> exec test_refcursor(:c)
i=1 A=2.34918902271419539646174247635873956432
i=2 A=2.56750207716412271518557753846143685854
i=3 A=1.70284234254242920269887643840712379534
i=4 A=2.83594935702793625132163051743763205196
i=5 A=2.83824781360378159541867216098845904218
i=6 A=2.08201674804357009448143776223705370628
i=7 A=1.03136209556961946846782435277583053826
i=8 A=1.00235582967561294654214979449125114068
i=9 A=1.7792074042846073169094257121752646452
i=10 A=1.31971118608704167019571788611205213326
-----------------

PL/SQL procedure successfully completed.

SQL> print c
COLUMN_VALUE
------------
  2.34918902
  2.56750208
  1.70284234
  2.83594936
  2.83824781
  2.08201675
   1.0313621
  1.00235583
   1.7792074
  1.31971119

10 rows selected.

As you can see, with a precise question you have a precise answer.

Regards
Michel

[Updated on: Sun, 07 December 2008 12:23]

Report message to a moderator

Previous Topic: Converting a string to comma seperated value
Next Topic: Export rows with all dependent rows
Goto Forum:
  


Current Time: Sat Dec 10 08:50:14 CST 2016

Total time taken to generate the page: 0.15505 seconds