Bind PL/SQL array types

[tgulacsi]

HI,

I need some help: I want to bind PL/SQL array types - use of DBMS_OUTPUT.get_lines works, so OUT is OK. For IN OUT types (TYPE vc_tab_typ IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; PROCEDURE x(p_vc IN OUT NOCOPY vc_tab_typ)),

1. dpiConn_newVar" typ=2001 natTyp=3004 arraySize=2 bufSize=32767 isArray=1,
2. dpiVar_setFromBytes pos=1 then pos=2, and then call
3. dpiVar_setNumElementsInArray n=2
4. dpiStmt_bindByPos
5. dpiStmt_execute result is ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array

        stmt.go:259: enter="bindVars" args=[]driver.NamedValue{driver.NamedValue{Name:"", Ordinal:1, Value:sql.Out{_Named_Fields_Required:struct {}{}, Dest:(*[]string)(0xc42000c380), In:true}}}
        stmt.go:341: msg="bindVars" i=0 in=true out=true value="[]string []string{\"string\", \"bring\"}"
        stmt.go:482: msg="newVar" i=0 plSQLArrays=true typ=2001 natTyp=3004 sliceLen=2 bufSize=32767
        conn.go:179: C="dpiConn_newVar" conn=&goracle._Ctype_struct_dpiConn{} typ=2001 natTyp=3004 arraySize=2 bufSize=32767 isArray=1 v=(*goracle._Ctype_struct_dpiVar)(nil)
        stmt.go:511: msg="set" i=0 j=1 n=2 v="string=\"string\""
        stmt.go:670: C="dpiVar_setFromBytes" dv=&goracle._Ctype_struct_dpiVar{} pos=1 p=(*goracle._Ctype_char)(0xc4200126a8) len=6
        stmt.go:511: msg="set" i=0 j=2 n=2 v="string=\"bring\""
        stmt.go:670: C="dpiVar_setFromBytes" dv=&goracle._Ctype_struct_dpiVar{} pos=2 p=(*goracle._Ctype_char)(0xc420012710) len=5
        stmt.go:519: C="dpiVar_setNumElementsInArray" n=2
        stmt.go:530: C="dpiStmt_bindByPos" dpiStmt=&goracle._Ctype_struct_dpiStmt{} i=0 v=&goracle._Ctype_struct_dpiVar{}
        stmt.go:174: C="dpiStmt_execute" mode=0x20 colCount=0x0
        z_test.go:236: ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array
                ORA-06512: at line 1

                dpiStmt_execute(mode=32 arrLen=1024)
                gopkg.in/goracle%2ev2.(*statement).ExecContext
                        /home/gthomas/src/gopkg.in/goracle.v2/stmt.go:179

I've tried ofsetting the pos at dpiVar_setFromBytes, changing n of setNumElementsInArray, resulting in different errors.

What am I doing wrong?

Thanks in advance, Tamás Gulácsi

[tgulacsi]

Ok. The called function appends an element to the array, so I have to call setNumElementsInArray with the maximum acceptable array size.

[tgulacsi]

Next question: how to invoke with TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER?

        z_test.go:266: num=[]string{"3.14", "-2.48"}
        stmt.go:261: enter="bindVars" args=[]driver.NamedValue{driver.NamedValue{Name:"", Ordinal:1, Value:sql.Out{_Named_Fields_Required:struct {}{}, Dest:(*interface {})(0xc42006a640), In:true}}}
        stmt.go:336: msg="bindVars" i=0 in=true out=true value="[]string []string{\"3.14\", \"-2.48\"}"
        stmt.go:493: msg="newVar" i=0 plSQLArrays=true typ=2001 natTyp=3004 sliceLen=1024 bufSize=32767 isSlice=true
        conn.go:179: C="dpiConn_newVar" conn=&goracle._Ctype_struct_dpiConn{} typ=2001 natTyp=3004 arraySize=1024 bufSize=32767 isArray=1 v=(*goracle._Ctype_struct_dpiVar)(nil)
        stmt.go:521: C="dpiVar_setNumElementsInArray" i=0 n=2
        stmt.go:530: msg="set" i=0 j=0 n=2 v="string=\"3.14\""
        stmt.go:689: C="dpiVar_setFromBytes" dv=&goracle._Ctype_struct_dpiVar{} pos=0 p=(*goracle._Ctype_char)(0xc4200129a8) len=4
        stmt.go:530: msg="set" i=0 j=1 n=2 v="string=\"-2.48\""
        stmt.go:689: C="dpiVar_setFromBytes" dv=&goracle._Ctype_struct_dpiVar{} pos=1 p=(*goracle._Ctype_char)(0xc420012a08) len=5
        stmt.go:549: C="dpiStmt_bindByPos" dpiStmt=&goracle._Ctype_struct_dpiStmt{} i=0 v=&goracle._Ctype_struct_dpiVar{}
        stmt.go:176: C="dpiStmt_execute" mode=0x20 colCount=0x0
        z_test.go:272: BEGIN test_pkg.inout_num(:1); END;
                ORA-06550: line 1, column 26:
                PLS-00418: array bind type must match PL/SQL table row type
                ORA-06550: line 1, column 7:
                PL/SQL: Statement ignored

                dpiStmt_execute(mode=32 arrLen=-1)
                gopkg.in/goracle%2ev2.(*statement).ExecContext
                        /home/gthomas/src/gopkg.in/goracle.v2/stmt.go:181

