Rather compare your DB config file as you might have missed steps.
There is nothing with your source code as it was tested in one Environment.
Do you mind sharing those DB config files with me to inspect? Both Windows and AWS DB config pls
Original Message:
Sent: Wed October 09, 2024 07:34 PM
From: Santhosh Ramanathan
Subject: Error while calling Oracle Stored procedure from ESQL
Thanks Justice Theledi.
The StoredProcedure call is working fine , when I deploy the bar file in ACE SaaS console on AWS. But when i make the call from my developer toolkit (windows environment), getting response as "numeric overflow". On further analysis, the response from Stored Procedure is not being handled properly in windows environment (response from Stored Procedure is a single character value , i.e. 'S' or 'M' or 'E').
It looks like issue related to ODBC properties set on Windows environment, yet to identify the reason.
ACE versions are:
Windows: 12.0.2.0 - Stored procedure call failing with error as "numeric overflow"
ACE SaaSconsole: 12.0.5.0-r2 - Stored procedure call is working fine
Oracle DB: v9.2.0.8.0
------------------------------
Thanks,
Santhosh Ramanathan
Original Message:
Sent: Wed October 02, 2024 04:07 AM
From: Tlabo Justice Theledi
Subject: Error while calling Oracle Stored procedure from ESQL
Hi Santoch,
Based on the findings and errors details you provided, you might have missed the following section
Add the following section to the odbc.ini file on {Coordination Host}
Top of the file under the List of data sources stanza section:
YourApplicationName=DataDirect 7.0 SQL Server Wire Protocol
Bottom of Individual data source stanzas section:
;# MSSQL YourApplicationName stanza
[SABOR]
Driver=/opt/IBM/mqsi/ace-11.0.0.12/server/ODBC/drivers/lib/UKsqls95.so
Description=DataDirect 7.0 SQL Server Wire Protocol
Address={Database Instance},{Database Port}
Database={Database Name}
HostName={Database Instance}
PortNumber={Database Port}
AnsiNPW=1
LoginTimeout=0
QueryTimeout=0
QuotedId=No
ColumnSizeAsCharacter=1
Run the following command to set the username and password for the YourApplicationName Datasource:
mqsisetdbparms {Integration Node} -n YourApplicationName -u {Database USer} -p {Password}
Reload the execution group with the following command:
mqsireload {Integration Node} –e ExecutionGroup
Test the database connection using the following command:
mqsicvp {Integration Node} –n YourApplicationName
Let me know if this works or not, then we can try something else
Regards
Justice Theledi
------------------------------
Tlabo Justice Theledi
Original Message:
Sent: Tue October 01, 2024 09:04 PM
From: Santhosh Ramanathan
Subject: Error while calling Oracle Stored procedure from ESQL
Hi,
I have a requirement to invoke an Oracle Stored Procedure from ACE (v12) using ESQL. The Stored procedure is having 3 parameters and data formats for those parameters are VARCHAR, VARCHAR and CLOB respectively.
Have declared the procedure definition in ESQL as follows:
CREATE PROCEDURE ProcName(INOUT status CHAR,INOUT error CHAR,IN inpdata CHAR) LANGUAGE DATABASE EXTERNAL NAME "<Schema>.<Package>.<Procedure>";
When I call the stored procedure as : CALL ProcName('','','test message'), getting error as
Error occured in procedure (ProcName). Root SQL exception (-1, odbc32.dll). Child SQL exception (HY000, -1426, [IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01426: numeric overflow)
When I test the same using Oracle SQLDeveloper tool, the procedure is working fine and, getting the error only when trying to invoke procedure from ESQL. What could be the reason for this error ?
------------------------------
Thanks,
Santhosh Ramanathan
------------------------------