Hi Tom,
I want to write an UPDATE statement in which the number of columns may vary for each run based on the user choice.
Eg: DDL
CREATE TABLE "XX_MASK_REF"
( "TABLE_NAME" VARCHAR2(150 BYTE),
"COLUMN_NAME" VARCHAR2(150 BYTE),
"FLAG" VARCHAR2(2 CHAR) DEFAULT 'N'
);
A predefined set of table names and the respective column names are inserted into this table. The default value for the FLAG is set to 'N'.The user depends on his requirement will set FLAG to 'Y' which will vary for each run.
Sample data: Run 1
Table_Name Column_Name Flag
T1 C1 Y
T1 C2 Y
T1 C3 N
T2 C1 N
T2 C2 YSample data: Run 2
Table_Name Column_Name Flag
T1 C1 Y
T1 C2 N
T1 C3 N
T2 C1 N
T2 C2 YI need an UPDATE statement which should run only for those columns to which the FLAG is set to Y group by table_name. From the dataset given above, In the Run 1, the No of columns for table T1 is 2 whereas in the Run 2 it is only one column that needs to be updated and so on for the remaining tables.
Please advise
Not sure what you are really after here - but here's how you could generate the update SQL based on the metadata
SQL> CREATE TABLE XX_MASK_REF
2 ( "TABLE_NAME" VARCHAR2(150 BYTE),
3 "COLUMN_NAME" VARCHAR2(150 BYTE),
4 "FLAG" VARCHAR2(2 CHAR) DEFAULT 'N'
5 );
Table created.
SQL>
SQL> insert into XX_MASK_REF values('T1','C1','Y');
1 row created.
SQL> insert into XX_MASK_REF values('T1','C2','Y');
1 row created.
SQL> insert into XX_MASK_REF values('T1','C3','N');
1 row created.
SQL> insert into XX_MASK_REF values('T2','C1','N');
1 row created.
SQL> insert into XX_MASK_REF values('T2','C2','Y');
1 row created.
SQL>
SQL> set serverout on
SQL> declare
2 l_sql varchar2(1000);
3 begin
4 for i in (
5 select t.*,
6 row_number() over ( partition by table_name order by column_name ) r,
7 count(*) over ( partition by table_name ) c
8 from XX_MASK_REF t
9 where flag = 'Y'
10 order by 1,2
11 )
12 loop
13 if i.r = 1 then
14 l_sql := 'update '||i.table_name||' set '||i.column_name||' = :b'||i.r;
15 end if;
16 if i.r = i.c then
17 if i.c > 1 then
18 l_sql := l_sql || ','||i.column_name||' = :b'||i.r;
19 end if;
20 dbms_output.put_line(l_sql);
21 else
22 l_sql := l_sql || ','||i.column_name||' = :b'||i.r;
23 end if;
24 end loop;
25 end;
26 /
update T1 set C1 = :b1,C1 = :b1,C2 = :b2
update T2 set C2 = :b1
PL/SQL procedure successfully completed.