Hi

My Req is "data will be taken into a flat file from one database. I need to read the data from this flat file and to put into another database."

I would like to write a procedure (pl/sql) for this.
Any ideas of how it cud b done.

Use UTL_FILE Package to load data into a flat file and then use the same UTL_FILE to read that data from the Flat File to the DB of your choice....

Good Luck!!

Thats good idea..

Any sample script available for that...coz some care to be taken when handling null values.

1. The file to be imported may be comma separated
2. The file to be imported may be tab separated.
3. The file to be imported may be some spaces separated.

Is there any other way except reading the entries line by line ?

You have to specify what specify what separated the file is. You have to create a dynamic PL/SQL to handle that, there is no easy way out.
For the second part of your question about reading file in totality rather than line by line... I GUESS NOT.
I CAN give you sample file for reading any "separated" file but I guess you use your sweat a bit to find out that, isn't life full of thorns ? If you can't get to it please let me know I'll create a Proc for you. Best Of luck...

thanx arin for ur support

cud u send me the sample file for reading any "separated" file .

If it serves my purpose i use it or i will make changes accordingly............and definitely get back to you....with modified script..

Konda,
Its nearing New Year... will be back after New Year to give you the code. Am sorry to dissapoint you. But you cud spare me sometime for myself and my family...
Cheers!!!!!!

This is a function. Now this function will accept table Name and when u give that it's determine what the columns are and will accordingly make the dynamic insert . This is as dynamic as you can get... You aslo have to specify your directory where the data will be along with the file name. BUT BE SURE TO CHANGE UTL_FILE_DIR VALUE IN ur SP FILE AND BOUNCE THE DATABASE.

Cheers!!!!!!!!