go.mod

module gist.github.com/robstradling/f8f8c04b73af72e7f137c1eedb523d15

go 1.20

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_v2.7.10.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  101.609773ms

Call stored procedure with string_in="中国人中国人中国人":
string_in:  中国人中国人中国人 	string_out:  中国人中国人中国人
Finish call stored procedure:  92.140162ms
Finish drop package:  97.531465ms

output_v2.7.21.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  85.038117ms

Call stored procedure with string_in="中国人中国人中国人":
string_in:  中国人中国人中国人 	string_out:  中国人中国人中国人
Finish call stored procedure:  79.663822ms
Finish drop package:  92.143456ms

output_v2.7.6.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  89.933237ms

Call stored procedure with string_in="中国人中国人中国人":
string_in:  中国人中国人中国人 	string_out:  中国人中国人中国人
Finish call stored procedure:  74.78755ms
Finish drop package:  92.547172ms

output_v2.7.7.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  86.9844ms

Call stored procedure with string_in="中国人中国人中国人":
string_in:  中国人中国人中国人 	string_out:  ýýýýýýýýý
Finish call stored procedure:  79.117546ms
Finish drop package:  93.460866ms

output_v2.7.8.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  80.265803ms

Call stored procedure with string_in="中国人中国人中国人":
string_in:  中国人中国人中国人 	string_out:  ýýýýýýýýý
Finish call stored procedure:  82.817437ms
Finish drop package:  94.42009ms

output_v2.7.9.txt

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

Call stored procedure with string_in="a":
string_in:  a 	string_out:  a
Finish call stored procedure:  75.911673ms

Call stored procedure with string_in="中国人中国人中国人":
Can't call store procedure ORA-01460: unimplemented or unreasonable conversion requested

Finish drop package:  85.511265ms

stored_proc_named.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

	PROCEDURE TEST_PROC(
		STRING_IN IN VARCHAR2,
		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.Since(t))
	return nil
}

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

	PROCEDURE TEST_PROC(
		STRING_IN IN VARCHAR2,
		STRING_OUT OUT VARCHAR2
	) IS
	BEGIN
		STRING_OUT := STRING_IN;
	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.Println("Finish drop package: ", time.Since(t))
	return nil
}

func callStoredProcedure(conn *go_ora.Connection, string_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: string_in},
		{Ordinal: 2, Value: go_ora.Out{Dest: &string_out}}})

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

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