Skip to main content
Delphix

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

 

KBA

KBA# 6373

 

Issue

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 already has a database connection open (via JDBC), the Masking Engine executes a customer script by sending the SQL across the JDBC connection to be executed in the database.

Many customers are familiar with executing a Stored Procedure from a SQL browser prompt (such as TOAD or SQLPlus). They are often used to using browser shortcuts (such as EXECUTE) to accomplish this.  Unfortunately, because we are not going through a SQL browser but directly to the database across the 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
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Resolution

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);

Note

Note:

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.

 

 

Troubleshooting

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.  Or, 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: