How to use dynamic table in nested for loop in oracle pl sql

Question

I have a oracle pl sql script which has two for loop (nested). In second loop i am not able to dynamic table name in select query. it is always taking static query.But i want dynamic table name like SIGNALALERTPROP_1 ,SIGNALALERTPROP_2 ... etc. could some one please help to sort out this issue. I have given the below sql script

/**
 * 
 * Script to add two new columns abbrev and color to SIGNALALERTPROP_% table * 

 * 
 * Prompts for input parameter: update_scope
 * Response can be a specific configuration ID, ALL for all configurations
 * 
 * The script logs progress and errors and warnings to the spool file.
 */

SPOOL update_signalalertprop.log
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT update_scope PROMPT "Enter Signal Configuration ID, or ALL [ALL]: " DEFAULT ALL

declare
configId number;
alertprop_tablename varchar2(20);
v_abrev varchar2(1 CHAR);
v_colorId number;
block varchar2(4000);

procedure executeBlock (block varchar2) is    
begin
  begin
    execute immediate block;
    exception when others then
      dbms_output.put_line('');
      dbms_output.put_line('Error executing...');
      dbms_output.put_line(substr(block, 1, 100));
      dbms_output.put_line(SQLERRM); 
      dbms_output.put_line('');
      return;
  end; 
end;

function stringToInt(p_str in varchar2) return number as
 retVal number := null;
begin
 if regexp_instr(p_str, '^[[:space:]]*[[:digit:]]{1,5}[[:space:]]*$') > 0 then
  retVal := to_number(p_str);
 end if;
return retVal;
end;

function does_constraint_exist (i_constraint_name varchar2 ) return boolean is    
    cnt number;    
    begin
    select count(*) into cnt from user_constraints where constraint_name = i_constraint_name;
    return (cnt > 0);
end; 

begin
    configId := stringToInt('&update_scope');
    alertprop_tablename := 'SIGNALALERTPROP_' || configId;
FOR v_rec IN (select 'SIGNALALERTPROP' item_type, id config_id from signalconfig where id = decode(configId, null, id, configId)) LOOP
    dbms_output.put_line('');
    dbms_output.put_line('Adding Columns to SIGNALALERTPROP_' || v_rec.config_id );
    dbms_output.put_line('table_name=' || v_rec.item_type || ', config_id=' || v_rec.config_id);
    dbms_output.put_line('');
    block := 'ALTER TABLE SIGNALALERTPROP_' || v_rec.config_id || ' ADD (ABBREV VARCHAR2(1 CHAR) DEFAULT null, COLOR DEFAULT 0 NOT NULL )';
    executeBlock(block);

    -- adding foreign key
    if(not does_constraint_exist('SIGNALALERTPROP_COLOR_'||configId||'_FK'))
    then
      block := 'ALTER TABLE "SIGNALALERTPROP_'||configId||'" ADD CONSTRAINT "SIGNALALERTPROP_COLOR_'||configId||'_FK" FOREIGN KEY ( color )
      REFERENCES "SIGNALALERTCOLORS" ( id )
      NOT DEFERRABLE';
      executeBlock(block);
    end if; 

    -- update color and abbrev for tracked alerts
    -- 
 FOR v_alert_rec IN (select ID alertPropId, LABEL alertLabel from alertprop_tablename WHERE ALERT_TYPE = 2 ) LOOP
    v_abrev := SUBSTR(v_alert_rec.alertLabel, 1, 1);    
    v_colorId := MOD(v_alert_rec.alertPropId, 14) + 1;
    dbms_output.put_line('v_abrev ' || v_abrev);
    dbms_output.put_line('v_colorId ' || v_colorId);
    block := 'UPDATE SIGNALALERTPROP_'||configId||  ' SET COLOR = ' || v_colorId || ', ABBREV = ' || v_abrev || ' WHERE ID = ' || v_alert_rec.alertPropId ;
    executeBlock(block);    
    dbms_output.put_line('block ' || block);

END LOOP; 
END LOOP;
commit; 
end;
/
spool off;

i am getting error like v_alert_rec is invalid. I have checked FOR v_alert_rec IN (select ID alertPropId, LABEL alertLabel from alertprop_tablename WHERE ALERT_TYPE = 2 ) LOOP this line it is getting failed as alertprop_tablename is not getting populated.

Answer 1

alertprop_tablename is not a database object, so trying to select values from it will not work.

What you need is a cursor for the dynamic SQL, and to loop through that.

Here is an example of how that works