ORA-00984 inside trigger validations

Question

I'm kinda new at pl/sql developing, and I have this trigger created:

create or replace trigger schema.trg_CP 
          after insert on tdlrp
          referencing old as old 
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkidnc                       schema.tdlrp.fkidnc%type;   
          v_errortype                    schema.tdlrp.xerrort%type;
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                           schema.tpm.pipm%type;
          v_pkdocid_r                    schema.tddr.pidr%type;
          ---------------------------------------------------------------------------------------------------------
          
          begin
            if inserting then
              select fkidnc, xerrort
                into v_fkidnc, v_errortype
                from schema.tdlrp;
              --
              if v_fkidnc = 1 then
                if v_errortype = 1 then
                  select ferror, fipcm
                  into v_fkerrorID, v_linerror
                  from schema.tepm;
                  
                  select pipm 
                  into v_pr
                  from schema.tpm
                  where fipcm = v_linerror;
                  
                  insert into schema.tddr(pidr, fipc,user, datea, fiptm) 
                  values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
                  
                  select pidr
                  into v_pkdocid_r
                  from tddr 
                  where fiptm = v_pr; 
                  
                  insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
                END IF;
              END IF;
            END IF;
                
EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  
END trg_CP;

When i run the script, i get the error:

PL/SQL: ORA-00984: column not allowed in here,

which is referred to the select attr into variable

How can i bypass the error? Is The syntax wrong?

EDIT 15-09-2022 15:31

with suggested changes, now i get:

PLS-00382: expression is of wrong type

in begin statement.

My trigger is now like this:

create or replace trigger schema.trg_CP 
          after insert on tdlrp
          referencing old as old 
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkidnc                       schema.tdlrp.fkidnc%type;   
          v_errortype                    schema.tdlrp.xerrort%type;
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                           schema.tpm.pipm%type;
          v_pkdocid_r                    schema.tddr.pidr%type;
          ---------------------------------------------------------------------------------------------------------
          
          begin
            
              select fkidnc, xerrort
                into v_fkidnc, v_errortype
                from schema.tdlrp;
              --
              if :new.fkidnc = 1 and :new.errortype = 1 then
                  select ferror, fipcm
                  into v_fkerrorID, v_linerror
                  from schema.tepm;
                  
                  select pipm 
                  into v_pr
                  from schema.tpm
                  where fipcm = v_linerror;
                  
                  insert into schema.tddr(pidr, fipc,user, datea, fiptm) 
                  values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
                  
                  select pidr
                  into v_pkdocid_r
                  from tddr 
                  where fiptm = v_pr; 
                  
                  insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
              END IF;
              --
                
EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  
END trg_CP;

There is no `select attr into variable` statement in the code you posted. — Justin Cave, Sep 15 '22 at 13:39

karam yakoub agha · Answer 1 · 2022-09-15T14:56:55.413

I believe this select

select fkidnc, xerrort into v_fkidnc, v_errortype from schema.tdlrp;

is gonna return many rows and you are trying to assign it to a variable and but need to be assigned to an array of value.

it looks for me that you want to compare the new value inserted, so no need to make select on the same table actually oracle doesn't allow that you should use

if :new.fkidnc = 1 then
--your logic
end if;

Try to see the code below, I am trying to edit it as I can, because I don't know how your tables are

create or replace trigger schema.trg_CP 
      after insert on tdlrp
      referencing old as old 
      for each row
      
      ---------------------------------------------------------------------------------------------------------      
      declare           
      v_fkerrorID                    schema.tepm.ferror%type;
      v_linerror                     number;
      v_pr                           schema.tpm.pipm%type;
      v_pkdocid_r                    schema.tddr.pidr%type;
      ---------------------------------------------------------------------------------------------------------
      
      begin
        if inserting then
          
          --
          if :new.fkidnc = 1 and :new.errortype = 1 then
            
              select ferror, fipcm
              into v_fkerrorID, v_linerror
              from schema.tepm; -- you should use a condition to return 1 values
              
              select pipm 
              into v_pr
              from schema.tpm
              where fipcm = v_linerror --this can return error in execution because can return many values;
              
              insert into schema.tddr(pidr, fipc,user, datea, fiptm) 
              values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
              
              select pidr
              into v_pkdocid_r
              from tddr 
              where fiptm = v_pr --this can return error in execution because can return many values; 
              
              insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm) 
              values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);                
          END IF;
        END IF;
            
 EXCEPTION
    WHEN OTHERS THEN
 RAISE;

 END trg_CP;

create or replace trigger schema.trg_CP 
      after insert on tdlrp
      referencing old as old 
      for each row
      
      --------------------------------------------------------------------------------------------------------- 
 
      declare          
      v_fkerrorID                    schema.tepm.ferror%type;
      v_linerror                     number;
      v_pr                           schema.tpm.pipm%type;
      v_pkdocid_r                    schema.tddr.pidr%type;
      ---------------------------------------------------------------------------------------------------------
      
      begin                        
          --
          if :new.fkidnc = 1 and :new.errortype = 1 then
              select ferror, fipcm
              into v_fkerrorID, v_linerror
              from schema.tepm where --some condition to return 1 row of ferror,fipcm;
              
              select pipm 
              into v_pr
              from schema.tpm
              where fipcm = v_linerror;
              
              insert into schema.tddr(pidr, fipc,user, datea, fiptm) 
              values(schema.seq_tddr.nextval, :old.fipc,'A', systimestamp, v_pr);
              
              select pidr
              into v_pkdocid_r
              from tddr 
              where fiptm = v_pr; 
              
              insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm) 
              values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
          END IF;
          --
            
EXCEPTION WHEN OTHERS THEN  RAISE;

END trg_CP;

using that way, i get: PLS-00049: bad bind variable 'NEW.v_fkidnc' PLS-00049: bad bind variable 'NEW.v_errortype_' — Nhoj Anec, Sep 15 '22 at 14:09
sorry I meant :New.fkidnc no need to use the variables v_fkidnc because you can access the new value of the inserted row in the table tdlrp — karam yakoub agha, Sep 15 '22 at 14:11
sorry i could not understand, can u please edit your above answer? — Nhoj Anec, Sep 15 '22 at 14:14
beside the the comments I added above please check your insert command (the columns names) the order of the values, generally this error is because some value is not allowed to be saved or it is in a wrong format. check this stackoverflow https://stackoverflow.com/questions/17054221/ora-00984-column-not-allowed-here — karam yakoub agha, Sep 15 '22 at 14:32
Also USER is a reserved word in ORACLE, so using it as column name is a bad idea. — p3consulting, Sep 16 '22 at 06:19

ORA-00984 inside trigger validations

1 Answers1