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]

[code=oracle]
DECLARE
vfile UTL_FILE.file_t ype;
vdata VARCHAR2 (32767);
vfilename VARCHAR2 (100);
vsystime VARCHAR2 (10);
CURSOR wcspolicydetail s IS SELECT * FROM inf_wcs_policy_ sub_tab
WHERE wcsb_pol_tracke r_id = 489 AND wcsbph_record_t ype IN (2, 3);
BEGIN
vsystime := TO_CHAR (SYSDATE, 'hh24miss');
vfilename := 'ARVINDPOL164.W CA' || vsystime;
vfile := UTL_FILE.fopen ('WCS_DIR', vfilename, 'W');
FOR wcspolicydetail srec IN wcspolicydetail s
LOOP -- vFile := UTL_FILE.FOPEN ('WCS_DIR', vFileName, 'A');
IF wcspolicydetail srec.wcsbpt_rec _id = 1
THEN
vdata := RPAD ('ARVIND SINGH', 100);
UTL_FILE.put_li ne (vfile, vdata);
END IF;
IF wcspolicydetail srec.wcsbpt_rec _id = 2
THEN
vdata := RPAD ('RAHUL DRAVID', 100);
UTL_FILE.put_li ne (vfile, vdata);
END IF;
END LOOP;
UTL_FILE.fclose (vfile);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put _line (SQLERRM);
END;[/code]

[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_OPERATI ON 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?

[code=oracle]

CREATE OR REPLACE PROCEDURE sal_status
(p_filedir IN VARCHAR2, p_filename IN VARCHAR2)
IS
v_filehandle UTL_FILE.FILE_T YPE;
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_LI NE (v_filehandle);
FOR v_emp_rec IN emp_info LOOP
v_newdeptno := v_emp_rec.deptn o;
IF v_newdeptno <> v_olddeptno THEN
UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n',
v_emp_rec.deptn o);
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_LI NE (v_filehandle, '*** END OF REPORT ***');
UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
WHEN UTL_FILE.INVALI D_FILEHANDLE THEN
RAISE_APPLICATI ON_ERROR (-20001, 'Invalid File.');
WHEN UTL_FILE.WRITE_ ERROR THEN
RAISE_APPLICATI ON_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.D OC'); -- ONLY CREATES UNDER THE C:\ DRIVE
[/code]