Pro*C Array Fetch Can Return Too Many Rows (Doc ID 1335978.1)

Last updated on JULY 05, 2017

Applies to:

Precompilers - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

If a Pro*C program has an EXEC SQL SELECT INTO statement that fetches into an array, and is executed many times in a loop, using a different bind value for the WHERE clause, then the number of rows returned can exceed the array size by 1 in certain circumstances.

For example if the array has 50 rows and the query returns 60,55,7,60 rows in subsequent executions, then the number of rows returned is:

query result 60 --> fetches 50 (i.e. size of array).
query result 55 --> fetches 50
query result 7 --> fetches 7
query result 60 --> fetches 51

The problem seems to occur consistently on the execution that follows a query that fetches less than the maximum array size.

The following test program demonstrates this issue:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <unistd.h>

#define DB_ROWS      60
#define NUM_TO_FETCH 50

EXEC SQL INCLUDE SQLCA;

typedef struct res
{
    int      ora_sel_n;
    varchar  ora_sel_str[4000];
} res;
 
typedef struct res_ind
{
    short    ora_sel_n;
    short    ora_sel_str;
} res_ind;
 
int main(int argc, char *argv[])
{
 
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR hostDbLogin[61 + 1];

        int      ora_var;
        int      ora_ins_n[DB_ROWS] = { 1,2,3,4,5,6,7,8,9,10,
                                   11,12,13,14,15,16,17,18,19,20,
                                   21,22,23,24,25,26,27,28,29,20,
                                   31,32,33,34,35,36,37,38,39,30,
                                   41,42,43,44,45,46,47,48,49,50,
                                   51,52,53,54,55,56,57,58,59,60};
        varchar  ora_ins_str[DB_ROWS][11];

        res r[NUM_TO_FETCH];
        res_ind ri[NUM_TO_FETCH];
    EXEC SQL END DECLARE SECTION;
    char    *dbUser = "scott";
    char    *dbPswd = "tiger";
    int lcv;
    int lcv2;
    int lcv3;
    int var[] = {55,55,3,55,55};
         
    /*
     * Initializel Variables
     */     
     hostDbLogin.len  = sprintf((char *)hostDbLogin.arr, "%s/%s", dbUser,dbPswd);
     for (lcv3=0; lcv3<DB_ROWS; lcv3++)
         ora_ins_str[lcv3].len = sprintf(ora_ins_str[lcv3].arr, "abc-%2d-abc", lcv3);
   
   
     /*     
      * Make new db connection
      */
     EXEC SQL CONNECT :hostDbLogin;
     if (sqlca.sqlcode != 0)
     {
         printf("connect: sqlcode: [%d]\n", sqlca.sqlcode);
             return(-1);
     }
     printf("connected\n");
   
   
    /*
     * create table
     */     
    EXEC SQL DECLARE test_bug_tmrf TABLE (n number, s varchar2(2));

    EXEC SQL EXECUTE IMMEDIATE 'DROP TABLE test_bug_tmrf';
    if (sqlca.sqlcode == -942)
        ;
    else
    if (sqlca.sqlcode != 0)
    {
        printf("drop: sqlcode: [%d] [%.*s]\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return(-1);
    }
    else
        printf("dropped table\n");
   
    EXEC SQL EXECUTE IMMEDIATE 'CREATE TABLE test_bug_tmrf (n number, s varchar2(20))';
    if (sqlca.sqlcode != 0)
    {
        printf("create: sqlcode: [%d] [%.*s]\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return(-1);
    }
    printf("created table\n");
   
 
    /*
     * insert test data: 10 rows with values from 1 to 10
     */     
    EXEC SQL INSERT INTO test_bug_tmrf (n, s) VALUES (:ora_ins_n, :ora_ins_str);
    if (sqlca.sqlcode != 0)
    {
        printf("insert: sqlcode: [%d] [%.*s]\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return(-1);
    }
    printf("inserted  [%d] rows\n", sqlca.sqlerrd[2]);
    EXEC SQL COMMIT;


    /*
     * Enable tracing
     */     
    EXEC SQL ALTER SESSION SET tracefile_identifier='demo_bug_fetch_too_many';
    EXEC SQL ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
   
 
    /*
     * Run 5 select statements
     * - 1: return more rows than array holds
     * - 2: return more rows than array holds
     * - 3: return LESS rows than array holds
     * - 4: return more rows than array holds - notice in sql trace that one extra row is fetched
     * - 5: return more rows than array holds
     */     
    for (lcv=0; lcv < 5; lcv++)
    {
        ora_var = var[lcv];
        EXEC SQL SELECT n, s INTO :r:ri FROM test_bug_tmrf WHERE n < :ora_var ORDER BY 1;
        if ((sqlca.sqlcode != 0) && (sqlca.sqlcode != -2112) && (sqlca.sqlcode != 1403))
        {
            printf("select: sqlcode: [%d] [%.*s]\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
            return(-1);
        }


        printf("\n\nfetched   [%d] rows, criteria was: <= %d\n", sqlca.sqlerrd[2], ora_var);
        for(lcv2=0; lcv2<NUM_TO_FETCH; lcv2++)
        {
            r[lcv2].ora_sel_str.arr[r[lcv2].ora_sel_str.len] = 0x0;
            printf("ora_sel_str[%d] = [%s]\n", lcv2, r[lcv2].ora_sel_str.arr);
        }
       
    }
 
    return(0);
}  

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