Understanding UTL_FILE in Oracle

UTL_FILE EXAMPLE

--EXAMPE TO UNDERSTAND UTL_FILE PACKAGE WHICH WILL READ/WRITE IN THE FILE AT THE OS LEVEL

/*
FOPEN  --FUNCTION   OPENS  A FILE FOR INPUT OR OUTPUT UTL_FILE.FOPEN();
PUT_LINE-- PROCEDURE  WRITE A STRING TO A FILE
PUT------- WRITE A LINE TO A FILE AND DONS NOT APPEDS AN OPERATING SYSTME SPECITC LINE TERMINATOR
PUTF------ A PUT PROCEDURE WITH FORMATTING
FCLOSE---- PROCEDURE  CLOSES A FILE
FCLOSE_ALL---PROCEDURE CLOSESS ALLOPEN FILE HANDLES.
*/



CONN / as sysdba

create user pc  identified by pc
/
grant connect ,resource,create any directory to pc
/
GRANT EXECUTE ON UTL_FILE TO PC
/
GRANT CREATE ANY DIRECTORY TO PC
/

conn pc/pc

create or replace directory USER_DIR as 'C:\TEMP'  --for  window
/

create or replace directory USER_DIR as  '/temp'  -- for linux , give your own location

SET SERVEROUTPUT ON


DECLARE
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1:=UTL_FILE.FOPEN('USER_DIR','HELLO.TXT','W'); --the HELLO.TXT  file will be  created if not present
UTL_FILE.PUT_LINE(F1,'HELLO WORLD THIS FILE IS WRITTEN WITH THE HELP OF  UTL_FILE PACKAGE ON '||

SYSDATE);
UTL_FILE.FCLOSE(F1);
DBMS_OUTPUT.PUT_LINE('FILE CREATED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||' '||SQLERRM);
END;
/



--READING FROM A FILE

--CREATE A EMPLOYEE.CSV FILE WITH THE BELOW DATA AND PLACE IT IN USER_DIR  DIRECTORY
/*
7369,SMITH,800,20
7499,ALLEN,1600,30
7521,WARD,1250,30
7566,JONES,2975,20
7654,MARTIN,1250,30
7698,BLAKE,2850,30
7782,CLARK,2450,10
7788,SCOTT,3000,20
7839,KING,5000,10
7844,TURNER,1500,30
7876,ADAMS,1100,20
7900,JAMES,950,30
7902,FORD,3000,20
7934,MILLER,1300,10
*/


DECLARE
V1 VARCHAR2(20000); --MAX 32767
EMP_FILE UTL_FILE.FILE_TYPE;
BEGIN
EMP_FILE:=UTL_FILE.FOPEN('USER_DIR','EMPDATA.CSV','R');
LOOP
BEGIN
UTL_FILE.GET_LINE(EMP_FILE,V1);
DBMS_OUTPUT.PUT_LINE(V1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(EMP_FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR '||TO_CHAR(SQLCODE)||' '||SQLERRM);
END;
/



-- READ DATA FROM FILE AND INSERT INTO TABLE

CONN scott/tiger


DROP TABLE  EMPCOPY PURGE
/
CREATE TABLE EMPCOPY AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM scott.EMP
WHERE 1=2
/

CREATE OR REPLACE  DIRECTORY USER_DIR  as 'C:\TEMP'
/


set serveroutput on



DECLARE
V1 VARCHAR2(20000);
V2 VARCHAR2(20000);
SQLSTMT VARCHAR2(20000);
EMP_FILE UTL_FILE.FILE_TYPE;
BEGIN
EMP_FILE:=UTL_FILE.FOPEN('USER_DIR','EMPLOYEE.CSV','R');
LOOP
BEGIN
UTL_FILE.GET_LINE(EMP_FILE,V1);
DBMS_OUTPUT.PUT_LINE(V1);
SELECT REPLACE(V1,',',''',''') INTO V2 FROM DUAL;
SQLSTMT:='INSERT INTO EMPCOPY VALUES('''||V2||''')';
EXECUTE  IMMEDIATE  SQLSTMT;
--DBMS_OUTPUT.PUT_LINE(SQLSTMT);
EXCEPTION WHEN NO_DATA_FOUND  THEN EXIT;
END;
END  LOOP;
UTL_FILE.FCLOSE(EMP_FILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR  '||TO_CHAR(SQLCODE)||'  '||SQLERRM);
END;
/




--WRITING DATA FROM PROCEDURE TO FILE  AT OS LEVEL USE HR SCHEMA

CONN / AS SYSDBA

GRANT EXECUTE ON UTL_FILE TO HR
/
GRANT CREATE ANY DIRECTORY TO HR
/
conn hr/hr


set serveroutput on


create or replace directory USER_DIR as 'D:\'
/

CREATE OR REPLACE PROCEDURE UTL_PROC(PDEPTNO  IN NUMBER)
IS
EMP_FILE UTL_FILE.FILE_TYPE;
CURSOR C1 IS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=PDEPTNO;
I  C1%ROWTYPE;
BEGIN
EMP_FILE:=UTL_FILE.FOPEN('USER_DIR','DATA_FROM_PROCEDURE.TXT','A');
OPEN C1;
LOOP
FETCH  C1 INTO I;
EXIT  WHEN C1%NOTFOUND;
UTL_FILE.PUT_LINE(EMP_FILE,I.EMPLOYEE_ID||','||I.FIRST_NAME||','||I.LAST_NAME||','||I.EMAIL||','||

I.PHONE_NUMBER||','||I.HIRE_DATE||','||I.JOB_ID||','||I.SALARY||','||I.COMMISSION_PCT||','||

I.MANAGER_ID||','||I.DEPARTMENT_ID);
UTL_FILE.NEW_LINE(EMP_FILE,3);
END LOOP;
UTL_FILE.FCLOSE(EMP_FILE);
DBMS_OUTPUT.PUT_LINE('THE FILE IS CREATED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS '||SQLCODE||' '||TO_CHAR(SQLERRM));
END UTL_PROC;
/

--EXECUTE THE PROCEDURE TO CHECK IT IS WRITING IN THE TEXT FILE OR NOT

EXEC UTL_PROC(&DEPTID_VALUES_BETWEEN_10_270);



--HAPPY LEARNING--------- YOU  CAN DOWNLOAD  THE FILE FROM  HERE DOWNLOAD