Skip to main content

Executing a Stored Procedure as a Masking Pre-Script or Post-Script (KBA6373)



KBA# 6373



When creating a Masking pre-script or post-script, you may want to include the execution of a Stored Procedure as part of that script.  Since the Masking Engine is already setup to communicate with the database across JDBC, the Masking Engine executes a custom script by sending the SQL across a JDBC connection to be executed in the database.

Many of you are familiar with executing a Stored Procedure from a SQL browser prompt (such as TOAD or SQLPlus), often these are executed using browser shortcuts (such as the sqlplus EXECUTE statement in Oracle).  Unfortunately, because the scripts are not going through a SQL browser but directly to the database across a JDBC connection, these methods will not work.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases




5.0,,,,,,,,, ,,,,,


4.2,,,,,,, ,,



For Oracle

The proper way to execute a Stored Procedure across a JDBC connection is to use the SQL CALL command.  For Oracle, this looks like:

CALL [<schema>].[<package>].<stored_procedure_name>([<arg1>], [<arg2>], ...);

An example of a script to execute a simple Stored Procedure called foo_tag not in any package, but in the schema delphix which requires 1 argument which is the current date and time as a TIMESTAMP WITH TIMEZONE data type would look like:

CALL delphix.foo_tag(CURRENT_TIMESTAMP);
For MS SQL Server 

The proper way to execute a Stored Procedure across a JDBC connection is to use the SQL EXEC command.  For MS SQL Server, this looks like:

EXEC [<schema>].<stored_procedure_name> @[<arg1>] = <value>, @[<arg2>] = <value>, ...);

An example of a script to execute a simple Stored Procedure called test1 in the schema dbo which requires 1 argument called reason which is a varchar data type would look like:

EXEC dbo.test1 @reason = 'REDACTED';


When you create these Stored Procedures, a common problem is that the Masking DB user does not have permissions for the Stored Proc or for subsequent tables, etc. that the stored proc attempts to manipulate.




Debugging information is only available in the logs or in our internal database.  If you download a support logs bundle, you can decompress and un-tar the bundle to get to the logs.  Alternatively, you can get to the log information from the UI in the Admin Tab.  You can also use the GET /application-logs API endpoint in combination with the GET /file-download/{fileDownloadId} endpoint to download an appropriate log file or set of files.  Lastly, you can open a case with Delphix Support and upload a support logs bundle, letting your support agent know what job ID was encountering the problem to receive assistance with debugging issues with pre- and post-scripts.


Related Articles

The following articles may provide more information or related information to this article: