| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle storeProc with cursor problem |
« View previous topic :: View next topic » |
| Author |
Message
|
| Rookwood |
 Posted: Thu Jul 27, 2006 1:02 am Post subject: Oracle storeProc with cursor problem |
 |
|
Novice
Joined: 26 Aug 2004
Posts: 13
|
Hi,
running:
Broker: MQSI v5.05,
ODBC : MQseries DataDirect 4.10 Oracle 8
Oracle : 9.2
Accessing an Oracle stored procedure that internally uses a cursor to access data and builds a XML string which is returned via an OUT parameter. e.g. <root><app><name>blahblah<name><queue>aa<queue></app></root>
Note the <root></root> tags will be returned if no app data found.
When proc is called via SQLPlus, works fine and returns app data.
When proc is called from broker it completes with no obvious error (SQLCODE=0) and returns <root></root>
Have confirmed via the broker debug and a direct select on data table in esql that data does exist prior/post storedproc call.
Its as if the cursors fail to open when called from the broker.
Any suggestions welcome.
_________________
GS |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Jul 27, 2006 1:11 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you post the oracle procedure definition and the ESQL you are using?
_________________
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 |
|
|
| Rookwood |
| Posted: Thu Jul 27, 2006 5:16 am Post subject: Problem solved - not cursor related issue |
|
|
Novice
Joined: 26 Aug 2004
Posts: 13
|
HI,
solved the problem, its was a discrepancy between the Oracle NLS_DATE_FORMAT being used by the broker and that used by the Oracle database.
The where clause in the cursor sql referenced a DATE datatype column and date datatype variable. The stored proc seems to have formatted the variable differently when called from the broker than when called from sqlplus or Toad sessions.
As I've been unable to determine how/where the NLS settings for the broker client are configured and I'm unable to alter the store proc in any fashion, I've used
PASSTHRU('ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY"')
as a workaround to force the broker, albeit temporarily to use the same NLS_DATE_FORMAT as the Oracle server.
Regards
R
_________________
GS |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 27, 2006 5:45 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
NLS_* settings are environment variables that can be set in broker service user profile.
_________________
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
|
|
|
|
