Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOB.Instr ORA-06502: PL/SQL: numeric or value error
icon5.gif  DBMS_LOB.Instr ORA-06502: PL/SQL: numeric or value error [message #252352] Wed, 18 July 2007 12:33 Go to next message
floode
Messages: 8
Registered: February 2006
Junior Member
Hi,
On one of our Qa machines we get the error
ORA-06502: PL/SQL: numeric or value error
sql run is
select rese_reportid from custom_reportsearches where DBMS_LOB.Instr(UPPER(rese_queryfields), N'ORQU')=0

This will run fine on other machines (all Oracle 9i rel 2)
The field rese_queryfields is an NCLOB

However on this machine if I run
select rese_reportid from custom_reportsearches where DBMS_LOB.Instr(UPPER(rese_queryfields), 'ORQU')=0
i.e. take away the N before the string it runs fine. Any suggestion why the N causes this error, or if there is some config issue I can check on this machine.
The database is AL32UTF8

Thanks a lot
Re: DBMS_LOB.Instr ORA-06502: PL/SQL: numeric or value error [message #271573 is a reply to message #252352] Tue, 02 October 2007 01:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It runs fine on both my 10gR2 (XE) and one of our 9iR2 (9.2.0.3 on Unix). What version/OS are you on? Is there anything different on that particular machine? I know that DBMS_LOB might come with a few bugs on certain versions. If you have access to Oracle Metalink, I'd check over there.

This is my test script:
CREATE TABLE mytable( col1 NUMBER
                    , col2 NCLOB
                    )
/

INSERT INTO mytable VALUES (1, LPAD('A',4000, 'ORQU'))
/

SELECT *
FROM   mytable
WHERE  DBMS_LOB.Instr(UPPER(col2), N'ORQU')=0
/

INSERT INTO mytable VALUES (1, LPAD('A',25000, 'VER'))
/

SELECT *
FROM   mytable
WHERE  DBMS_LOB.Instr(UPPER(col2), N'ORQU')=0
/

DROP TABLE mytable PURGE -- remove 'purge' on 9i test box
/


[edit]Can you provide us with a code snippet that fails? PL/SQL or SQL sample code?

MHE

[Updated on: Tue, 02 October 2007 01:50]

Report message to a moderator

Previous Topic: UTL_FILE.FOPEN
Next Topic: Problem in deleting more than 50 laks of records. . .
Goto Forum:
  


Current Time: Sun Dec 11 02:25:33 CST 2016

Total time taken to generate the page: 0.08761 seconds