Pl Sql Invalid Cursor Error
You save me from a big problem. Senior Member The two semi-colons thing is a workround you need in SQL Developer if you try to create procedures in a SQL Editor window. Additionally, if you define your own weak ref cursor type like this:create or replace package pkg_types as type rc is ref cursor; end; /and use that insteadof sys_refcursor, then things work. But rather than blindly start tweaking parameters, it's time to look at what the user's session is actually doing right up until the point of failure.
Teaching a blind student MATLAB programming What does the image on the back of the LotR discs represent? I've got 10.2.0.3 up and running, and I get the same error. If you or your DBAs have access to Oracle Support then you can look the bug number up. –Alex Poole Jul 5 '12 at 12:37 Ok, thank you very
If you try to close a cursor which is already closed, you get the error message. I think I have interpretted the documentation incorrectly. Why would breathing pure oxygen be a bad idea? Hot Network Questions Why not to cut into the meat when scoring duck breasts?
Longest "De Bruijn phrase" in English Text above line in TikZ probability tree What is a tire speed rating and is it important that the speed rating matches on both axles? How do I replace and (&&) in a for loop? any idea? -- PROC1 CREATE OR REPLACE PROCEDURE TEST_PROC1 ( oCur OUT SYS_REFCURSOR ) AS BEGIN OPEN oCur FOR SELECT entity_id FROM entity; END TEST_PROC1; -- PROC2 CREATE OR REPLACE PROCEDURE http://stackoverflow.com/questions/11341166/strange-error-ora-01001-invalid-cursor-in-procedure Is there any chance to see this bug report and description? –Akhmed Kharaev Jul 5 '12 at 12:16 The T&Cs don't allow me to reproduce information from Oracle Support
But don't ask me about this stuff; I'm a DBA, not a developer, remember?) Well, there was no chance of throwing this problem back on the developers and saying "it's your Initialize the ref cursor to a non-NULL value at the highest level at which it will be accessed begin /* Dummy open to avoid bug 7174888 */ open v_cur for 'select A common solution is to jack up the value of MAXOPENCURSORS. (Note that this is not an Oracle parameter as some people seem to think. All rights reserved. Search BC Oracle Sites HomeE-mail Us Oracle Articles New Oracle Articles Oracle TrainingOracle Tips Oracle ForumClass Catalog Remote DBAOracle TuningEmergency 911RAC SupportApps
To me, the real issue is in depending on IS_OPEN to determine if a cursor is valid or not. Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358486 is a reply to message #358474] Tue, 11 November 2008 09:44 S.Rajaram Messages: 1027Registered: Creating a procedure whos responsibility is to just OPEN a cursor is a bit strange to me (but I'm sure there was some reason), and can cause hard to explain issues The workaround in the bug report cures both the fetch and close problem.
They amy have a workaround. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7106 Regards Raj Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358474 is a reply to message #358465] Tue, 11 November 2008 08:57 SQL> exec pr_sal;RupalHeroJainJohnRijuSamKiranPeterKingRoshanRoshanDavidBEGIN pr_sal; END; *ERROR at line 1:ORA-01001: invalid cursorORA-06512: at "SCOTT.PR_SAL", line 7ORA-06512: at line 1 SQL> create or replace procedure pr_sal iscursor c1 is select emp_name from emp;begin Specify a correct LDA area or open the cursor as required.
Option #2 Make sure you've OPENed the cursor before calling a FETCH cursor or CLOSE cursor. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of That can only mean… Connection pool! have a peek at these guys Resolution The option(s) to resolve this Oracle error are: Option #1 Make sure you haven't CLOSEd the cursor and are still referencing it in your code.
Why was no new session created? Just wanted to add a few things. I guess that this is one of those things you're just supposed to "know".
Regards Michel Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358471 is a reply to message #358465] Tue, 11 November 2008 08:54 S.Rajaram
In this case you don't need to specify explicit close for the cursor. Oracle version: Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production PL/SQL Release 126.96.36.199.0 - Production CORE 188.8.131.52.0 Production TNS for Solaris: Version 184.108.40.206.0 - Production NLSRTL Version 220.127.116.11.0 - That solved problem. Make sure that you do not have a misplaced CLOSE statement.
Add a comment Name: Email: URL: Chars left:1000 (1000 max) (No HTML, but newlines will be preserved) Home : Code Library : Sponsors : Privacy : I have seen many presentations about how the "modern" DBA has to be intimately concerned with all layers of the application stack and cannot just focus on the database. There haven't been any comments added for this error yet. Just my ramblings ;-) share|improve this answer answered Jul 5 '12 at 16:42 tbone 9,44911729 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign
Execution failed on statement if v_cursor%isopen then close v_cursor; -- here was an error end if; After some digging into I discovered that problem was in subprogram that opened this cursor. The test for ISOPEN, however, would still return TRUE, but then trying to use the cursor (fetch) would fail. If you must have another program open a cursor for you, then you might want to enclose the code that fetches and eventually closes the cursor in an anonymous block and This usually means that you have either forgotten to code an open statement before using a cursor, or have not noticed that the cursor has been closed and have tried to
If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF. Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358776 is a reply to message #358678] Wed, 12 November 2008 08:15 harishmk Messages: 7Registered: Mostly, you will have either forgotten to code an open statement before using a cursor, or have not noticed that the cursor has been closed and have tried to continue using Make sure that lines of code do not exceed 80 characters when you format.
How Aggregate Result are count against the Governor Limits? Derivatives: simplifying "d" of a number without being over "dx" Select Only Printed Out Cells Why don't cameras offer more than 3 colour channels? (Or do they?) Can an irreducible representation