[database/sql] table of varchar2 is not equal []string

[ducknificient (Jeremy Kenn)]

Hi, in oracle i have this type

create or replace TYPE TYPE_SLICE
AS TABLE OF varchar2(4000);

in golang, i’m using the library https://github.com/sijms/go-ora.

i run a stored procedure that has OUT parameter of type slice

CREATE OR REPLACE PROCEDURE PR_GO_SLICE(
    text1 IN VARCHAR2,
    text2 IN VARCHAR2,
    slice_out OUT TYPE_SLICE
)AS 
example_slice TYPE_SLICE;
BEGIN
  example_slice := TYPE_SLICE();
   example_slice.extend;
   example_slice.extend;
  example_slice (example_slice.LAST) := text1;
  example_slice (example_slice.LAST) := text2;
  example_slice(1) := 'test1';
  example_slice(2) := 'test2';
END PR_GO_SLICE;

the go code is pretty straight forward

func test_slice(db *sql.DB) {

	var (
		text1     string
		text2     string
		slice_out []string
	)
	text1 = `hello`
	text2 = `world`

	// prepare a callable statement
	cstmt, err := db.Prepare("BEGIN PR_GO_SLICE(:1, :2, :3); END;")
	if err != nil {
		fmt.Println("prepare:" + err.Error())
		panic(err)
	}
	defer cstmt.Close()

	_, err = cstmt.Exec(text1, text2, sql.Out{Dest: &slice_out})
	if err != nil {
		fmt.Println("exec: " + err.Error())
		panic(err)
	}
	fmt.Printf("text1 : %v#\ntext2 : %#v\nslice_out:%#v\n", text1, text2, slice_out)

	return
}

but i got error from oracle

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

Which somehow is because the data type send from golang to oracle TYPE_SLICE is not right.

digging deeper to the library,

the value that sent as parameter is actually in this function, as Bytes()

//github.com/sijms/go-ora/v2/parameter_encode_array.go - LOC 278

func (par *ParameterInfo) encodeArrayString(conn *Connection, value []string) {
	par.DataType = NCHAR
	par.ContFlag = 16
	session := conn.session
	arrayBuffer := bytes.Buffer{}
	session.WriteUint(&arrayBuffer, par.MaxNoOfArrayElements, 4, true, true)
	if len(value) > 0 {
		for _, tempVal := range value {
			strConv, _ := conn.getStrConv(par.CharsetID)
			tempBytes := strConv.Encode(tempVal)
			session.WriteClr(&arrayBuffer, tempBytes)
			if par.MaxLen < len(tempBytes) {
				par.MaxLen = len(tempBytes)
			}
		}
		par.MaxCharLen = par.MaxLen
		par.BValue = arrayBuffer.Bytes()
		if par.MaxLen == 0 {
			par.MaxLen = 1
			par.MaxCharLen = 0
		}

	} else {
		par.MaxLen = conn.maxLen.varchar
		par.MaxCharLen = par.MaxLen / converters.MaxBytePerChar(par.CharsetID)
	}
}

is there anyway to accomodate the data structure TABLE OF VARCHAR2 in golang ? based on driver.Value , i think it should be []string ? or else ?

best regards

[bluefire]

Based on the code and error message you provided, it seems that the issue lies in how the Go code is passing the TYPE_SLICE parameter to the Oracle stored procedure.

In the Go code, you defined slice_out as []string, which is correct. However, the library you are using (go-ora) internally handles the encoding and passing of array values to Oracle, and it appears that the encoding for TYPE_SLICE is not handled correctly.

To accommodate the TABLE OF VARCHAR2 data structure in Go, you can try the following approach:

1. Modify the encodeArrayString function in the library’s source code (github.com/sijms/go-ora/v2/parameter_encode_array.go) to handle the TYPE_SLICE data type properly. You may need to implement custom encoding logic for TYPE_SLICE to match the expected format in Oracle.
2. Alternatively, you can try using a different Oracle driver for Go that provides better support for passing array types. Some popular options include “GitHub - mattn/go-oci8: Oracle driver for Go using database/sql” and “GitHub - godror/godror: GO DRiver for ORacle DB”. These drivers might offer more extensive support for Oracle data types, including arrays.

Before making any changes to the library or trying a different driver, it’s important to check the documentation and examples provided by the library or driver you’re using to see if they offer specific guidance on working with array types in Oracle.

Remember to thoroughly test any modifications or alternative drivers to ensure compatibility and correctness with your specific use case.