Solved

Stored procedure with one output parm and a result-set

Posted on 2014-10-15
2
319 Views
Last Modified: 2014-10-15
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.

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;

Open in new window


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
...

Open in new window


When I try to call the new proc, I get an error:

var theId number;
DECLARE  MYCURSOR SYS_REFCURSOR; 
exec MySchema.GetNextVersionWithResultset (:theId, :MYCURSOR);

Open in new window


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
0
Comment
Question by:daveslash
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40382706
Using sqlplus try this:
var theId number;
var MYCURSOR REFCURSOR;
exec MySchema.GetNextVersionWithResultset (:theId, :MYCURSOR);
print mycursor

declare is a pl/sql block designation.  you need a complete block if you go down that path.
0
 
LVL 18

Author Closing Comment

by:daveslash
ID: 40382730
You nailed it, slightwv.

That worked perfectly.

Thanks!
DaveSlash
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question