Re: PL/SQL and Unix flat files

On 6 Jun 1996 02:30:56 GMT, rtb_at_interaccess.com (Richard Bergdahl) wrote:

>Can PL/SQL read a unix flat file as input? I'm thinking of writing a
>procedure to read in a flat file and update/insert it's contents to a table
>after doing a little string manipulation on each record read. Normally I
>would use Pro*C to do this, but the client seems to have a little C-phobia.
>
>Is this possible with PL/SQL?
>
>Rich

Oracle 7.3 gives you the ability to allow pl/sql to read/write files on the server.

Developer/2000 (eg: forms) 4.5 allows you to do this with any 7.x version.

database pipes (all 7.x versions) can also be used (with a little help from C tho).

7.1 and above plus sql*loader may be all you need however. If you write up pl/sql functions to do a little string manipulation on each field(s), you can use sqlloader to call your pl/sql for you. For example:

create or replace function foo1( p_str in varchar2 ) return varchar2 as
begin
  ......
end;
/

... likewise for foo2 and foo3...

And then use a .ctl like:

LOAD DATA
INFILE *
REPLACE
INTO TABLE DELIMITED_TEST
(

  FIELD1 position(1:4096) "foo1(:field1)",
  FIELD2 position(1:4096) "foo2(:field2)",
  FIELD3 position(1:4096) "foo3(:field3)"

Then each and every record will be send down to foo1, foo2, foo3 which can look at the entire record and do whatever they want. Of course, you don't have to send down the entire record, you can send any combination of fields you want to the stored procs. for example:

LOAD DATA
INFILE *
REPLACE
INTO TABLE DELIMITED_TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

( field1	"foo1( :field1, :field2 )",
  field2        "foo2( :field2, :field3 )",
  field3        "foo3( :field1, :field2, :field3 )"

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Jun 06 1996 - 00:00:00 CEST