CREATE OR REPLACE DIRECTORY 
"base_dir" AS 
'E:\post_install';


GRANT READ, WRITE ON DIRECTORY  "base_dir" TO SCOTT WITH GRANT OPTION;

CREATE TABLE CREATE_POST_INSTALL
(
  ORIGINAL_SCRIPT  CLOB,
  NEW_SCRIPT       CLOB
)

CREATE OR REPLACE PROCEDURE SCOTT.dpr_clobToFile
( p_fileName       IN VARCHAR2, 
  p_dir            IN VARCHAR2,
  p_clob           IN LONG ) IS

  c_amount         CONSTANT BINARY_INTEGER := 32767;
  l_buffer         VARCHAR2(32767);
  l_chr10          PLS_INTEGER;
  l_clobLen        PLS_INTEGER;
  l_fHandler       UTL_FILE.FILE_TYPE;
  l_pos            PLS_INTEGER    := 1;

BEGIN

  l_clobLen  := DBMS_LOB.GETLENGTH(p_clob);
  l_fHandler := UTL_FILE.FOPEN(p_dir, p_fileName,'W',c_amount);

  WHILE l_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);      
    EXIT WHEN l_buffer IS NULL;
    l_chr10  := INSTR(l_buffer,CHR(10),-1);
    IF l_chr10 != 0 THEN
      l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
    END IF;
    UTL_FILE.PUT_LINE(l_fHandler, l_buffer,TRUE);
    l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
  END LOOP;

  UTL_FILE.FCLOSE(l_fHandler);

EXCEPTION
WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(l_fHandler) THEN
    UTL_FILE.FCLOSE(l_fHandler);
  END IF;
  RAISE;

END;
/