hi,
i wana know how can i transfer data from text file in oracle database. it is like stagging. please tell both ways like coding of plsql or any other oracle utility to do this
rizwan
Usual method is using Oracle SQLLoader. You will have to create a Control file defining the stucture of your text file and issue an SQL Loader command on operation system command line.
It is a bit complex to explain vie short message, I offer to look up the SqlLoader manual.
There is a more dirty, and processor - hungry method too, your DBA will eat you out of world doing this on some thousands of records on a multi user database: Generate insert commands from your data, and run these commands as an sql command file. I mean, if your data are something:
AAAA, 1, b
BBBBK, 34, ef
you can use insert sql commands generated into an sql file:
INSERT INTO table_name VALUES (value1, value2,....)
or
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)
If your table is 'my_table' with 3 columns approprieta data type and size, and you have at least insert grant on it you can apply e.g.:
insert into my_table (first_col,my_number,mycode) values ('AAAA',1,'b');
insert into my_table (first_col,my_number,mycode) values ('BBBBK',34,'ef');
etc...
commit;
String literals have to be between apostrophes, numericals not, and don't forget the semicolon on the end of the rows.
Using loader is less hard, but you MUST learn how to create a control file for your data first.