--PROGRAM TO READ DATA FROM FILE BY USING utl_file----

HI frndz,,here we c how to read file data in plsql........
--In SHORT THIS IS A FILE HANDLING---
---THIS IS SAME AS .NET ,,c,FILE HANDLING----
--PROGRAM TO READ DATA FROM FILE BY USING utl_file----
------------------------------------------------------------------------------------------------------------------------------------
/*first create a diretctory name as My_DIR(giv any name)  */
CREATE OR REPLACE DIRECTORY My_DIR AS '/u01/dbtst/db/tech_st/11.1.0/appsutil/outbound/TST_pn-vm000001';  (run this script)
/*after creating directory assign some permition to tht directory so any user can access tht directory*/
GRANT read, write ON DIRECTORY ROHIT_DIR TO PUBLIC; ( run this script)
then put your file in that directoy wich u want to read... (here we use "test.txt" file)

----------------------------------------------------------------------------------------------
here is the plsql code to read file data wich is present in directory "My_DIR".
 ------------------------------------------------------------------------------------------------
set serveroutput on
DECLARE
------here we create one object of utl_file.file_type, it name as "handle"(step 1)----

handle  utl_file.file_type;
line  VARCHAR2(250);
BEGIN

--here we open the file in read mode on wich we want to work and assign tht to the our object "handle"(step 2)--
  handle := utl_file.fopen('My_DIR', 'test.txt', 'R');
  LOOP
    BEGIN
  
    --we use utl_file.get_line() function to read file line by line and save tht line data in variable "line" wich is declared above(step 3)--
      utl_file.get_line(handle, line);
      dbms_output.put_line(line);
      --utl_file.get_line() funcion used to write the read data()--
     
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
 
  --after all operation close the file(step 4)--
  utl_file.fclose(handle);
END fopen;
/