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.
/*************************
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
http://www.sybase.com/detail?id=44450
You will need to declare as REF CURSOR.
regards-