[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Cursor problem when running Oracle stored procedure in TOAD

Why do I get this error when running (RT Click=>Execute Procedure) a stored procedure in Toad?
Your help is much appreciated, as I toil from my humble abode.

ORA-06550: line 2, column 14:
PLS-00201: identifier 'CURSOR' must be declared
ORA-06550: line 2, column 10:
PL/SQL: Item ignored

The spec, where Cursortype is defiend, is separate from the procedure.  Do I need to define the type in the Begin code in the Toad dialog box?

CREATE OR REPLACE package Pkg_Sales As
  TYPE CURSORTYPE IS REF CURSOR;

Function A_Summary(ParmEndDate char, ParmTimePeriod char, ParmBusinessUnit char,
             ParmEntity char) return Cursortype;
...other functions defined in .sps...

...spb code:...
Function A_Summary(ParmEndDate char, ParmTimePeriod char, ParmBusinessUnit char, ParmEntity char) return Cursortype is
     Result_Set Cursortype;

Generated Toad Code:
DECLARE
   
  RetVal REF CURSOR;
  PARMENDDATE CHAR(200);
  PARMTIMEPERIOD CHAR(200);
  PARMBUSINESSUNIT CHAR(200);
  PARMENTITY CHAR(200);

BEGIN
  PARMENDDATE := '2004-02-29';
  PARMTIMEPERIOD := 'M';
  PARMBUSINESSUNIT := 'Retail IC';
  PARMENTITY := NULL;

  RetVal := EI.PKG_SALES.A_SUMMARY ( Parms...)
0
andjesdav
Asked:
andjesdav
2 Solutions
 
konektorCommented:
u should :
DECLARE
  TYPE tRefCur IS REF CURSOR;
  RetVal tRefCur;
...
0
 
seazodiacCommented:
I think it should be:

TYPE CURSORTYPE IS SYSREFCURSOR;

then declare:

RetVal CURSORTYPE;
0
 
andrewstCommented:
In 9i, just use:

Retval SYS_REFCURSOR;

SYS_REFCURSOR is a pre-declared REF CURSOR type.
0
 
andjesdavAuthor Commented:
Thanks!  Konektor's worked and was first, Andrewst's worked, too, and is shorter.
Now I have to find out how to see the results, but I think that's already been asked, so I'll look it up.
Ribbeting.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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