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!

is the trigger is in status invalid in the target schema after the import is finished ? If so, if you try to compile the trigger, what error do you get ? — Roberto Hernandez, Oct 27 '21 at 15:20
@RobertoHernandez the trigger does not exist in the target schema (VBSDB2). — J. Ouwehand, Oct 27 '21 at 15:27
can you run this ? `impdp USERID=xxxx directory=xxx dumpfile=xxxxx logfile=xxxx.txt remap_schema=VBSDB:VBSDB2 include=trigger content=metadata_only` — Roberto Hernandez, Oct 27 '21 at 15:34
@RobertoHernandez it gives ORA-04079: invalid trigger specification also. — J. Ouwehand, Oct 27 '21 at 15:46
@RobertoHernandez I figure that the issue must have to do with way that the CREATE TRIGGER-sql is executed. It turns out that if I execute the exact same sql in DBForge, the export and import does not give any errors. However, when that sql is executed through the software (even through the same Oracle user) the problem arises. I use ODT for Visual Studio 2019 in combination with the .NET framework. Still, it is pretty weird that Oracle cannot succesfully do the export and import on a schema for a trigger with VALID status... — J. Ouwehand, Oct 27 '21 at 16:45
It turns out that is has to do with the string literals in the PL/SQL. If I remove all literals and single quotes, then I can successfully export and import the schema again. — J. Ouwehand, Oct 28 '21 at 06:52
I tried to replicate your issue in Oracle EE and I don't have any problem importing/exporting with datapump triggers with or without quotes. I suspect that perhaps your problem is related to Oracle XE and that very old version you are using — Roberto Hernandez, Oct 28 '21 at 06:59
Hi @RobertoHernandez, I haven't tried yet with other Oracle versions, but I'd like to point out that the problem only arises when the CREATE TRIGGER-sql is executed through .NET framework (v4.8). The problem does not arise when the exact same create-SQL is executed through DBForge, for instance. — J. Ouwehand, Oct 28 '21 at 07:33

score 0 · Answer 1 · answered Oct 27 '21 at 15:45

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.

@prmdba, I don't think that is the issue. Datapump remap schema is converting that clause. The log from datapump shows CREATE TRIGGER "VBSDB2"."T_FIN_BETALING_DELETE" BEFORE DELETE ON FIN_BETALING FOR EACH ROW — Roberto Hernandez, Oct 27 '21 at 15:52
@pmdba based on your answer I imported the dump without a remap_schema back into VBSDB (I emptied the schema first). Unfortunatelly, also then the same issue arises. — J. Ouwehand, Oct 27 '21 at 15:54

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

1 Answers1