Anilrgowda

Question: I was to trying to use the solution provided by mszacik for convert long raw into varchar2 data type in oracle 8i. I am using it in Oracle 9i.
Mostly it works fine. In some cases I got the error in SQLPlus as:
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ABC.TEXTRAWIO", line 62
The line 62:
62 hex_data := rawtohex(raw_data);
63 rawlen := length(hex_data);

I think this may related to the varchar2 data limit? if so , how can I deal with it ?

------------------

Anilrgowda

I think that your problem is in this piece of code:

IS
raw_data LONG RAW;
rawlen NUMBER;
hex_data VARCHAR2(32760); --<<---------- Variable to small ? Try: hex_data CLOB
char_data VARCHAR2(32760);
loop_counter NUMBER;
BEGIN

-- Get the blob using the rowid passed in.
-- Will always retrun only one row.

SELECT ole_object_blob
INTO raw_data
FROM tidblob
WHERE v_object_id = rowid;

-- Convert the raw data to hex.
hex_data := rawtohex(raw_data);
rawlen := length(hex_data); --<<------ DBMS_LOB.GET_LENGTH instead

------------------