This content has been marked as final. Show 10 replies
Copy the generated code and run in toad. You may not have the insert/update privileges. 1st check it.
San,1 person found this helpful
The oracle user which is being used to login to Oracle (used to create the data server). This user should be granted privileges to create the tables in your "Work Schema" (specified while creating physical schema).
Ideally, the user in data server and in Physical "work schema" should be same. That makes life lot more easier. because then you dont have to grant create any table priv or drop any table priv to that user.
The data server user is the same as the work schema user under physical schema. The checkboxes for creation of I$ and other temporary tables are also checked. I don't have any problem at insert into I$ step and C$ step it's only failing at mergee step when merging records into the temp table.
what acdti is trying to say is that the work schema user does not have necessary grants ie insert/update on the target schema.1 person found this helpful
For Example - ODI_TEMP is your work schema and XYZ is your Target Schema and AAA is your Target Table.
ODI_TEMP can easiliy create $ tables as its the owner but while trying to perform insert on XYZ.AAA it is throwing Insufficient privileges because its missing grants
Execute this command on the Target Schema (XYZ)
GRANT SELECT,INSERT,UPDATE ON XYZ.AAA TO ODI_TEMP
My work schema: ODI_TEMP
Taregt Schema: ODI_TEMP
Target table: T_ORD_DET
When the work schema and the target schema are the same, do I still need to grant privileges for insert,updating? I tried granting insert,select, update, delete to ODI_TEMP logged in as ODI_TEMP user(this is not required but tried to be doublesure)
Still having the same problem.
No its not required .Check all the tables involved in Merge statement . are all from ODI_TEMP schema. ?
Yes all the tables are from ODI_TEMP. Not sure why is this failing when all the tables are from same schema. I verified that the I$ table is getting created in the same ODI_TEMP schema.
merge into ODI_TEMP.T_ORD_DET T
using ODI_TEMP.I$_T_ORD_DET S
Moreover, do we really grant privileges on temp tables?
Please check with your DBA.
Got it working. Issue resolved.
The id was based on a oracle sequence which did not have grants.After granting privileges the statement is running fine.
Thanks for all the help.
Glad you got it .Now you can close the thread and mark it answered.