Incorrectly formed Subset scripts for scramble functionality can cause Oracle errors: ORA-31693, ORA-29913, ORA-29540

Document ID : KB000009189
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Using the SCRAMBLE database, a mask of the 'FirstName' column from the 'People' table, with 'Build Windows DataPump Masked Export' from GTSubset is used to generate scripts.  When the scripts generated by Subsetting were executed, the following error messages are produced:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting \"TRAVEL\".\"~PEOPLE\": userid=travel/********@orcl content=ALL JOB_NAME=~PEOPLE directory=MaskingInFlightExample dumpfile=MaskingInFlightExample_TRAVEL_PEOPLE.dmp tables=TRAVEL.PEOPLE parfile=MaskingInFlightExample_exportdp_TRAVEL.PE
OPLE.par LOGFILE=MaskingInFlightExample_exportdp_PEOPLE.log remap_data=TRAVEL.PEOPLE.FIRST_NAME:PUMPW_MASK.F_93080_3 remap_data=TRAVEL.PEOPLE.EMAIL:PUMPW_MASK.F_93080_7 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 128 KB â€‹
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
ORA-31693: Table data object \"TRAVEL\".\"PEOPLE\" failed to load/unload and is being skipped due to error: 
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout 
ORA-29540: class maskfunctions does not exist 
Master table \"TRAVEL\".\"~PEOPLE\" successfully loaded/unloaded 
Dump file set for TRAVEL.~PEOPLE is: 
C:\\GTWORK\\MASKINGINFLIGHTEXAMPLE\\MASKINGINFLIGHTEXAMPLE_TRAVEL_PEOPLE.DMP
Job \"TRAVEL\".\"~PEOPLE\" completed with 1 error(s) at 11:42:45

Environment:
TDM 4.2 GAFDM GASCRAMBLE DB
Cause:

Is the Oracle scramble database properly installed?

A)  If no, re-install.

B)  If yes, then the actual scripts that were generated need to be evaluated. It could be something simple like double brackets or quotes:


Example Script: 

GO 
IF EXISTS(SELECT 1 FROM sys.views 
WHERE object_id = OBJECT_ID('[dbo].[ORDERS_v]')) BEGIN DROP VIEW [dbo].[ORDERS_v] END GO CREATE VIEW [dbo].[ORDERS_v] as select [ord_num] [ord_num], [cust_id] [cust_id], [prod_id] [prod_id], [qty] [qty], [order_date] [order_date], dbo.selectCAR_PARTS(200,CAST(description AS VARCHAR(1)),'') [description] from [Sales].[dbo].[ORDERS] L0 where 1 = 1 GO 


Please note this line section: 
dbo.selectCAR_PARTS(200,CAST(description AS VARCHAR(1)),'') [description] 

The double "s are incorrect. They should be singles and not doubles. This is corrected in a patch. 
If the above is observed, a Subsetting patch is needed. 

Resolution:

Processes Used to Verify the Database Install:

To verify if the SCRAMBLE database is installed properly, please follow these instructions to make sure it is installed with the correct version and full set to yes: Exporting and Importing Between Different Database Releases 


Data Pump can be used to migrate all or any portion of a database between different releases of the database software. The Data Pump Export VERSION parameter is typically used to do this. This will generate a Data Pump dump file set compatible with the specified version. 

The default value for VERSION is COMPATIBLE, indicating that exported database object definitions will be compatible with the release specified for the COMPATIBLE initialization parameter. 

In an upgrade situation, when the target release of a Data Pump-based migration is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release.

An exception is when an entire Oracle Database 11g (release 11.2.0.3 or higher) is exported in preparation for importing into Oracle Database 12c Release 1 (12.1.0.1) or later. In this case, explicitly specify VERSION=12 in conjunction with FULL=YES in order to include a complete set of Oracle internal component metadata. 

PLEASE NOTE:  A common exception to "everything looks fine" is that it is NOT.  If nothing else works, uninstall the SCRAMBLE database and re-install.

 

Subsetting Patch Required:

If a subsetting patch is needed due to the double quotes/brackets issue as mentioned and shown in the Cause section, determine if your date on the subsetting.exe is prior to 22 JUNE 2017.  If it is, open a Support Case (if needed, please review the video to refresh how to open a Support case) and request a patch after 22 JUNE 2017.  

If a GA version has come out after 30 AUGUST 2017, open a Support case and ask if this patch is included in the GA after 30 AUGUST 2017. 

Additional Information:

You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.   

To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html