How to prepare an insert script from pl/sql procedure
Thanks a lot for your earlier response!
Here, I am discussing a new problem regarding the creation of a script through PL/SQL procedure to store all the exisitng records of a given table(A) in the form of an insert stmt. This script would later be run to insert all the exisitng records into a new table(B).
Its more like copying the contents of table A into table B where A & B have the same table structure. The output of the pl/sql procedure would be the script file. If the table A contains 100 records then the newly generated script should have 100 insert stmts. One INSERT stmt for each record in the Table A. So that we can use this script to populate Table B by changing only the table name within the insert clause.
The procedure needs to be generic and as such the table names (A & B) will be passed through parameters and the column names of these tables can not be hardcoded inside the procedure. The client would just provide the names of the two tables A & B to the procedure. The procedure should be able to find out the column names and construct the insert stmts properly and then put it in a script on the db server. The validation for date format, handling quotes etc should be done inside the procedure.
Pls mention the right approach to solve this problem.
When you use exp to create an export file, that file does in fact contain sql statements for creating you database and inserting records into your database. If you don't believe me, try editing one. You can also do a "more exp.dmp" - unix or "type exp.dmp | more" - windows.
However, I'm guessing that what you are really struggling with is how to write SQL. If this is the case, then read a book about SQL. You might try reading "SQL for mere mortals." There are certainly other books out there.
If you aren't having trouble with the SQL then I probably don't understand your question.