8 Replies Latest reply on Jul 9, 2019 9:06 AM by Arnab P

    ODI Load Plan - Which schema user the procedures execute from

    Arnab P



      I do not have much experience in ODI. I have a simple question on Procedures and how they are executed from Load Plans.


      I have a procedure where in I have this statement:-



      dbms_stats.gather_table_stats(ownname =>  user, tabname => 'T_TEST', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, cascade => TRUE);  



      This procedure should execute from the same schema that it is owned by; which is why the user has been set as the ownname above in the statement.


      This procedure is executed from inside a Load Plan. While executing this procedure from the Load Plan it gives an error saying:-


      Insufficient Privileges "<Schema_Name>"."T_TEST".


      The Schema Name that the Load Plan picked up while executing was not the user who owned the table T_TEST and hence resulted in the above error. 


      How do we make sure that the Load Plan picks the correct user in the above circumstances and executes procedure correctly. I could mention the user directly by hardcoding it but in case the schema changes later it would require another set of changes in all the places where this has been called.

      I checked the ODI Logical Schema for the procedure which was set correct as the schema where the table and the procedure resides. The ODI Physical Schema has two properties - Schema and Work Schema both are set to the schema who owns the the above procedure and the table.

      But I am not sure why it still picks up the wrong schema and hits into this error in ODI Load Plan execution.


      Any ideas would be of great help.