This discussion is archived
8 Replies Latest reply: Aug 4, 2013 3:03 PM by 956320 RSS

Dynamic Insert procedure

956320 Newbie
Currently Being Moderated

    
Hi Everyone,

 

I am new so may be I could not explain well but here is my issue

 

I have a product_master table having product_code as a primary key  and few product_detail tables like   product_detail1,product_detail2, and so on having product_code as a foreign key.
What I want is write a procedure that will open a new  product with old information , it   accepts two parameters (old_product_code,  new_product code) means it open a new product with new product code but with all records information of old_product_code
but it should be dynamically i dont want to specify tables and columns

 

What I thinking was that I can choose all tables names   by
@   select Tname from tab where tname like 'Product%' :


and create some dynamic sql insert like

 

@ execute immediate 'insert into   '  || table_name_dynamically || ' select * from ' ||  table_name_dynamically || '  where product_code= old_product_code' ;

but it will insert the  old product code i want it with new code

 

I am using oracle database 11g version

 

 

 

Thanks

  • 1. Re: Dynamic Insert procedure
    rp0428 Guru
    Currently Being Moderated

    956320 wrote:

     

        
    Hi Everyone,

     

    I am new so may be I could not explain well but here is my issue

     

    I have a product_master table having product_code as a primary key  and few product_detail tables like   product_detail1,product_detail2, and so on having product_code as a foreign key.
    What I want is write a procedure that will open a new  product with old information , it   accepts two parameters (old_product_code,  new_product code) means it open a new product with new product code but with all records information of old_product_code
    but it should be dynamically i dont want to specify tables and columns

     

    What I thinking was that I can choose all tables names   by
    @   select Tname from tab where tname like 'Product%' :


    and create some dynamic sql insert like

     

    @ execute immediate 'insert into   '  || table_name_dynamically || ' select * from ' ||  table_name_dynamically ;

    but it will insert the  old product code i want it with new code

     

    I am using oracle database 11g version

     

    I also read about invisible columns in 12 c any alternative to that in oracle 11g

    Nothing in your question has anything to do with 'invisible' columns. If you have a question about those create a new thread for it.

     

    You need to specify the table names or the code won't know what tables to work with.

     

    You can create a variable (e.g. myMaster) of type 'PRODUCT_MASTER%ROWTYPE'. Then SELECT * INTO myMaster where myKey = old_product_code.

     

    Then replace the 'old_product_code' value with 'new_product_code' and insert the row back into the table.

  • 2. Re: Dynamic Insert procedure
    956320 Newbie
    Currently Being Moderated

    Thanks for quick reply

     

    sorry about invisible columns it will be confusing so I remove it

     

    I want to insert in  my all products table (master and details both)

     

    Do i have to create a variable for each table or it can be make dynamically also and how to replace in the end can you gave an example

     

    Thanks

  • 3. Re: Dynamic Insert procedure
    956320 Newbie
    Currently Being Moderated

    I will also appreciate if there is another technique please share it also

     

    Thanks

  • 4. Re: Dynamic Insert procedure
    rp0428 Guru
    Currently Being Moderated

    956320 wrote:

     

    Thanks for quick reply

     

    sorry about invisible columns it will be confusing so I remove it

     

    I want to insert in  my all products table (master and details both)

     

    Do i have to create a variable for each table or it can be make dynamically also and how to replace in the end can you gave an example

     

    Thanks

    Yes - create a variable for each table.

     

    See Example 5-38 in the %ROWTYPE Attribute section of the PL/SQL Language doc

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#i6079

     

    %ROWTYPE Attribute

    The %ROWTYPE attribute lets you declare a record variable that represents either a full or partial row of a database table or view.

  • 5. Re: Dynamic Insert procedure
    Ishan Journeyer
    Currently Being Moderated

    It would be good if you could paste the table structure and the exact output you are looking for.

     

    From the look of it, it doesn't seem to be a complex problem, would be easier to understand if we have the exact requirement clear.

  • 6. Re: Dynamic Insert procedure
    956320 Newbie
    Currently Being Moderated

    I will made a simple structure for better understanding

     

    create table product_master (product_code   varchar2(100)  , col1 varchar2(50), col2 number, col3 date )

    /

    create table product_detail   (product_code   varchar2(100),   col4 varchar2(50),   col5   number,col6 date )

    /

    create table product_detail1 (product_code   varchar2(100),  col7 varchar2(50),  col8  number,col9 date ,col10 number , col11 varchar2(10) )

    /

    insert into  product_master  values ( '1', 'ABC', 1011, '01-JAN-2009');

    insert into  product_master  values ( '2', DEF', 2011, '01-JAN-2010');

    /

    insert into  product_detail  values   ( '1', 'X', 9979, '11-DEC-2009');

    insert into  product_detail  values   ( '1', 'Y', 1345, '11-DEC-2009');

    insert into  product_detail  values   ( '2', 'Z', 1547, '19-DEC-2009');

    /

    insert into  product_detail1  values   ( '1', 'IT',  8877, '11-DEC-2009' ,112,'U');

    insert into  product_detail1  values   ( '1', 'HI',  4578, '11-DEC-2009', 103,'I');

    insert into  product_detail1  values   ( '2', 'GO', 51748, '19-DEC-2009',140,'O');



    What I want is to write a procedure which copies the existing product with new product code and all old information associated with it. (Duplication with new code)
    Procedure accepts two parameter 1) old code 2) New Code

     

    My_Procedure ('1','13');

     

    Result will be like

     

    insert into  product_master  values ( '13', 'ABC', 1011, '01-JAN-2009');

    insert into  product_detail  values   ( '13', 'X', 9979, '11-DEC-2009');

    insert into  product_detail  values   ( '13', 'Y', 1345, '11-DEC-2009');

    insert into  product_detail1  values   ( '13', 'IT',  8877, '11-DEC-2009' ,112,'U');

    insert into  product_detail1  values   ( '13', 'HI',  4578, '11-DEC-2009', 103,'I');

     

     

    There can be more detail tables so i don't want to hardcoded the tables names nor column names,  tables names can found from 
    select Tname from tab where tname like 'Product%' ;


    Thanks

     

     



  • 7. Re: Dynamic Insert procedure
    rp0428 Guru
    Currently Being Moderated

    There can be more detail tables so i don't want to hardcoded the tables names nor column names,  tables names can found from

    select Tname from tab where tname like 'Product%' ;


     

    Whether you want to hardcode the table names is rather irrevelant. You have to hardcode the table names in order to make a copy of the rows into variables that use %ROWTYPE. You also need to hardcode the column names for the primary key columns so you can assign the new key values.

     

    You CANNOT query an existing row and then insert a duplicate row into the table when there is a primary key.

     

    And you CANNOT substitute a new primary key without first storing the copy of the existing row somewhere first.

     

    And you CANNOT store a copy of the existing row in a variable that is named dynamically unless you use an implicit cursor.

     

    The forum is NOT a coding service where people do your work for free. The forum is to help you with YOUR code.

     

    We have told you the approach you need to take.

     

    You need to put your fingers on the keyboard and try to write the code yourself. If you then need help with that code post the code and tell us what you need help with.

     

    Don't be afraid of breaking Oracle by actually trying things.

  • 8. Re: Dynamic Insert procedure
    956320 Newbie
    Currently Being Moderated

    Thanks a lot for the guidance

     

     

    I wrote it and it seems that it is working good till now

     

    CREATE OR REPLACE PROCEDURE  copy_proc ( Old_PC VARCHAR2, NEW_PC VARCHAR2) IS

      SQL_ST  VARCHAR2(1000);

       BEGIN 

     

    FOR O IN ( select tname from tab where tname like 'PRODUCT%' order by 1 desc )

      LOOP

      SQL_ST :=  ' BEGIN               FOR I IN  (SELECT * FROM  ' ||  O.TNAME ||  '   WHERE PRODUCT_CODE = ' || Old_pc || '  )

                          LOOP

                           i.PRODUCT_CODE := ' || NEW_PC || ' ;

       INSERT INTO  ' || O.TNAME || ' VALUES i ; cOMMIT ;

       END LOOP ;

      END ; '  ;

       EXECUTE IMMEDIATE sql_st;

       END LOOP;

       END ;

     

    I ask questions here because there are experts here and they will guide very well. but it does not meant that I am not working , I regularly  try to break  Oracle by trying multiple things .

     

    Anyways once again thanks a lot

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points