Oracle PL/SQL dynamic if statement global vars

Question

I'm having trouble with dynamic sql, Issue is (I think) reading and setting global variable. Here's what I have and any help at all is greatly appreciated. Please let me know if you need table data too although I have included the data in comments.

 CREATE OR REPLACE PACKAGE data_load
    IS
      curr_rec          NUMBER;
      curr_rule         VARCHAR2(200);
      curr_sql          VARCHAR2(4000);
      curr_sql_two      VARCHAR2(4000);
      curr_data_element VARCHAR2 (200);
      curr_rule_text    VARCHAR2(200);
      curr_error_code   VARCHAR2(10);
      curr_error_flag   VARCHAR2(10);
      curr_flag_val     NUMBER;
      v_check           NUMBER;
      v_ID              NUMBER;
      cur_hdl           INT ;
      rows_processed    NUMBER;
      PROCEDURE check_rules;
    END data_load;

The package body:

    create or replace PACKAGE BODY data_load IS
      PROCEDURE check_rules IS
        CURSOR c1
        IS
          SELECT * FROM STAGING_TABLE where rownum < 3;
        CURSOR c2
        IS
          SELECT * FROM ERROR_CODES WHERE rule_text IS NOT NULL AND status =1;
      BEGIN
        FOR rec1 IN c1
        LOOP
          FOR rec2 IN c2
          LOOP
            curr_data_element := 'rec1.'||rec2.data_element; --- this results in value  "rec1.SHIP_FROM_ACCOUNT_ORG_CODE" without quotes
            curr_rule_text    := rec2.rule_text; --- this value is "is not null" without quotes
            curr_error_flag   := rec2.error_flag; --this value is "FLAG_03" without quotes
            curr_flag_val     := to_number(rec2.error_code); --- this value is 31
            curr_sql :='begin if  :curr_data_element  '||curr_rule_text||'  then update table_with_column_FLAG_03  set  '||curr_error_flag ||' =  0; else  update table_with_column_FLAG_03 set  '||curr_error_flag ||' =  '||curr_flag_val||';  end if; end;';
            dbms_output.put_line(curr_sql); -- results in "begin if  :curr_data_element  is null  then update table_with_column_FLAG_03  set  FLAG_03 =  0; else  update table_with_column_FLAG_03 set  FLAG_03 =  31;  end if; end;"
            EXECUTE IMMEDIATE curr_sql USING curr_data_element ; -- this always  updates the column with 31 even when curr_data_element/ rec1.SHIP_FROM_ACCOUNT_ORG_CODE is null and that's the problem
            COMMIT;
          END LOOP;
          curr_rec := curr_rec+1;
        END LOOP;
        dbms_output.put_line(curr_rec);
      END check_rules;
    END data_load;

I will ad table ddl but please see my comments within the code for the issue, — oracle_of, Sep 09 '16 at 17:01

Alex Poole · Answer 1 · 2016-09-09T17:16:16.610

You've already highlighted the problem really:

    curr_data_element := 'rec1.'||rec2.data_element; --- this results in value  "rec1.SHIP_FROM_ACCOUNT_ORG_CODE" without quotes

You can't refer to cursor columns dynamically. You are creating a string with value 'rec1.SHIP_FROM_ACCOUNT_ORG_CODE'; there is no mechanism to evaluate what that represents. You can't, for instance, try to dynamically select that from dual because the rec1 is not in scope for a SQL call, even dynamically.

When you bind that string value it is never going to be null. You are using that string, not the value in the outer cursor that it represents, and essentially you cannot do that.

The simplest way to deal with this, if you have a reasonably small number of columns in your staging table that might appear as the rec2.data_element value, is to use a case expression to assign the appropriate actual rec1 column value to the curr_data_element variable, based on the rec2.data_element value:

...
  BEGIN
    FOR rec1 IN c1
    LOOP
      FOR rec2 IN c2
      LOOP
        curr_data_element :=
          case rec2.data_element
            when 'SHIP_FROM_ACCOUNT_ORG_CODE' then rec1.SHIP_FROM_ACCOUNT_ORG_CODE
            when 'ANOTHER_COLUMN' then rec1.ANOTHER_COLUMN
            -- when ... -- repeat for all possible columns
          end;            
        curr_rule_text    := rec2.rule_text;
...

If you have a lot of columns you could potentially do that via a collection but it may not be worth the extra effort.

The curr_sql string stays the same, all that's changing is that you're binding the actual value from the relevant rec1 column, rather than never-null string you were forming.

Thanks Alex, I was reading somewhere that global variables can be referenced in the manner I need them to be do you have any opinion on how I might achieve that? — oracle_of, Sep 09 '16 at 17:48
@oracle_of - the global (but could be local) variable `curr_data_element` can be referenced as you've shown, and used a bind variable. That part is OK. The issue is what that variable contains. You can't have a variable that is essentially a pointer to another PL/SQL variable. There's no mechanism to evaluate a string which contains a PL/SQL variable name. — Alex Poole, Sep 09 '16 at 17:54

Oracle PL/SQL dynamic if statement global vars

1 Answers1