Home » SQL & PL/SQL » SQL & PL/SQL » regarding file access in pl/sql procedure
regarding file access in pl/sql procedure [message #37491] Mon, 11 February 2002 20:43 Go to next message
brindha
Messages: 1
Registered: February 2002
Junior Member
I have one '.dat' file & i want to access that file
thru pl/sql procedure.

Is there any possibility to fetch records from that file which contains no comma seperated values, indefinite sequence of numbers.

kindly help me with ur valuable suggestions if possible with example.
Re: regarding file access in pl/sql procedure [message #37492 is a reply to message #37491] Mon, 11 February 2002 23:50 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
use utl_file package to acess the flat file
Re: regarding file access in pl/sql procedure [message #37511 is a reply to message #37491] Tue, 12 February 2002 11:33 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
file must be on server, path or * must be in init.ora

set serveroutput on

DECLARE
   test_file   UTL_FILE.file_type;
   data_in     VARCHAR2 (200);
   errmsg      VARCHAR2 (240); -- dbms_ouput = max 255
BEGIN
   IF UTL_FILE.is_open (test_file)
   THEN
      UTL_FILE.fclose (test_file);
   END IF;

   test_file := UTL_FILE.fopen ('/tmp', 'hello.dat', 'r');

   LOOP
      BEGIN
         UTL_FILE.get_line (test_file, data_in);
         DBMS_OUTPUT.put_line ('record data : '|| data_in);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('no more records');
            EXIT;
      END;
   END LOOP;
EXCEPTION
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (-20500, 'Invalid Path or Path not in init.ora');
END;
/
Re: regarding file access in pl/sql procedure [message #38166 is a reply to message #37511] Tue, 26 March 2002 23:10 Go to previous message
sasha
Messages: 7
Registered: August 2000
Junior Member
Thanks, I'm still learning PL/SQL but you guys help tons.. :)
Previous Topic: Remove duplicates when inserting !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Urgent
Next Topic: SQL question - simple
Goto Forum:
  


Current Time: Fri Apr 19 14:32:14 CDT 2024