Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 during Procedure execution
ORA-01722 during Procedure execution [message #256636] Mon, 06 August 2007 03:23 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

There is a procedure in my database(Ora1),It's getting compiled
successfully,when i execute it,it gives the following error:

begin prepay('Y','601','Y','G','01/APR/2007','30/APR/2007','601'); end;

ORA-01722: invalid number
ORA-06512: at "PREPAY", line 466
ORA-06512: at line 1


There is another database(Ora2) in the same server,I created this procedue
in this database & tried to execute it.In this database(Ora2),this procedure
executed successfully.

Why this procedure execution gives error in database "Ora1",why it's not
giving error in database "Ora2"?

Kindly give ur suggesstion to rectify this error so that this procedure can
be executed successfully in database "Ora1".

Both the databases are running under same version(9.2.0.8 )

Thanks...

[Updated on: Mon, 06 August 2007 03:24]

Report message to a moderator

Re: ORA-01722 during Procedure execution [message #256641 is a reply to message #256636] Mon, 06 August 2007 03:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
My crystal ball is broken, what's on "Prepay" line 466?
Re: ORA-01722 during Procedure execution [message #256646 is a reply to message #256636] Mon, 06 August 2007 04:16 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi Thomas,

The line 466 contains,
FETCH Report_Cur
INTO Loan_Rec;

Where Report_Cur is REF CURSOR & Loan_Rec is RECORD.

But why the same procedure is executing successfully in database "Ora2" & not in "Ora1"?

What could be the reason for this?
Re: ORA-01722 during Procedure execution [message #256648 is a reply to message #256646] Mon, 06 August 2007 04:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
My guess at the moment is that the procedure is doing implicit date conversions (which is bad), and the date format on both databases is different, so it throws an error.

( Which is WHY implicit date conversions are bad. )

what's the definition of the Report_Cur cursor?
Re: ORA-01722 during Procedure execution [message #256669 is a reply to message #256636] Mon, 06 August 2007 05:18 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member

Hi Thomas,

This is definition of Ref_Cur Cursor...

select e.no AGREEMENTNO,e.dtdate disb_date,
decode(STATUS, 'F', 'Closure', 'L', 'Live', 'M', 'Maturity') agr_status,
f.dtupdated , nvl(f.famt,0) for_loss
FROM master e, TEMPSEC TS,
foredetails f
WHERE TS.TNO = e.NO and
TS.SZ IN ('601') AND
e.no = f.no(+) and
((STATUS = 'F' AND
e.no in (select fd.no from foredetails fd where
fd.no = e.no and fd.szclosure<>'D')) or
(e.STATUS = 'L' AND
e.no in (select r.no from rse_trail r where
r.cstructure = 'P' and r.no= e.no)))
Re: ORA-01722 during Procedure execution [message #256671 is a reply to message #256669] Mon, 06 August 2007 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is definition of Ref_Cur Cursor...

select e.no AGREEMENTNO,e.dtdate disb_date,
decode(STATUS, 'F', 'Closure', 'L', 'Live', 'M', 'Maturity') agr_status,
f.dtupdated , nvl(f.famt,0) for_loss
FROM master e, TEMPSEC TS,
foredetails f
WHERE TS.TNO = e.NO and
TS.SZ IN ('601') AND
e.no = f.no(+) and
((STATUS = 'F' AND
e.no in (select fd.no from foredetails fd where
fd.no = e.no and fd.szclosure<>'D')) or
(e.STATUS = 'L' AND
e.no in (select r.no from rse_trail r where
r.cstructure = 'P' and r.no= e.no)))

Quote:
The line 466 contains,
FETCH Report_Cur
INTO Loan_Rec;

I stop there.
Your cursor returns N>1 values.
You statement has only one output variable.

Regards
Michel
Re: ORA-01722 during Procedure execution [message #256678 is a reply to message #256671] Mon, 06 August 2007 05:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Post the FORMATTED complete procedure.

And the FORMATTED output of :
SELECT NAME, value FROM v$parameter WHERE NAME LIKE '%nls%';

from both databases.
Re: ORA-01722 during Procedure execution [message #256868 is a reply to message #256636] Mon, 06 August 2007 23:31 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Thanks All...

This problem is occurring not only during particular procedure execution,We're getting this error almost for all procedures during its execution.

These procedures were executing successfully last week,but now
only it's giving problem.

These two errors are occurring during procedure exec,

Error-1
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "CW.INTRATE", line 285
ORA-06512: at line 1


Error-2
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "CW.INFORMATION", line 460
ORA-06512: at line 1


I doubt that some corruption has occurred in the database.
How to fix this problem so that procedures will execute successfully?

We're getting these errors not during procedure compilation but only during execution.

Thanks...


Re: ORA-01722 during Procedure execution [message #256880 is a reply to message #256868] Tue, 07 August 2007 00:16 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't provide what has been asked.
You didn't follow what has been asked.
You're On Your Own (YOYO).

Regards
Michel
Previous Topic: user created function taking more time
Next Topic: how to dump oracle database?
Goto Forum:
  


Current Time: Thu Dec 08 22:09:54 CST 2016

Total time taken to generate the page: 0.10580 seconds