Home » SQL & PL/SQL » SQL & PL/SQL » Reading into a Defined structure (merged)
Reading into a Defined structure (merged) [message #384179] Mon, 02 February 2009 10:16 Go to next message
dbina
Messages: 1
Registered: February 2009
Location: Pittsburgh
Junior Member
OK, I am new to PL/SQL and have been requested to convert UNIX COBOL programs to PL/SQL programs. When all is said and done we will FTP a text file to a server, then trigger a script and execute the PL/SQL. I need to read the text file into a sturctured layout this is where I am having trouble (not may examples that I have found).

Here is my code so far.

DECLARE
v_inputfile UTL_FILE.FILE_TYPE; ---filehandle
--v_inputrec VARCHAR2(21); --Input Record
v_dir VARCHAR(41) := '/oracle/app/oracle/admin/#####/adhoc/util';
v_name VARCHAR2(12) := 'TESTFILE.TXT'; --file name

type r_input_rec is Record
(r1_WORKS VARCHAR2(3),
r1_INVC_NUM VARCHAR2(6),
r1_RMS_INVC_NUM VARCHAR2(6),
r1_INVC_DT VARCHAR2(6));

r_Rec r_input_rec;

v_readctr NUMBER :=0;
v_WORKS VARCHAR2(3);
v_INVC_NUM VARCHAR2(6);
v_INVC_DT VARCHAR2(6);
/*-----------------*
* M A I N *
*-----------------*/
BEGIN
v_commit_ctr := 0;

--OPEN FILE
DBMS_OUTPUT.PUT_LINE ('Opening file '||v_dir||'/'||v_name);
v_inputfile := UTL_FILE.FOPEN(v_dir,v_name,'r',21); --open file

<<READ_LOOP>>
LOOP
-- DBMS_OUTPUT.PUT_LINE ('Reading file');
BEGIN
UTL_FILE.GET_LINE(v_inputfile,r_input_rec); --get record from file
v_readctr := v_readctr + 1;

The UTL_FILE.GET_LINE will not let me use the r_input_rec. Again I am new to PL/SQL, I generally do Mainframe programming. Any suggetions on how to read the text file directly into my record layout.
Re: Reading into a Defined structure [message #384183 is a reply to message #384179] Mon, 02 February 2009 10:25 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I would suggest to not re-invent the wheel.

What you are trying to do can be done with External Tables or with SQL*Loader.

They are both part of the Oracle Utilities.
Previous Topic: Help on an Update statement for select records
Next Topic: Determine rows without a Date/Time column?
Goto Forum:
  


Current Time: Mon Dec 05 23:46:19 CST 2016

Total time taken to generate the page: 0.17493 seconds