Sql updating multiple columns
The example query below uses a subquery where we are returning XML data for the USR_NAME from table USRS and joining this to the outer query by SEC_ID from table SALES_SECTORS. For each value from the inner query we are concatenating a ";" and then the actual value to have all of the data from all rows concatenated into one column. In this case, it really represents an ARRAY of rows in our table. COM create or replace package body do_update 2 as 3 type rowid Array is table of rowid index by binary_integer; 4 5 type emp Rec is record 6 ( 7 empno dbms_sql.number_table, 8 ename dbms_sql.varchar2_table, 9 job dbms_sql.varchar2_table, 10 mgr dbms_sql.number_table, 11 hiredate dbms_sql.date_table, 12 sal dbms_sql.number_table, 13 comm dbms_sql.number_table, 14 deptno dbms_sql.number_table, 15 rowid rowid Array, 16 changed dbms_sql.varchar2_table 17 ); 18 19 this little routine just mimicks your setup.
I hope this is one more of those examples that shows there are sometimes easier approaches than you think might be available.
That way, all of the subroutines in this package could access the i'th element of the RECORD of TABLES we have defined.
It would make argument passing trivial and avoid that substr trick alltogether.
We are grouping by SEC_NAME to show all USERS within that SECTOR.
USR_NAME FROM USR_ADV_CAMPAIGN UAC INNER JOIN USRS US ON US.