Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I read/write data files (text file) from PL/SQL Script

Warren TolentinoOct 7 2005 — edited Oct 11 2005
I had an oracle forms pl/sql program to read/write a data file (text file). When this code is run on a command line as a PL/SQL script using the SQL*Plus I am getting an error:

-- sample.sql
DECLARE
  vLocation  			VARCHAR2(50)  := 'r:\';
  vFilename  			VARCHAR2(100) := 'sample.dat';
  vTio    			TEXT_IO.FILE_TYPE;
  vLinebuf			VARCHAR2(2000);
  vRownum			NUMBER        := 0;

  -- use array to store data FROM each line of the text file	
  TYPE 		array_type IS VARRAY(15) OF VARCHAR2(100);
  vColumn 	array_type := array_type('');
  --
  		
  PROCEDURE prc_open_file(p_filename IN VARCHAR, p_access IN VARCHAR2) is 
  BEGIN
    vTio := TEXT_IO.FOPEN(vLocation||p_filename,p_access);
  EXCEPTION 
    WHEN OTHERS then 
      --  raise_application_error(-20000,'Unable to open '||p_filename);
      message(sqlerrm);pause;
  END;
  
  PROCEDURE prc_close_file is 
  BEGIN
    IF TEXT_IO.IS_OPEN(vTio) then 
       TEXT_IO.FCLOSE(vTio); 
    END IF;
  END;
   
BEGIN 
  	
  --extend AND initialize the array to 4 columns
  vColumn.EXTEND(4,1);
  --
  prc_open_file(vFilename,'r');
    
  LOOP
      LTEXT_IO.GET_LINE(vTio,vLinebuf);

      vColumn(1)  := SUBSTR(vLineBuf, 1, 3);
      vColumn(2)  := SUBSTR(vLineBuf, 5, 8);
      vColumn(3)  := SUBSTR(vLineBuf,10,14);      

      Insert Into MySampleTable
      Values
        (vColumn(1), vColumn(2), vColumn(3));

      EXIT WHEN vLinebuf IS NULL;

   END LOOP;
   prc_close_file;
END;
/
SQL> @c:\myworkspace\sql\scripts\sample.sql;

PLS-00201: identifier 'TEXT_IO.FILE_TYPE' must be declared
It works on the oracle forms but not on the SQL*Plus. Is there an alternative method using a PL/SQL script? A simple sample would help. Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 8 2005
Added on Oct 7 2005
5 comments
1,473 views