Comments (12)
Thanks @anthony-tuininga , both of your responses nailed the problem, I could fix both!
from odpi.
Ok.
The called function appends an element to the array, so I have to call setNumElementsInArray with the maximum acceptable array size.
from odpi.
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)
?
from odpi.
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.
from odpi.
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!
from odpi.
Great! I'll close this, then.
from odpi.
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
from odpi.
That one should be type DPI_ORACLE_TYPE_NATIVE_INT.
from odpi.
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)
from odpi.
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!
from odpi.
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")!
from odpi.
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!
from odpi.
Related Issues (20)
- XMLTYPE as "real" XML HOT 3
- Ability to set OCI_ATTR_LOBPREFETCH_SIZE HOT 7
- "ORA-03106: fatal two-task communication protocol error" when CLOB is fetched as strings and then lob locators HOT 3
- core dumps generated in libclntsh when timeouts are configured HOT 10
- Any plan to support Apple's M1 plat? HOT 1
- Is there a reference example dequeue message with message ID dequeue option HOT 9
- When ODPI tries to translate a variable to a temporary LOB, and there is inadequate TEMP tablespace, ODPI segfaults when the cursor is closed HOT 6
- M1 Mac issue ( incompatible architecture) HOT 2
- How to use ODPI-C with Oracle Times Ten? HOT 9
- Column Alias Error HOT 7
- Add an option to convert numbers between double and decimal in the same way as node.js, python3, ruby, rust or so HOT 5
- Oracle Rust driver HOT 2
- Option to get json float as float. It is got as double now. HOT 8
- Bytes after nul in members of dpiXid are zeroed. HOT 2
- Is DPI_TPC_END_SUSPEND an undocumented flag in the OCI manual? HOT 3
- dpiEnqOptions_setVisibility and dpiLob_getType are listed not in round_trips.rst HOT 2
- Get column name for position HOT 2
- `dpiStmt_execute` with `DPI_MODE_EXEC_PARSE_ONLY` will create a table. HOT 4
- Retrieving CLOB from an OUT parameter of an Store Procedure HOT 1
- ERROR: DPI-1047: Cannot locate a 32-bit Oracle Client library HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from odpi.