UTL_FILE

-------------------------------------

--Get the Path, which is registered for UTL_FILE
select value from v$parameter where name like 'utl%file%'
---------------
/usr/tmp


--Create Table to Hold the Data
create table names
(
 name varchar2(30)
);


--Create anonymous plsql block to insert data into table
SET SERVEROUTPUT ON;
DECLARE
  line VARCHAR2(100);
  namesfile UTL_FILE.FILE_TYPE;
BEGIN
  --  Syntax : FOPEN ( directory alias, filename, open mode)
  namesfile := UTL_FILE.FOPEN('/usr/tmp','abc.txt','R'); -- open in read mode

  --UTL_FILE.('/tmp','xyz.txt','R'); -- open in read mode

  LOOP
    -- read  a line from NAMES.TXT
    -- NO_DATA_FOUND exception occurs when you reach end of file
    -- Syntax : GET_LINE( filename, variable , max length)
   
    begin
   
    UTL_FILE.GET_LINE(namesfile,line,100);
   
    INSERT INTO names VALUES
      ( line
      ); -- insert into NAMES table

    exception
    when no_data_found then
    return;
    end;

commit;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
--  UTL_FILE.FCLOSE(namesfile); -- close file
--WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

SELECT * FROM names;


DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
  SELECT name FROM per_jobs where rownum <= 10;
BEGIN
 -- OPEN FILE FOR WRITING
 JOBSFILE := UTL_FILE.FOPEN('/usr/tmp','job.txt','W');

 -- TAKE ONE RECORD FROM CURSOR
 FOR REC IN JOBSCUR
 LOOP
   UTL_FILE.PUT_LINE(JOBSFILE, REC.name);  -- WRITE INTO FILE
 END LOOP;

 UTL_FILE.FCLOSE(JOBSFILE);  -- CLOSE FILE

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);
END;



----------------


For Windows with XE


grant execute on UTL_FILE to hr;

create directory filesdir as 'c:\oracle';

GRANT READ ON DIRECTORY FILESDIR TO HR;

grant write on directory filesdir to hr;

create table names
(
 name varchar2(30)
);

SET SERVEROUTPUT ON;
DECLARE
  line VARCHAR2(100);
  namesfile UTL_FILE.FILE_TYPE;
BEGIN
  --  Syntax : FOPEN ( directory alias, filename, open mode)
  namesfile := UTL_FILE.FOPEN('FILESDIR','NAMES.TXT','R'); -- open in read mode
  LOOP
    -- read  a line from NAMES.TXT
    -- NO_DATA_FOUND exception occurs when you reach end of file
    -- Syntax : GET_LINE( filename, variable , max length)
    UTL_FILE.GET_LINE(namesfile,line,100);
    INSERT INTO names VALUES
      ( line
      ); -- insert into NAMES table
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  UTL_FILE.FCLOSE(namesfile); -- close file
--WHEN OTHERS THEN
--  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

SELECT * FROM names;

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
  SELECT JOB_TITLE FROM JOBS ORDER BY JOB_TITLE;
BEGIN
 -- OPEN FILE FOR WRITING
 JOBSFILE := UTL_FILE.FOPEN('FILESDIR','JOBS.TXT','W');

 -- TAKE ONE RECORD FROM CURSOR
 FOR REC IN JOBSCUR
 LOOP
   UTL_FILE.PUT_LINE(JOBSFILE, REC.JOB_TITLE);  -- WRITE INTO FILE
 END LOOP;

 UTL_FILE.FCLOSE(JOBSFILE);  -- CLOSE FILE

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);
END;