PROCEDURE MAKE_CLOB

   (p_rid in NUMBER,

    p_doc in out NOCOPY clob)

   IS

   v_title        varchar2 (200);

   v_buffer       varchar2 (4000);

   v_text_as_blob blob;

BEGIN

    select NOME, TEXT

    into v_title, v_text_as_blob

    from LB_MULTILANG_DOCS where DOC_ID = p_rid;

    v_buffer := '<mytitle>' || v_title || '</mytitle><mytext>';

    Dbms_Lob.write (p_doc, length(v_buffer ), 1, v_buffer);

    Ctx_Doc.Ifilter (v_text_as_blob, p_doc);

    v_buffer := '</mytext>' ;

    Dbms_Lob.writeappend (p_doc, length(v_buffer ),v_buffer);

    EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line (SubStr( 'Error '||TO_CHAR (SQLCODE)|| ': '||SQLERRM , 1, 255));

END ;

SCOTT@10gXE> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SCOTT@10gXE> CREATE TABLE lb_multilang_docs
  2    (doc_id	NUMBER,
  3  	nome	VARCHAR2 (200),
  4  	text	BLOB)
  5  /

Table created.

SCOTT@10gXE> INSERT INTO lb_multilang_docs
  2  VALUES (1, 'nome1', UTL_RAW.CAST_TO_RAW ('this is a test'))
  3  /

1 row created.

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE make_clob
  2  	 (p_rid in NUMBER,
  3  	  p_doc in out NOCOPY clob)
  4  IS
  5  	 v_title	varchar2 (200);
  6  	 v_buffer	varchar2 (4000);
  7  	 v_text_as_blob blob;
  8  BEGIN
  9  	 select NOME, TEXT
 10  	 into v_title, v_text_as_blob
 11  	 from LB_MULTILANG_DOCS where DOC_ID = p_rid;
 12  	 v_buffer := '<mytitle>' || v_title || '</mytitle><mytext>';
 13  	 Dbms_Lob.write (p_doc, length(v_buffer ), 1, v_buffer);
 14  	 Ctx_Doc.Ifilter (v_text_as_blob, p_doc);
 15  	 v_buffer := '</mytext>' ;
 16  	 Dbms_Lob.writeappend (p_doc, length(v_buffer ),v_buffer);
 17  EXCEPTION
 18  	 WHEN OTHERS THEN
 19  	     dbms_output.put_line (SubStr( 'Error '||TO_CHAR (SQLCODE)|| ': '||SQLERRM , 1, 255));
 20  END make_clob;
 21  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> GRANT EXECUTE ON make_clob TO PUBLIC
  2  /

Grant succeeded.

SCOTT@10gXE> DECLARE
  2    v_clob  CLOB;
  3  BEGIN
  4    DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);
  5    make_clob (1, v_clob);
  6    DBMS_OUTPUT.PUT_LINE (SUBSTR (v_clob, 1, 60));
  7    DBMS_LOB.FREETEMPORARY (v_clob);
  8  END;
  9  /
<mytitle>nome1</mytitle><mytext>this is a test</mytext>

PL/SQL procedure successfully completed.

SCOTT@10gXE>