In 11.2.0.2 ORA-12899 occurs with Indicator variables and cursor_sharing is set to FORCE (Doc ID 1290471.1)

Last updated on JULY 05, 2017

Applies to:

Precompilers - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 29th May 2017***

Symptoms

A  11.2.0.2  Pro*C application executes an INSERT statement which contains indicator variables and has the CURSOR_SHARING database parameter set to FORCE returns the following error.

ORA-12899: value too large for column


The same code works prior  to 11.2.0.2.

The same INSERT also fails when using 11.2.0.2 SQL*Plus with indicator / bind variables.

Also an ora-907 error has been reported in some cases

ORA-00907: missing right parenthesis




Changes

1. Create table

CREATE TABLE BIB1
(BIB_ID NUMBER (10),
BIB_TYPE NUMBER (10),
BIB_DATE1 DATE,
BIB_CHAR1 VARCHAR2 (1),
BIB_CHAR2 VARCHAR2 (1));


2. Set cursor_sharing to Force

ALTER SYSTEM SET cursor_sharing='FORCE';


3. Pro*C Code

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sqlca.h>

#define Z(_v) _v.arr [_v.len] = '\0'
#define VARCPY(_v,_s) strcpy ((char *)_v.arr, _s), _v.len = strlen (_s)
#define STRVARCPY(_s,_v) Z(_v), strcpy (_s, (char *) _v.arr)
#define UTLBDD_RESERVEE (-54)
#define UTLBDD_IND_NULL -1

/* Declare error handling function. */
void sql_error(msg)
char *msg;
{
char err_msg[128];
size_t buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}


static void connect (void)
{
VARCHAR user [30 + 1];
VARCHAR password [30 + 1];
struct sqlca sqlca;

VARCPY(user, "scott");
VARCPY(password, "tiger");
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
EXEC SQL CONNECT :user IDENTIFIED BY :password ;

}

int main (int argc, char *argv [])
{
struct sqlca sqlca;
short i_bib_type;
short i_bib_id;
int bib_id ;
int bib_type ;
char bib_date1 [14 + 1];
char bib_char1 [1 + 1];
char bib_char2 [1 + 1];
printf ("Hi there \n") ;
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
connect();
printf ("Connected fine \n") ;
bib_id = 1 ;
bib_type = 0 ;
strcpy (bib_char1, "X") ;
strcpy (bib_char2, "Z") ;
strcpy (bib_date1, "") ;

i_bib_type = UTLBDD_IND_NULL ;
i_bib_id = UTLBDD_IND_NULL ;
printf ("Now Insert \n") ;
EXEC SQL INSERT INTO BIB1(BIB_ID, BIB_TYPE, BIB_DATE1, BIB_CHAR1, BIB_CHAR2)
VALUES (:bib_id:i_bib_id, :bib_type:i_bib_type,TO_DATE (:bib_date1, 'YYYYMMDDHH24MISS'), :bib_char1, :bib_char2);

printf ("Inserted Fine \n") ;
EXEC SQL COMMIT;
printf ("Done exit \n") ;
return 0 ;
}


4. Build the Pro*C Executable

make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk build EXE=ITest OBJS=ITest.o


Output

> ITest
Hi there
Connected fine
Now Insert

ORACLE error--

ORA-12899: value too large for column "SCOTT"."BIB"."BIB_CHAR1" (actual: 16, maximum: 1)


5. SQL Test

variable bib_id number;
variable bib_type number;
variable bib_date1 char(15);
variable bib_char1 char(1);
variable bib_char2 char(1);

exec :bib_id := 1;
exec :bib_type := 0;
exec :bib_date1 := null;
exec :bib_char1 := 'X';
exec :bib_char2 := 'Y';

INSERT INTO BIB1(BIB_ID, BIB_TYPE, BIB_DATE1, BIB_CHAR1, BIB_CHAR2)
VALUES (:bib_id:i_bib_id, :bib_type:i_bib_type,TO_DATE (:bib_date1, 'YYYYMMDDHH24MISS'), :bib_char1, :bib_char2);


ERROR at line 2:
ORA-12899: value too large for column "SCOTT"."BIB1"."BIB_CHAR1" (actual: 16,maximum: 1)

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