Importing dump using impdp throws ORA-39083 and ORA-04079 (invalid trigger specification)

Question

Importing a dp dump file that contains a trigger gives error ORA-39083 and ORA-04079. I'm using Oracle XE 11g 64 bit on Windows 10. I export a schema that contains a trigger with status 'VALID'. When I import the dump file again in the same database (but different schema), it raises aforementioned exception.

The import log shows the failing SQL. However, it shows only part of the CREATE TRIGGER-sql. Next follows the export and import log, and the concerning trigger.

Export log

;;; 
Export: Release 11.2.0.2.0 - Production on Wed Oct 27 16:42:13 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "VBSDB"."SYS_EXPORT_SCHEMA_01":  VBSDB/********@XE DIRECTORY=VBS DUMPFILE=20211027_164212_$VBSDB.dpdmp LOGFILE=20211027_164212_$VBSDB.dplog VERSION=11 EXCLUDE=STATISTICS 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.812 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
...
. . exported "VBSDB"."FIN_BETALING"                          0 KB       0 rows
...
Master table "VBSDB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for VBSDB.SYS_EXPORT_SCHEMA_01 is:
  D:\DATABASES\20211027_164212_$VBSDB.DPDMP
Job "VBSDB"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:42:27

Import log

;;; 
;;; 
Import: Release 11.2.0.2.0 - Production on Wed Oct 27 16:42:35 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "VBSDB2"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "VBSDB2"."SYS_IMPORT_SCHEMA_01":  USERID=VBSDB2/********@XE directory=VBS dumpFILE=20211027_164212_$VBSDB.DPDMP logfile=271021164234.oracleimplog.txt schemas=VBSDB remap_schema=VBSDB:VBSDB2 transform=oid:n 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
...
. . imported "VBSDB2"."FIN_BETALING"                         0 KB       0 rows
...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-04079: invalid trigger specification
Failing sql is:
CREATE TRIGGER "VBSDB2"."T_FIN_BETALING_DELETE" 
BEFORE DELETE
ON FIN_BETALING
FOR EACH ROW
 WHEN (OLD.VERREKENINGFACTUURREGELID IS NOT NULL)     V_MSG := 'De betaling-id ' || TO_CHAR(:OLD.FIN_BETALINGID) || ' is reeds doorgestort en kan derhalve niet verwijderd worden';    IF V_AFREKENINGID IS NOT NULL THEN        V_MSG := V_MSG || ' (afrekening-id ' || TO_CHAR(V_AFREKENINGID) || ', beheer
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Job "VBSDB2"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 16:42:44

Trigger

CREATE TRIGGER VBSDB.T_FIN_BETALING_DELETE
  BEFORE DELETE
  ON VBSDB.FIN_BETALING
  FOR EACH ROW
  WHEN (OLD.VERREKENINGFACTUURREGELID IS NOT NULL)
DECLARE
  V_AFREKENINGID NUMBER;
  V_BOVK         VARCHAR2(50 CHAR);
  V_MSG          VARCHAR2(255 CHAR);
BEGIN

  SELECT A.PRG_AFREKENINGID,
         C.CONTRACTNRSTR
    INTO V_AFREKENINGID,
         V_BOVK
    FROM FIN_FACTUURREGEL FR
      JOIN FIN_FACTUUR F
        ON FR.FIN_FACTUURID = F.FIN_FACTUURID
      JOIN PRG_CONTRACT C
        ON C.PRG_CONTRACTID = F.PRG_CONTRACTID
      JOIN PRG_AFREKENING A
        ON F.BEHFACTAFREKENINGID = A.PRG_AFREKENINGID
    WHERE FR.FIN_FACTUURREGELID = :OLD.VERREKENINGFACTUURREGELID;

  V_MSG := 'De betaling-id ' || TO_CHAR(:OLD.FIN_BETALINGID) || ' is reeds doorgestort en kan derhalve niet verwijderd worden';

  IF V_AFREKENINGID IS NOT NULL
  THEN
    V_MSG := V_MSG || ' (afrekening-id ' || TO_CHAR(V_AFREKENINGID) || ', beheerovereenkomst ' || V_BOVK || ')';
  END IF;

  RAISE_APPLICATION_ERROR(-20500, V_MSG);
END;
/

It turns out that the problem only occurs when the CREATE TRIGGER-sql is executed through .NET with ODT for Visual Studio 2019 (ODP.net 4.122.19.1). However, the problem also arises when executing the SQL through the System.Data.OracleClient-namespace. When that same CREATE TRIGGER-sql is executed through DBForge, then the export and import are successful. Although, it is peculiar that in the first case Oracle cannot successfully do the pumping job, as trigger has VALID status.

The code that I use:

private const string SQL = @"CREATE OR REPLACE TRIGGER T_FIN_BETALING_DELETE 
BEFORE DELETE 
ON FIN_BETALING 
FOR EACH ROW 
WHEN (OLD.VERREKENINGFACTUURREGELID IS NOT NULL)
DECLARE
    V_AFREKENINGID NUMBER;
    V_BOVK VARCHAR2(50 CHAR);
    V_MSG VARCHAR2(255 CHAR);
BEGIN

    SELECT
    A.PRG_AFREKENINGID,
    C.CONTRACTNRSTR INTO V_AFREKENINGID, V_BOVK
    FROM
    FIN_FACTUURREGEL FR
    JOIN FIN_FACTUUR F on FR.FIN_FACTUURID = F.FIN_FACTUURID
    JOIN PRG_CONTRACT C ON C.PRG_CONTRACTID = F.PRG_CONTRACTID
    JOIN PRG_AFREKENING A on F.BEHFACTAFREKENINGID = A.PRG_AFREKENINGID
    WHERE FR.FIN_FACTUURREGELID = :OLD.VERREKENINGFACTUURREGELID;

    V_MSG := 'De betaling-id ' || TO_CHAR(:OLD.FIN_BETALINGID) || ' is reeds doorgestort en kan derhalve niet verwijderd worden';

    IF V_AFREKENINGID IS NOT NULL THEN
        V_MSG := V_MSG || ' (afrekening-id ' || TO_CHAR(V_AFREKENINGID) || ', beheerovereenkomst ' || V_BOVK || ')';
    END IF;

    RAISE_APPLICATION_ERROR(-20500, V_MSG);
END;";

    private static void CreateTrigger(OracleConnection connection)
    {
      using (var cmd = new OracleCommand(SQL, connection))
      {
        cmd.ExecuteNonQuery();
      }
    }

I have tried changing the ending-part of the CREATE TRIGGER-statement:

• by adding/removing ';'
• by adding/removing CRLF's
• by adding/removing '\'

Any variations attempted that deviate from the above SQL-statement cause the trigger to go into INVALID state.

The question that remains is: what causes ODP.net to create an Oracle trigger with status VALID that cannot be exported and imported again using datapump?

Any help would be hugely appreciated!

Answer 1

The key is likely this clause in your DDL:

ON VBSDB.FIN_BETALING

The remap_schema in impdp changes the schema name in the trigger's name (CREATE TRIGGER VBSDB.T_FIN_BETALING_DELETE becomes CREATE TRIGGER VBSDB2.T_FIN_BETALING_DELETE), but not in the following code. The trigger creation then fails during import because it is referencing a table in another schema.

Try redefining your original trigger like this and remove any other schema name references:

CREATE TRIGGER VBSDB.T_FIN_BETALING_DELETE
  BEFORE DELETE
  ON FIN_BETALING
  FOR EACH ROW
  WHEN (OLD.VERREKENINGFACTUURREGELID IS NOT NULL)
DECLARE
  V_AFREKENINGID NUMBER;
  V_BOVK         VARCHAR2(50 CHAR);
  V_MSG          VARCHAR2(255 CHAR);
BEGIN

  SELECT A.PRG_AFREKENINGID,
         C.CONTRACTNRSTR
    INTO V_AFREKENINGID,
         V_BOVK
    FROM FIN_FACTUURREGEL FR
      JOIN FIN_FACTUUR F
        ON FR.FIN_FACTUURID = F.FIN_FACTUURID
      JOIN PRG_CONTRACT C
        ON C.PRG_CONTRACTID = F.PRG_CONTRACTID
      JOIN PRG_AFREKENING A
        ON F.BEHFACTAFREKENINGID = A.PRG_AFREKENINGID
    WHERE FR.FIN_FACTUURREGELID = :OLD.VERREKENINGFACTUURREGELID;

  V_MSG := 'De betaling-id ' || TO_CHAR(:OLD.FIN_BETALINGID) || ' is reeds doorgestort en kan derhalve niet verwijderd worden';

  IF V_AFREKENINGID IS NOT NULL
  THEN
    V_MSG := V_MSG || ' (afrekening-id ' || TO_CHAR(V_AFREKENINGID) || ', beheerovereenkomst ' || V_BOVK || ')';
  END IF;

  RAISE_APPLICATION_ERROR(-20500, V_MSG);
END;
/

Then you should be able to export/import it without problem.