Delphix Products

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  Can a Post script reference a table from a different Connector (oracle schema)?

    Posted 01-16-2018 02:57:00 PM
    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
    #Masking


  • 2.  RE: Can a Post script reference a table from a different Connector (oracle schema)?
    Best Answer

    Posted 01-16-2018 03:08:00 PM
    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




  • 3.  RE: Can a Post script reference a table from a different Connector (oracle schema)?

    Posted 01-16-2018 03:39:00 PM
    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


  • 4.  RE: Can a Post script reference a table from a different Connector (oracle schema)?

    Posted 01-16-2018 03:56:00 PM
    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



  • 5.  RE: Can a Post script reference a table from a different Connector (oracle schema)?

    Posted 01-16-2018 04:08:00 PM
    Great. Thank you Jatinder.

    Renee