Re: Defining an input file in a PL/SQL program

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Tue, 15 Dec 1998 11:38:57 +0800
Message-ID: <3675D9D1.2A49@bhp.com.au>

Maria G wrote:
>
> I am looking for a way to define an input file in a PL/SQL program.
>
> I was able to code a PL/SQL program to read a record from a file and
> write this record to a file using the UTL_FILE package. The problem
> I'm having isI would like to make the program a little more
> intelligent and process the input record based on different fields
> within the record (i.e. employees last name -- positions 20 to 50;
> employees first name -- positions 51 to 61, etc.). Is there any way
> within PL/SQL to define the record layout of the input file? The
> documentation I've found only shows how to read an entire line of data
> from the file into the line buffer using the GET_LINE procedure. It
> doesn't show how to redefine this entire line of data into individual
> fields based on the positioning of the data (i.e. employees last name
> -- positions 20 to 50; employees first name -- positions 51 to 61,
> etc.).
>
> Thanks for your help!!

Without having tried this, I suppose one way would be:

1. Have a plsql table of records to store definition info type field is record (name varchar2, start_pos number, field_len number); type array is table of field index by binary_integer; num_fields number; def array;
2. As each record is read from the data file...

	for i in 1 .. num_fields loop
		field_value := substr(row_of_data, 
				def(i).start_pos, 
				def(i).field_len);
	end loop;

You get the basic idea...

HTH
--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"

• Salvador Dali

Received on Mon Dec 14 1998 - 21:38:57 CST