Home » SQL & PL/SQL » SQL & PL/SQL » % concatenation problem in string (Oracle 9i)
% concatenation problem in string [message #315884] Wed, 23 April 2008 02:19 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am trying to insert an string.
The string works correctly without the dot(.) and % sign.

v_sms_logins := ' Less than ' ||v_login_percentage|| 
'Dr inactive on login for last 1 month:' ;


But doesn't work when given dot and % sign.
v_sms_logins := ' Less than ' ||v_login_percentage||
 '% Dr. inactive on login for last 1 month:' ;


Gives the following error when I use % and dot:
ERROR at line 1:
ORA-20001: An error was encountered - -6502 -ERROR-ORA-06502: PL/SQL: numeric
or value error
ORA-06512: at "ATLASADMIN_REACH.TS_SMS", line 188
ORA-06512: at line 1

Please advice.

Thanks,
Mahi

[Updated on: Wed, 23 April 2008 02:20]

Report message to a moderator

Re: % concatenation problem in string [message #315885 is a reply to message #315884] Wed, 23 April 2008 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem?
Show us.

Regards
Michel
Re: % concatenation problem in string [message #315899 is a reply to message #315884] Wed, 23 April 2008 02:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is a user defined exception raised by ATLASADMIN_REACH.TS_SMS

(Perhaps the SMS gateway can't use % and . ?)

Talk to the person who wrote the ATLASADMIN_REACH package.

Neither me nor Google have heard of it before.
Re: % concatenation problem in string [message #315905 is a reply to message #315899] Wed, 23 April 2008 03:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think the ORA-20001 is the result of a stupid when others then raise <my unneeded new exception> "exception handler"
Re: % concatenation problem in string [message #315906 is a reply to message #315905] Wed, 23 April 2008 03:07 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks to all of you for your patience.
It was not working because the length given was getting less by 3 characters. Sad

I have increased the length of the variable and it worked.


Thanks,
Mahi
Re: % concatenation problem in string [message #315940 is a reply to message #315884] Wed, 23 April 2008 05:20 Go to previous messageGo to next message
msaurabh
Messages: 1
Registered: April 2008
Location: mumbai
Junior Member
friend be cool it works properly.
Re: % concatenation problem in string [message #315941 is a reply to message #315940] Wed, 23 April 2008 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
msaurabh wrote on Wed, 23 April 2008 12:20
friend be cool it works properly.

Question
If you are talking about Frank's comment, I aggre with him.
Why the procedure returns "ORA-20001: An error was encountered - -6502 -ERROR-ORA-06502: PL/SQL: numeric" and not direclty "ORA-06502: PL/SQL: numeric" with a reference to the line that raise the error?
Because of a bl... "when others" clause.

Regards
Michel

Re: % concatenation problem in string [message #315949 is a reply to message #315884] Wed, 23 April 2008 05:45 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Does it means that I should remove the When other clause
WHEN OTHERS THEN
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
   ROLLBACK;
Re: % concatenation problem in string [message #315952 is a reply to message #315949] Wed, 23 April 2008 05:58 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Let Oracle naturally raise an exception (unless you expect it to happen and know exactly how to deal with it).
Re: % concatenation problem in string [message #315956 is a reply to message #315949] Wed, 23 April 2008 06:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
virmahi wrote on Wed, 23 April 2008 12:45
Does it means that I should remove the When other clause
WHEN OTHERS THEN
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
   ROLLBACK;


In addition, the "rollback" is NEVER executed as it appears after RAISE.

Regards
Michel
Previous Topic: Group function MAX( ) inside a query
Next Topic: regarding fact tab
Goto Forum:
  


Current Time: Sun Dec 04 08:30:07 CST 2016

Total time taken to generate the page: 0.07899 seconds