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;