CODEWITHSUNDEEP
encodingdatabasepackageoracle-database
mahmoud alsaadi
mahmoud alsaadi

Reputation:

Sending data in a file.txt by oracle stored procedure using utl_file package

I want to send data in a file.txt to a table in database by oracle store procedure using utl_file package,

the file.txt contain: 

QRIFR6M Index,BID,0.7875, aaa
QRIFR9M Index,BID,0.8625, bbb
QRIFR1Y Index,BID,0.975, ccc
QRIFROVN Index,ASK,0.74375, ddd
QRIFR1W Index,ASK,0.78125, hhh
QRIFR1M Index,ASK,0.90625, hhh

I wrote this code to open the file and close it, but now how to read the data? where to write the code for read and send data? I have to use array or there is better solution? 

CREATE DIRECTORY sampledata AS 'c:sampledata';
declare
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('SAMPLEDATA','test.txt','R');
utl_file.get_line(f,s);
utl_file.fclose(f);
dbms_output.put_line(s);
end;

Thanks and Regards

Upvotes: 2

Views: 627

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

If you want to stay in the "oracle environment" (only sql and plsql - no batch files), then you might consider using "External Tables". These allow you to read the file as if it was a table.
http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
http://www.adp-gmbh.ch/ora/misc/ext_table.html

Upvotes: 2

John Doyle
John Doyle

Reputation: 7793

I would recommend using SQL*Loader instead. See this FAQ on it. utl_file.get_line is fine but you will then have to split the input to get the values for your table inserts.

Upvotes: 1

Related Questions