go-ora issue 323

go.mod

module gist.github.com/robstradling/3bf7c340bc4479f3b3a71c58a9842657

go 1.19

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

go.sum

github.com/sijms/go-ora/v2 v2.7.21 h1:BbfkcgoRYanmQkHklvRFJ7v/Cil8gPSxfG6ExZrHHlY=
github.com/sijms/go-ora/v2 v2.7.21/go.mod h1:EHxlY6x7y9HAsdfumurRfTd+v8NrEOTR3Xl4FWlH6xk=

output.txt

Connection string:  oracle://user:pass@server/service_name?charset=UTF8
Finish create package:  129.795769ms
Finish create package body:  98.933618ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  84.368038ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
Can't call store procedure ORA-01460: unimplemented or unreasonable conversion requested

Finish drop package:  97.446249ms

output_v2.7.10.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8&PREFETCH_ROWS=1000
Finish create package:  347.341737ms
Finish create package body:  183.436223ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  167.432081ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  ab中国人中国人中国人
Finish call stored procedure:  112.838765ms
Finish drop package:  115.065053ms

output_v2.7.21.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8
Finish create package:  159.480579ms
Finish create package body:  88.02839ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  78.515963ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  ab中国人中国人中国人
Finish call stored procedure:  80.730121ms
Finish drop package:  133.341263ms

output_v2.7.6.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8&PREFETCH_ROWS=1000
Finish create package:  111.558924ms
Finish create package body:  85.377804ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  77.39427ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  ab中国人中国人中国人
Finish call stored procedure:  77.369113ms
Finish drop package:  85.759632ms

output_v2.7.7.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8&PREFETCH_ROWS=1000
Finish create package:  173.893483ms
Finish create package body:  80.086079ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  76.021982ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  abýýýýýýýýý
Finish call stored procedure:  76.763312ms
Finish drop package:  85.604543ms

output_v2.7.8.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8&PREFETCH_ROWS=1000
Finish create package:  192.089396ms
Finish create package body:  87.523224ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  79.369965ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  abýýýýýýýýý
Finish call stored procedure:  85.612733ms
Finish drop package:  86.225288ms

output_v2.7.9.txt

Connection string:  oracle://www:[email protected]:1522/saspdev.sectigo.gb?CLIENT+CHARSET=UTF8&PREFETCH_ROWS=1000
Finish create package:  140.849155ms
Finish create package body:  94.682913ms

Call stored procedure with {"a", "b", "c"}:
strings_in:  [a b c] 	string_out:  abc
Finish call stored procedure:  85.816513ms

Call stored procedure with {"a", "b", "中国人中国人中国人"}:
strings_in:  [a b 中国人中国人中国人] 	string_out:  ab中国人中国人中国人
Finish call stored procedure:  77.571388ms
Finish drop package:  96.118319ms

stored_proc_array.go

package main

import (
	"context"
	"database/sql/driver"
	"flag"
	"fmt"
	go_ora "github.com/sijms/go-ora/v2"
	"os"
	"strings"
	"time"
)

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_ARRAY_IN IN VARCHAR2TABLE_T,
		STRING_OUT OUT VARCHAR2
	);

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

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

	PROCEDURE TEST_PROC(
		STRING_ARRAY_IN IN VARCHAR2TABLE_T,
		STRING_OUT OUT VARCHAR2
	) IS
	BEGIN
		FOR i IN STRING_ARRAY_IN.FIRST..STRING_ARRAY_IN.LAST LOOP
			STRING_OUT := STRING_OUT || STRING_ARRAY_IN(i);
		END LOOP;
	END;
END GOORA_TEMP;`
	t := time.Now()
	_, err := conn.Exec(sqlText)
	if err != nil {
		return err
	}
	fmt.Println("Finish create package body: ", time.Now().Sub(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.Println("Finish drop package: ", time.Now().Sub(t))
	return nil
}

func callStoredProcedure(conn *go_ora.Connection, strings_in []string) error {
	t := time.Now()
	string_out := strings.Repeat(" ", 256)

	_, err := conn.ExecContext(context.Background(), `BEGIN GOORA_TEMP.TEST_PROC(:1, :2); END;`, []driver.NamedValue{
		{Ordinal: 1, Value: strings_in},
		{Ordinal: 2, Value: go_ora.Out{Dest: &string_out}}})

	if err != nil {
		return err
	}
	fmt.Println("strings_in: ", strings_in, "\tstring_out: ", string_out)
	fmt.Println("Finish call stored procedure: ", time.Now().Sub(t))
	return nil
}
func usage() {
	fmt.Println()
	fmt.Println("stored_proc_array")
	fmt.Println("  a complete code of using stored procedure with string array input parameter.")
	fmt.Println()
	fmt.Println("Usage:")
	fmt.Println(`  stored_proc_array -server server_url`)
	flag.PrintDefaults()
	fmt.Println()
	fmt.Println("Example:")
	fmt.Println(`  stored_proc_array -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.Println("\nCall stored procedure with {\"a\", \"b\", \"c\"}:")
	err = callStoredProcedure(conn, []string{"a", "b", "c"})
	if err != nil {
		fmt.Println("Can't call store procedure", err)
	}

	fmt.Println("\nCall stored procedure with {\"a\", \"b\", \"中国人中国人中国人\"}:")
	err = callStoredProcedure(conn, []string{"a", "b", "中国人中国人中国人"})
	if err != nil {
		fmt.Println("Can't call store procedure", err)
	}
}

fixed in new release