Home » Developer & Programmer » Forms » Write Long or CLOB to file [ Not supporting more then 4000 on long] (Oracle Form 6i, Oracle 10 G)
Write Long or CLOB to file [ Not supporting more then 4000 on long] [message #618593] Mon, 14 July 2014 03:07 Go to next message
javed.khan
Messages: 312
Registered: November 2006
Location: Banglore
Senior Member

Hey Gurus,

I have created one form which is having two long field , i am not doing much but just writing the contents of the field to a file say .xml file. i can do everything until i generate the file to disk.
It wont write more then 4000 characters.

I am attaching my script , form and directory here. Any suggestion , way forward will be highly appreciated.

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
)



Thanks & Regards
Javed A. Khan
Re: Write Long or CLOB to file [ Not supporting more then 4000 on long] [message #618600 is a reply to message #618593] Mon, 14 July 2014 03:42 Go to previous messageGo to next message
javed.khan
Messages: 312
Registered: November 2006
Location: Banglore
Senior Member

Sorry forgot to attach my procedure.

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;
/
Re: Write Long or CLOB to file [ Not supporting more then 4000 on long] [message #618903 is a reply to message #618600] Wed, 16 July 2014 06:57 Go to previous messageGo to next message
javed.khan
Messages: 312
Registered: November 2006
Location: Banglore
Senior Member

Anyone ? Sad
Re: Write Long or CLOB to file [ Not supporting more then 4000 on long] [message #621310 is a reply to message #618903] Tue, 12 August 2014 09:07 Go to previous message
javed.khan
Messages: 312
Registered: November 2006
Location: Banglore
Senior Member

Sad
***********Closing this thread*****************
Previous Topic: set_block_property - visual attribute
Next Topic: Regarding Oracle forms and reports
Goto Forum:
  


Current Time: Thu Nov 27 05:07:15 CST 2014

Total time taken to generate the page: 0.13928 seconds