Exec Sql Collection Get From A Varray(5) Of Char(5) Into C Host Variable Returns Nothing (Doc ID 1123736.1)

Last updated on JULY 05, 2017

Applies to:

Precompilers - Version: 10.2.0.1 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

Reading and writing data of type VARRAY(n) OF CHAR(m) does nothing. There are no error messages, but the host variables are not filled with the data.

TESTCASE
========
Create types in SQL*Plus as follows:

create type w2_a5char5 is varray(5) of char(5);
/
create type w2_a5num is varray(5) of number;
/

Create in.type containing:

case=lower
type w2_a5char5
type w2_a5num

and translate it with OTT as follows:

ott userid=scott/tiger intype=in.type outtype=out.type code=c hfile=demo.h

Create the following Pro*C testcase:

/* Operating system includes */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
/* Oracle includes */
EXEC SQL INCLUDE sqlca;
/* My includes - the collection type definitions */
#include "demo.h"
/* The host variables */
#define C_Max 5
EXEC SQL BEGIN DECLARE SECTION;
char     username[31];      /* Username/password for db connect */
char     password[31];
int      key;               /* PK for the table */
t_a5num  *p_a5num;          /* Pointers for the SQL VARRAY types */
t_a5vc5  *p_a5vc5;
int      a5num[C_Max];      /* C variables for the VARRAYs */
char     a5vc5[C_Max][6];
short    a5numi[C_Max];     /* Indicator variables for the VARRAYs */
short    a5vc5i[C_Max];
EXEC SQL END DECLARE SECTION;
main()
{
    int  i;
    int  j;
    printf( "CONNECT\n" );
    strcpy( username, "scott" );
    strcpy( password, "tiger" );
    EXEC SQL CONNECT :username IDENTIFIED BY :password;
    printf( "ALLOCATE P_A5NUM, P_A5VC5\n" );
    EXEC SQL ALLOCATE :p_a5num;
    EXEC SQL ALLOCATE :p_a5vc5;
    key = 1;
    printf( "SELECT row #%d\n", key );
    EXEC SQL SELECT a5num,a5vc5 INTO :p_a5num,:p_a5vc5 FROM my_table WHERE pk = :key;
    printf( "COLLECTION GET \n" );
    sqlca.sqlerrd[2] = 0;
    EXEC SQL COLLECTION GET :p_a5num INTO :a5num:a5numi;
    printf( "  read: %d elements from A5NUM\n", sqlca.sqlerrd[2] );
    printf( "  data: %d %d %d %d %d\n", a5num[0], a5num[1], a5num[2], a5num[3], a5num[4] );
    EXEC SQL COLLECTION GET :p_a5vc5 INTO :a5vc5:a5vc5i;
    printf( "  read: %d elements from A5CVC5\n", sqlca.sqlerrd[2] );
    printf( "  data: " );
    for ( i = 0; i < C_Max; i++ ) {
        a5vc5[i][5] = 0;
        printf( "%s ", a5vc5[i] );
    }
    printf( "\n" );
    printf( "MODIFY\n" );
    for ( i = 0; i < C_Max; i++ ) {
        a5num[i] = ( a5num[i] * 2 ) % 10000;
        for ( j = 0; j < 5; j++ ) {
            if ( ( a5vc5[i][j] >= 'A' ) && ( a5vc5[i][j] <= 'Z' ) ) {
                a5vc5[i][j] = ( ( a5vc5[i][j] - 'A' + 1 ) % 26 ) + 'A';
            } else if ( ( a5vc5[i][j] != ' ' ) && ( a5vc5[i][j] != 0 ) ) {
                a5vc5[i][j] = 'A';
            }
        }
    }
    printf( "  data: %d %d %d %d %d\n", a5num[0], a5num[1], a5num[2], a5num[3], a5num[4] );
    printf( "  data: " );
    for ( i = 0; i < C_Max; i++ ) printf( "%s ", a5vc5[i] );
    printf( "\n" );
    printf( "COLLECTION SET \n" );
    EXEC SQL COLLECTION SET :p_a5num TO :a5num:a5numi;
    EXEC SQL COLLECTION SET :p_a5vc5 TO :a5vc5:a5vc5i;
    printf( "UPDATE row\n" );
    EXEC SQL UPDATE my_table
                SET a5num = :p_a5num, a5vc5 = :p_a5vc5
              WHERE pk = :key;
    printf( "COMMIT\n" );
    EXEC SQL COMMIT;
    printf( "COLLECTION RESET \n" );
    EXEC SQL COLLECTION RESET :p_a5num;
    EXEC SQL COLLECTION RESET :p_a5vc5;
}


Build with

make -f demo_proc.mk build EXE=test OBJS=test.o PROCFLAGS="intype=out.type"

The output is:
num: 1 2 3 4 5
str: '' '' '' '' ''

The expected output is:
num: 1 2 3 4 5
str: 'a' 'bb' 'ccc' 'dddd' 'eeeee'

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