?
Solved

Cursor problem when running Oracle stored procedure in TOAD

Posted on 2004-10-08
4
Medium Priority
?
4,893 Views
Last Modified: 2012-05-05
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
Comment
Question by:andjesdav
4 Comments
 
LVL 9

Assisted Solution

by:konektor
konektor earned 200 total points
ID: 12259542
u should :
DECLARE
  TYPE tRefCur IS REF CURSOR;
  RetVal tRefCur;
...
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12259579
I think it should be:

TYPE CURSORTYPE IS SYSREFCURSOR;

then declare:

RetVal CURSORTYPE;
0
 
LVL 15

Accepted Solution

by:
andrewst earned 200 total points
ID: 12259706
In 9i, just use:

Retval SYS_REFCURSOR;

SYS_REFCURSOR is a pre-declared REF CURSOR type.
0
 

Author Comment

by:andjesdav
ID: 12260203
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month3 days, 22 hours left to enroll

599 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