-- The example below shows Concatenation of two BLOB data into a 
-- single BLOB using DBMS_LOB.append(blob1, blob2). The dynamic sql
-- (query) can be created by appending header and body.

DECLARE
 v_ssql   BLOB;
 v_hdr    BLOB := UTL_RAW.cast_to_raw('SELECT ');
 v_body   BLOB;

FUNCTION build_sql(i_hdr IN BLOB, i_body In BLOB)
 RETURN BLOB IS
 o_sql  BLOB;
BEGIN
   DBMS_LOB.createtemporary(o_sql, TRUE);
   DBMS_LOB.APPEND(o_sql, i_hdr);
   DBMS_LOB.APPEND(o_sql, i_body);
   RETURN o_sql;
END build_sql;

BEGIN
 v_body := UTL_RAW.cast_to_raw(q'(TO_CHAR(SYSDATE,'mm/dd/yyyy')
 as_of FROM DUAL;)');

 v_ssql := build_sql(v_hdr, v_body);

 DBMS_OUTPUT.PUT_LINE('SQL string:');
 DBMS_OUTPUT.PUT_LINE(UTL_RAW.cast_to_varchar2(v_ssql));
END;
/