| Author |
Message
|
| danielaphex |
 Posted: Mon Apr 07, 2003 11:12 pm Post subject: CALL A ORACLE STORED PROCEDURE FROM MQSI |
 |
|
Apprentice
Joined: 07 Apr 2003
Posts: 28
|
Hi to everybody,
I am trying to call a Oracle Stored Procedure from a Compute Node in a MQSI 2.1 Message Flow.
The code I am using is:
PASSTHRU ('{CALL test_me (?)}',InputBody.Message.Data.Call);
But I find the following error code when executing that part of code:
: Database error: SQL State 'HY000'; Native Error Code '6550'; Error Text '[MERA
NT][ODBC Oracle 8 driver][Oracle 8]ORA-06550: line 1, column 8:: EAIBKDESA1_BK.1
14f8f4c-f200-0000-0080-cfb54220f4dc: /build/S210_P/src/DataFlowEngine/ImbOdbc.cp
p: 247: ImbOdbcHandle::checkRcInner: :
This Oracle error code looks like if the problem were caused in the compilation of the source stored procedure (in this case called test_me), but I am able to compile and even execute this procedure throught a sqlplus session from my personal computer.
I think the trouble is related to odbc.ini configuration but, anyway, I would appreciate anyone´s help.
Cheers, |
|
| Back to top |
|
 |
| MikeTamari |
| Posted: Wed Apr 09, 2003 12:58 am Post subject: RE |
|
|
Apprentice
Joined: 04 Feb 2002
Posts: 26
|
Im using Oracle supplied ODBC driver, by installing Oracle 8.1.7 client on my NT server and the same type of call works fine.
Maybe you can try using this ODBC driver... |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Apr 09, 2003 6:04 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Mike,
| Quote: |
Im using Oracle supplied ODBC driver, by installing Oracle 8.1.7 client on my NT server and the same type of call works fine.
Maybe you can try using this ODBC driver...
|
Websphere MQ Integrator v2.1 only support specific drivers to connect to specific databases.
Refer to the WMQIv2.1 Administarion Guide (page 37):
| Quote: |
Defining an ODBC connection for the broker database
Oracle8
When you define a data source for Oracle8 you must choose the driver named "MQSeries MERANT 3.7 32-BIT Oracle8" .
|
This explicitly states which driver you must use and thereby infers that other divers are not supported.
This reference is applicable to both the broker and user database connections.
_________________
Regards, Ian |
|
| Back to top |
|
|
| MikeTamari |
| Posted: Wed Apr 09, 2003 6:30 am Post subject: |
|
|
Apprentice
Joined: 04 Feb 2002
Posts: 26
|
As far as I understand the statement that you quoted refers to
Defining an ODBC connection for the broker database while I was refering to a connection to a user/application DB. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 09, 2003 8:16 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
When talking about setting up ODBC data connections for user/application databases, the Administration guide also says
| Quote: |
| To create an ODBC conection, refer to "Defining an ODBC connection for the broker database" on page 34 |
.
The implication of this is that you can't use other drivers for user databases either.
Practically, it appears to be working for you. However, you might run into issues with transactionality or data conversion if the driver you are using does not work the same way that the Merant driver does. |
|
| Back to top |
|
|
| danielaphex |
| Posted: Wed Apr 09, 2003 8:19 am Post subject: |
|
|
Apprentice
Joined: 07 Apr 2003
Posts: 28
|
Anyway,
does anyone know the way I have to follow to solvent my porblem?? |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Apr 10, 2003 5:11 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
ORA-06550 appears to contain the line and column number of the error within the stored procedure or PL/SQL code.
An accompanying error message contains the actual error.
For example :
| Code: |
ORA-06550: line 1, column 8:
PLS-00201: identifier 'anIdentifier' must be declared
|
You have only included an extract of the error.
To be able to assist you with this further you will have to included a fuller error report.
You indicate that you can compile this procedure ?
Is this a Java written Oracle stored procedure or is this PL/SQL ?
Either way, to assist us in helping you, you would have to include the source of the procedure and the command being issued from the SQLPlus command line indicating that you can invoke this procedure successfully.
Can you also confirm the following :
| Code: |
You can execute the procedure from the SQLPlus command prompt where the command format is :
sqlplus username/password@servicename
where :
-> username is the DataSourceUserID/ServiceUserID (mqsicreatebroker command)
-> password is the DataSourceUserPassword/ServiceUserPassword (mqsicreatebroker command)
-> servivename is the service name as found in the tnsnames.ora file
|
_________________
Regards, Ian |
|
| Back to top |
|
|
| danielaphex |
| Posted: Thu Apr 10, 2003 7:53 am Post subject: |
|
|
Apprentice
Joined: 07 Apr 2003
Posts: 28
|
Hi Ian,
the procedure I am trying to invoke is a very simple example designed just to show whether is possible to call this from MQSI or not. Anyway if you want to see the code it is like this one:
| Quote: |
procedure test_me is
begin
insert into HR023_CONFIGURACION values ('a','b');
commit;
end; |
As you can see, apparently, there is no mistake in this fragment of code; besides it is possible to compile it and, even, to execute it from both the server directly or throught a sqlplus console opened in my computer. |
|
| Back to top |
|
|
| Ian |
| Posted: Fri Apr 11, 2003 1:26 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi,
Your call to the stored procedure from within WMQI :
| Quote: |
PASSTHRU ('{CALL test_me (?)}',InputBody.Message.Data.Call);
|
Your stored procedure definition :
| Quote: |
procedure test_me is
begin
insert into HR023_CONFIGURACION values ('a','b');
commit;
end;
|
The lookup for the stored procedure defined on the database will broadly cover three areas :
- the schema name (if none supplied then it will default to the useris as per the mqsicreatebroker command)
- the procedure name
- and the number of arguments for that procedure name
I suspect that your procedure is not being found (and that an appropriate WMQI error message is being returned) as your invoking a stored procedure which has one argument whereas you only have a stored procedure defined which takes no arguments.
I assume your call from the SQLPlus command line does not pass an argument ? If so, then this is not a valid test as it is not invoking the stored procedure in the same manner as from WMQI.
Change your call to the stored procedure from within WMQI to something like :
| Quote: |
PASSTHRU ('{CALL test_me}');
|
_________________
Regards, Ian |
|
| Back to top |
|
|
|
|
