Convert BLOB to VARCHAR2 [message #270237] |
Wed, 26 September 2007 04:30  |
tech_quest
Messages: 3 Registered: September 2007
|
Junior Member |
|
|
Hi,
I am trying to fetch 31k chunks from a column 'TEST_ALLOWEDB' which is of BLOB type. I am getting 'ORA-06502' error. Database is ORACLE 9i.
The query I am executing is:
SELECT DISTINCT
TQ.TEST_CODE,
TQ.TEST_SORT,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(TEST_ALLOWEDB, 31000)) TEST_ALLOWEDB
FROM
TEST_QUESTION TQ
WHERE
INSTR(',1091,1092,1107,1769,1770,1771,1772,1773,1774,1775,',','||TQ.TEST_CODE||',')>0
The error its throwing is:
SQL Error: ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
Please share your ideas to solve the above mentioned issue.
Any ideas would be appreciated.
Thank You!
|
|
|
|
Re: Convert BLOB to VARCHAR2 [message #270521 is a reply to message #270240] |
Thu, 27 September 2007 05:12   |
tech_quest
Messages: 3 Registered: September 2007
|
Junior Member |
|
|
As per your reply, I tried to fetch only 4000 chunks from the BLOB field but, it is throwing the same error - ORA:06502 PL/SQL: numeric or value error: raw variable length too long.
Here is the query I executed:
SELECT DISTINCT
TQ.TEST_CODE,
TQ.TEST_SORT,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(TEST_ALLOWEDB, 4000,1)) TEST_ALLOWEDB
FROM
TEST_QUESTION TQ
WHERE
INSTR(',1091,1092,1107,1769,1770,1771,1772,1773,1774,1775,',','||TQ.TEST_CODE||',')>0
Please suggest more ideas to handle this scenario.
Thank You!!
[Updated on: Thu, 27 September 2007 05:13] Report message to a moderator
|
|
|
|
|
|
Re: Convert BLOB to VARCHAR2 [message #271275 is a reply to message #271274] |
Mon, 01 October 2007 02:13  |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
"this will" or "this returns".
I want you to check it not to tell us what you think it is.
Use both LENGTH and LENGTHB
Regards
Michel
|
|
|