-
How to prepare an insert script from pl/sql procedure
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
-
Re: How to prepare an insert script from pl/sql procedure
Originally posted by parijat67 Pls mention the right approach to solve this problem.
USE exp/imp utility
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
can not use exp/imp
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
-
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.
-
Re: How to prepare an insert script from pl/sql procedure
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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
-
get very intimate with dbms_sql...
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|