Home » SQL & PL/SQL » SQL & PL/SQL » Ora-0604
Ora-0604 [message #283524] Tue, 27 November 2007 07:22 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi all...

I got problem with my code.........

in fact a procedure is been made....
its work fine for all parameters but for
certain set of params it gives up

ora-0604 message
followwd by ora-01003

what could be the cause......please
help , I am badly stuck
Sad

from
freakabhi
Re: Ora-0604 [message #283529 is a reply to message #283524] Tue, 27 November 2007 07:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Impossible to answer this question with any accuracy based on the complete lack of any pertinent information.
READ AND FOLLOW
Re: Ora-0604 [message #283531 is a reply to message #283524] Tue, 27 November 2007 07:29 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says:
Quote:

ORA-01604: illegal number range in clause "string" of string

Cause: The number range was invalid for this parameter.

Action: Correct the INIT.ORA parameter and restart the instance.

Quote:

ORA-01003 no statement parsed

Cause: A host language program call referenced a cursor with no associated parsed SQL statement. A SQL call (for example, OSQL3) must be used to pass a SQL statement to Oracle and to associate the statement with an open cursor. A cursor must already have an associated SQL statement if referenced in any of the following calls: DESCRIBE, NAME, DEFINE, BIND, EXECUTE, and FETCH.

Action: Do the SQL call, for example, OSQL, to pass the required SQL statement before referencing the cursor.

Could you show us the code, or even build a test case?
Re: Ora-0604 [message #283532 is a reply to message #283524] Tue, 27 November 2007 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00604: error occurred at recursive SQL level %s
 *Cause:  An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
 *Action: If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

ORA-01003: no statement parsed

Without your code that's the only thing we can say.

Regards
Michel
Re: Ora-0604 [message #283533 is a reply to message #283524] Tue, 27 November 2007 07:30 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Only thing I can say (based on the supplied information):

Quote:


/share/scripts/oracle/tmp> oerr ora 604
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.
cwvabs00 [????] /share/scripts/oracle/tmp> oerr ora 1003
01003, 00000, "no statement parsed"
// *Cause:
// *Action:

Re: Ora-0604 [message #283537 is a reply to message #283533] Tue, 27 November 2007 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, I think that he now knows what we can say with what he posted. Wink

Regards
Michel
Re: Ora-0604 [message #283540 is a reply to message #283537] Tue, 27 November 2007 07:44 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
When I posted I indeed saw that I was not the first, and that I added to the overkill. Cool

<grin>
To my (our) defense : all answers are given whitin 3 minutes timeframe. I (we) started before the other replies were posted.
</grin>
Re: Ora-0604 [message #283552 is a reply to message #283540] Tue, 27 November 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was not a criticism, I also see the timestamp, it was just to emphasize that we all have the same idea.

Regards
Michel

[Edit: missing word]

[Updated on: Wed, 28 November 2007 00:11]

Report message to a moderator

Re: Ora-0604 [message #283673 is a reply to message #283552] Tue, 27 November 2007 22:59 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi ,

I am sorry for being supplied with this much less information..
as code is confidential but anyways....

Proc ABC( cur ref_cursor, source varchar2,  date varchar2)
As
CUR IS REF CURSOR;
Query varchar2;
Begin

If (source = ‘N’)
Then Query = ‘SELECT * from
[b]N_SOURCE[/b]
Where date = ‘date’;
Elseif  (source = ‘M’)

Then Query = ‘SELECT * from
[b]M_SOURCE[/b]
Where date = ‘date’;
Else
        Query =SELECT from
[b]N_SOURCE[/b]
Where date = ‘date’
UNION ALL
SELECT * from
[b]M_SOURCE[/b]
Where date = ‘date’;
END IF;

OPEN CUR FOR QUERY;

End ABC;


Now this is dummy code...

Proc runs fine for
ABC( cur , 'N',  date ); 
or ABC( cur , 'M',  date );
but when used with

ABC( cur , 'Both',  date );

it gives up
the message... ORA-0604 followed by ORA-01003
now I suppose...this should clear the picture....

Please help



[Edited by Taj: add code tags and remove IM speak]

[Updated on: Tue, 27 November 2007 23:06] by Moderator

Report message to a moderator

Re: Ora-0604 [message #283676 is a reply to message #283524] Tue, 27 November 2007 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

I am sorry for not posting a complete answer, but it is confidential.
Re: Ora-0604 [message #283695 is a reply to message #283673] Wed, 28 November 2007 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can't post the full code then post a similar one with which you and we can reproduce the error.
There what you posted is useless.

Regards
Michel
Re: Ora-0604 [message #283730 is a reply to message #283524] Wed, 28 November 2007 01:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
After formatting the code, the error in the third SELECT statement is visible (missing expression SELECT clause), although it is hard to say, whether it is so in original code, as the single quotes are messed.
If the code flow is similar, concentrate on the SQL statement called for given parameter. Alternatively change dynamic SQL to static SQL, opening the cursor for each query separately (then you will see the error at compile time).
Re: Ora-0604 [message #283737 is a reply to message #283676] Wed, 28 November 2007 01:15 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
anacedent

I am sorry for not posting a complete answer, but it is confidential.

Read: If I tell you that, I'll have to kill you.
Re: Ora-0604 [message #283738 is a reply to message #283695] Wed, 28 November 2007 01:16 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The "similar code" must be a valid one.
As I said we must be able to reproduce it.

Regards
Michel
Previous Topic: Selecting unlocked rows?
Next Topic: dividing time when two hour gap found
Goto Forum:
  


Current Time: Mon Dec 05 07:10:47 CST 2016

Total time taken to generate the page: 0.14797 seconds