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.