Splitting a deployment in two statements
sbahn
Posts: 7
Hi,
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.
DECLARE
null_value CHAR(1) := NULL;
statement1 CHAR(91);
statement2 CHAR(67);
BEGIN
statement1 := 'INSERT INTO quant.atp11 VALUES (:0, :1, :2,
, :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';
END;
/
COMMIT;
This gives me some issues with foreign keys, and I see no reason for this behaviour.
I am running DataCompare 2.6.5.1090.
Best regards.
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.
DECLARE
null_value CHAR(1) := NULL;
statement1 CHAR(91);
statement2 CHAR(67);
BEGIN
statement1 := 'INSERT INTO quant.atp11 VALUES (:0, :1, :2,
, :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';
END;
/
COMMIT;
This gives me some issues with foreign keys, and I see no reason for this behaviour.
I am running DataCompare 2.6.5.1090.
Best regards.
Comments
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com