This discussion is archived
8 Replies Latest reply: Aug 26, 2013 8:07 AM by oraman RSS

Dynamic SQL - Update Primary Key for list of tables

oraman Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated


    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 Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    > 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 Explorer
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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?

Legend

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