Can a Post script reference a table from a different Connector (oracle schema)?

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I need to create a post script SQL that uses the first and last name of a table in one oracle schema to update a field that holds the full name (Last, First) in a table in a different schema. My connectors are based on schemas, so I'm guessing I need a path in my sql or something.

Example SQL used as postscript in masking job on table 1 in schema A:

Update B.table2
Set B.table2_full_name = (select A.table1_last_name||'', '||A.table1_first_name from A.table1)

I don't know if this is even possible or if the post scripts can only reference tables within the same connectors. Thanks for any help.

Renee
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of Jatinder Luthra

Jatinder Luthra, Employee

  • 2,996 Points 2k badge 2x thumb
Hello Renee,
                  Postscript is SQL script which will execute as per the permissions.

As per your example above, you can provide read/select permissions in schema "A" to schema "B", which I assume is your connector schema.

Tip: Before adding as the postscript, try to test this sql statement by executing manually in schema "B", which will show you if any permission errors.

~Thanks
Jatinder
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Thank you Jatinder. In the dmsuite I have schema A as one Connector and schema B as a different Connector. So are you saying as long as the database has the proper permissions between the schemas, the post script in Connector A can update a table outside of the post script's Connector schema?

Renee
Photo of Jatinder Luthra

Jatinder Luthra, Employee

  • 2,996 Points 2k badge 2x thumb
Yes Renee. It is not about the connector, but its about permissions in Oracle.

Think of this as sql statement, in which you are pulling information from one schema and updating in another schema.

As per my understanding, you first need to provide read privilege on schema A table, something like this "grant select on A.table1 to B".

Then, test your update script in schema B. If it works fine, plug in as postscript.

Hope this helps.

~Thanks
Jatinder
Photo of Renee Greenley

Renee Greenley

  • 604 Points 500 badge 2x thumb
Great. Thank you Jatinder.

Renee