8 Replies Latest reply: Aug 26, 2013 10:07 AM by oraman RSS

    Dynamic SQL - Update Primary Key for list of tables

    oraman

      hi experts,

       

      I'm stuck with the script. I would like to update all PK columns from the list of tables.

       

      DECLARE

         v_table VARCHAR2(30);

      BEGIN

      select object_name into v_table from user_objects where OBJECT_TYPE = 'TABLE' and object_name like '%_COPY';

       

      selected_column := 'select COLUMN_NAME from USER_CONS_COLUMNS

      where table_name= ' || v_table || ' and CONSTRAINT_NAME = (select CONSTRAINT_NAME from all_constrainTs where table_name= ' || v_table || ' and constraint_type='P')';

       

        EXECUTE IMMEDIATE 'UPDATE ' || v_table || ' SET ' || selected_column || ' = ' || selected_column || ' + 1000000 ';

      commit;

      END;

      /

       

      this script outputs

      ORA-06550

      PLS-00103

       

      I appreciate your help!

       

      oraman

        • 1. Re: Dynamic SQL - Update Primary Key for list of tables
          Karthick_Arp


          What are you going to do about the referential integrity? If you have foreign key referring to the primary key then you will end up with error. Do you have a plan for that? Your requirement seems wierd to me. Can you explain why you are trying to do this. What is the business objective?

          • 2. Re: Dynamic SQL - Update Primary Key for list of tables
            Raunaq

            I have corrected the below line in the code

             

            {code}

            selected_column := 'select COLUMN_NAME from USER_CONS_COLUMNS

            where table_name= ' || v_table || ' and CONSTRAINT_NAME = (select CONSTRAINT_NAME from all_constrainTs where table_name= ' || v_table || ' and constraint_type='||chr(39)||'P'||chr(39)||')';

             

             

             

            {/code}

             

            The issue was with extra ' near P

            • 3. Re: Dynamic SQL - Update Primary Key for list of tables
              Karthick_Arp

              > The issue was with extra ' near P


              The issue is not with the code, its primarily with the requirement. Would it not be a crazy idea to update all the primary key values, please think!!

              • 4. Re: Dynamic SQL - Update Primary Key for list of tables
                Raunaq

                You are right , i agree.

                 

                But considering the person is asking for it , i thought to give him what he wants , and let him think on his own.

                The issue was with code also.

                 

                i have included chr(39) for it.

                • 5. Re: Dynamic SQL - Update Primary Key for list of tables
                  Billy~Verreynne

                  Raunaq wrote:

                   

                  You are right , i agree.

                   

                  But considering the person is asking for it , i thought to give him what he wants , and let him think on his own.

                  What do you call this approach from yours?

                   

                  It is not educating the user.

                  It is not enlightening the user.

                  It is not helping the user in understanding an analytical approach to problems.

                   

                  So just what are you trying to do....??

                  • 6. Re: Dynamic SQL - Update Primary Key for list of tables
                    oraman

                    yes you are right, it would be crazy!

                     

                    sorry, the correct requirement is to update copied tables which dont have constraints. but I need to query the original tables to retrieve the names of the fields - which in original tables have PK and in copied tables havent.

                     

                    this is the qode I tried:

                     

                    DECLARE

                       v_otable VARCHAR2(30);     -- original tables with PK

                       v_ctable VARCHAR2(30);     -- copied tables without PK

                    BEGIN

                    -- retrieve original table names

                    select replace( table_name, '_COPY', '' ) into v_otable from user_tables where table_name like '%_COPY';

                     

                    selected_column := 'select COLUMN_NAME from USER_CONS_COLUMNS

                    where table_name= ' || v_otable || ' and CONSTRAINT_NAME = (select CONSTRAINT_NAME from all_constrainTs where table_name= ' || v_otable || ' and constraint_type='P')';

                     

                    -- update copied tables

                    EXECUTE IMMEDIATE 'UPDATE ' || v_ctable || ' SET ' || selected_column || ' = ' || selected_column || ' + 1000000 ';

                    commit;

                    END;

                    /

                     

                    but once again it gives me

                    ORA-06550

                    PLS-00103

                    • 7. Re: Dynamic SQL - Update Primary Key for list of tables
                      Karthick_Arp

                      How did you copy the tables? And why are you not doing the modification when you copy them. For example if the method you use to copy is CREATE TABLE .. SELECT FROM then you can do it like this

                       

                      CREATE TABLE <your_table>_COPY AS SELECT <your_pk_column> + 1000000, <other_columns> FROM <your_table>

                       

                      Would this not be more simpler and efficient?

                      • 8. Re: Dynamic SQL - Update Primary Key for list of tables
                        oraman

                        I copy the tables with create table as select ...

                         

                        yes this would be simpler, but the problem is that the requirement is to do it automatically, so I dont know the column list.

                        I eather do it with * like

                        create table as select * from

                         

                        or I'm not sure whether it's possible to handle it with sql,  probably only with dinamic sql:

                        I would store a column list for each table into a variable and the PK column into another variable

                         

                        CREATE TABLE <your_table>_COPY AS SELECT var1 + 1000000, var2 FROM <your_table>


                        what would be the best solution?