Home » SQL & PL/SQL » SQL & PL/SQL » ora-01001 invalid cursor (Forms Version 10.1.2.0.2, Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)
ora-01001 invalid cursor [message #393514] Mon, 23 March 2009 03:19 Go to next message
kbeeharry
Messages: 4
Registered: March 2009
Junior Member
Hi All Users,

Below is the code listing for a stored procedure. I'm calling it from the trigger WHEN-VALIDATE-ITEM TO GET THE CURSOR TO FETCH DATA. IF I OPEM THE CURSOR WITH A STRING (sSqlStmt),
Oracle raises the error ora-01001 invalid cursor when fetching data in the WHEN-VALIDATE-ITEM trigger. Else if I open the cursor like in the commented part, it works fine. Please can anyone tell me why when calling OPEN WITH A SELECT IN A STRING VARIALBE IT DOES NOT WORK.

THX.

PACKAGE BODY ProcSqlStmt IS
PROCEDURE OpenCursor(p_SqlStmt IN VARCHAR2, p_SqlCursor IN OUT SYS_REFCURSOR, p_sStr OUT VARCHAR2) IS
BEGIN
-- OPEN p_SqlCursor FOR SELECT REG_CODE, REG_DESC
-- FROM REGIMES
-- WHERE REG_CODE LIKE 'AA%' AND
-- TRUNC(SYSDATE) BETWEEN TRUNC(REG_DT_FROM) AND TRUNC(REG_DT_TO);
OPEN p_SqlCursor FOR sSqlStmt;
IF p_SqlCursor%ISOPEN THEN
p_sStr := 'CURSOR OPEN';
ELSE
p_sStr := 'CURSOR NOT OPEN';
END IF;
END OpenCursor;
END ProcSqlStmt;
Re: ora-01001 invalid cursor [message #393515 is a reply to message #393514] Mon, 23 March 2009 03:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There is no sSqlStmt declared/defined in your code
Re: ora-01001 invalid cursor [message #393516 is a reply to message #393514] Mon, 23 March 2009 03:38 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
try

CREATE OR REPLACE PACKAGE ProcSqlStmt AS

[B]type refc is ref cursor;[/B]

PROCEDURE OpenCursor(p_SqlStmt IN VARCHAR2, p_SqlCursor IN OUT [B]refc[/B], p_sStr OUT VARCHAR2);
END mypkg;
/


PACKAGE BODY ProcSqlStmt IS
PROCEDURE OpenCursor(p_SqlStmt IN VARCHAR2, p_SqlCursor IN OUT [B]refc[/B], p_sStr OUT VARCHAR2) IS
.....
Re: ora-01001 invalid cursor [message #393517 is a reply to message #393514] Mon, 23 March 2009 03:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Your code has a typo in it. You use 'sSqlStmt' but you declare it nowhere. You probably meant 'p_SqlStmt'.

And where do you close your cursor?

MHE
Re: ora-01001 invalid cursor [message #393530 is a reply to message #393514] Mon, 23 March 2009 04:15 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before your next questions please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Creating Temporary tables on fly
Next Topic: Effective delete Syntax
Goto Forum:
  


Current Time: Tue Dec 06 16:23:20 CST 2016

Total time taken to generate the page: 0.17343 seconds