ORA-07445 [pfrb1_assign_aad2var()+1084] Frequently Reported (Doc ID 1590026.1)

Last updated on FEBRUARY 07, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Frequent ORA-07445 [pfrb1_assign_aad2var()+1084] errors are filling up the file system. The trace file generated for this error contains the following information:

----- Current SQL Statement for this session (sql_id=7vsc01yz0v1r9) -----
INSERT INTO IA.ORD_STATUS (ORDNO_OS, NO_OS, SOURCE_OS, STAT_TYPE_OS, STAT_DATE_OS, STAT_CMNT_OS, USER_MSG_OS, IN_DT_OS, IN_U_OS) SELECT :B1 , NO_OS + 1, 'I', :B3 , SYSDATE, :B2 , 'PREPOP system process status information', SYSDATE, 'PREPOP' FROM IA.ORD_STATUS WHERE ORDNO_OS = :B1 AND NO_OS = (SELECT MAX(NO_OS) FROM IA.ORD_STATUS WHERE ORDNO_OS = :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
3f1a776f8        20  AP.AHBH_INSERT_TRIG
3f1b6ebe0       114  package body AP.PREPOP
3f1b6ebe0      2019  package body AP.PREPOP
3f1b6ebe0      3079  package body AP.PREPOP
3f1b6ebe0      3147  package body AP.PREPOP
3f1b7a3c0         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)    
-------------------- -------- -------------------- ----------------------------
ksedst1 <- ksedst <- dbkedDefDump <- ssexhd <- sighndlr <- call_user_handler <- sigacthandler <- pfrb1_assign_aad2va <- pevm_GBVAR <- pfrinstr_GBVAR  <- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- peiet_execute_trigger <- kkxtexe <- kxtExecuteTriggerRecursive <- rpiswu2 <- kxtExecuteTriggerRecursive <- insExecExtraPostStm <- qerltcPostInsertProcessing <- qerltcFetch <- insexe <- opiexe <- opipls<- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_EXECC <- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe<- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real  <- ssthrdmain <- main <- start

 The presence of the functions 'peiet_execute_trigger and 'kxtExecuteTriggerRecursive' in the call stack in the trace file confirms that the error is generated during trigger execution.

Changes

- The crash occurs because the after statement part of the compound trigger calls a procedure which tries to reference :old and :new.

Example:

create or replace TRIGGER "SCOTT"."TC_TRIGGER"
 FOR UPDATE ON  scott.emp
 Compound Trigger
 VINSTAL  CHAR(1);
 RETIFICA  CHAR(1);
 ret_val PLS_INTEGER;
 
 Procedure atualiza_emp is
  Begin
  VINSTAL := null;
   RETIFICA := 'N';
 
    if ((:NEW.ENAME <> :OLD.ENAME)) THEN
     RETIFICA := 'S';
     VINSTAL := :OLD.ENAME;
    END IF;
 
 End atualiza_emp;
 
 After Each Row is
   Begin
   null;
   End After Each Row;
 
 After Statement is
   Begin
  atualiza_emp();
   End After Statement;
 
 
 End TC_TRIGGER;
 /


However this functionality is only applicable to row level trigger operations, not statement level. If I write a simple after statement trigger with the same code then
the trigger fails to create in the first place with: ORA-4082: NEW or OLD references not allowed in table level triggers

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms