Home » SQL & PL/SQL » SQL & PL/SQL » Blob to long raw conversion
Blob to long raw conversion [message #486716] Sun, 19 December 2010 08:14 Go to next message
mtanany
Messages: 3
Registered: December 2010
Location: cairo
Junior Member
Hi all

I have a problem i need to convert a blob column contains pic file to long row
i had many tries but no one succeeded
-----------------------------------
Source table | destination table
id number | id number
img blob | img long raw
------------------------------------
1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2

i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)

2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2

now errors but the lengh is 0 (i have no file )

What can i Do please advise and thanks in advance
Re: Blob to long raw conversion [message #486718 is a reply to message #486716] Sun, 19 December 2010 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to write your own program for this (and not in SQL or PL/SQL).
LONG RAW is obsolete since the last century, why do you want to use it?

Regards
Michel

[Updated on: Sun, 19 December 2010 08:18]

Report message to a moderator

Re: Blob to long raw conversion [message #486719 is a reply to message #486718] Sun, 19 December 2010 08:41 Go to previous messageGo to next message
mtanany
Messages: 3
Registered: December 2010
Location: cairo
Junior Member
thanks for your reply
put i have to use this data type as an old system uses it
what king of programs do this :
Re: Blob to long raw conversion [message #486720 is a reply to message #486719] Sun, 19 December 2010 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyone in C, VB, Java or other non-Oracle language.

Regards
Michel
Re: Blob to long raw conversion [message #486730 is a reply to message #486720] Sun, 19 December 2010 10:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
You can use dbms_lob.substr in pl/sql to get 2000 characters at a time into a long raw, concatenating each one into a final long raw using utl_raw.concat, then insert that, as demonstrated below in the middle section.

-- source table and data:
SCOTT@orcl_11gR2> create table source
  2    (id   number,
  3  	img  blob)
  4  /

Table created.

SCOTT@orcl_11gR2> declare
  2    v_clob	      clob;
  3    v_blob	      blob;
  4    v_dest_offset  integer := 1;
  5    v_src_offset   integer := 1;
  6    v_lang_context integer := dbms_lob.default_lang_ctx;
  7    v_warning      integer;
  8  begin
  9    dbms_lob.createtemporary (v_clob, true);
 10    dbms_lob.createtemporary (v_blob, true);
 11    for i in 1 .. 1000 loop
 12  	 dbms_lob.writeappend
 13  	   (v_clob,
 14  	    length ('This is test' || i || '.  '),
 15  	    'This is test' || i || '.  ');
 16    end loop;
 17    dbms_lob.converttoblob
 18  	 (v_blob,
 19  	  v_clob,
 20  	  dbms_lob.lobmaxsize,
 21  	  v_dest_offset,
 22  	  v_src_offset,
 23  	  dbms_lob.default_csid,
 24  	  v_lang_context,
 25  	  v_warning);
 26    insert into source (id, img)
 27    values (1, v_blob);
 28    commit;
 29    dbms_lob.freetemporary (v_clob);
 30    dbms_lob.freetemporary (v_blob);
 31  end;
 32  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> declare
  2    v_clob	      clob;
  3    v_blob	      blob;
  4    v_dest_offset  integer := 1;
  5    v_src_offset   integer := 1;
  6    v_lang_context integer := dbms_lob.default_lang_ctx;
  7    v_warning      integer;
  8  begin
  9    dbms_lob.createtemporary (v_clob, true);
 10    dbms_lob.createtemporary (v_blob, true);
 11    for i in reverse 1 .. 1000 loop
 12  	 dbms_lob.writeappend
 13  	   (v_clob,
 14  	    length ('This is test' || i || '.  '),
 15  	    'This is test' || i || '.  ');
 16    end loop;
 17    dbms_lob.converttoblob
 18  	 (v_blob,
 19  	  v_clob,
 20  	  dbms_lob.lobmaxsize,
 21  	  v_dest_offset,
 22  	  v_src_offset,
 23  	  dbms_lob.default_csid,
 24  	  v_lang_context,
 25  	  v_warning);
 26    insert into source (id, img)
 27    values (2, v_blob);
 28    commit;
 29    dbms_lob.freetemporary (v_clob);
 30    dbms_lob.freetemporary (v_blob);
 31  end;
 32  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select id,
  2  	    dbms_lob.getlength (img),
  3  	    utl_raw.cast_to_varchar2 (dbms_lob.substr (img, 20, 1))
  4  	    || ' ... '||
  5  	    utl_raw.cast_to_varchar2
  6  	       (dbms_lob.substr (img, 20, dbms_lob.getlength (img) - 20))
  7  from   source
  8  /

        ID DBMS_LOB.GETLENGTH(IMG)
---------- -----------------------
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(IMG,20,1))||'...'||UTL_RAW.CAST_TO_VARC
--------------------------------------------------------------------------------
         1                   17893
This is test1.  This ...   This is test1000.

         2                   17893
This is test1000.  T ... t2.  This is test1.


2 rows selected.


-- destination table and insert:
SCOTT@orcl_11gR2> create table destination
  2    (id   number,
  3  	img  long raw)
  4  /

Table created.

SCOTT@orcl_11gR2> declare
  2    v_rawt  long raw;
  3    v_raw   long raw;
  4  begin
  5    for r in
  6  	 (select * from source)
  7    loop
  8  	 v_raw := null;
  9  	 for i in 0 .. floor (dbms_lob.getlength (r.img) / 2000)
 10  	 loop
 11  	   v_rawt := dbms_lob.substr (r.img, 2000, ((i * 2000) + 1));
 12  	   v_raw := utl_raw.concat (v_raw, v_rawt);
 13  	 end loop;
 14  	 insert into destination (id, img)
 15  	 values (r.id, v_raw);
 16    end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.


-- confirmation of results by converting back:
SCOTT@orcl_11gR2> create table confirm
  2    (id   number,
  3  	img  blob)
  4  /

Table created.

SCOTT@orcl_11gR2> insert into confirm (id, img)
  2  select id, to_lob (img)
  3  from   destination
  4  /

2 rows created.

SCOTT@orcl_11gR2> select id,
  2  	    dbms_lob.getlength (img),
  3  	    utl_raw.cast_to_varchar2 (dbms_lob.substr (img, 20, 1))
  4  	    || ' ... '||
  5  	    utl_raw.cast_to_varchar2
  6  	       (dbms_lob.substr (img, 20, dbms_lob.getlength (img) - 20))
  7  from   confirm
  8  /

        ID DBMS_LOB.GETLENGTH(IMG)
---------- -----------------------
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(IMG,20,1))||'...'||UTL_RAW.CAST_TO_VARC
--------------------------------------------------------------------------------
         1                   17893
This is test1.  This ...   This is test1000.

         2                   17893
This is test1000.  T ... t2.  This is test1.


2 rows selected.

SCOTT@orcl_11gR2>

Re: Blob to long raw conversion [message #486735 is a reply to message #486730] Sun, 19 December 2010 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Works only if the result is smaller than 32K.

Regards
Michel
Re: Blob to long raw conversion [message #486762 is a reply to message #486735] Mon, 20 December 2010 02:15 Go to previous messageGo to next message
mtanany
Messages: 3
Registered: December 2010
Location: cairo
Junior Member
Thanks All for your help
seems to be Works only if the result is smaller than 32K.
i have files 300 , 400 K
this is frustrating
Re: Blob to long raw conversion [message #486766 is a reply to message #486762] Mon, 20 December 2010 02:43 Go to previous message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, PL/SQL is limited to 32K (but LOBs), this is why I said you have to create your own program outside.

Regards
Michel

[Updated on: Mon, 20 December 2010 02:43]

Report message to a moderator

Previous Topic: primary key and foreign key
Next Topic: query (merged)
Goto Forum:
  


Current Time: Tue May 28 09:24:19 CDT 2024