Utl file

[singharvind]

Hi All,

I've a problem with the utl file.

I'm using put_line to write the lines into the file.

The problem is that I'm getting new line character at the end of each line which i dont want.

How to solve this problem?

[debasisdas]

KIndly post your code for reference of our experts.

[singharvind]

DECLARE
vfile UTL_FILE.file_type;
vdata VARCHAR2 (32767);
vfilename VARCHAR2 (100);
vsystime VARCHAR2 (10);
CURSOR wcspolicydetails IS SELECT * FROM inf_wcs_policy_sub_tab
WHERE wcsb_pol_tracker_id = 489 AND wcsbph_record_type IN (2, 3);
BEGIN
vsystime := TO_CHAR (SYSDATE, 'hh24miss');
vfilename := 'ARVINDPOL164.WCA' || vsystime;
vfile := UTL_FILE.fopen ('WCS_DIR', vfilename, 'W');
FOR wcspolicydetailsrec IN wcspolicydetails
LOOP -- vFile := UTL_FILE.FOPEN ('WCS_DIR', vFileName, 'A');
IF wcspolicydetailsrec.wcsbpt_rec_id = 1
THEN
vdata := RPAD ('ARVIND SINGH', 100);
UTL_FILE.put_line (vfile, vdata);
END IF;
IF wcspolicydetailsrec.wcsbpt_rec_id = 2
THEN
vdata := RPAD ('RAHUL DRAVID', 100);
UTL_FILE.put_line (vfile, vdata);
END IF;
END LOOP;
UTL_FILE.fclose (vfile);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;

[debasisdas]

put_line always adds new line character at the end of each line .

try to use PUT instead.

NOTE :- You must have opened the file using mode 'w' or mode 'a'; otherwise, an INVALID_OPERATION exception is raised.

[Dave44]

just be aware that if you use PUT you are limited to 32K, thats all the buffer can hold. Put_line flushes the buffer and restarts counting at 0. so if you are making small (< 32K) files you are fine. otherwise the rest of your data will simply not appear in the file.

[amitpatel66]

make use of the UTL_FILE.FFLUSH procedure to flush out the previous data in the buffer and start from zero in case if you are using UTL_FILE.PUT

[subashsavji]

Hi All,

I've a problem with the utl file.

I'm using put_line to write the lines into the file.

The problem is that I'm getting new line character at the end of each line which i dont want.

How to solve this problem?

 
CREATE OR REPLACE PROCEDURE sal_status
(p_filedir IN VARCHAR2,  p_filename IN VARCHAR2)
IS
 v_filehandle UTL_FILE.FILE_TYPE;
 CURSOR emp_info IS
   SELECT ename, sal, deptno
    FROM emp
    ORDER BY deptno;
 v_newdeptno emp.deptno%TYPE;
 v_olddeptno emp.deptno%TYPE := 0;
BEGIN
 v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w');
 UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON
                             %s\n', SYSDATE);
 UTL_FILE.NEW_LINE (v_filehandle);
 FOR v_emp_rec IN emp_info LOOP
  v_newdeptno := v_emp_rec.deptno;
IF v_newdeptno <> v_olddeptno THEN
      UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n',
                     v_emp_rec.deptno);
    END IF;
    UTL_FILE.PUTF (v_filehandle,'  EMPLOYEE: %s earns: %s\n',
                   v_emp_rec.ename, v_emp_rec.sal);
    v_olddeptno := v_newdeptno;
  END LOOP;
  UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR (-20001, 'Invalid File.');
  WHEN UTL_FILE.WRITE_ERROR THEN
   RAISE_APPLICATION_ERROR (-20002, 'Unable to write to
                                     file');
END sal_status;
/
 
create OR REPLACE DIRECTORY emp_dir as 'c:\';
/
execute sal_status('EMP_DIR','YY.TXT'); 
/
execute sal_status('EMP_DIR','C:\YYY.DOC');  -- ONLY CREATES UNDER THE C:\ DRIVE