Link to home
Start Free TrialLog in
Avatar of smgillis
smgillis

asked on

Returning result sets from an oracle stored procedure

I have a procedure in oracle that looks something like the following:

/**********************************************/
Procedure p_result(o_result_set OUT cursor_type)
  AS
  BEGIN
    OPEN o_result_set FOR
      SELECT column
      FROM table;
   END;
END;
/***********************************************/

If i am in SQL Plus I would type the following to show the result set:

variable my_cursor refcursor
exec p_result(:my_cursor)
print my_cursor


I am looking for a method to get the resultset in powerbuilder.  Ideally i would like to create a datastore with the result set, however I would be happy with simply populating  a cursor using this procedure.   Any info would be greatly appreciated.  thanks.


Avatar of namasi_navaretnam
namasi_navaretnam
Flag of United States of America image

Please refer to this excellent doc:

http://www.sybase.com/detail?id=44450

You will need to declare as REF CURSOR.

regards-
ASKER CERTIFIED SOLUTION
Avatar of namasi_navaretnam
namasi_navaretnam
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gajender_99
gajender_99

hi

define your prodecure as a subroutine in Local external function of your local transaction of sqlca.

declare something like

subroutine rpf_p_result(ref  double my_cursor)RPCFUNC ALIAS FOR "~"P_RESULT~""


IN THE SCRIPT WHERE YOU WANT TO CALL THE PROCEDURE


double ll_rc

SQLCA.RPF_P_RESULT(ll_rc)

If SQLCA.SQLCode < 0 then    // generate error
    MessageBox("SQL error", SQLCA.SQLErrText)
    SQLCA.AutoCommit = FALSE
    return -1
Else
//what ever you would like to do
end if



thanks
Gajender