How to prepare an insert script from pl/sql procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to prepare an insert script from pl/sql procedure

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Cool 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

  3. #3
    Join Date
    Dec 2001
    Posts
    120

    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    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.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Cool 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

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    get very intimate with dbms_sql...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width