Can I execute a dynamic sql statement in an after update trigger?

Question

I am using the following statement:

query_str='SELECT :NEW.FIRST_NAME||:NEW.LAST_NAME INTO  HostID 
FROM INPUT_TABLE WHERE INPUT_ID='

The trigger has the following code:

EXECUTE IMMEDIATE query_str ||:NEW.INPUT_ID;

I'm getting the following error when updating INPUT_TABLE: ORA-01008: not all variables bound

If I'm updating a record where input_id=111, I would think Oracle would just execute the following statement:

SELECT :NEW.FIRST_NAME||:NEW.LAST_NAME INTO  HostID 
FROM INPUT_TABLE WHERE INPUT_ID=111

Why is it having binding issues?

I'm using SQLDeveloper.

create or replace   
TRIGGER DATA_DETAIL_TRIG
AFTER INSERT OR UPDATE
ON INPUT_TABLE
FOR EACH ROW
 DECLARE
   DATA_SOURCE_ID_RET  NUMBER;
   resultcount        NUMBER;
   query_str          VARCHAR2(512);
   using_cl          VARCHAR2(512);
   HostId      VARCHAR2(256);
   DATA_SOURCE_ID       NUMBER;
   INPUT_ID         NUMBER(38,0);
   AUTOGENERATE_IND       VARCHAR2(1);
   autogen_const varchar(200);
   pragma autonomous_transaction;
   CURSOR C_DATA_SOURCES IS 
    SELECT DATA_SOURCE_ID
    FROM DATA_SOURCE_DETAIL
    WHERE AUTOGENERATE_IND='Y'
    AND DATA_SOURCE_REF.ACTIVE_IND='Y';
BEGIN


OPEN C_DATA_SOURCES;
LOOP
  FETCH C_DATA_SOURCES INTO DATA_SOURCE_ID_RET;
  EXIT WHEN C_DATA_SOURCES%NOTFOUND;
    query_str:=getHostQuery( DATA_SOURCE_ID_RET);
--SELECT :FIRST_NAME||:LAST_NAME||to_char(:DOB,'yyyy/mm/dd')    
From INPUT_TABLE WHERE     INPUT_ID=:INPUT_ID
using_cl:=getHostUsing(DATA_SOURCE_ID_RET);
--:NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DOB, :NEW.INPUT_ID
EXECUTE IMMEDIATE query_str  INTO HostId USING using_cl;

  IF INSERTING THEN
    INSERT INTO DETAIL_TABLE
  (
  DETAIL_ID,
  INPUT_ID,
  HOST_ID,
  DATA_SOURCE_ID,
  NOTE,
  DATE_MODIFIED
  ) VALUES
  (
   DETAIL_SEQ.NEXTVAL,
    :NEW.INPUT_ID,
    HostId,
    DATA_SOURCE_ID_RET,
    'Autogenerate Data Source Insert for Insert Input',
    SYSDATE
  );

ELSIF UPDATING THEN



SELECT COUNT(DATA_SOURCE_ID) INTO resultcount FROM DETAIL_TABLE WHERE     
INPUT_ID=:NEW.INPUT_ID AND DATA_SOURCE_ID=DATA_SOURCE_ID_RET;
  IF resultcount>0 THEN


    UPDATE DETAIL_TABLE
    SET
  HOST_ID = HostId,
  NOTE ='Autogenerate Data Source Update for Update Input',
  DATE_MODIFIED =SYSDATE
  WHERE INPUT_ID=:NEW.INPUT_ID
  AND DATA_SOURCE_ID=DATA_SOURCE_ID_RET;


  ELSE
      INSERT INTO DETAIL_TABLE
  (
  DETAIL_ID,
  INPUT_ID,
  HOST_ID,
  DATA_SOURCE_ID,
  NOTE,
  DATE_MODIFIED
  ) VALUES
  (
  DETAIL_SEQ.NEXTVAL,
  :NEW.INPUT_ID,
  HostId,
   DATA_SOURCE_ID_RET,
   'Autogenerate Data Source Insert for Update Input ',

    SYSDATE
  );
END IF;
--end if insert or update inside update

END IF;
--end IF UPDATING
END LOOP;
Close C_DATA_SOURCES;

COMMIT;
END DATA_DETAIL_TRIG;
--end trigger

Alex Gitelman · Accepted Answer

Once you you placed your :new variables in quotes, Oracle looses their meaning as special trigger variables and interprets them as regular bind variables. You probably need to use USING clause with your dynamic sql. Something like

query_str:='SELECT :FIRST_NAME||:LAST_NAME
 FROM INPUT_TABLE WHERE INPUT_ID=:ID';
EXECUTE IMMEDIATE query_str INTO HostID USING 
  :NEW.FIRST_NAME,:NEW.LAST_NAME,:NEW.INPUT_ID;

Update.

Now as more details emerge, I wonder why do you need to query at all? From what I can see, you just compose HostID from some values in INPUT_TABLE. But since you already have have trigger attached to the same table, you already have all values you need and can simply do

HostID := :NEW.FIRST_NAME||:NEW.LAST_NAME;

Your approach would only make sense if you query different table. In this case, as I mentioned, you can't quote :NEW or :OLD when you place them in USING. You may compose other value from them, though.

Can I execute a dynamic sql statement in an after update trigger?

Answers (2)

Related Questions