go-ora: Unable to process input arrays that have no non-empty values

empty_arrays_demo.go

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"flag"
	"fmt"
	"os"
	"time"

	go_ora "github.com/sijms/go-ora/v2"
)

func createPackage(conn *go_ora.Connection) error {
	sqlText := `CREATE OR REPLACE PACKAGE GOORA_TEMP IS

	TYPE VARCHAR2TABLE_T IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

	PROCEDURE TEST_PROC_STRING(
		STRING_IN IN VARCHAR2
	);

	PROCEDURE TEST_PROC_STRINGARRAY(
		STRINGARRAY_IN IN VARCHAR2TABLE_T
	);

	PROCEDURE TEST_PROC_BYTEARRAY(
		BYTEARRAY_IN IN RAW
	);

END GOORA_TEMP;`
	t := time.Now()
	_, err := conn.Exec(sqlText)
	if err != nil {
		return err
	}
	fmt.Println("Finish create package: ", time.Since(t))
	return nil
}

func createPackageBody(conn *go_ora.Connection) error {
	sqlText := `CREATE OR REPLACE PACKAGE BODY GOORA_TEMP IS

	PROCEDURE TEST_PROC_STRING(
		STRING_IN IN VARCHAR2
	) IS
	BEGIN
		NULL;
	END;

	PROCEDURE TEST_PROC_STRINGARRAY(
		STRINGARRAY_IN IN VARCHAR2TABLE_T
	) IS
	BEGIN
		NULL;
	END;

	PROCEDURE TEST_PROC_BYTEARRAY(
		BYTEARRAY_IN IN RAW
	) IS
	BEGIN
		NULL;
	END;

END GOORA_TEMP;`
	t := time.Now()
	_, err := conn.Exec(sqlText)
	if err != nil {
		return err
	}
	fmt.Println("Finish create package body: ", time.Since(t))
	return nil
}

func dropPackage(conn *go_ora.Connection) error {
	t := time.Now()
	_, err := conn.Exec("DROP PACKAGE GOORA_TEMP")
	if err != nil {
		return err
	}
	fmt.Printf("\nFinish drop package: %v\n", time.Since(t))
	return nil
}

func callStoredProcedure_String(conn *go_ora.Connection, string_in string) error {
	t := time.Now()

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC_STRING(:1); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: string_in},
	})

	if err != nil {
		return err
	}
	fmt.Printf("OK: %v\n", time.Since(t))
	return nil
}

func callStoredProcedure_StringArray(conn *go_ora.Connection, strings_in []string) error {
	t := time.Now()

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC_STRINGARRAY(:1); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: strings_in},
	})

	if err != nil {
		return err
	}
	fmt.Printf("OK: %v\n", time.Since(t))
	return nil
}

func callStoredProcedure_StringPointerArray(conn *go_ora.Connection, strings_in []*string) error {
	t := time.Now()

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC_STRINGARRAY(:1); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: strings_in},
	})

	if err != nil {
		return err
	}
	fmt.Printf("OK: %v\n", time.Since(t))
	return nil
}

func callStoredProcedure_SqlNullStringArray(conn *go_ora.Connection, strings_in []sql.NullString) error {
	t := time.Now()

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC_STRINGARRAY(:1); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: strings_in},
	})

	if err != nil {
		return err
	}
	fmt.Printf("OK: %v\n", time.Since(t))
	return nil
}

func callStoredProcedure_ByteArray(conn *go_ora.Connection, bytes_in []byte) error {
	t := time.Now()

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC_BYTEARRAY(:1); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: bytes_in},
	})

	if err != nil {
		return err
	}
	fmt.Printf("OK: %v\n", time.Since(t))
	return nil
}

