Home » SQL & PL/SQL » SQL & PL/SQL » Accessing Constants from a package (Oracle 10g)
Accessing Constants from a package [message #439653] Mon, 18 January 2010 23:55 Go to next message
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 #439656 is a reply to message #439653] Tue, 19 January 2010 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First remove/comment the WHEN OTHERS clauses (all of them in your procedure and the ones it calls recursively) and reexecute then you will know where the error comes from, maybe it is NOT where you think it happens.

Regards
Michel
Re: Accessing Constants from a package [message #439658 is a reply to message #439656] Tue, 19 January 2010 00:09 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
I remove the exception part, but as i said that the procedure is only having one simple sql.

It says now :--
The command executed successfully with no results returned.

Thanks
Sandeep
Re: Accessing Constants from a package [message #439661 is a reply to message #439658] Tue, 19 January 2010 00:44 Go to previous messageGo to next message
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 #439662 is a reply to message #439656] Tue, 19 January 2010 00:48 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 11:33
First remove/comment the WHEN OTHERS clauses (all of them in your procedure and the ones it calls recursively) and reexecute then you will know where the error comes from, maybe it is NOT where you think it happens.

Regards
Michel


I did n` t find any exception part in the OP `s post? do you?

sriram Smile
Re: Accessing Constants from a package [message #439665 is a reply to message #439662] Tue, 19 January 2010 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I did n` t find any exception part in the OP `s post? do you?

Quote:
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

Regards
Michel
Re: Accessing Constants from a package [message #439667 is a reply to message #439661] Tue, 19 January 2010 00:54 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
sanyadu wrote on Tue, 19 January 2010 07:44
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.

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

Previous Topic: query to get first and last values (merged)
Next Topic: To run DML every hour in PL/SQL
Goto Forum:
  


Current Time: Mon Feb 17 18:55:14 CST 2025