Need help with custom type VARCHAR2 Array
sankethpb opened this issue · 4 comments
Describe the help request
Request help in inserting values with a Stored Procedure having custom type of VARCHAR2 Array. I have the data in a slice.
Custom Type: VARCHAR2_ARRAY
CREATE OR REPLACE TYPE MYSCHEMA."VARCHAR2_ARRAY" AS TABLE OF VARCHAR2(2000);
Stored Procedure
MYSCHEMA.MYPROCEDURE(p_in_serial_nos VARCHAR2_ARRAY,
p_in_created_by VARCHAR2,
p_out_insert_status OUT VARCHAR2)
The 'p_in_serial_nos' data is in a string slice, I am not sure how to set it as object type. I am able to get the object type but unable to set the slice data.
Kindly share an example. Thanks a lot in advance.
Your oracle client version
e.g. 12.2.0.1.0
Your goracle version
e.g. 2.19
Your go version
e.g. 1.12.1
Your gcc version
e.g. 8.1.0
Machine (please complete the following information):
- OS: Windows
- Architecture x64
- Version: 10 Pro
Try the current master branch, see TestCallWithObject in z_plsql_types_test.go
TL;DR; you have to GetObjectType("MYSCHEMA.VARCHAR2_ARRAY"), CreateObject with the type, populate it with obj.Collection().Append() then use it in the call.
Or (the waaaay easier way) use PL/SQL associated array:
var status string
ExecContext(ctx, `
DECLARE
TYPE vc2000_tab_typ IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
v_tbl vc2000_tab_typ := :1;
v_idx PLS_INTEGER;
v_arr MYSCHEMA.VARCHAR2_ARRAY := MYSCHEMA.VARCHAR2_ARRAY();
BEGIN
-- copy the PL/SQL associative array to the nested table:
v_idx := v_tbl.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_arr.EXTEND;
v_arr(v_arr.LAST) := v_tbl(v_idx);
v_idx := v_tbl.NEXT(v_idx);
END LOOP;
-- call the procedure:
MYSCHEMA.MYPROCEDURE(p_in_serial_nos=>v_arr,
p_in_created_by=>:2,
p_out_insert_status=>:3);
END;
`, goracle.PlSQLArrays(), []string{"a", "b"}, "me", sql.Out{Dest:&status})
...
(not tested)
@tgulacsi
Thank you very much, the code with PLSQL Arrays worked.
The initialization of v_idx PLS_INTEGER; in the declare section had issues, but figured it out. (Before you fixed it here)
Thanks a lot again.
I did not try the Object way, could you please let me know which method would be have more performance?