SQL Execution as with Post Migration Step

I have a use case where we currently use a Stat workflow to execute data fixes in production. The CSR is only used for approvals and to attach the SQL script file. Once the CSR goes to Prod the SQL is downloaded and executed with SQLPlus. 

The script file always contains the number of rows to be updated/inserted/deleted. It is critical that this matches. 

Han anyone developed an automated process to execute SQL scripts that validates the execution prior to commit? I have seen automated execution but there is no validation. The SQL syntax is correct, it is important that a script is not committed if the row counts are off. 

  • Hello,

    The Pre/Post Migration Steps Parameters are user-defined and server-based parameters.

    It is all up to the user's definition.

    We strongly suggest you test your script on a database and validated that the output is exactly as you need, prior to adding this to the Pre/Post Migration Steps.

    Keep in mind that the user must have one of the following rights to open the Pre/Post Migration Step Parameters Maintenance table:
    • Pre/Post Migration Server Parm - Add
    • Pre/Post Migration Server Parm - Edit
    • Pre/Post Migration User Parm - Add
    • Pre/Post Migration User Parm - Edit

  • In PLSQL, you can use the builtin sql%rowcount to accomplish it. Use a PL/SQL variable for the expected count and rollback if it does not match the sql%rowcount using an if statement