Last active
May 5, 2023 16:14
-
-
Save robstradling/ca8619f832e19b0785a4c89502cfedbf to your computer and use it in GitHub Desktop.
go-ora: Unable to process input arrays that have no non-empty values
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
module gist.github.com/robstradling/ca8619f832e19b0785a4c89502cfedbf | |
go 1.20 | |
require github.com/sijms/go-ora/v2 v2.7.2 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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= |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment