I wish to send data inside a file.txt to some 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 authored this code to spread out the file and close it, however how you can browse the data? where you can write the code for read and send data? I must use array or there's 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
I would suggest using SQL*Loader rather. Check this out FAQ onto it.
utl_file.get_line is okay but you'll then need to split the input to obtain the values for the table card inserts.
If you wish to remain in the "oracle atmosphere" (only sql and plsql - no batch files), then you definitely might think about using "Exterior Tables". These permit you to browse the file as though it had been a table.