where the called function's signature isPROCEDURE inout_num(p_num IN OUT num_tab_typ) ?

[anthony-tuininga]

The called function appends an element to the array, so I have to call setNumElementsInArray with the maximum acceptable array size.

Actually, this is not true. When you create the variable you need to set the maximum size that the array can contain with the parameter maxArraySize to the function dpiConn_newVar(). Since the application is specifying the buffers it must also specify the maximum size. The function dpiVar_setNumElementsInArray() is used to specify the actual number of elements in the array, not the maximum.

[anthony-tuininga]

Next question: how to invoke with TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER?

You are using the type 2001 (DPI_ORACLE_TYPE_VARCHAR) but for a "TABLE OF NUMBER" type you need to use the type DPI_ORACLE_TYPE_NUMBER instead. PL/SQL is quite picky about this. The type must match exactly. No coercion is permitted!

[tgulacsi]

Thanks @anthony-tuininga , both of your responses nailed the problem, I could fix both!

[anthony-tuininga]

Great! I'll close this, then.

[tgulacsi]

Just one last case: TYPE int_tab_typ IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; PROCEDURE inout_int(p_int IN OUT int_tab_typ)

        stmt.go:341: msg="bindVars" i=0 in=true out=true value="[]int32 []int32{3, 1, 4}"
        stmt.go:358: doManyCount=1 arrLen=-1 doExecMany=false minArrLen="maxArrLen"
        stmt.go:559: msg="newVar" i=0 plSQLArrays=true typ=2010 natTyp=3000 sliceLen=1024 bufSize=0 isSlice=true
        conn.go:181: C="dpiConn_newVar" conn=&goracle._Ctype_struct_dpiConn{} typ=2010 natTyp=3000 arraySize=1024 bufSize=0 isArray=1 v=(*goracle._Ctype_struct_dpiVar)(nil)
        stmt.go:589: C="dpiVar_setNumElementsInArray" i=0 n=3
        stmt.go:598: msg="set" i=0 j=0 n=3 v="int32=3"
        stmt.go:598: msg="set" i=0 j=1 n=3 v="int32=1"
        stmt.go:598: msg="set" i=0 j=2 n=3 v="int32=4"
        stmt.go:611: C="dpiStmt_bindByPos" dpiStmt=&goracle._Ctype_struct_dpiStmt{} i=0 v=&goracle._Ctype_struct_dpiVar{}
        stmt.go:176: C="dpiStmt_execute" mode=0x20 colCount=0x0
        z_test.go:279: BEGIN test_pkg.inout_int(:1); END;
                ORA-06550: line 1, column 26:
                PLS-00418: array bind type must match PL/SQL table row type
                ORA-06550: line 1, column 7:
                PL/SQL: Statement ignored

                dpiStmt_execute(mode=32 arrLen=-1)
                gopkg.in/goracle%2ev2.(*statement).ExecContext
                        /home/gthomas/src/gopkg.in/goracle.v2/stmt.go:181

[anthony-tuininga]

That one should be type DPI_ORACLE_TYPE_NATIVE_INT.

[tgulacsi]

Ok, I've miscounted (2010 is NUMBER, 2009 is NATIVE_INT) But it is the same with 2009:

        stmt.go:559: msg="newVar" i=0 plSQLArrays=true typ=2009 natTyp=3000 sliceLen=1024 bufSize=0 isSlice=true
        conn.go:181: C="dpiConn_newVar" conn=&goracle._Ctype_struct_dpiConn{} typ=2009 natTyp=3000 arraySize=1024 bufSize=0 isArray=1 v=(*goracle._Ctype_struct_dpiVar)(nil)
        stmt.go:589: C="dpiVar_setNumElementsInArray" i=0 n=3
        stmt.go:598: msg="set" i=0 j=0 n=3 v="int32=3"
        stmt.go:598: msg="set" i=0 j=1 n=3 v="int32=1"
        stmt.go:598: msg="set" i=0 j=2 n=3 v="int32=4"
        stmt.go:611: C="dpiStmt_bindByPos" dpiStmt=&goracle._Ctype_struct_dpiStmt{} i=0 v=&goracle._Ctype_struct_dpiVar{}
        stmt.go:176: C="dpiStmt_execute" mode=0x20 colCount=0x0
        z_test.go:279: BEGIN test_pkg.inout_int(:1); END;
                ORA-06550: line 1, column 26:
                PLS-00418: array bind type must match PL/SQL table row type
                ORA-06550: line 1, column 7:
                PL/SQL: Statement ignored

                dpiStmt_execute(mode=32 arrLen=-1)

[anthony-tuininga]

In that case, it might not work that way at all. Since pls_integer is a PL/SQL type that is actually quite likely. In that case you have to use the other method -- using objects instead. I don't have an ODPI-C sample for that at the moment. This cx_Oracle example should help if you want to give it a try. I'll create an ODPI-C example at some point but it may be a while!

[tgulacsi]

It is the same with TYPE int_tab_typ IS TABLE OF BINARY_INTEGER INDEX BY PLS_INTEGER. I can live without this, just strange.

But you've aroused my curiosity - would using objects allow me to call stored procedures with records / table of records defined only in PL/SQL package headers (not database types from CREATE TYPE!)? That'd save me a lot of work (I have a generator which slices the "table of records" into "tables of tables of simple types")!

[anthony-tuininga]

Yes! It supports all collections and records. And yes, collections of records, too. Here is another example for PL/SQL records. I'll be adding these examples for ODPI-C too, at some point -- just not sure when!