Home » SQL & PL/SQL » SQL & PL/SQL » How to call in through pl/sql to get result (Oracle 9.2.0.1)
How to call in through pl/sql to get result [message #376152] Tue, 16 December 2008 05:32 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member
create or replace type array_1 is table of number;

create or replace procedure test_dummy( c1 OUT SYS_REFCURSOR)
    AS
         A1 array_1;
        begin
          select round((dbms_random.value(1,9))) 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(cast(a1 as array_1));
   end;
/

Now I want to fetch those values from the cursor and want to store in variable and print.How can I do that.

I tried this way in calling procedure to get result but failed.

DECLARE
 
  v2  array_1;
  c1 sys_refcusrosr;
BEGIN
  
  FETCH c1 BULK COLLECT INTO v2;
  
-- Here is where you process the elements in the collections
 
  FOR i IN v2.FIRST .. v2.LAST
    LOOP
                DBMS_OUTPUT.PUT_LINE( v2(i) );
       
    END LOOP;
END;
/


Re: How to call in through pl/sql to get result [message #376155 is a reply to message #376152] Tue, 16 December 2008 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't see you calling the procedure test_dummy at any point in your anonymous pl/sql block.

Also 'failed' is not an Oracle error message.
Re: How to call in through pl/sql to get result [message #376157 is a reply to message #376152] Tue, 16 December 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where do you call the procedure inside the PL/SQL block?

Also Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: How to call in through pl/sql to get result [message #376176 is a reply to message #376157] Tue, 16 December 2008 07:25 Go to previous message
avik2009
Messages: 61
Registered: November 2008
Member
I was been able to get result.

  1  DECLARE
  2     a       NUMBER;
  3     cv_c1  sys_refcursor;
  4  BEGIN
  5     test_dummy (cv_c1);
  6     LOOP
  7        FETCH cv_c1 INTO a;
  8        EXIT WHEN cv_c1%NOTFOUND;
  9        DBMS_OUTPUT.put_line (a);
 10     END LOOP;
 11     CLOSE cv_c1;
 12* END;


Previous Topic: user_tab_columns (merged)
Next Topic: Oracle default directory
Goto Forum:
  


Current Time: Sun Dec 04 12:24:53 CST 2016

Total time taken to generate the page: 0.08178 seconds