Home » SQL & PL/SQL » SQL & PL/SQL » Strange Issue in executing procedure (Oracle 11g)
Strange Issue in executing procedure [message #671121] Wed, 15 August 2018 20:55 Go to next message
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 #671123 is a reply to message #671121] Wed, 15 August 2018 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

What results occur if the procedure is modified as below & is owned & compiled by ZVIA?

procedure ETL_TRUNC
as
begin
execute immediate 'TRUNCATE TABLE LOST_IMEI';
end;
Re: Strange Issue in executing procedure [message #671124 is a reply to message #671123] Wed, 15 August 2018 22:03 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thank you! I will have to request my DBA to make changes on the proc.

But when I executed just EXEC ETL_TRUNC, it gave the same error.

Re: Strange Issue in executing procedure [message #671127 is a reply to message #671124] Thu, 16 August 2018 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If it works in SQL*Plus or TOAD and not with your ETL, with same credentials, then the problem is in the ETL and has nothing to do with Oracle.

Note that EXEC is NOT a SQL or PL/SQL statement but a SQL*Plus command. It is a short cut for "BEGIN ... END;".

Re: Strange Issue in executing procedure [message #671130 is a reply to message #671127] Thu, 16 August 2018 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
Most likely explanation is that the ETL tool isn't using the schema name when calling the proc.
Try creating a synonym for the procedure in the zbapp schema.
Re: Strange Issue in executing procedure [message #671141 is a reply to message #671130] Thu, 16 August 2018 07:38 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thank you!
I created a synonym and it still does not recognize. But I created a different name for the synonym (I believe that does not matter here?)

[Updated on: Thu, 16 August 2018 07:39]

Report message to a moderator

Re: Strange Issue in executing procedure [message #671143 is a reply to message #671141] Thu, 16 August 2018 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I believe that does not matter here?
You're right, synonym name does not matter.

Re: Strange Issue in executing procedure [message #671148 is a reply to message #671143] Thu, 16 August 2018 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
Using a different name is fine so long as the ETL tool has been told about the different name.

What's the exact error message the ETL tool throws?
Re: Strange Issue in executing procedure [message #671150 is a reply to message #671148] Thu, 16 August 2018 08:59 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
This is the exact error message that is thrown on the ETL Side

Call DB Procedure - Because of an error, this step can't continue:
Unable to call procedure
ORA-06550: line 1, column 13:
PLS-00222: no function with name 'TRUNC_LOST_IMEI' exists in this scope
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Here the synonym name is TRUNC_LOST_IMEI
Re: Strange Issue in executing procedure [message #671151 is a reply to message #671150] Thu, 16 August 2018 09:02 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Does it work correctly in sql*plus? Can you paste the output from that with both users trying it please.
Re: Strange Issue in executing procedure [message #671152 is a reply to message #671150] Thu, 16 August 2018 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your ETL tool is doing something weird.
I suggest you trace it's oracle session to see exactly what SQL it's trying to run on the DB.
Re: Strange Issue in executing procedure [message #671153 is a reply to message #671150] Thu, 16 August 2018 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

use COPY & PASTE to SHOW us the actual code & whole session of what you do & how Oracle responds.
stop telling us bits & pieces of what you see.

We can't debug code we can NOT see.
Re: Strange Issue in executing procedure [message #671154 is a reply to message #671152] Thu, 16 August 2018 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You seem to have a very good crystal ball.
I don't what OP actually did:
* how and with which user he created the procedure
* how and with which user he created the synonym
* how his ETL call the procedure and/or synonym and with which user
* ...

I leave this topic and wish you a good luck with it.

Re: Strange Issue in executing procedure [message #671158 is a reply to message #671154] Thu, 16 August 2018 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13720
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 #671166 is a reply to message #671154] Thu, 16 August 2018 10:10 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Ok. Let me give detailed explanation.

SQL> CREATE OR REPLACE procedure VIAWARE.ETL_TRUNC
2 as
3 begin
4 execute immediate 'TRUNCATE TABLE VIAWARE.STOLEN_LOST_IMEI';
5 end;
6 /

Procedure created.

SQL> CREATE or replace PROCEDURE BPAPPS.trunc_lost_imei
2 AS
3 BEGIN
4 VIAWARE.ETL_TRUNC();
5 END;
6 /

Procedure created.

Now, I call the procedure BPAPPS.trunc_lost_imei from the ETL Tool. (We are using Pentaho and there are two types of steps in Pentaho you can execute DB statements from it. One is Call DB proc Step. Here it just asks User ID/Password and the procedure name). I use the BPAPPS user ID/Password in Pentaho and the above procedure is givem in the procedure name.

The following are the error message that is shown in Pentaho Error Log:

2018/08/16 10:49:18 - Call DB Procedure.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue:
2018/08/16 10:49:18 - Call DB Procedure.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unable to call procedure
2018/08/16 10:49:18 - Call DB Procedure.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ORA-06550: line 1, column 13:
PLS-00222: no function with name 'TRUNC_LOST_IMEI' exists in this scope
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Re: Strange Issue in executing procedure [message #671167 is a reply to message #671166] Thu, 16 August 2018 10:16 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you log into sqlplus as bpapps and just run
exec trunc_lost_imei;
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.
Re: Strange Issue in executing procedure [message #671169 is a reply to message #671167] Thu, 16 August 2018 10:18 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
From SQLPlus, it works and it is not visible in ETL Tool. Thats the issue.

We have other procedures called in the same way inside ETL Tool. But they belong to the same DB user that ETL is connected to.

Only here, the users are different and hence the problem.
Re: Strange Issue in executing procedure [message #671172 is a reply to message #671169] Thu, 16 August 2018 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I call the procedure BPAPPS.trunc_lost_imei from the ETL Tool.
1/ With which user?
2/ How do you call it? Post the statement sent by the ETL.

Re: Strange Issue in executing procedure [message #671174 is a reply to message #671169] Thu, 16 August 2018 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I've said twice now - you need to trace the oracle session of the ETL tool.
That'll show what it's really doing and then we can work back from that to determine how to fix it.
Re: Strange Issue in executing procedure [message #671179 is a reply to message #671169] Thu, 16 August 2018 10:34 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
manikandan23 wrote on Thu, 16 August 2018 08:18
From SQLPlus, it works and it is not visible in ETL Tool. Thats the issue.

We have other procedures called in the same way inside ETL Tool. But they belong to the same DB user that ETL is connected to.

Only here, the users are different and hence the problem.
When you observe different result, please be 100% confident & assured that something is DIFFERENT.
Only you can compare & contrast the users that work OK against those which throw errors.
We can't delve into your configuration; only you can do so!
Oracle is the victim; not the culrit.
Re: Strange Issue in executing procedure [message #671201 is a reply to message #671179] Thu, 16 August 2018 12:42 Go to previous messageGo to next message
manikandan23
Messages: 34
Registered: February 2017
Member
Thank you everyone. This seems to be an open issue and my DBA is truncating on his side every day and I am taking out this procedure call from my ETL Job.

I am still curious why it is causing the issue.

Thank you again who provided valuable guidance and direction and really appreciate this forum.

Mani A
Re: Strange Issue in executing procedure [message #671236 is a reply to message #671201] Fri, 17 August 2018 14:03 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
If he is running the procedure on a schedule, why not setup a scheduled database job and run it in the correct schema right in the database.
Re: Strange Issue in executing procedure [message #671275 is a reply to message #671236] Mon, 20 August 2018 10:01 Go to previous message
JPBoileau
Messages: 78
Registered: September 2017
Member
That's the right way to do it, with a scheduled job.

JP
Previous Topic: Row Movement disabled in Partitioned table
Next Topic: Joining multiple rows and creating multiple columns
Goto Forum:
  


Current Time: Thu Nov 14 18:03:37 CST 2019