-- -------------------------------------------------------
-- blob_insert_into script copyright 2007 roqet - http://www.roqet.org.
-- This script can be distributed and modified under the terms of the
-- GNU General Public License: http://www.gnu.org/copyleft/gpl.html
--   ~~~
--   'l'
--    -
-- -------------------------------------------------------
-- how to insert into a blob using sql*plus...
-- use UTL_RAW.CAST_TO_RAW()

-- 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