1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic SQL - Variable no: columns & data type

Discussion in 'SQL PL/SQL' started by man_expo, Nov 25, 2009.

  1. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I want to generate a dynamic UPDATE statement. Now this statement will update a single value in each column in n number of tables. That is a single value in each row of one column, another single value in each row of another column and so on.

    Code (SQL):
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 1234;
    Now this update statement has to be generated dynamically. Also the values have to be generated at run time.

    I will come to know how many columns in a particular table has to be updated at run time only. I will also come to know about the type of each column at run time

    I can generate the SQL statement string dynamically using for loop. But how can I run this statement. If I had only one column always(or fixed number of columns with its type known beforehand) I could use the following.

    Code (SQL):
    EXECUTE IMMEDIATE sql_stmt USING v_random_Value;
    I have to generate all the below type of update statements:

    Code (SQL):
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 1234;
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 'XYZ';
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 'XYZ'column_name2 = 1234;
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 'XYZ'column_name3 = 1234 column_name4 = 'mno';
    Can I make the EXECUTE IMMEDIATE statement dynamic and run it with another EXECUTE IMMEDIATE?

    Also internally is there any difference in the execution of

    Code (SQL):
    UPDATE table_name1 SET column_name1 = 'abc' column_name2 = 1234;
    and running 2 statements like this

    Code (SQL):
    UPDATE table_name1 SET column_name1 = 'abc';
    UPDATE table_name1 SET column_name2 = 1234;
    Lets say there are 5 lakh rows to update.:eek:

    There will not be a where clause anywhere.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    1)
    If number of input values( Number of columns in a particular table has to be updated ) is One, then col1 has to be updated
    If number of input values is two, then Col1 and Col2 have to be updated.

    If this is the scenario,

    Then, you can do using a script. You can use data dictionary (user_tab_columns) to define the the column positions.

    2 )

    Running all the columns at once will be better in terms of performance

    Code (SQL):

    UPDATE table_name1 SET column_name1 = 'abc' ,
                                        column_name2 = 1234;
     
    A comma was missing in your post.