Skip to main content
Delphix

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

 

KBA

KBA# 6373

 

At a Glance

Description:  This KBA describes How To execute Stored Procedures (Oracle and SQL Server) in the Masking Job Pre and Post-Script
Applicable To: This KBA relates to executing Stored Procedures (SP) over JDBC and as such, it is applicable to all versions of the Masking Engine.
Location: UI: Environments > [Environment] > [Masking Job] 
How to Call:
 - Oracle
CALL [<schema>].[<package>].<stored_procedure_name>([<arg1>], [<arg2>], ...);
How to Call:
 - SQL Server
EXEC [<schema>].<stored_procedure_name> @[<arg1>] = <value>, @[<arg2>] = <value>, ...);
Troubleshooting: Any error messages will show in INFO logs. 

Search in the logs for (RegEx syntax for Pre and Post Script):
"JOB_ID_xx_yy.*Execute_P.*script"

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, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.5.0, 6.0.6.0, 6.0.6.1

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

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 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.

Resolutions

For Oracle

Syntax

The proper way to execute a Stored Procedure across a JDBC connection is to use the SQL CALL command.  For Oracle, review the following syntax:

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

Example

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 

Syntax

The proper way to execute a Stored Procedure across a JDBC connection is to use the SQL EXEC command.  For MS SQL Server, review the following syntax:

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

Example

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';
 
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 that the stored proc attempts to manipulate.

 

Troubleshooting

The error will appear in the INFO logs which are accessible from the UI or the API.

  • UI: Admin > Logs
  • API: GET /application-logs

For assistance in accessing the Masking Logs - please see KBA7988 (link below).

Find error using Linux/Unix

This searches for an error in a specific job (xx JobID and yy ExecID) and then list a few additional lines to show the error message from the database.

Note: This uses grep with RegEx. 

grep -A5 "JOB_ID_xx_yy.*ERROR" info.log | grep "Execute_P.*script"

Finding error using Windows PowerShell

This searches for an error in a specific job (xx JobID and yy ExecID) and then list a few additional lines to show the error message.

Select-String "JOB_ID_xx_yy.*Execute_P.*script.*ERROR" info.log -Context 0,5

 

 


Related Articles

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