CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
user1970875
user1970875

Reputation: 1

Stored procedures and their output

Client that calls a stored procedure can handle only recordset output only.

However store procedure is supposed to return simple 0 or 1 value as a result of several internal queries.

Is there any work around I could have a store procedure variable value returned as a kind a recordset?

Upvotes: 0

Views: 178

Answers (3)

codingbiz
codingbiz

Reputation: 26396

From your PROCEDURE do

OPEN resultsCursor_ FOR
Select 1 As aValue FROM DUAL;

OR

CREATE OR REPLACE PROCEDURE GetAValue
(
  results_       OUT     SYS_REFCURSOR
)
IS
    MY_COUNT_  INT;
BEGIN 

    MY_COUNT_ := 10;

    OPEN results_ FOR
    SELECT MY_COUNT_ AS MyCount FROM DUAL;

END GetAValue;

Upvotes: 1

A B
A B

Reputation: 4158

A procedure cannot return a value; you need a function to do this.

If you really want a procedure to "give" a cursor back to the caller, you can use something like this:

 --Declare this type in a package X
 --The caller must have access to the package X.
 TYPE ref_cursor IS REF CURSOR;
 CREATE OR REPLACE
 PROCEDURE test (
                   p_param1           IN  VARCHAR2,
                   p_cur              OUT X.REF_CURSOR,
                   p_error_code       OUT NUMBER,
                   p_error_message    OUT VARCHAR2
                 )
 AS
 BEGIN
      OPEN p_cur FOR
        SELECT * FROM TABLE;
 EXCEPTION
 WHEN OTHERS THEN

     p_error_code := SQLCODE;
     p_error_message := SQLERRM;

 END;

If there is no error set, the caller can then execute this cursor and fetch rows against it.

Upvotes: 0

antlersoft
antlersoft

Reputation: 14751

Return a cursor variable; open the cursor variable with a SELECT .. FROM DUAL of the values you want to return from the procedure.

The procedure will return a cursor returning a single row containing those values.

Upvotes: 1

Related Questions