Bind pl/sql index of table with non-integer index

[Anonymous]

PL/SQL code is:

TYPE TEST_VCHAR200_PLSQLTBL IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR(255);

PROCEDURE tbl_proc_aarray(pi_tbl IN TEST_VCHAR200_PLSQLTBL, po_tbl out TEST_VCHAR200_PLSQLTBL) is
BEGIN
po_tbl := pi_tbl;
po_tbl('xxx') := 'new value';
END;

ocilib code:

OCI_Statement *statement = OCI_StatementCreate(connection);

if (!OCI_Prepare(statement, "BEGIN test_pg.tbl_proc_aarray(:s1, :s2); END;")) {
printf("Can't prepare\n");
return 0;
}

char s1[32][64];
char s2[32][64];
memset(s1, 0, sizeof(s1));
memset(s2, 0, sizeof(s2));

OCI_BindArrayOfStrings(statement, ":s1", (char)s1, 63, 3);
OCI_BindArrayOfStrings(statement, ":s2", (char)s2, 63, 32);

for (unsigned i = 0; i < 3; i++)
sprintf(s1[i], "Name %d", i+1);

if (!OCI_Execute(statement)) {
printf("Can't execute\n");
return 0;
}

for (unsigned i = 0; i < 32; i++) {
printf("[%u] s1 = %s, s2 = %s\n", i, s1[i], s2[i]);
}

Result:

code : ORA-06550
msg : ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TBL_PROC_AARRAY'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TBL_PROC_AARRAY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Does anybody know way to retrieve both part of table - index and data ?

[Vincent Rogier]

Hi,

Oracle OCI does not allow to bind associative arrays indexed by varchar2.
Only associative array indexed by pls_integer can be binded from Oracle client.

But you can use a VARRAYs or a nested table instead that can be used in C/C++ program.
With OCILIB C API, you need to use an OCI_Collection (Collection object with the OCILIB C++ API) to use VARRAYs and pl/sql nested tables.

Regards,

Vincent

[Anonymous]