| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Call a stored proc from within a package |
« View previous topic :: View next topic » |
| Author |
Message
|
| 360604 |
 Posted: Tue Feb 17, 2009 1:24 am Post subject: Call a stored proc from within a package |
 |
|
Newbie
Joined: 17 Feb 2009
Posts: 2
|
Hi,
I'm trying to call an Oracle 9 stored procedure that is in a package from ESQL but keep getting the following error:
'"Whilst attempting to obtain a procedure definition, the database reported that the procedure ''MyDataSource.CUSTOMER.getCustomer'' does not exist, or cannot be accesed.'
The procedure and package definitely exist and have the correct permissions against them as I can call the proc from PL/SQL, having logged into the database as the same user as the Broker logs in as. I have granted execure rights to the Broker's username and created a synonym.
If I move the proc out of the package then I can call it from ESQL so it does seem to be the problem is caused by fact that it is in a package.
In ESQL, I have the correct external name:
CREATE PROCEDURE getCustomer(OUT custId CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "CUSTOMER.getCustomer";
I am using version 6 of the message broker.
The driver entry in the odbc.local file is:
Driver=/opt/IBM/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
From looking at a post from 2005, there was a mention of the ODBC driver causing a problem. Does anyone know if this is still an issue? Any help much appreciated.
Thanks. |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Tue Feb 17, 2009 1:46 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
which version of WMB are u at ??
how are u calling/invoking the procedure in database from eSQL ?? paste that code pls
_________________
Cheers |
|
| Back to top |
|
|
| 360604 |
| Posted: Tue Feb 17, 2009 1:50 am Post subject: |
|
|
Newbie
Joined: 17 Feb 2009
Posts: 2
|
Hi,
I'm using version 6 of the broker.
The ESQL code to call the proc is:
DECLARE custId CHAR;
CALL getCustomer(custId);
Thanks. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Feb 17, 2009 3:41 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Does the id the broker uses have access to the package?
Does the schema name qualifier pose a problem. Oracle does a lot of aliasing where you may not be allowed to specify a schema qualifier name.
Have fun and speak to your oracle admin 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 17, 2009 4:22 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
From the docs:
| Quote: |
However, if the procedure belongs to an Oracle package, the package is treated as part of the procedure's name. Therefore you must provide a schema name and the package name, in the form:
| Code: |
| EXTERNAL NAME "mySchema.myPackage.myProc"; |
This form allows the schema, but not the package name, to be chosen dynamically in the CALL statement. |
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
