How to prepare an insert script from pl/sql procedure

[parijat67]

Hi,

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.

thanks

Parijat paul

[LKBrwn_DBA]

Originally posted by parijat67 Pls mention the right approach to solve this problem.

USE exp/imp utility

[parijat67]

hi,

Exp/Imp can not give me the script. I need a script as the output. The script should contain all the insert statements and a commit at the end.

thanks
Parijat Paul

[gandolf989]

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.

[LKBrwn_DBA]

You originally asked for:

Originally posted by parijat67
the right approach to solve this problem

The right approach is not to generate INSERT statements.

Another better option also would be to create a comma/tab/or pipe delimited file and use sql loader.

[hacketta1]

You want a generic procedure that will copy a table from one database to another as I understand it. Look into EXECUTE IMMEDIATE, CREATE TABLE AS SELECT, and DATABASE LINKS.

e.g.

create database link
create table y as select * from y@dblink

[marist89]

get very intimate with dbms_sql...