Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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.

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
Dave Ford
Asked:
Dave Ford
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
You nailed it, slightwv.

That worked perfectly.

Thanks!
DaveSlash
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now