CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Daud
Daud

Reputation: 7877

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

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

Upvotes: 1

Views: 1882

Answers (2)

Jacob
Jacob

Reputation: 14731

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]

Upvotes: 1

user1819920
user1819920

Reputation: 2248

You can Do Something Like This

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

Hope this Help

Upvotes: 0

Related Questions