GTSubset unable to generate trigger scripts for Oracle tables

Document ID : KB000016584
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

When trying to generate "Oracle Masking Insert Triggers" using Subset,  I am getting the following error:

Error running SQL: select count(*) from gtsrc_reference_data where rd_ref_id = 'FIRSTNAME' 

Is there a way I can resolve this issue?

Answer:

In this case,  you would need to generate the synonyms so your user can access the scramble data without having to prefix the schema name, or address the permission issues to the scramble database.

To confirm this is your issue,  you should do the following in Subset with the user you are trying to generate the masking triggers:

select * from scramble.gtsrc_reference_data

If a result set returns,  then proceed to the next step.  If you get another error,  check with your DBA to determine if your connected user has access to the scramble database,  or if the scramble database has been installed to your environment.

Next step is to run this SQL statement in the subset window:

 select * from gtsrc_reference_data 

If you get the error "table or source does not exist", this means the synonyms have not been created with the scramble database. The synonyms are created when you use our database installer package that comes with TDM.

You will then need to run the following SQL scripts with a user that has access to your scramble database.  

---DDL START---

 

spool scramble-post-config.log

set echo on

WHENEVER SQLERROR EXIT SQL.SQLCODE

 

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_CONTROL_STEPS" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_CONTROL" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_XREF" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_XREF2" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_SHUFFLE" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_REFERENCE_DATA" TO PUBLIC;

GRANT SELECT,UPDATE,DELETE,INSERT ON  scramble."GTSRC_REFERENCE_LOV" TO PUBLIC;

GRANT SELECT ANY TABLE TO SCRAMBLE;

GRANT SELECT   ON  scramble."GTSRC_REFERENCE_DATA" TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLE2"      TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLEN2"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLED2"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SCRAMBLED3"     TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_CHECKSUM"       TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SETCOUNT"       TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_REPLACE"        TO PUBLIC;

GRANT EXECUTE  ON  scramble."GTSRC_SHUFFLEID"      TO PUBLIC;

 

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CONTROL_STEPS FOR scramble."GTSRC_CONTROL_STEPS";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CONTROL FOR scramble."GTSRC_CONTROL";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_XREF FOR scramble."GTSRC_XREF";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_XREF2 FOR scramble."GTSRC_XREF2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SHUFFLE FOR scramble."GTSRC_SHUFFLE";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SHUFFLEID FOR scramble."GTSRC_SHUFFLEID";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REPLACE FOR scramble."GTSRC_REPLACE";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REFERENCE_DATA FOR scramble."GTSRC_REFERENCE_DATA";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_REFERENCE_LOV FOR scramble."GTSRC_REFERENCE_LOV";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_CHECKSUM FOR scramble."GTSRC_CHECKSUM";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SETCOUNT FOR scramble."GTSRC_SETCOUNT";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLE2 FOR scramble."GTSRC_SCRAMBLE2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLED2 FOR scramble."GTSRC_SCRAMBLED2";

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLED3 FOR scramble."GTSRC_SCRAMBLED3"

CREATE OR REPLACE PUBLIC SYNONYM GTSRC_SCRAMBLEN2 FOR scramble."GTSRC_SCRAMBLEN2";

exit---DDL END---

 

After you have completed executing the SQL commands above,  you should now be able to generate your masking triggers.

Additional Information:

If you see a simliar error while trying to create scripts for Build Windows DataPump Masked Export, we suggest looking at the resolution in this document: https://support.ca.com/us/knowledge-base-articles.TEC1630578.html