insert_clob_from_file.sql

CREATE OR REPLACE PROCEDURE load_expression (dir NVARCHAR2, filename NVARCHAR2, id number) AS
    dest_clob   CLOB;
    src_clob    BFILE ;
    dst_offset  number := 1 ;
    src_offset  number := 1 ;
    lang_ctx    number := DBMS_LOB.DEFAULT_LANG_CTX;
    warning     number;
    stmt VARCHAR2(500):= 'CREATE OR REPLACE DIRECTORY MY_DIR AS ' || '''' ||dir|| '''';
BEGIN
   DBMS_OUTPUT.ENABLE(100000);
   EXECUTE IMMEDIATE stmt;
    src_clob := BFILENAME('MY_DIR', filename);
    UPDATE TEST_SCRIPT_PARAM_VAL set expression = empty_clob() WHERE id = id
    RETURNING EXPRESSION INTO dest_clob;
    DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadCLOBFromFile(
          DEST_LOB     => dest_clob
        , SRC_BFILE    => src_clob
        , AMOUNT       => DBMS_LOB.GETLENGTH(src_clob)
        , DEST_OFFSET  => dst_offset
        , SRC_OFFSET   => src_offset
        , BFILE_CSID   => DBMS_LOB.DEFAULT_CSID
        , LANG_CONTEXT => lang_ctx
        , WARNING      => warning
    );
    DBMS_LOB.CLOSE(src_clob);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).');
    EXECUTE IMMEDIATE 'drop directory my_dir';
END;
/