SQL*Plus and Blobs/Clobs
how to insert into a blob using sql*plus
For BIG blobs/clobs
declare myClobVar varchar2(32767) := 'string >4000 and <32767 bytes'; begin update tableWithClob set clobcol = myClobVar; end;
eg (where SCRIPT_CONTENT is of a BLOB datatype):
INSERT INTO SCRIPTS ( SCRIPT_NO, SERVER_NO, SCRIPT_NAME, SCRIPT_CONTENT ) VALUES ( RR_S_SCRIPTS.NextVal, 99, 'delme.txt', utl_raw.CAST_TO_RAW('can I insert plain values into this?'));
eg2:
INSERT INTO SCRIPTS ( SCRIPT_NO, SERVER_NO, SCRIPT_NAME, SCRIPT_CONTENT ) VALUES ( RR_S_SCRIPTS.NextVal, 99, 'delme2.txt', utl_raw.CAST_TO_RAW('there will be many lines'));
to select from it:
SELECT script_name , DBMS_LOB.getLength(script_content) script_content_length -- truncates blob output to 200 chanrs ,utl_raw.cast_to_varchar2( dbms_lob.substr( script_content, 200, 1 ) ) FROM scripts
create clob and append small stings
set def off; declare myclob clob := 'Place a huge string here (redacted for readability)'; string2 varchar2(32767) := 'Place another huge string here (redacted for readability)'; string3 varchar2(32767) := 'Place yet another huge string here (redacted for readability)'; begin myclob := myclob || string2 ; myclob := myclob || string3 ; update file_binary_data set file_data = clob_to_blob(myclob) where file_bin_id = 1361651; end;
then use this function:
create or replace function clob_to_blob (p_clob_in in clob) return blob is v_blob blob; v_offset integer; v_buffer_varchar varchar2(32000); v_buffer_raw raw(32000); v_buffer_size binary_integer := 32000; begin if p_clob_in is null then return null; end if; DBMS_LOB.CREATETEMPORARY(v_blob, TRUE); v_offset := 1; FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / v_buffer_size) loop dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar); v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar); dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw); v_offset := v_offset + v_buffer_size; end loop; return v_blob; end clob_to_blob;