cannot get value of custom table type from stored procedure

[ducknificient]

i have this type table

create or replace TYPE SLICE
AS TABLE OF varchar2(1000);

i'm using version 2.7.6 and can't figure out how to get value of it. The procedure i'm using

create or replace PROCEDURE GO_TESTMULTI(
    text1 IN VARCHAR2,
    text2 IN VARCHAR2,
  slice_out OUT SLICE
)AS 

example_slice SLICE;
test3 VARCHAR2(50);

BEGIN

  example_slice := SLICE();

   example_slice.extend;
   example_slice.extend;

  example_slice (example_slice.LAST) := text1;
  example_slice (example_slice.LAST) := text2;

  slice_out := example_slice;

END GO_TESTMULTI;

here's the function i use

func test_slice(db *sql.DB) {

    var (
        text1 string
        text2 string
        slice_out []string
    )

    text1 = `hello`
    text2 = `world`

    // prepare a callable statement
    cstmt, err := db.Prepare("BEGIN CIC.GO_TESTMULTI(:1, :2, :3); END;")
    if err != nil {
        fmt.Println("prepare:" + err.Error())
        panic(err)
    }
    defer cstmt.Close()

    _, err = cstmt.Exec(
        text1,
        text2,
        go_ora.Out{Dest: &slice_out, Size: 2},
    )
    if err != nil {
        fmt.Println("exec: " + err.Error())
        panic(err)
    }

    fmt.Printf("text1 : %v#\n", text1)
    fmt.Printf("text2 : %#v\n", text2)

    return
}

not sure about the example/arrays/main.go , but theoreticaly it should be working with []string

thank you

[sijms]

In this case the SLICE is defined as custom object not an array so it need different implementation as you see in the following function in connection.go

func RegisterType(conn *sql.DB, typeName, arrayTypeName string, typeObj interface{}) error {
    // ping first to avoid error when calling register type after open connection
    err := conn.Ping()
    if err != nil {
        return err
    }
    if driver, ok := conn.Driver().(*OracleDriver); ok {
        return RegisterTypeWithOwner(conn, driver.UserId, typeName, arrayTypeName, typeObj)
    }
    return errors.New("the driver used is not a go-ora driver type")
}

here I restrict array object to custom type but in your example we have array object of regular type so I need to make investigations with original driver and add this feature

[ducknificient]

digging deeper so far away, i'd found that its called Collections and records from oracle official docs

i made a new function called RegisterCollectionWithOwner and use this query to detect the collections in connection.go

        var (
            attName     sql.NullString
            attTypeName sql.NullString
            length      sql.NullInt64
        )
        sqlText := `SELECT TYPE_NAME,ELEM_TYPE_NAME,LENGTH
                    FROM ALL_COLL_TYPES
                    WHERE UPPER(OWNER)=:1 AND UPPER(TYPE_NAME)=:2`
        err := conn.QueryRow(sqlText, strings.ToUpper(owner), strings.ToUpper(collectionName)).Scan(&attName, &attTypeName, &length)
        if err != nil {
            return err
        }

But there's a lot of things that i still understand yet.

1. First, i don't know what data type shall be used when using in the PL/SQL Begin End Block. I tried to copy as user defined type (udc is user defined collections), but has problem in parameter_encode.go, when goType.kind() is struct

   type MULTIVARCHAR2 struct {
   VALUE string `udc:VARCHAR2`
   }
   var (
       text1     string
       text2     string
       list      []string
       listSlice []MULTIVARCHAR2
   )
   
   // list = make([]string, 0)
   listSlice = make([]MULTIVARCHAR2, 0)
   
   err = go_ora.RegisterCollection(m.DB, "TYPE_MULTIVARCHAR2", list)
   if err != nil {
       fmt.Println("Can't register UDC", err)
       return
   }
   
   // prepare a callable statement
   cstmt, err := conn.PrepareContext(m.Ctx, "BEGIN PR_GO_TEST_MULTI(:1, :2); END;")
   if err != nil {
       return err
   }
   defer cstmt.Close()
   
   _, err = cstmt.Exec(
       text1,
       text2,
       sql.Out{Dest: &listSlice},
   )
   if err != nil {
       fmt.Println(err)
       return err
   }

2. If i'm not using struct and plain []string array as scan value, i'm thinking about the byte size that need to initialized first. Afaik go slice can't define the string length

   // this is example for string that accept VARCHAR2(100) from database. But how if it a slice of string (VARCHAR2) ?
   Out := string(make([]byte, 100))

3. This is not limited to collections only, but also records. And the official docs mentioned we can write custom index type

   
   DECLARE
   -- Associative array indexed by string:
   
   TYPE population IS TABLE OF NUMBER  -- Associative array type
   INDEX BY VARCHAR2(64);            --  indexed by string
   
   city_population  population;        -- Associative array variable
   i  VARCHAR2(64);                    -- Scalar variable

BEGIN -- Add elements (key-value pairs) to associative array:

city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000;

-- Change value associated with key 'Smallville':

city_population('Smallville') := 2001;

-- Print associative array:

i := city_population.FIRST; -- Get first element of array

WHILE i IS NOT NULL LOOP DBMS_Output.PUT_LINE ('Population of ' || i || ' is ' || city_population(i)); i := city_population.NEXT(i); -- Get next element of array END LOOP; END; /

[ducknificient]

opening discussion in https://github.com/sijms/go-ora/discussions/476

[sijms]

fixed in v2.8.7

[ducknificient]

Tested and working. Thank you for the fix