Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 11g function error
Oracle 11g function error [message #582235] Tue, 16 April 2013 05:03 Go to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
I'm using the following code for a function which is working fine in Oracle 9i, but throwing error like ORA-06512 (Numeric value error) in Oracle 11g. Please guide me to solve the problem.

CREATE OR REPLACE FUNCTION Decrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
asc_val NUMBER;
len NUMBER;
BEGIN
str_new := '';
len := LENGTH(toconvert);
FOR j IN 1..len LOOP
sub_str := SUBSTR(toconvert,j,1);
asc_val := ASCII(sub_str);
asc_val := (asc_val ) - 100 * 1.5;
str_new := str_new || CHR(asc_val);
END LOOP;
RETURN str_new;
END;
/
Re: Oracle 11g function error [message #582242 is a reply to message #582235] Tue, 16 April 2013 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

2/ For which input it returns an error?

Regards
Michel
Re: Oracle 11g function error [message #582243 is a reply to message #582242] Tue, 16 April 2013 05:45 Go to previous messageGo to next message
Littlefoot
Messages: 19689
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
For inputs whose ASC_VAL evaluates to a negative number, I guess.
Re: Oracle 11g function error [message #582247 is a reply to message #582243] Tue, 16 April 2013 06:40 Go to previous messageGo to next message
cookiemonster
Messages: 11073
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does this give on each DB:
SELECT ROWNUM, (ROWNUM - 100 * 1.5) FROM dual CONNECT BY LEVEL < 10;
Re: Oracle 11g function error [message #582263 is a reply to message #582247] Tue, 16 April 2013 08:24 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
FOR Oracle-9I, I'M getting following output.


SQL> SELECT ROWNUM, (ROWNUM - 100 * 1.5) FROM dual CONNECT BY LEVEL < 10;

ROWNUM (ROWNUM-100*1.5)
--------- ----------------
1 -149

For Oracle-11g, the output is

SQL> SELECT ROWNUM, (ROWNUM - 100 * 1.5) FROM dual CONNECT BY LEVEL < 10;

ROWNUM (ROWNUM-100*1.5)
--------- ----------------
1 -149
2 -148
3 -147
4 -146
5 -145
6 -144
7 -143
8 -142
9 -141

9 rows selected.
Re: Oracle 11g function error [message #582270 is a reply to message #582263] Tue, 16 April 2013 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 11073
Registered: September 2008
Location: Rainy Manchester
Senior Member
At this point you need to work out what input data is causing it to fail.
It could be the problem is really that the corresponding encrypt function (I assume there is one) is behaving differently on the two versions.
Re: Oracle 11g function error [message #582272 is a reply to message #582270] Tue, 16 April 2013 08:55 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
The script of ENCRYPT function is:

CREATE OR REPLACE FUNCTION Encrypt(toconvert VARCHAR2) RETURN VARCHAR2 IS
str_original VARCHAR2(30);
str_new VARCHAR2(30);
sub_str VARCHAR2(1);
j NUMBER;
asc_val NUMBER;
len NUMBER;
BEGIN
str_new := '';
len := LENGTH(toconvert);
FOR j IN 1..len LOOP
sub_str := SUBSTR(toconvert,j,1);
asc_val := ASCII(sub_str);
asc_val := (asc_val ) + 100 * 1.5;
str_new := str_new || CHR(asc_val);
END LOOP;
RETURN str_new;
END;
/
Re: Oracle 11g function error [message #582276 is a reply to message #582270] Tue, 16 April 2013 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 11073
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 16 April 2013 14:50
At this point you need to work out what input data is causing it to fail.

Re: Oracle 11g function error [message #582282 is a reply to message #582272] Tue, 16 April 2013 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 April 2013 12:40
1/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

2/ For which input it returns an error?

Regards
Michel

Re: Oracle 11g function error [message #582436 is a reply to message #582276] Thu, 18 April 2013 06:56 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
Actually, we have imported 32 bit Oracle 9I dump into 64 bit Oracle 11g. Would it be the problem for that reason?
Re: Oracle 11g function error [message #582452 is a reply to message #582436] Thu, 18 April 2013 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The difference in the character set is most likely more important.

Once again, give us an example which previously works and now no more works.

Regards
Michel
Re: Oracle 11g function error [message #582506 is a reply to message #582452] Thu, 18 April 2013 23:54 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
In Oracle 9I, the same decrypt function is working as given below:
(We store the encrypted data in table using ENCRYPT function as per the coding given above)

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select decrypt('éïéêÛãÇÊÉ') from dual;

DECRYPT('ÉÏÉÊÛÃÇÊÉ')
-------------------------------------------------------------
SYSTEM143

But in Oracle 11g, it gives the following error:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select decrypt('éïéêÛãÇÊÉ') from dual;
select decrypt('éïéêÛãÇÊÉ') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PEST.DECRYPT", line 12

Re: Oracle 11g function error [message #582508 is a reply to message #582506] Fri, 19 April 2013 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the character sets of both databases and clients?

Regards
Michel
Re: Oracle 11g function error [message #582509 is a reply to message #582506] Fri, 19 April 2013 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the problem is in the client character set.

C:\>chcp 850
Page de codes activeÿ: 850

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Ven. Avr. 19 07:22:40 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> @c
Connected.
SQL> select decrypt('‚‹‚ˆêƀҐ') from dual;
select decrypt('‚‹‚ˆêƀҐ') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MICHEL.DECRYPT", line 15


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>chcp 1252
Page de codes active : 1252

C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Ven. Avr. 19 07:22:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> @c
Connected.
SQL> select decrypt('éïéêÛãÇÊÉ') from dual;
DECRYPT('ÉÏÉÊÛÃÇÊÉ')
--------------------------------------------------------------------------------------------
SYSTEM143

1 row selected.

I bet in 9i, you used Windows SQL*Plus; in 11g you use DOS SQL*Plus.

Regards
Michel

[Updated on: Fri, 19 April 2013 00:25]

Report message to a moderator

Re: Oracle 11g function error [message #582511 is a reply to message #582509] Fri, 19 April 2013 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm, no this is not the same error (anyway it is a potential error).
I bet your 9i database character is 1-byte one and your 11g one is multi-byte one.

Regards
Michel
Re: Oracle 11g function error [message #582520 is a reply to message #582509] Fri, 19 April 2013 02:07 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member

Yes, in Oracle 9i, Windows SQL*Plus is there & in 11g DOS SQL*Plus is there. But it is coming automatically (default) by installation. We have not done anything. Can we change the DOS one to Windows ?


Regards,
M Behera
Re: Oracle 11g function error [message #582523 is a reply to message #582511] Fri, 19 April 2013 02:11 Go to previous messageGo to next message
ht_maya
Messages: 16
Registered: April 2009
Location: ORISSA, INDIA
Junior Member
Character set in 11g

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS
2 ;

PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0

20 rows selected.

Character set in 9I

SQL> /

PARAMETER VALUE
------------------------------ -----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.1.0

20 rows selected.
Re: Oracle 11g function error [message #582532 is a reply to message #582523] Fri, 19 April 2013 03:16 Go to previous message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I showed there are 2 problems:
1/ The client character set
2/ The different database character set

You cannot prevent from the first problem as it is a matter of the client that can choose its character set as it wants (and maybe it even doesn't know what it has).
You have to rewrite the function to solve the second problem.
Anyway, the principle of the procedure is wrong, an encrypted data should in a binary datatype and not in character one to prevent from all these problems.

Regards
Michel


Regards
Michel
Previous Topic: cursor using subquery
Next Topic: what is error in below query
Goto Forum:
  


Current Time: Wed Oct 22 18:27:07 CDT 2014

Total time taken to generate the page: 0.12893 seconds