Forum Stats

  • 3,827,077 Users
  • 2,260,739 Discussions
  • 7,897,158 Comments

Discussions

Using Execute Immediate with FORALL for Update Statement

User_NY02I
User_NY02I Member Posts: 356 Blue Ribbon

Hi All,

Greetings !!

I Require to update a table based on data fetched from a query. however in query I am generating Column Name and its respective values using LISTAGG. I am able to update the table using the row by row processing with simple loop however I am exploring ways to utilize the FORALL with EXECUTE IMMEDIATE since I am changing the SET clause of UPDATE statement runtime.

set serveroutput on;

DECLARE 
l_sql VARCHAR2(4000);
cursor c1 is SELECT 
    OFFER_MIGRATION_ID,
    listagg(column_name||' = nvl('||column_name||','''||DEFAULT_CHAR_VALUE||''')', ','||chr(13)) within group (order by column_name) as set_clause
FROM
(
SELECT  distinct och.OFFER_MIGRATION_ID,
        och.ATTR_ID,
        ch_map.COLUMN_NAME,
        coalesce(och_list.value_name,och.DEFAULT_CHAR_VALUE) as DEFAULT_CHAR_VALUE
 FROM OFFERING_CHARS och,
      CHAR_MAP ch_map,
      OFFERING_CHARS_list och_list
Where ch_map.transformation = 'CONSTANT_DICT'
  and ch_map.attr_id = och.ATTR_ID
  and och.DEFAULT_CHAR_VALUE is not null
  and och_list.offer_migration_id(+) = och.offer_migration_id
  and och_list.characteristic_id(+) = och.characteristic_id
  and och_list.value(+) = och.DEFAULT_CHAR_VALUE
)
GROUP BY OFFER_MIGRATION_ID;  

TYPE char_list IS TABLE OF c1%rowtype index by pls_integer;
l_char_list char_list;
BEGIN 

/******** CONSTANT_FROM_DICT *********/
open c1;
fetch c1 bulk collect into l_char_list;
close c1;

FORALL i IN l_char_list.first .. l_char_list.last

    EXECUTE IMMEDIATE 'UPDATE product_char SET :1 WHERE offer_migration_id = '||l_char_list(i).offer_migration_id
    using l_char_list(i).set_clause;
 
 COMMIT;
 
END;

This block gives error --

Error report -
ORA-06550: line 35, column 92:
PLS-00440: FORALL bulk IN-bind variables cannot be used here
ORA-06550: line 35, column 5:
PL/SQL: Statement ignored
ORA-06550: line 35, column 5:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The values for set_clause column from the cursor will be like for example --

OFFER_MIGRATION_ID, SET_CLAUSE

10001   "CCA_CATEGORY = nvl(CCA_CATEGORY,'PD'),CCA_LIFECYCLE_STATUS = nvl(CCA_LIFECYCLE_STATUS,'A'),EQUIPMENT_TYPE = nvl(EQUIPMENT_TYPE,'Devices')"
20001   DATA_ALLOWANCE_SO = nvl(DATA_ALLOWANCE_SO,'9225')
20023   "DURATION = nvl(DURATION,'6'),DATA_ALLOWANCE_SUP_OFFER = nvl(DATA_ALLOWANCE_SUP_OFFER,'9203')"
20024   DATA_ALLOWANCE_SUP_OFFER = nvl(DATA_ALLOWANCE_SUP_OFFER,'9203')

My DB is -

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

The loop which is working inside the PLSQL block I am looking for ways to better implement it instead of going row by row since it takes long time to execute.

FOR i IN 1..l_char_list.COUNT
 LOOP
    l_sql := 'UPDATE stgdm_product_char SET '||chr(13)||l_char_list(i).set_clause||chr(13)||' WHERE offer_migration_id = '||l_char_list(i).offer_migration_id;
--    dbms_output.put_line(l_sql);
    EXECUTE IMMEDIATE l_sql;
 END LOOP;


Answers