Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Convert BLOB to VARCHAR2
tech_quest
post Sep 26 2007, 05:02 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 26-September 07
Member No.: 12,367



Hi,

I am trying to fetch 31k chunks from a column 'TEST_ALLOWEDB' which is of BLOB type. I am getting 'ORA-06502' error. The database is ORACLE 9i.

The query I am executing is:

SELECT DISTINCT
TQ.TEST_CODE,
TQ.TEST_SORT,[font=Verdana]
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"

I made following changes in the query but it didn't work:

1. Replaced INSTR with IN
2. Tried to transfer the BLOB data INTO a variable wTEMP of type BLOB and then converted it to
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(wTEMP, 31000));
3. Tried to transfer the BLOB data INTO a variable wTEMP of type CLOB and then converted it to
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(wTEMP, 31000));


Please share your ideas to solve the above mentioned issue.

Any ideas would be appreciated.

Thank You!
Go to the top of the page
 
+Quote Post
aussie_dba
post Sep 26 2007, 07:56 AM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 28-August 04
Member No.: 495



This was found in experts_exchange.com:

The following program selects CLOB data and puts in VARCHAR2 variable:

SQL> @test.sql

CREATE OR REPLACE PROCEDURE TEST_PROC AS

CNT NUMBER := 1;
LOCATOR CLOB;
BEGINNING INTEGER :=4000;
ENDING INTEGER := 1;
CLOB_TEXT VARCHAR2(4000);
BEGIN

cursor c is
SELECT TAG
INTO LOCATOR
FROM TEST2
order by TAG;
open c;
LOOP
c = DBMS_LOB.READ (CLOB_TEXT);
DBMS_OUTPUT.PUT_LINE('TAG RECORD = '||c);
END LOOP;
close c;

END;
/

ALTER PROCEDURE TEST_PROC COMPILE;
SHOW ERRORS;

SET SERVEROUTPUT ON SIZE 1000000
EXECUTE TEST_PROC;
Go to the top of the page
 
+Quote Post
crottykt
post Sep 26 2007, 07:59 AM
Post #3


Advanced Member
***

Group: Members
Posts: 180
Joined: 2-August 05
From: Raleigh, NC
Member No.: 2,640



Yes, I would say your problem lies in that a varchar2 data type has a limit of 4000 bytes. You are trying to take a 31000 byte strings and converting it.


--------------------
Kent Crotty
Go to the top of the page
 
+Quote Post
tech_quest
post Sep 27 2007, 05:27 AM
Post #4


Newbie
*

Group: Members
Posts: 2
Joined: 26-September 07
Member No.: 12,367



As per your suggestion, 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 issue.

Thank You!!
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Sep 27 2007, 10:07 AM
Post #5


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



the maximum length of a raw to use with UTL_RAW is 2000


--------------------
Go to the top of the page
 
+Quote Post
crottykt
post Sep 27 2007, 10:24 AM
Post #6


Advanced Member
***

Group: Members
Posts: 180
Joined: 2-August 05
From: Raleigh, NC
Member No.: 2,640



I didn't know this but it seems that RAW types are limited to 2000 bytes. See this page:

http://download.oracle.com/docs/cd/B10501_...a.htm#SQLRF0021

Try using 2000 instead of 4000 and see what happens.


--------------------
Kent Crotty
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Sep 28 2007, 04:25 AM
Post #7


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



here is a function that convert your blob in a clob


CODE
create or replace function F(B BLOB)
return clob is
  c clob;
  n number;
begin
  if (b is null) then
    return null;
  end if;
  if (length(b)=0) then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  return c;
end;
/


--------------------
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th October 2014 - 04:07 PM