Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Trigger erroneously entering IF loop/compiling error

    Hi!
    I'm having a bit of a pickle figuring out where I'm going wrong.

    I have a query that generates a trigger creating query in a loop (so I can build the same trigger on many tables with relevant column names). This is then executed immediately and the loop ended. I've used this trick successfully already, but now that I've had to change some parameters, I'm getting stuck.

    In this loop, sometimes a column will not exist. I've tried several ways to make an IF statment, and this is where I'm left.

    Table of table names column = ASSAYN

    Code:
    trigSql:= 'CREATE OR REPLACE TRIGGER ' || v_trigname ||
    ...
    select count(column_name) into v_c_con from all_tab_columns where table_name=''' || n.ASSAYN || ''' and column_name=''P_CONTROL_UPTAKE_SEM'';
    IF v_c_pse=1 THEN
    select AVG(P_CONTROL_UPTAKE_SEM) into v_sem from ' || n.ASSAYN || ' where ID_STRUCTURES = t.ID and P_CONTROL_UPTAKE is not null and UM_DATA=1;
    END IF;
    ...
    END;';
    dbms_output.put_line(trigSql);
    execute immediate trigSql;
    end loop;
    end;
    (I'm leaving out a bunch of code for convenience, but am happy to provide more if it appears the error might be out of where I think it is).

    This query loops through the table of table names (ASSAYN), creating a trigger as it goes. It will create one trigger and then throw a 'success with compilation error'. Looking at the errors of the trigger that was created, I get:

    Code:
    PL/SQL: ORA-00904: "P_CONTROL_UPTAKE_SEM": invalid identifier.
    This is correct - the column P_CONTROL_UPTAKE_SEM does not exist in the first table in the loop. However, the generated trigger code is:

    Code:
    select count(column_name) into v_c_pse from all_tab_columns where table_name='VGLUT_ASSAYS' and column_name='P_CONTROL_UPTAKE_SEM';
    IF v_c_pse=1 THEN
    select AVG(P_CONTROL_UPTAKE_SEM) into v_sem from VGLUT_ASSAYS where ID_STRUCTURES = t.ID and P_CONTROL_UPTAKE is not null and UM_DATA=1;
    END IF;
    Which shouldn't even enter the loop because v_c_pse should be 0. If I do a manual test:

    Code:
    declare
    v_c_pse integer;
    begin
    v_c_pse:=0;
    select count(column_name) into v_c_pse from all_tab_columns where table_name='VGLUT_ASSAYS' and column_name='P_CONTROL_UPTAKE_SEM';
    dbms_output.put_line(v_c_pse);
    if v_c_pse = 1 then
    dbms_output.put_line('loop');
    end if;
    end;
    The output is 0, as expected. If I change P_CONTROL_UPTAKE_SEM to a column name that does exist, then the output is the expected 1 loop.

    So why is the trigger failing because that column doesn't exist when it's inside a loop? Will a compiling trigger check every identifier when it compiles? And if so, how can I get around it - I need to change the select command based on whether or not the column exists.

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    using PL/SQL to write trigger is like using a hammer to divide one board into two pieces, IMO.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by anacedent View Post
    using PL/SQL to write trigger is like using a hammer to divide one board into two pieces, IMO.
    Given my limited knowledge set, it was this or hand-write 67 triggers. I'll take the hammer

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it was this or hand-write 67 triggers.
    How many triggers fail to compile now.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by anacedent View Post
    >it was this or hand-write 67 triggers.
    How many triggers fail to compile now.
    The first trigger generating loop worked fine, and produced 67 working triggers. It's this particular trigger-generating loop with the IF logic that is failing - and it stops after producing only one because of the error.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by anhydrouswater View Post
    Code:
    PL/SQL: ORA-00904: "P_CONTROL_UPTAKE_SEM": invalid identifier.
    This is correct - the column P_CONTROL_UPTAKE_SEM does not exist in the first table in the loop. However, the generated trigger code is:

    Code:
    select count(column_name) into v_c_pse from all_tab_columns where table_name='VGLUT_ASSAYS' and column_name='P_CONTROL_UPTAKE_SEM';
    IF v_c_pse=1 THEN
    select AVG(P_CONTROL_UPTAKE_SEM) into v_sem from VGLUT_ASSAYS where ID_STRUCTURES = t.ID and P_CONTROL_UPTAKE is not null and UM_DATA=1;
    END IF;
    Which shouldn't even enter the loop because v_c_pse should be 0. If I do a manual test:

    Code:
    declare
    v_c_pse integer;
    begin
    v_c_pse:=0;
    select count(column_name) into v_c_pse from all_tab_columns where table_name='VGLUT_ASSAYS' and column_name='P_CONTROL_UPTAKE_SEM';
    dbms_output.put_line(v_c_pse);
    if v_c_pse = 1 then
    dbms_output.put_line('loop');
    end if;
    end;
    The output is 0, as expected. If I change P_CONTROL_UPTAKE_SEM to a column name that does exist, then the output is the expected 1 loop.

    So why is the trigger failing because that column doesn't exist when it's inside a loop? Will a compiling trigger check every identifier when it compiles? And if so, how can I get around it - I need to change the select command based on whether or not the column exists.
    Your manual test succeeded because you did not put reference to P_CONTROL_UPTAKE_SEM inside IF condition. If you did, it would fail as well.

    You should the difference between compiling the code (checking its validity) and running it. As that generated code is static, it fails in the first step regardless the potential non-accessibility of that code during run time.

    The get around is putting that IF condition away generated SQL and use that SQL statement inside only when that column exists - that query on ALL_TAB_COLUMNS should not be in dynamic block at all. Concatenate that piece of code to TRIGSQL variable conditionally (conditions on that SELECT FROM all_tab_columns seem to be static).
    Code:
      trigsql := CREATE OR REPLACE TRIGGER ' || v_trigname || ' ...';
      select count(column_name) into v_c_pse
      from ...;
      if v_c_pse = 1 then
        trigsql := trigsql||' ... ';
      end if;
      trigsql := trigsql||' ... ';

  7. #7
    Join Date
    Oct 2011
    Posts
    4
    That makes a lot of sense. Thank you so much for explaining what was happening!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •