Solved

Cursor problem when running Oracle stored procedure in TOAD

Posted on 2004-10-08
4
4,499 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 50 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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
select only modified column name  based on the status 15 47
dates - loop 12 57
Schema creation in Oracle12c 6 39
case statement in where clause with not exist 15 46
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 …
Via a live example, show how to take different types of Oracle backups using RMAN.
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

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now