Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODI - Executing a procedure gives me a "Insufficient Privileges (1031 error code)"

Fabricio_JorgeFeb 14 2016 — edited Feb 18 2016

Hi all,

   ODI: 12.1.3

   OS.: OEL7

   Database: 12.1.0.2

   I am quite a newbie in the ODI world. What i am trying to do is execute a procedure (created via ODI Studio > Designer). But when i execute it, i get the following error:

     ODI-1226: Step PROC_CRIACAO_TABELA_TESTE1 fails after 1 attempt(s).

     ODI-1232: Procedure PROC_CRIACAO_TABELA_TESTE1 execution fails.

     ODI-1228: Task Procedure-PROC_CRIACAO_TABELA_TESTE1-CREATE_TABLE_TESTE1 fails on the target connection DS_belem.

     Caused By: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

My procedure (PROC_CRIACAO_TABELA_TESTE1) contains 3 tasks:

   1) DROP_TABLE_TESTE1, which simply executes a DROP TABLE. Here the CONTEXT is "DEV" and the logical schema is "ODI_LS"

   2) CREATE_TABLE_TESTE1: which creates a new table (CREATE TABLE).Also here, the CONTEXT is "DEV" and the logical schema is "ODI_LS"

   3) CARGA_TABLE_TESTE1: which inserts some rows in the created table. Also here, the CONTEXT is "DEV" and the logical schema is "ODI_LS"


   The table is to be created in the USR_TESTES_ODI schema.

I have created a Data Server, pointing to the database where i want to create the table mentioned above. After that, i created a Physica Schema named DS_Belem.ODI_PS (schema: ODI_PS | Work Schema: ODI_PS), added the context DEV amd mapped that context with the logical schema ODI_LS, which was previously created. In the logical schema ODI_LS, i have mapped the context DEV to the Physical schema DS_Belem.ODI_PS.

To the Physical Schema (ODI_PS) i have granted several privileges: CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY PROCEDURE, CREATE ANY INDEX, SELECT ANY TABLE

The dataserver connection test is successful.

What am i missing? I still didn't get the relationship between logical and physical schemas. I have created both in my database, but i don't know which one will execute the operations in the database.

Sorry for my very basic questions. Thanks a lot for the attention and help.

This post has been answered by JeromeFr on Feb 15 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 14 2016
Added on Feb 14 2016
1 comment
691 views