how to access an Oracle procedure's OUT parameter when calling it?

Question

If I write a simple function doSomething, I can get its result by executing :

select doSomething() from dual;

But, if I wish to call a procedure that has an OUT cursor being passed to it (along with another int parameter), how do I call that procedure inside a query and access the result of the cursor ?

Calling it inside a query is not compulsory.. its just that I want to access the results of that procedure

You want to execute the procedure to see what is the result? What does your procedure returns? Some kind of ref_cursor? — Jacob, Nov 12 '12 at 07:53
@Polppan it returns a custom cursor with multiple columns (all varchar type) — Daud, Nov 12 '12 at 07:57

Jacob · Accepted Answer · 2012-11-12T11:35:08.160

1

You can create a procedure like

    CREATE OR REPLACE PROCEDURE your_procedure(out_cursor OUT sys_refcursor)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT  employee_name
              FROM  employees;

    END;
    /

Once you create your procedure wrap the procedure in a function which returns a cursor like the following

CREATE OR REPLACE FUNCTION your_function
    RETURN sys_refcursor
AS
    o_param  sys_refcursor;
BEGIN
    o_param := NULL;
    your_procedure(o_param);
    RETURN o_param;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        -- raise                                  

    WHEN OTHERS
    THEN
        -- raise
END your_function;
/

To see the results from sql do as

select your_function from dual;

Update 1

To see result in SQL Developer

Step 1

Double click on your results in SQL Developer

[Results][1]

Step 2 Single Click on the button with dots. That will pop up the values

[Grid][2]

edited Nov 12 '12 at 11:35

answered Nov 12 '12 at 08:07

Jacob

14,463
65
207
320

Thanks.. but it prints out data (incomplete) in a single row.. like "{,, – Daud Nov 12 '12 at 08:36
If you edit your question and post your your code that would help in debugging more. When I executed in sql plus it executes row by row. – Jacob Nov 12 '12 at 08:39
I just copy pasted you exact code...though I am selecting first_name, last_name from the employees table.. I am using SqlDeveloper.. – Daud Nov 12 '12 at 08:43
I executed in sqlplus and TOAD, results showed in rows with emp_number and emp_name. Try in sqlplus how it works. – Jacob Nov 12 '12 at 08:50
@Daud When you execute in SQLDeveloper double click or single click on results grid with button with `three dots ...` It will pop up a screen where you could see the results in a data grid. – Jacob Nov 12 '12 at 09:35
I am sorry, but I can't find the button with 3 dots.. the number of rows being returned is also 1. The oracle server is on another machine and I can't ssh into that.. – Daud Nov 12 '12 at 10:29
@Daud I have updated my answer. I have mentioned how you could view the results in SQL Developer. – Jacob Nov 12 '12 at 10:41
Thanks a lot for that.. but this is different from my view of SqlDeveloper (3.0.04). Please see http://tinypic.com/r/kd0i1y/6 – Daud Nov 12 '12 at 10:48
@Daud let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19429/discussion-between-polppan-and-daud) – Jacob Nov 12 '12 at 10:54

score 0 · Answer 2 · answered Nov 12 '12 at 07:47

0

You can Do Something Like This

select doSomething(cursor (select int_col from your_table)) colname from dual

Hope this Help

answered Nov 12 '12 at 07:47

user1819920

2,078
2
16
14

the OUT variable has to be populated inside the procedure.. and I wish to access all the fields of the cursor.. shouldn't we be passing an empty cursor ? – Daud Nov 12 '12 at 07:49

how to access an Oracle procedure's OUT parameter when calling it?

2 Answers2

Linked