Ora-0604 [message #283524] |
Tue, 27 November 2007 07:22  |
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
from
freakabhi
|
|
|
|
Re: Ora-0604 [message #283531 is a reply to message #283524] |
Tue, 27 November 2007 07:29   |
 |
Littlefoot
Messages: 21823 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 #283533 is a reply to message #283524] |
Tue, 27 November 2007 07:30   |
martijn
Messages: 286 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 #283552 is a reply to message #283540] |
Tue, 27 November 2007 08:07   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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 #283730 is a reply to message #283524] |
Wed, 28 November 2007 01:04   |
flyboy
Messages: 1903 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 #283738 is a reply to message #283695] |
Wed, 28 November 2007 01:16  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The "similar code" must be a valid one.
As I said we must be able to reproduce it.
Regards
Michel
|
|
|