CODEWITHSUNDEEP
databaseoracle-databaseoracle11gdynamicquery
rav
rav

Reputation: 247

Oracle: update multiple columns with dynamic query

I am trying to update all the columns of type NVARCHAR2 to some random string in my database. I iterated through all the columns in the database of type nvarchar2 and executed an update statement for each column.

for i in (
    select 
        table_name,
        column_name 
    from 
        user_tab_columns
    where 
        data_type = 'NVARCHAR2'
    ) loop
execute immediate 
    'update ' || i.table_name || 'set ' || i.column_name ||
    ' = DBMS_RANDOM.STRING(''X'', length('|| i.column_name ||'))
    where ' || i.column_name || ' is not null';

Instead of running an update statement for every column of type nvarchar2, I want to update all the nvarchar columns of a particular table with a single update statement for efficiency(that is, one update statement per 1 table). For this, I tried to bulk collect all the nvarchar columns in a table, into a temporary storage. But, I am stuck at writing a dynamic update statement for this. Could you please help me with this? Thanks in advance!

Upvotes: 2

Views: 5800

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

You can try this one. However, depending on your table it could be not the fastest solution.

for aTable in (
    select table_name,
        listagg(column_name||' = nvl2('||column_name||', DBMS_RANDOM.STRING(''XX'', length('||column_name||')), NULL)') WITHIN GROUP (ORDER BY column_name) as upd,
        listagg(column_name) WITHIN GROUP (ORDER BY column_name) as con
    from user_tab_columns 
    where DATA_TYPE = 'NVARCHAR2'
    group by table_name
  ) loop

    execute immediate 
       'UPDATE '||aTable.table_name ||
       ' SET '||aTable.upd ||
       ' WHERE COALESCE('||aTable.con||') IS NOT NULL';

end loop;

Resulting UPDATE (verify with DBMS_OUTPUT.PUT_LINE(..)) should look like this:

UPDATE MY_TABLE SET 
   COL_A = nvl2(COL_A, DBMS_RANDOM.STRING('XX', length(COL_A)), NULL),
   COL_B = nvl2(COL_B, DBMS_RANDOM.STRING('XX', length(COL_B)), NULL)
WHERE COALESCE(COL_A, COL_B) IS NOT NULL;

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23381

I think that would do it. But as I said in the comments, you need to validate the syntax since I don't have an Oracle instance to test it.

for i in (
    select table_name,
           'update || i.table_name || set ' || 
             listagg( column_name || '= NLV( ' || column_name || ', ' 
               || 'DBMS_RANDOM.STRING(''X'', length('|| column_name ||') ) )'
               || ';'
             ) WITHIN GROUP (ORDER BY column_name) as updCommand
    from user_tab_columns 
    where DATA_TYPE = 'NVARCHAR2'
    group by table_name
  ) loop

    execute immediate i.updCommand;

end loop;

If you find any error, let me know in the comments so I can fix it.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Please try this:

DECLARE
    CURSOR CUR IS
        SELECT
            TABLE_NAME, 
            LISTAGG(COLUMN_NAME||' = DBMS_RANDOM.STRING(''X'', length(NVL('||
            COLUMN_NAME ||',''A''))',', ')
            WITHIN GROUP (ORDER BY COLUMN_ID) COLUMN_NAME
        FROM DBA_TAB_COLUMNS 
        WHERE DATA_TYPE = 'NVARCHAR2'
        GROUP BY TABLE_NAME;
    TYPE TAB IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
    T TAB;
    S VARCHAR2(4000);
BEGIN
    OPEN CUR;
    LOOP
        FETCH CUR BULK COLLECT INTO T LIMIT 1000;
        EXIT WHEN T.COUNT = 0;
        FOR i IN 1..T.COUNT LOOP
            S := 'UPDATE ' || T(i).TABLE_NAME || ' SET ' || T(i).COLUMN_NAME;
            EXECUTE IMMEDIATE S;
        END LOOP;
    END LOOP;
    COMMIT;
END;
/

Upvotes: 1

Related Questions