Oracle: update multiple columns with dynamic query

Question

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!

have an inner loop for all the columns of a specific table, and append columns to update. Is this something you have to run repeatedly, or one time? If one time, just write a script that emits another script with the updates, then run the second script. — OldProgrammer, Dec 02 '16 at 16:25
Appending the columns to 'SET' is where I got stuck. It is not possible to write a select inside the set. Do you have any idea on how to append? — rav, Dec 02 '16 at 16:30
Create the update with a string, append to that, then execute immediate on the string. — OldProgrammer, Dec 02 '16 at 16:39
It is possible to do it with just a single select. Using listagg and group by. I could answer it to you, but I can't garante that it will be with perfect syntax since I do not have an oracle instance to test it. — Jorge Campos, Dec 02 '16 at 16:44
@JorgeCampos thanks for the reply, any help you can do is appreciated. — rav, Dec 02 '16 at 16:54
How you are going to update only rows with not null in multicolumn update mode? — Konstantin Sorokin, Dec 02 '16 at 16:54
@KonstantinSorokin you won't add an where, instead you update every column with NVL or COALESCE :) — Jorge Campos, Dec 02 '16 at 16:57
@KonstantinSorokin something like `...SET column1 = NVL ( column1, 'random string')...` — mustaccio, Dec 02 '16 at 16:57
@JorgeCampos thanks! `col = nvl2(col, rand, null)` seems suitable — Konstantin Sorokin, Dec 02 '16 at 17:08
How about checking that columns belong to a table, not view? — Konstantin Sorokin, Dec 02 '16 at 17:53

Wernfried Domscheit · Answer 1 · 2016-12-02T20:18:03.590

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;

Gurwinder Singh · Answer 2 · 2016-12-02T17:17:40.523

1

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;
/

edited Dec 02 '16 at 17:17

answered Dec 02 '16 at 17:02

Gurwinder Singh

38,557
6
51
76

@rav the NVL part is to take care of the null values in multi-column update. If it's not suitable for you, then you have to do it one by one. no other go. – Gurwinder Singh Dec 02 '16 at 17:27
Thanks for the effort. Appreciated! – rav Dec 02 '16 at 17:30
`''A''))'` -> `''A'')))'`. Add analogue of `where colname is not null` filter, please. – Konstantin Sorokin Dec 02 '16 at 17:49
@Konstantin I already submitted that it cant be done. See my comment above. – Gurwinder Singh Dec 02 '16 at 18:12
how about `col = nvl2(col, dbms_random.string('x', nvl(length(col, 0)), null)`? – Konstantin Sorokin Dec 02 '16 at 18:20

score 1 · Answer 3 · edited Dec 02 '16 at 19:20

1

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.

edited Dec 02 '16 at 19:20

Sentinel

6,379
1
18
23

answered Dec 02 '16 at 17:39

Jorge Campos

22,647
7
56
87

`within group` is absent – Konstantin Sorokin Dec 02 '16 at 17:49
Error report - SQL Error: ORA-02000: missing WITHIN keyword 02000. 00000 - "missing %s keyword" – rav Dec 02 '16 at 17:50
@rav Fixed it. Try now please. – Jorge Campos Dec 02 '16 at 17:53
Error at Command Line : 11 Column : 3 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:*Action: Error starting at line : 15 in command - end loop Error report - Unknown Command – rav Dec 02 '16 at 18:11
1

The OP wanted to update columns that contained data, so the NVL function is not necessary, though NVL2 would avoid errors when the column is null. Also to avoid unnecessary updates where all columns are null, use listagg a second time to build the where clause: `' WHERE '|| LISTAGG(column_name||' is not null', ' OR ') within group (order by column_id)` – Sentinel Dec 02 '16 at 19:41

Oracle: update multiple columns with dynamic query

3 Answers3