CODEWITHSUNDEEP
oracle-databaseloopsdynamicplsqlsql-update
user5507535
user5507535

Reputation: 1800

Oracle PL/SQL - Loop value as dynamic column name without dynamic SQL

I would like to update multiple tables that have the same column name with the same value, instead of having an update for each table, and since Oracle doesn't provide a way to update multiple tables at once I though of using a loop for this.

I tried this but it doesn't work as expected:

begin
  for i in (select column_value from table(sys.dbms_debug_vc2coll('tab1', 'tab2'))) loop
    update i set my_col = 'my value';
  end loop;
end;

I know I can use dynamic SQL for this with execute immediate but is there a way to avoid it?

Upvotes: 1

Views: 335

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

The question (and so the problem) looks pretty simple:

update multiple tables that have the same column name with the same value

How about creating a view based on those tables & an instead of trigger which would do the job? Here's how:

Sample tables:

SQL> select * from tab_a;

        ID M
---------- -
         1 x
         2 y

SQL> select * from tab_b;

NA MY_
-- ---
LF www
JW zzz
MC

View:

SQL> create or replace view v_tabs as
  2    select to_char(id) idn, my_col from tab_a
  3    union all
  4    select name           , my_col from tab_b;

View created.

SQL> select * from v_tabs;

IDN                                      MY_
---------------------------------------- ---
1                                        x
2                                        y
LF                                       www
JW                                       zzz
MC

Instead of trigger:

SQL> create or replace trigger trg_tabs
  2    instead of update on v_tabs
  3    for each row
  4  begin
  5    update tab_a set my_col = :new.my_col;
  6    update tab_b set my_col = :new.my_col;
  7  end;
  8  /

Trigger created.

Testing:

SQL> update v_tabs set my_col = 'e';

5 rows updated.

SQL> select * from tab_a;

        ID M
---------- -
         1 e
         2 e

SQL> select * from tab_b;

NA MY_
-- ---
LF e
JW e
MC e

SQL>

All MY_COL values, in all tables involved, are set to e. That's what you asked for, right?

Upvotes: 1

SuperPoney
SuperPoney

Reputation: 660

If you can't use execute immediate try with the DBMS_SQL package: https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i996963

Here is a syntyax sample of how to use this package taken from the doc:

    CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
        cursor_name INTEGER;
        rows_processed INTEGER;
    BEGIN
        cursor_name := dbms_sql.open_cursor;
        DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
        rows_processed := DBMS_SQL.EXECUTE(cursor_name);
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_SQL.CLOSE_CURSOR(cursor_name);
    END;
    /

Upvotes: 0

Related Questions