My Oracle Support Banner

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

Last updated on FEBRUARY 26, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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 TC.TC_TABLE (...........
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
3f1a776f8        20  TC.TC_INSERT_TRIG  ---> this is a trigger
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 "TC"."TC_TRIG"
 FOR UPDATE ON  TC.TC_TABLE
 Compound Trigger
 col1 CHAR(1);
 col2 CHAR(1);
 ret_val PLS_INTEGER;
 
 Procedure update_tc_table is
  Begin
  col1:= null;
  col2:= 'N';
 
    if ((:NEW.ENAME <> :OLD.ENAME)) THEN
     col2:= 'S';
     col1:= :OLD.ENAME;
    END IF;
 
 End update_tc_table;
 
 After Each Row is
   Begin
   null;
   End After Each Row;
 
 After Statement is
   Begin
  update_tc_table();
   End After Statement;
 
 
 End TC_TRIGGER;
 /

NOTE:  In the SQL statements above, the user information and metadata used represents fictitious data.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.