CODEWITHSUNDEEP
oracle-databasestored-procedures
Sathish Kumar
Sathish Kumar

Reputation: 11

Output array in Oracle stored procedure

I'm trying to get result of a table "empDetails" using simple stored proc in Oracle SQL developer. Below is the SP definition

create or replace
PROCEDURE TEST_ALL (all_Cursor OUT SYS_REFCURSOR ) AS 
BEGIN
open all_Cursor for
    select * from empDetails;
END TEST_ALL;

when I execute the stored proc, I cannot able to view the output in Output result tab. But I can fetch the data through direct SQL query select * from empDetails;

Any clue ?

Upvotes: 1

Views: 1087

Answers (2)

Boneist
Boneist

Reputation: 23578

An alternative is to use the following:

VARIABLE rc REFCURSOR;

begin
  test_all(:rc);
end;
/

PRINT rc;

which works in SQL*Plus and in most other GUIs that allow you to run the above as a script (e.g. SQL Developer, Toad, etc)

Upvotes: 1

XING
XING

Reputation: 9886

Use this way:

 declare
        a  SYS_REFCURSOR;

        v_emp_detls  empDetails%rowtype;

        begin

         TEST_ALL (all_Cursor=>a );

          loop
            FETCH a INTO v_emp_detls;
                EXIT WHEN a%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_emp_detls.col1||v_emp_detls.col2..and so on);

          end loop;
  end;

Upvotes: 2

Related Questions