| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| What Oracle rights do I need to run a stored procedure? |
« View previous topic :: View next topic » |
| Author |
Message
|
| sorn73 |
 Posted: Fri Jun 16, 2006 1:38 am Post subject: What Oracle rights do I need to run a stored procedure? |
 |
|
Novice
Joined: 24 Mar 2004
Posts: 24
Location: Sweden
|
Short version: What rights do I need in Oracle, on schema and package level, to execute a stored procedure that has been defined as an external procedure in the broker? I'm on WBIMB 5.0 sp2 (upgrades are, unfortunately, not an option. You know how it is.) I can't use PASSTHRU either.
Long version: I'm having problems accessing a stored procedure in an Oracle database. Since the stored procedure have both IN and OUT parameters I can't use PASSTHRU. I have declared the procedure with an external reference, like so:
| Code: |
CREATE PROCEDURE ProcessQuery(
IN xmlIn BLOB,
OUT xmlOut BLOB) EXTERNAL NAME "%.PACKAGE.ProcessQuery"; |
which matches the stored procedure definition that I've been given.
When I tro to call the procedure I'm getting a "The procedure is unknown to the database and no definition could be found." error . (And subsequent tries gives me the famous bug "The ESQL deployed to the node is invalid.")
However, I am able to execute the procedure from SQLPlus, and get good results. I can't see the procedure, or even the package, using Toad or any other tool, which makes me suspect that I might have execute rights but not view/browse rights (or something, I'm not an Oracle dba).
So, up with a trusty sniffer and debug the ODBC traffic. And, sure enough, the SQL query that I catch is not really an execute, but it looks likes the broker is trying to look up the definition of the stored procedure, probably to check that it matches the definition in my code. Here is the actual lookup, albeit taken from a network package dump of the ODBC traffic, therfore there are som "escape" and stuff. Oh yeah, the package name is really not PACKAGE, but that's the only cleanup I've done.
| Code: |
| select a.owner, a.object_name, INSTR(a.object_type, 'PROC'), '1' from sys.all_objects a where a.object_type IN ('FUNCTION','PROCEDURE') and a.status = 'VALID' and a.owner like USER escape'\' and a.object_name like'PACKAGE'escape'\' union all select c.owner, c.synonym_name, INSTR(a.object_type, 'PROC'), '1' from sys.all_objects a, sys.all_synonyms c where c.table_owner = a.owner and c.table_name = a.object_name and a.object_type IN ('FUNCTION','PROCEDURE') and a.status = 'VALID' and c.owner like USER escape'\' and c.synonym_name like'PACKAGE'escape'\' |
which is what makes me believe that the broker is not satisified by being able to execute the procedure, it must also be able to access the procedure definition, since it looks up package name and associated objects in sys.*-tables. The answer back from the database is "ORA01403 - No data found".
So, to reiterate, what rights do I need, on a schema and package level, to be able to execute a stored procedure in an Oracle database? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Jun 16, 2006 2:41 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Make sure you're on a supported Oracle for the broker version you're using.
Make sure your ODBC datasource is built using the Merant odbc drivers that come with broker, and not with something else.
_________________
I am *not* the model of the modern major general. |
|
| 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
|
|
|
|
