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  |
 |
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 #486730 is a reply to message #486720] |
Sun, 19 December 2010 10:42  |
 |
Barbara Boehmer
Messages: 9090
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>
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 00:37:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
