| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Calling an Oracle stored proc with a dynamic schema name |
« View previous topic :: View next topic » |
| Author |
Message
|
| petervh1 |
 Posted: Thu Oct 13, 2011 5:15 am Post subject: Calling an Oracle stored proc with a dynamic schema name |
 |
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
Environment: WMB 7.0.0.2 on AIX
I'm trying to call an Oracle stored proc, setting the schema name dynamically. I've read the info centre on this topic but something is not clear to me:
My CALL is as follows:
CALL xyz (parm...) EXTERNAL SCHEMA 'BROKER_DEV';
-- I will eventually change BROKER_DEV in the line above to be a variable
and my PROCEDURE definition:
CREATE PROCEDURE xyz (parm...)
EXTERNAL NAME "%.NTF_NOTIFICATION.USP_LOG_EVENT";
When I run a user trace, the error message field from the Oracle SP contains blanks, but the database upadte does not take place.
Anyone seen this before?
Thanks |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Thu Oct 13, 2011 6:18 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
is the SP working with static Schema?
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Oct 13, 2011 7:20 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Oct 13, 2011 7:37 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
how does the user trace looks like...is it properly populating.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Oct 13, 2011 7:49 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
It looks correct to me. That is, I can see the call to the full SP name (including schema/package/proc), and the error message field returning blanks). Will post a trace extract tomorrow.
Do you see anything wrong with my syntax? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 13, 2011 8:24 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
But what is broker actually calling?
The best way to find out is to enable ODBC Tracing.
However this might not be possible on a multi use system during working hours.
I've solved many a silly ODBC connectivity problem (mainly to SQLServer I might add) using ODBC Trace.
The 'open' calls to the DB would be very illuminating I think
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Oct 13, 2011 9:58 pm Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
Here's an extract from the use trace file:
[
2011-10-14 05:51:59.776072 3864 UserTrace BIP2544I: Node 'za.co.xxx.indibano.NotificationHandler.ConsumeNotifications': Executing database SQL statement ''{ CALL BROKER_DEV.NTF_NOTIFICATION.USP_LOG_EVENT ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) }'' derived from ('za.co.xxx.indibano.NotificationHandler_ConsumeNotifications.updateEvents', '1.1'); expressions '''57', NULL, NULL, 'notification', '20
11-08-29 14:36:25', NULL, NULL, '<summaryMessage>Marker for SP call</summaryMessage>', 'c13c223acc9711e0b6e7000000000000', NULL, -16002, 'Broker+Execution Group+Message Flow+Node', 'error', 'Not populated', NULL''; resulting parameter values '''57', NULL, NULL, 'notification', '2011-08-29 14:36:25', NULL, NULL, '<summaryMessage>Marker for SP call</summaryMessage>', 'c13c223acc9711e0b6e7000000000000', NULL, -16002, 'Broker+
Execution Group+Message Flow+Node', 'error', 'Not populated', NULL''.
2011-10-14 05:51:59.776160 3864 UserTrace BIP2537I: Node 'za.co.xxx.indibano.NotificationHandler.ConsumeNotifications': Executing statement ''IF LENGTH(P_ERRORMSG) > 0 THEN... END IF;'' at ('za.co.xxx.indibano.NotificationHandler_ConsumeNotifications.Main', '87.4').2011-10-14 05:51:59.776184 3864 UserTrace BIP2539I: Node 'za.co.xxx.indibano.NotificationHandler.ConsumeNotifications': Evaluating expression ''P_ERRORMSG'' at ('za.co.xxx.indibano.NotificationHandler_ConsumeNotifications.Main', '87.14'). This resolved to ''P_ERRORMSG''. The
result was ''NULL''.
2011-10-14 05:51:59.776200 3864 UserTrace BIP2539I: Node 'za.co.xxx.indibano.NotificationHandler.ConsumeNotifications': Evaluating expression ''LENGTH(P_ERRORMSG)'' at ('za.co.xxx.indibano.NotificationHandler_ConsumeNotifications.Main', '87.7'). This resolved to ''LENGTH(NULL
)''. The result was ''NULL''.
2011-10-14 05:51:59.776248 3864 UserTrace BIP2539I: Node 'za.co.xxx.indibano.NotificationHandler.ConsumeNotifications': Evaluating expression ''LENGTH(P_ERRORMSG) > 0'' at ('za.co.xxx.indibano.NotificationHandler_ConsumeNotifications.Main', '87.26'). This resolved to ''NULL >
0''. The result was ''NULL''.] |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Oct 13, 2011 10:12 pm Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
| I should add that the the above trace output is the same as for the "happy scenario" where I hard-code the schema name in the EXTERNAL NAME statement. The only difference is that the DB update does not occur now, whereas if the schema name is hard-coded, the update does occur. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 13, 2011 10:21 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I refer the OP to my previous post.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| 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
|
|
|
|
