Strange MERGE behaviour when using static/constant ON clause

Question

CREATE TABLE test (c1 NUMBER(10) NOT NULL);

exec DBMS_ERRLOG.CREATE_ERROR_LOG ( 'test', 'err_test');

I want to insert into table test some values from an other table, and log the errors. I also want to log the ids of records which could not be inserted into the err_test table. In pseudocode this would result in something like this:

INSERT INTO test(c1)
SELECT some_nr AS special_nr FROM some_table_which_has_an_unique_id_for_each_number
LOG ERRORS INTO err_test('Could not insert record with id:' || some_table.id /* where id is the id number of the special_nr */ ) REJECT LIMIT UNLIMITED;

The above obviously doesn't work because src.id is unknown. But I can do the above insert with a merge statement that acts as an insert statement:

MERGE INTO test trg
USING (SELECT NULL AS special_nr, 17 AS id FROM DUAL ) src
ON      (1 = 2) -- force INSERTS (!!) Not using INSERT statement because in err log extra data is needed.
WHEN NOT MATCHED THEN 
        INSERT  (trg.c1)
        VALUES  (src.special_nr)
LOG ERRORS INTO err_test('Could not insert record with id:' || src.id) REJECT LIMIT UNLIMITED;

After executing the above, the error is not logged in the err_table, but thrown back to the user:

    ORA-01489: result of string concatenation is too long
    ORA-01400: cannot insert NULL into ("SCV_DPN"."TEST"."C1")

String too long? Then I try without the concatenation in the err_mesg$:

MERGE INTO test trg
USING (SELECT NULL AS special_nr, 17 AS id FROM DUAL ) src
ON      (1 = 2) -- force INSERTS (!!) Not using INSERT statement because in err log extra data is needed.
WHEN NOT MATCHED THEN 
        INSERT  (trg.c1)
        VALUES  (src.special_nr)
LOG ERRORS INTO err_test(src.id) REJECT LIMIT UNLIMITED;

Auch:

    ORA-38908: internal error occurred during DML Error Logging
    ORA-01024: invalid datatype in OCI call
    ORA-01400: cannot insert NULL into ("SCV_DPN"."TEST"."C1")

It becomes even better:

MERGE INTO test trg
USING (SELECT NULL AS special_nr, 17 AS id FROM DUAL ) src
ON      (1 = 2) -- force INSERTS (!!) Not using INSERT statement because in err log extra data is needed.
WHEN NOT MATCHED THEN 
        INSERT  (trg.c1)
        VALUES  (src.special_nr)
LOG ERRORS INTO err_test('Could not insert record with id:' || nvl(src.id, -1)) REJECT LIMIT UNLIMITED;

Now I am kicked:

    ORA-03113: end-of-file on communication channel
    Process ID: 25352
    Session ID: 171 Serial number: 979

What's going on here? Seems the 1 = 2 is the culprit, because

MERGE INTO test trg
USING (SELECT NULL AS special_nr, 17 AS id FROM DUAL ) src
ON      (src.id IS NULL) -- force INSERTS (!!) Not using INSERT statement because in err log extra data is needed.
WHEN NOT MATCHED THEN 
        INSERT  (trg.c1)
        VALUES  (src.special_nr)
LOG ERRORS INTO err_test('Could not insert record with id:' || src.id) REJECT LIMIT UNLIMITED;

works as it should and (!!) logs the error in the err_table

    ORA_ERR_NUMBER$     ORA_ERR_MESG$                                                   ORA_ERR_ROWID$  ORA_ERR_OPTYP$  ORA_ERR_TAG$                            C1
    1400                ORA-01400: cannot insert NULL into ("SCV_DPN"."TEST"."C1")                      I               Could not insert record with id:17

Now it works without issues.

Can someone explain to me what is going on here and/or also give a better approach for solving the above problem where extra information from the source table has to be logged in the ORA_ERR_MESG$ field.

Note: According to the documentation I can use 1 = 2: To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is ON (0=1).

Allan · Answer 1 · 2014-09-18T13:37:45.610

I believe you've missed one possible culprit: the inclusion of a row value in the tag parameter. I don't believe that this functionality is intended to provide the functionality that you are attempting to use it for. While I can't find any documentation to that effect, a simple change to the initial merge (the one with the concatenation error) seems to confirm it:

MERGE INTO test trg
USING (SELECT NULL AS special_nr, 17 AS id FROM DUAL ) src
ON      (1 = 2) -- force INSERTS (!!) Not using INSERT statement because in err log extra data is needed.
WHEN NOT MATCHED THEN 
        INSERT  (trg.c1)
        VALUES  (src.special_nr)
LOG ERRORS INTO err_test('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') REJECT LIMIT UNLIMITED;

This SQL is successful, despite the tag exceeding the length of the concatenated value that was originally provided.

To get the more detailed logging that you desire, I'd suggest using a cursor for loop containing an insert. It may be slightly slower, but it will allow you to log as much detail as you'd like when there's an error.

The documentation appears to support this conclusion, though it's more implied than stated outright. In describing the "simple expression" allowed for error logging, it states: "The expression can be either a text literal, a number literal, or a general SQL expression such as a bind variable."

Yes, you are correct. I deliberately excluded your example because that wasn't what I was after, but it didn't throw the error. Please also consider my note in the original post. — Davor Josipovic, Sep 18 '14 at 13:36
Imho, the above is a "general SQL expression". Anyhow, using `ON(src.id IS NULL)` seems to work. — Davor Josipovic, Sep 18 '14 at 13:56
I'd advise against using your current solution. It's perilously close to undocumented behavior, and I wouldn't be at all surprised if a future version broke this code. It's seems clear to me, from the strange errors that resulted from some of the attempts, that this is not the intended usage of the `LOG ERRORS` clause. — Allan, Sep 18 '14 at 17:52

Strange MERGE behaviour when using static/constant ON clause

1 Answers1