Home » SQL & PL/SQL » SQL & PL/SQL » Convert BLOB to VARCHAR2
Convert BLOB to VARCHAR2 [message #270237] Wed, 26 September 2007 04:30 Go to next message
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!

Report message to a moderator

Re: Convert BLOB to VARCHAR2 [message #270240 is a reply to message #270237] Wed, 26 September 2007 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PL/SQL can only handle 32K string but SQL is limited to 4000 bytes.

Regards
Michel

Report message to a moderator

Re: Convert BLOB to VARCHAR2 [message #270521 is a reply to message #270240] Thu, 27 September 2007 05:12 Go to previous messageGo to next message
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 #270533 is a reply to message #270521] Thu, 27 September 2007 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What are your database and session character sets?

Regards
Michel

Report message to a moderator

Re: Convert BLOB to VARCHAR2 [message #270534 is a reply to message #270521] Thu, 27 September 2007 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does the query with LENGTH around the TEST_ALLOWEDB expression give?

Regards
Michel

Report message to a moderator

Re: Convert BLOB to VARCHAR2 [message #271274 is a reply to message #270534] Mon, 01 October 2007 02:06 Go to previous messageGo to next message
tech_quest
Messages: 3
Registered: September 2007
Junior Member
This will return the 4000 chars in the form of VARCHAR2 from the column TEST_ALLOWEDB.

Report message to a moderator

Re: Convert BLOB to VARCHAR2 [message #271275 is a reply to message #271274] Mon, 01 October 2007 02:13 Go to previous message
Michel Cadot
Messages: 68767
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

Report message to a moderator

Previous Topic: how to use remote database data
Next Topic: Error in inserting image in BLOB column
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Aug 21 20:04:18 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.