Member_2_2484401
asked on
Stored procedure with one output parm and a result-set
Greetings, experts!
I apologize for so many "rookie questions”. I’m finding it challenging to transfer my DB2 experience into Oracle, but I'm getting closer.
I wrote a stored procedure in Oracle 11g that returns two output parameters, and that works beautifully.
output:
theId = 8
theDesc = V15
Unfortunately, the client developer is having a problem dealing with the varchar2 output parameter, so he asked that I change that second parameter into a one-record result-set. I successfully changed the stored proc, but I’m having a hard time testing it.
The new stored proc header looks like this:
When I try to call the new proc, I get an error:
output:
ORA-06550: line 2, column 66:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
I realize this is probably mind-numbingly easy, but what am I doing wrong?
Thanks!
DaveSlash
I apologize for so many "rookie questions”. I’m finding it challenging to transfer my DB2 experience into Oracle, but I'm getting closer.
I wrote a stored procedure in Oracle 11g that returns two output parameters, and that works beautifully.
var theId number;
var theDesc varchar2;
exec MySchema.GetNextVersion (:theId, :theDesc);
begin
dbms_output.put_line(theId = ' || :theId);
dbms_output.put_line('thedesc = ' || :theDesc);
end;
output:
theId = 8
theDesc = V15
Unfortunately, the client developer is having a problem dealing with the varchar2 output parameter, so he asked that I change that second parameter into a one-record result-set. I successfully changed the stored proc, but I’m having a hard time testing it.
The new stored proc header looks like this:
CREATE OR REPLACE PROCEDURE MySchema.GetNextVersionWithResultset (
pVersionId OUT NUMBER,
mycursor OUT SYS_REFCURSOR)
IS
...
When I try to call the new proc, I get an error:
var theId number;
DECLARE MYCURSOR SYS_REFCURSOR;
exec MySchema.GetNextVersionWithResultset (:theId, :MYCURSOR);
output:
ORA-06550: line 2, column 66:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
I realize this is probably mind-numbingly easy, but what am I doing wrong?
Thanks!
DaveSlash
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked perfectly.
Thanks!
DaveSlash