Accessing Constants from a package [message #439653] |
Mon, 18 January 2010 23:55  |
sanyadu
Messages: 30 Registered: October 2006 Location: Chicago
|
Member |
|
|
Hi,
I am trying to execute a procedure which brings currency information in ref cursor.
The procedure is having below sql
OPEN o_rc_cursor for
SELEC * from MASTER_TAB
WHERE TYPE = pkg_constant.c_currency;
Now the value of pkg_constant.c_currency is nothing but 'CURRENCY', I have created a global constant package for storing these constant values.
Uptil yesterday it was running fine, but now i am getting below error :-
Error: ORA-20000: PKG_MASTER_DATA.PROC_GET_CURRENCY ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1, Batch 1 Line 1 Col 1
I have tried every resoluton related to the error i got.
Also when i run the same in an anonymous block as below
declare
v_status varchar2(100);
begin
dbms_output.put_line(pkg_constants.c_currency);
v_status := pkg_constants.c_currency;
dbms_output.put_line(v_status);
end;
/
Sometimes it is running fine and give me the desired output, but sometimes it fails and give the same error.
I have tried recreating the package and all related objects but still got the same error, However the code is running perfectly in other environment.
Also restarting the database gives no luck. As per the DBA's, check up from database and server, memory and CPU looks normal. There is no over usage of memory.
My Development got stuck because of this .
Please Help me in resolving this issue,
Thanks in Advance
Sandeep
|
|
|
|
|
Re: Accessing Constants from a package [message #439661 is a reply to message #439658] |
Tue, 19 January 2010 00:44   |
sanyadu
Messages: 30 Registered: October 2006 Location: Chicago
|
Member |
|
|
Also i have noticed one thing,
If i run it for the first time in the session , it gives me the run time error, but if i execute it again in the same session,it executes successfully but did not give the desired output.
|
|
|
|
|
Re: Accessing Constants from a package [message #439667 is a reply to message #439661] |
Tue, 19 January 2010 00:54  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
sanyadu wrote on Tue, 19 January 2010 07:44Also i have noticed one thing,
If i run it for the first time in the session , it gives me the run time error, but if i execute it again in the same session,it executes successfully but did not give the desired output.
Without the code who knows what can happen? And where.
As I said, remove the WHEN OTHERS and copy and paste the WHOLE
SQL*Plus from connection till you get the error.
And post the code.
Regards
Michel
|
|
|