| Author |
Message
|
| vikas.bhu |
 Posted: Mon Dec 06, 2010 3:30 am Post subject: Calling Stored procedure through ESQl code |
 |
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Hi
My oraclestored procedure is like below..
CREATE OR REPLACE FUNCTION AP.EDIT_EOT_ORDERS(
p_ORDER_NOÂ Â Â Â Â Â Â Â Â Â Â Â Â IN VARCHAR2, -- required
p_SERIAL_NOÂ Â Â Â Â Â Â Â Â Â Â Â IN VARCHAR2, -- required
p_DR_INDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â IN VARCHAR2, -- required
f_XOE_FOLD_NOÂ Â Â Â Â Â Â Â Â Â IN BOOLEAN,
p_XOE_FOLD_NOÂ Â Â Â Â Â Â Â Â Â IN NUMBER,
f_CANC_LETTER_015_DTÂ Â Â IN BOOLEAN,
p_CANC_LETTER_015_DTÂ Â Â IN VARCHAR2, --'MMDDYY'
f_HRD_CPY_RCV_045_DTÂ Â Â IN BOOLEAN,
p_HRD_CPY_RCV_045_DTÂ Â Â IN VARCHAR2, --'MMDDYY'
f_REQ_REM_165_DTÂ Â Â Â Â Â Â IN BOOLEAN,
p_REQ_REM_165_DTÂ Â Â Â Â Â Â IN VARCHAR2, --'MMDDYY'
f_ECAD_100_DTÂ Â Â Â Â Â Â Â Â Â IN BOOLEAN,
p_ECAD_100_DTÂ Â Â Â Â Â Â Â Â Â IN VARCHAR2,
p_CAPS_SENDÂ Â Â Â Â Â Â Â Â Â Â Â IN BOOLEAN)
RETURN NUMBER
MY code is like below.
IN p_ORDER_NO CHARACTER,
IN p_SERIAL_NO CHARACTER,
IN p_DR_IND CHARACTER,
IN f_XOE_FOLD_NO BOOLEAN,
IN p_XOE_FOLD_NO INTEGER,
IN f_CANC_LETTER_015_DT BOOLEAN,
IN p_CANC_LETTER_015_DT CHARACTER,
IN f_HRD_CPY_RCV_045_DT BOOLEAN,
IN p_HRD_CPY_RCV_045_DT CHARACTER,
IN f_REQ_REM_165_DT BOOLEAN,
IN p_REQ_REM_165_DT CHARACTER,
IN f_ECAD_100_DT BOOLEAN,
IN p_ECAD_100_DT CHARACTER,
IN p_CAPS_SEND BOOLEAN)
EXTERNAL NAME "AP.EDIT_EOT_ORDERS";
When I am calling through tode every thing is working..but when calling with direct parameter as with tode. I am getting following error..
(My paramer are correct)
The message broker detected an error whilst executing the function or procedure ''UpdateIAEOTField''. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2010-12-06 04:17:43.676059 2624 RecoverableException BIP2921E: The procedure ''IAQ.AP.EDIT_EOT_ORDERS'' with '14' parameters could not be matched with a corresponding Database stored procedure.
Stored Procedures must match the ESQL CREATE PROCEDURE definition in the following ways: 1: The ESQL parameters must all be of SCALAR type. 2: The directions of each ESQL and Database parameter (IN / OUT / INOUT) must match. 3: The number of parameters in both the ESQL and database definitions must match.
Ensure these conditions are met and redeploy the message flow.
Please suggest. |
|
| Back to top |
|
 |
| mgk |
| Posted: Mon Dec 06, 2010 4:16 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Well the Oracle function has "RETURN NUMBER" but I do not see the equivalent for the ESQL.
_________________
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 |
|
|
| vikas.bhu |
| Posted: Mon Dec 06, 2010 4:30 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Please let me know What I need to add in the code, as I am new to this..
is it like out parameter? |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Mon Dec 06, 2010 4:53 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Thanks I did that..
But I am getting new error like below..
he message broker detected an error whilst executing the function or procedure ''UpdateIAEOTField''. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2010-12-06 07:50:04.207585 3552 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2010-12-06 07:50:04.207592 3552 DatabaseException BIP2322E: Database error: SQL State ''HYC00''; Native Error Code '0'; Error Text ''[DataDirect][ODBC Oracle driver]Optional feature not implemented.''.
The error has the following diagnostic information: SQL State ''HYC00'' SQL Native Error Code '0' SQL Error Text ''[DataDirect][ODBC Oracle driver]Optional feature not implemented.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Dec 06, 2010 6:51 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You need to set the options in the ODBC configuration for ProcedureReturnsResults and SQLDescribeParam (these are not the exact name but are close - from memory)...
Kind 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 |
|
|
| vikas.bhu |
| Posted: Mon Dec 06, 2010 10:01 pm Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
One more thing I forgot to mention that above is function defined in oracle but In esql code I am calling as procedure.
I thing it will not cause any issue ? |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 07, 2010 12:38 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Againg I am getting the same error like feture not implemented?
Please suggest.  |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 07, 2010 3:44 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
Does boolean value is causing the problem....
which in defined as boolen in oracle stored procedure and in ESQl I am sending as boolea  n |
|
| Back to top |
|
|
| harish_td |
| Posted: Thu Dec 09, 2010 4:11 am Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
| vikas.bhu wrote: |
Does boolean value is causing the problem....
which in defined as boolen in oracle stored procedure and in ESQl I am sending as boolea n |
Please follow mgk's advise. The search button also points to this previously solved post
http://www.mqseries.net/phpBB2/viewtopic.php?p=215061&sid=4294585e55f78a3e7d514a9a33fac988
| Code: |
# Oracle stanza
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1 |
|
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Tue Dec 14, 2010 11:29 pm Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
it did not worked for me. 
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2010-12-15 02:24:08.008543 4152 DatabaseException BIP2322E: Database error: SQL State ''HYC00''; Native Error Code '0'; Error Text ''[DataDirect][ODBC Oracle driver]Optional feature not implemented.''.
The error has the following diagnostic information: SQL State ''HYC00'' SQL Native Error Code '0' SQL Error Text ''[DataDirect][ODBC Oracle driver]Optional feature not implemented.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Dec 15, 2010 1:33 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Are you using the DataDirect driver (as provided by WMB) or the oracle driver ? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 15, 2010 2:00 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Quote: |
'[DataDirect][ODBC Oracle driver]Optional feature not implemented.
|
It looks like the DataDirect Driver (but one could be wrong)
_________________
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 |
|
|
| harish_td |
| Posted: Wed Dec 15, 2010 2:39 am Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
| vikas.bhu wrote: |
| it did not worked for me. |
Did you re-start your broker after the change to the odbc.ini file? |
|
| Back to top |
|
|
| vikas.bhu |
| Posted: Mon Dec 20, 2010 3:23 am Post subject: |
|
|
Disciple
Joined: 17 May 2009
Posts: 159
|
| yes I am using data direct and and restarted the broker.. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Dec 20, 2010 5:46 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello. The BOOLEAN may well be the problem. Try a simpler procedure as a test to get it working and work up from there. Once you know what is failing you could try raising a PMR...
Kind 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 |
|
|
|
|
