IBM Support

Problems to generate BO when using ODA with Oracle Stored Procedure that has boolean parameters

Troubleshooting


Problem

Your Database is Oracle and you have a stored procedure that has Boolean parameters. The BO generated by ODA does not contain the Boolean attribute and if you add the attribute manually after executing your scenario you get the following error: [Type: Error] [MsgID: 37010] [Mesg: Stored Procedure Execution Failed: java.sql.SQLException: ORA-06550: line 1, column 1: PLS-00306: wrong number or types of arguments in call to 'MYSTOREDPROC' ORA-06550: line 1, column 1: PL/SQL: Statement ignored ]

Cause

The JDBC driver does not support passing boolean parameters to Oracle PL/SQL stored procedures.

Resolving The Problem

You can work around this problem by wrapping your stored procedure with a second stored procedure and create your BO from wrapper procedure. Define the wrapper stored procedure that it can accept arguments as an INT and it can convert it to Boolean before calling your stored procedure and passing the Boolean parameters:
Here is an example:

CREATE OR REPLACE PROCEDURE wrapperproc(x int)
AS
BEGIN
IF (x=1) THEN
mystoredproc(TRUE);
ELSE
mystoredproc(FALSE);
END IF;
END;


CREATE OR REPLACE PROCEDURE mystoredproc(x boolean)
AS
BEGIN
[...]
END;

[{"Product":{"code":"SSMKUK","label":"WebSphere Adapters Family"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Adapter for JDBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"2.4;2.5;2.6","Edition":"-NA-","Line of Business":{"code":"","label":""}}]

Document Information

More support for:
WebSphere Adapters Family

Software version:
2.4, 2.5, 2.6

Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows

Document number:
98871

Modified date:
15 June 2018

UID

swg21304726

Manage My Notification Subscriptions