func usage() {
	fmt.Println()
	fmt.Println("empty_string_bug_demo")
	fmt.Println("  a complete code of using stored procedure with string array input parameter.")
	fmt.Println()
	fmt.Println("Usage:")
	fmt.Println(`  empty_string_bug_demo -server server_url`)
	flag.PrintDefaults()
	fmt.Println()
	fmt.Println("Example:")
	fmt.Println(`  empty_string_bug_demo -server "oracle://user:pass@server/service_name"`)
	fmt.Println()
}
func main() {
	var (
		server string
	)

	flag.StringVar(&server, "server", "", "Server's URL, oracle://user:pass@server/service_name")
	flag.Parse()

	connStr := os.ExpandEnv(server)
	if connStr == "" {
		fmt.Println("Missing -server option")
		usage()
		os.Exit(1)
	}
	fmt.Println("Connection string: ", connStr)
	conn, err := go_ora.NewConnection(connStr)
	if err != nil {
		fmt.Println("Can't create connection: ", err)
		return
	} else if err = conn.Open(); err != nil {
		fmt.Println("Can't open the driver: ", err)
		return
	}
	defer func() {
		err = conn.Close()
		if err != nil {
			fmt.Println("Can't close driver: ", err)
		}
	}()

	err = conn.Ping(context.Background())
	if err != nil {
		fmt.Println("Can't ping connection: ", err)
		return
	}

	err = createPackage(conn)
	if err != nil {
		fmt.Println("Can't create package", err)
		return
	}
	defer func() {
		err = dropPackage(conn)
		if err != nil {
			fmt.Println("Can't drop package", err)
		}
	}()
	err = createPackageBody(conn)
	if err != nil {
		fmt.Println("Can't create package body", err)
		return
	}

	fmt.Printf("\n*** STRING, using string\n")

	fmt.Printf(" \"\" => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_String(conn, "")
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("\" \" => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_String(conn, " ")
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}


	fmt.Printf("\n*** STRING ARRAY, using []string\n")

	fmt.Printf("       []string{} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("     []string{\"\"} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{""})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("    []string{\" \"} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{" "})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf(" []string{\"\", \"\"} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{"", ""})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]string{\"\", \" \"} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{"", " "})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]string{\" \", \"\"} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringArray(conn, []string{" ", ""})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	s0 := ""
	s1 := " "

	fmt.Printf("\n*** STRING ARRAY, using []*string\n")

	fmt.Printf("             []*string{} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("        []*string{&(\"\")} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{&s0})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("       []*string{&(\" \")} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{&s1})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf(" []*string{&(\"\"), &(\"\")} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{&s0, &s0})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]*string{&(\"\"), &(\" \")} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{&s0, &s1})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]*string{&(\" \"), &(\"\")} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_StringPointerArray(conn, []*string{&s1, &s0})
	if err != nil {
		fmt.Printf("ERROR: %v\n", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("\n*** STRING ARRAY, using []sql.NullString\n")

	fmt.Printf("                                                 []sql.NullString{} => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("                        []sql.NullString{ {String:\"\",Valid:false} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: "", Valid: false}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("                         []sql.NullString{ {String:\"\",Valid:true} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: "", Valid: true}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("                        []sql.NullString{ {String:\" \",Valid:true} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: " ", Valid: true}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf(" []sql.NullString{ {String:\"\",Valid:true}, {String:\"\",Valid:true} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: "", Valid: true}, {String: "", Valid: true}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]sql.NullString{ {String:\"\",Valid:true}, {String:\" \",Valid:true} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: "", Valid: true}, {String: " ", Valid: true}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]sql.NullString{ {String:\" \",Valid:true}, {String:\"\",Valid:true} } => TABLE OF VARCHAR2 :  ")
	err = callStoredProcedure_SqlNullStringArray(conn, []sql.NullString{{String: " ", Valid: true}, {String: "", Valid: true}})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("\n*** RAW, using []byte\n")

	fmt.Printf("    []byte{} => RAW :  ")
	err = callStoredProcedure_ByteArray(conn, []byte{})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}

	fmt.Printf("[]byte{0x00} => RAW :  ")
	err = callStoredProcedure_ByteArray(conn, []byte{0x00})
	if err != nil {
		fmt.Printf("ERROR: %v", err)
		conn.Close()
		conn.Open()
	}
}

go.mod

module gist.github.com/robstradling/ca8619f832e19b0785a4c89502cfedbf

go 1.20

require github.com/sijms/go-ora/v2 v2.7.2

go.sum

github.com/sijms/go-ora/v2 v2.7.2 h1:utIqD9dMvoZLcWuXhoB/3GeFgqolUVvVnvODikfG09g=
github.com/sijms/go-ora/v2 v2.7.2/go.mod h1:EHxlY6x7y9HAsdfumurRfTd+v8NrEOTR3Xl4FWlH6xk=

output.txt

Connection string:  oracle://www:www@bdddcaora4.brad.dc.comodoca.net:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8
Finish create package:  444.131811ms
Finish create package body:  84.788109ms

*** STRING, using string
 "" => TABLE OF VARCHAR2 :  OK: 78.921209ms
" " => TABLE OF VARCHAR2 :  OK: 81.801774ms

*** STRING ARRAY, using []string
       []string{} => TABLE OF VARCHAR2 :  ERROR: ORA-03101: invalid input data for 
     []string{""} => TABLE OF VARCHAR2 :  ERROR: ORA-03146: invalid buffer length for TTC field
    []string{" "} => TABLE OF VARCHAR2 :  OK: 75.759395ms
 []string{"", ""} => TABLE OF VARCHAR2 :  ERROR: ORA-03146: invalid buffer length for TTC field
[]string{"", " "} => TABLE OF VARCHAR2 :  OK: 74.961271ms
[]string{" ", ""} => TABLE OF VARCHAR2 :  OK: 79.411548ms

*** STRING ARRAY, using []*string
             []*string{} => TABLE OF VARCHAR2 :  ERROR: unsupported array type
        []*string{&("")} => TABLE OF VARCHAR2 :  ERROR: unsupported array type
       []*string{&(" ")} => TABLE OF VARCHAR2 :  ERROR: unsupported array type
 []*string{&(""), &("")} => TABLE OF VARCHAR2 :  ERROR: unsupported array type
[]*string{&(""), &(" ")} => TABLE OF VARCHAR2 :  ERROR: unsupported array type
[]*string{&(" "), &("")} => TABLE OF VARCHAR2 :  ERROR: unsupported array type

*** STRING ARRAY, using []sql.NullString
                                                 []sql.NullString{} => TABLE OF VARCHAR2 :  ERROR: ORA-03101: invalid input data for 
                        []sql.NullString{ {String:"",Valid:false} } => TABLE OF VARCHAR2 :  ERROR: ORA-03146: invalid buffer length for TTC field
                         []sql.NullString{ {String:"",Valid:true} } => TABLE OF VARCHAR2 :  ERROR: ORA-03146: invalid buffer length for TTC field
                        []sql.NullString{ {String:" ",Valid:true} } => TABLE OF VARCHAR2 :  OK: 80.846538ms
 []sql.NullString{ {String:"",Valid:true}, {String:"",Valid:true} } => TABLE OF VARCHAR2 :  ERROR: ORA-03146: invalid buffer length for TTC field
[]sql.NullString{ {String:"",Valid:true}, {String:" ",Valid:true} } => TABLE OF VARCHAR2 :  OK: 76.256211ms
[]sql.NullString{ {String:" ",Valid:true}, {String:"",Valid:true} } => TABLE OF VARCHAR2 :  OK: 72.521019ms

*** RAW, using []byte
    []byte{} => RAW :  ERROR: ORA-03146: invalid buffer length for TTC field
[]byte{0x00} => RAW :  OK: 88.51897ms

Finish drop package: 139.886199ms