What are the challenges you face when working across database platforms? Take the survey

Splitting a deployment in two statements

sbahnsbahn Posts: 7
edited April 8, 2016 10:02AM in Data Compare for Oracle

When deploying data changes I sometimes experience that deployment for a single row is split into two statements: an insert statement, which uses null_value for some columns, then an update statement replacing those null values, e.g.

null_value CHAR(1) := NULL;
statement1 CHAR(91);
statement2 CHAR(67);
statement1 := 'INSERT INTO quant.atp11 VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)';
EXECUTE IMMEDIATE statement1 USING 'ATP11 20130823', 'ATP prod', 'IM event', 'IM event', null_value, null_value, 0.847, 0, 'Mid', '1Y', '120Y', 'No', 0.005, null_value;
statement2 := 'UPDATE quant.atp11 SET rsrvtstag=:0, rsrvdisccf=:1 WHERE "NAME"=:w0';
EXECUTE IMMEDIATE statement2 USING 'DKKSWAP', 'Weighted MultiTS Test RsrvDisc 20130823', 'ATP11 20130823';

This gives me some issues with foreign keys, and I see no reason for this behaviour.
I am running DataCompare

Best regards.


  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5

    Data Compare for Oracle uses this format in the DML it creates for performance reasons. However, I cannot explain why it will insert a null_value and followed by a second statement with a value for the null_value.

    Like your previous forum post, another support call has been created for you, call reference #63821.

    Would please email support@red-gate.com with a copy of the DDL for the quant.atp11 so we can attempt a reproduction of the problem.

    Many Thanks
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.