PL/SQL Help Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PL/SQL Help

  1. #1
    Join Date
    May 2002

    PL/SQL Help

    Here is my task.
    I have main table Table1 (primary key on column Col1 and no foreign key constrains in this table) which is parent for N tables TableChild1, TableChild2, TableChild3, ... TableChildN.
    These N tables aer also perents to some other tables.
    I have to create PL/SQL procedure which will clone all records from these tables from primary key Col1 = Value1 to new Value2.
    So procedure has to have signature like this :

    CloneTablesRecords(tablename VARCHAR2(30), cPrimaryKeyFrom tablename.COL1%TYPE, cPrimaryKeyTo.COL1%TYPE).

    What would be the best approach?


  2. #2
    Join Date
    Apr 2001
    Here's my approach, I don't say it's the best ...
    Starting from the "root" table, insert records with new primary key:
    insert into tablename (primary_key, ... other columns list ...)
    select cPrimaryKeyTo, ... other columns ...
      from tablename
     where primary_key=cPrimaryKeyFrom
    In case the tablename is a variable, use Native Dynamic SQL (execute immediate command).
    The whole difference between a little boy and an adult man is the price of toys

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