-- The code below shows the use of DBMS_LOB.Append(clob1, clob2) to 
-- append two CLOB datatypes into a single CLOB variable. The CLOB
-- variables are setup as SQL header and detail and appended to
-- create dynamic SQL string.

SET SERVEROUTPUT ON;

DECLARE
 v_ssql   CLOB;
 v_hdr    CLOB := 'SELECT ';
 v_body   CLOB;

FUNCTION build_sql(i_hdr IN CLOB, i_body In CLOB)
 RETURN CLOB IS
 o_sql  CLOB;
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 := 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(DBMS_LOB.substr(v_ssql,60,1));
END;
/