0

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.

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.