OCI7 Calls Gets ORA-3116 When Executing Stored Procedure With More Parameters

(Doc ID 1216553.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 8th May 2015***

Symptoms

1. IBM's FILENET application uses OCI7 and it works fine up to certain number of parameters. But at parameter 120, it gets ORA-3116 occurs. 
2. Number of parameters does not matter, ORA-3116 problem can occur when passing at 80th parameter or 139th parameter.  In case of the IBM's vendor error ORA-3116 occurs when passing 120th parameter using IBM's FILENET application.  IBM recreated the problem with using OCI7 (Pure OCI7 code) and it reproduces ORA-3116 at 139th parameter. 
3. Same application running on Windows environment works fine. 
4. Same procedure call when run from "sqlplus" connecting to Oracle database passing in 139 parameters works fine without getting ORA-3116 error.  
5. OCI code is binding the varchar parameters in 4000 byte buffers, null terminated, with a type of SQLT_STR (null-terminated string).  The longest parameter is about 35 bytes, null terminated. 
6. Suggestion was to define the out parameter as a VARCHAR2 (instead of a CHAR or using %type) has no IMPACT.  ORA-3116 still occurs. 
Per Bug 5044087, Modifying procedure to have OUT datatype as VARCHAR2 instead of CHAR.
e.g., 
create or replace procedure testproc(in_param in char,
inout_param in out varchar2, <--- was char
out_param out varchar2, <--- was char
msg out varchar2) as
...
7. Parameter BLANK_TRIMMING = TRUE setting has no IMPACT.  ORA-3116 still occurs. 
The parameter which takes care of the trimming is blank_trimming which should be set to true. 
i.e., Set the parameter BLANK_TRIMMING=TRUE in init.ora of Database Server. 
8. Parameter nls_length_semantics setting to BYTE has no IMPACT.  ORA-3116 still occurs. 
To take care of the multibyte length, parameter nls_length_semantics needs to be set to BYTE instead of CHAR.  

Changes

IBM FileNet Process Engine is a C++ application that can let users call Oracle Stored Procedures.  FileNet code uses OCI7 APIs to call the Stored Procedures.  Typically a Stored Procedure with parameters (varchar2 and char data types).  This Stored Procedure can be called just fine if the servers (Oracle and "Process Engine Server") are on Windows environment.  But when the Process Engine is on AIX, it gets the "ORA-3116: invalid buffer length passed to a conversion routine" error. 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms