Strange Issue in executing procedure [message #671121] |
Wed, 15 August 2018 20:55 |
|
manikandan23
Messages: 34 Registered: February 2017
|
Member |
|
|
Hi, I have 2 Schema owners on the same DB.
ZVIA is one owner and under this there is a procedure that has a text as
procedure ETL_TRUNC
as
begin
execute immediate 'TRUNCATE TABLE ZVIA.LOST_IMEI';
end;
The second user in the same DB is called as ZBPAPP and this user has to execute the above procedure from a 3rd party ETL Tool.
Our DBA has provided Execute permission for ZBPAPP user for the above procedure.
I could execute the procedure by issuing the following statement when logged from the user ID ZBPAPP in Toad.
exec ZVIA.ETL_TRUNC; (I tried execute ZVIA.ETL_TRUNC; as well)
But when I execute the same from ETL Tool (using ZBPAPP DB User ID), it errors with Invalid SQL Statement error.
On the otherway, When I execute the same procedure from ETL Tool (using ZVIA DB User ID), it executes fine.
My DBA says, he gave execute procedure permission to ZBPAPP User to execute the above procedure.
But it errors out when executed using ZBPAPP User ID.
Can you please tell me what could be the issue here?
In the ETL Tool that I use, For the specific DB User ID, there is a browser button that brings all the procedures under that user. Since the above procedure is not created under that connected user (but has execute previledge on that though), it is still not coming under that connection.
Any help will be highly appreciated.
Thanks,
M
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Strange Issue in executing procedure [message #671158 is a reply to message #671154] |
Thu, 16 August 2018 09:41 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That error message means it's not just a case of missing/wrong synonyms and/or grants.
Those will get you:
PLS-00201: identifier '<whatever>' must be declared
To get the PLS-00222 the ETL tools needs to be doing something that isn't analogous to: exec <procedure>
A trace is the quickest way to see what it's really doing since the ETL tool itself may not make it obvious in it's front end.
|
|
|
|
Re: Strange Issue in executing procedure [message #671167 is a reply to message #671166] |
Thu, 16 August 2018 10:16 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you log into sqlplus as bpapps and just run
does it work?
If it does then you really need to trace the ETL tool session in the DB and see what it's actually doing when it tries to execute that procedure.
|
|
|
|
|
|
|
|
|
|