I am on Oracle 8.0.5 version. It is not an 8i database. I want to convert LONGRAW data into BLOB. I have written the following the code, but it gives me a PL/SQL numeric error on the dbms_lob.write statement. I really don't know how to solve this problem. My code:
Declare lv_bytes_to_write Number := 1000; buffer_var long raw; offset_var number := 1; v_chunklength Integer := 0; v_cnt Number := 0; v_loops Number := 0; t_blob blob; BEGIN insert into tmp_blob values (32618,empty_blob()); for rec in (select drawing from fsp_viewport_drawings where dwg_index_no = 32618) loop select tmp_dwg into t_blob from tmp_blob where index_no = 32618 for update; buffer_var := rec.drawing; v_chunklength := utl_raw.length(buffer_var); dbms_output.put_line('Chunk Length ' || to_char(v_chunklength)); If v_chunklength > 1000 Then If Mod(v_chunklength,1000) = 0 Then v_loops := v_chunklength/1000; else v_loops := (v_chunklength/1000) + 1; End if; Else v_loops := 1; End if; dbms_output.put_line('Loops' || to_char(v_loops)); dbms_output.put_line('Vcnt ' || to_char(v_cnt)); loop IF v_cnt > v_loops Then Exit; Else dbms_output.put_line('Before writing the data'); DBMS_LOB.WRITE(t_blob, lv_bytes_to_write, offset_var, buffer_var); dbms_output.put_line('Vcnt inside loop' || to_char(v_cnt)); offset_var := offset_var + lv_bytes_to_write; End if; v_cnt := v_cnt + 1; end loop; end loop; Exception When Others then dbms_output.put_line('Screwed up ' || SQLCODE ||' '||SQLERRM); rollback; End;
The following example is from Chapter 21 of the book Oracle8 PL/SQL Programming by Scott Urman (you'd pass in the query against your fsp_viewport_drawings table as the first parameter when you run it):
CREATE OR REPLACE PROCEDURE Long2Lob( -- Uses DBMS_SQL to select a LONG column identified by p_LongQuery, and -- returns it in p_CLob. p_LongQuery IN VARCHAR2, p_CLob IN OUT CLOB) AS c_ChunkSize CONSTANT INTEGER := 100; v_CursorID INTEGER; v_RC INTEGER; v_Chunk VARCHAR2(100); v_ChunkLength INTEGER; v_Offset INTEGER := 0; BEGIN -- Open the cursor, define, execute, and fetch. v_CursorID := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_CursorID, p_LongQuery, DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, 1); v_RC := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID); -- Loop over the LONG, fetching c_ChunkSize characters at a time from -- the LONG and adding them to the LOB. LOOP DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, 1, c_ChunkSize, v_Offset, v_Chunk, v_ChunkLength); DBMS_LOB.WRITE(p_CLob, v_ChunkLength, v_Offset + 1, v_Chunk); IF v_ChunkLength < c_ChunkSize THEN EXIT; ELSE v_Offset := v_Offset + v_ChunkLength; END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(v_CursorID); EXCEPTION WHEN OTHERS THEN -- Clean up, and reraise the error. DBMS_SQL.CLOSE_CURSOR(v_CursorID); RAISE; END Long2Lob; /This code worked fine in my quick tests. Hope it helps.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.