Hi All ,
I have a requirement to call a stored oracle procedure which has an out parameter which is a custom type.
One of the attributes of that custom type is BLOB. I am not able to get the BLOB contents from that attribute.
I am able to call a procedure with custom type that has non blob , clob columns and get the values mapped to go struct successfully. But if the oracle custom type has BLOB attribute it is not working.
Here is my code can someone please help ?
// Oracle Custom Type
CREATE OR REPLACE TYPE XX_TEST_OBJ FORCE AS OBJECT
(
FILE_NAME VARCHAR2 (200),
FILE_TYPE VARCHAR2(30),
FILE_CONTENTS BLOB
)
// Oracle Procedure
CREATE OR REPLACE PROCEDURE xxtest_procedure(
p_cco_id IN VARCHAR2,
p_partner_name IN VARCHAR2,
p_tp_ref_num IN VARCHAR2,
p_shipind_docs_o OUT XX_TEST_OBJ,
p_return_code_o OUT VARCHAR2,
p_return_message_o OUT VARCHAR2)
IS
l_shipind_docs_o XX_TEST_OBJ;
BEGIN
l_shipind_docs_o :=
XX_TEST_OBJ (NULL,
NULL,
NULL);
SELECT file_name,file_type, file_contents
INTO l_shipind_docs_o.file_name,l_shipind_docs_o.file_type,l_shipind_docs_o.file_contents
FROM xx_test_table
WHERE file_type = ‘e-Invoice’;
p_shipind_docs_o := l_shipind_docs_o;
p_return_code_o := ‘SUCCESS’;
p_return_message_o := ‘SUCCESS’;
END;
/
// GO Code
package main
import (
"context"
"database/sql"
"fmt"
"reflect"
"github.com/godror/godror"
_ "github.com/godror/godror"
)
// FileData struct
type FileData struct {
*godror.Object
FileName string
FileType string
FileContents []byte
}
// SetValues function
func (r *FileData) SetValues(src interface{}) error {
//var reader godror.Lob
obj, ok := src.(*godror.Object)
if !ok {
return fmt.Errorf("Cannot scan from type %T", src)
}
fName, err := obj.Get("FILE_NAME")
if err != nil {
return err
}
r.FileName = string(fName.([]byte))
fType, err := obj.Get("FILE_TYPE")
if err != nil {
return err
}
r.FileType = string(fType.([]byte))
fContents, err := obj.Get("FILE_CONTENTS")
if err != nil {
return err
}
ff, ok := (fContents.(*godror.Lob))
fmt.Println(reflect.TypeOf(ff))
fmt.Println(reflect.TypeOf(fType))
fmt.Println(reflect.TypeOf(fName))
ff.Hijack()
fmt.Println(ff)
fmt.Println(ok)
return nil
}
func main() {
db, err := sql.Open("godror", "xxeem/*****@ORAPOCDB")
if err != nil {
fmt.Println(err)
return
}
defer db.Close()
fmt.Println("Before Prepare Statement")
stmt, err := db.Prepare("BEGIN xxtest_procedure( :p_cco_id,:p_partner_name,:p_tp_ref_num,:p_shipind_docs_o,:p_return_code_o,:p_return_message_o); END;")
fmt.Println("After Prepare", err)
var code, message string
conn, err := db.Conn(context.TODO())
var lFileData FileData
fObjectType, err := godror.GetObjectType(context.TODO(), conn, "XX_TEST_OBJ")
fObject, err := fObjectType.NewObject()
attr0, _ := fObject.Attributes["FILE_NAME"]
attr1, _ := fObject.Attributes["FILE_TYPE"]
attr2, _ := fObject.Attributes["FILE_CONTENTS"]
//fObject.OracleTypeNum = C.DPI_ORACLE_TYPE_LONG_VARCHAR
//fObject.NativeTypeNum = 2
fmt.Println(err)
fmt.Println(code)
fmt.Println(message)
fmt.Println("Before Execute Statement")
_, err = stmt.ExecContext(context.TODO(),
godror.LobAsReader(),
sql.Named("p_cco_id", "Test"),
sql.Named("p_partner_name", "Test"),
sql.Named("p_tp_ref_num", "Test"),
sql.Named("p_shipind_docs_o", sql.Out{Dest: fObject}),
sql.Named("p_return_code_o", sql.Out{Dest: &code}),
sql.Named("p_return_message_o", sql.Out{Dest: &message}),
)
defer stmt.Close()
fmt.Println(fObject)
fmt.Println(code)
fmt.Println(message)
fmt.Println("Before Scan")
lFileData.SetValues(fObject)
fmt.Println("After Scan")
fmt.Println("File Name Native Type : ", attr0.NativeTypeNum)
fmt.Println("File Name Oracle Type : ", attr0.OracleTypeNum)
fmt.Println("File Type Native Type : ", attr1.NativeTypeNum)
fmt.Println("File Type Oracle Type : ", attr1.OracleTypeNum)
fmt.Println("File Contents Native Type : ", attr2.NativeTypeNum)
fmt.Println("File Contents Oracle Type : ", attr2.OracleTypeNum)
}