Driver doesn't support sys_refcursor parameter

[hadeshunter]

I have an oracle Procedure like, which returns a sys_refcursor:

PROCEDURE getTTVT
    (
        o_data OUT sys_refcursor   
    )
    IS
     v_Query varchar2(4000);
    BEGIN
    v_Query :='
       select unit_id, unit_name
       from admin_hcm.unit where unit_id in (41, 42, 43, 44, 45, 56, 57, 59, 60)
    ';
     OPEN o_data FOR v_Query;
END getTTVT;

My go function is:

// Unit of list
type Unit struct {
	unit_id int64
	unit_name string
}
func ListAllUnits {
        stmt, err := db.oracleDB.Prepare("begin dashboard.getTTVT(); end;")
	if err != nil {
		fmt.Println("Error create statment")
		fmt.Println(err)
		return nil, err
	}
	defer stmt.Close()
	units := []models.Unit{}
	result, err := stmt.Exec(sql.Out{Dest: &units})
	fmt.Println(result)
}

func main() {
   ListAllUnits()
}

When I run command:

go run main.go

I only get nil
@sijms Can you explain how to call Oracle Store Procedure?

[sijms]

go to the code in connection.go and examine function GetNLS()

output parameter is supported when use the package directly as explained in readme file

[sijms]

if your problem solved please close the issue

[hadeshunter]

if your problem solved please close the issue

Thank you, I'm trying, I will close after success and confirm.

[hadeshunter]

@sijms I'm sorry I lasted, I'm back. I read code in connection.go and examine function GetNLS()
My code now is

cmdText := `
                    DECLARE
                    o_cursor sys_refcursor;
                    BEGIN
                    dashboard.getTTVT(o_cursor);
                    END;`
	stmt := go_ora.NewStmt(cmdText, db.oracleDB)
	stmt.AddParam("o_cursor", "", 1000, go_ora.Output)
	// defer stmt.Close()
	result, err := stmt.Exec(nil)
	if err != nil {
		fmt.Printf("Error execute query: %s\n", err)
		return nil, err
	}
	fmt.Println(result)

I don't know how to add output sys_refcursor.

stmt.AddParam("o_cursor", "", 1000, go_ora.Output)

When run I receive error

Error execute query: ORA-00922: missing or invalid option

If my cmdText is wrong, pls show me the correct. How to add output of sys_refcursor and how can I add output to units array?

// Unit of list
type Unit struct {
 	unit_id int64
 	unit_name string
 }

[sijms]

I didn't test ref_cursor before only simple binding only
I think it need extra option to be added to network to let it working properly

[hadeshunter]

I didn't test ref_cursor before only simple binding only
I think it need extra option to be added to network to let it working properly

If it just simple query, it's ok but almost all my procedure open sys_refcursor. Do you have a plan to update work with procedure open sys_refcursor now or near future?

[sijms]

OK, no problem i will work on this and when finish i will update and inform you

[sijms]

I add support for RefCursor
please read the readme file first and see a simple example I make to explain how to use
download and test and if ok please close the issue

[hadeshunter]

Thank you for your support. Now with procedure get only output sys_refcursor, It work well but when I get input then it begin run forever without result or error.
Example

PROCEDURE getTTVT
(
    i_unitID IN number, 
    i_unitName IN nvarchar(200),
    o_data OUT sys_refcursor   
);

if I have a procedure with input is number or nvarchar2 and the output is sys_refcursor, my function is:

        units := []models.Unit{}
	// Create statment
	cmdText := `BEGIN test.getTTVT(:1, :2, :3); END;`
	stmt := go_ora.NewStmt(cmdText, db.oracleDB)
	stmt.AddParam("1", 41, 100, go_ora.Input)
        stmt.AddParam("2", "BCH", 1000, go_ora.Input)
	stmt.AddRefCursorParam("3")
	defer stmt.Close()

	// Query
	_, err := stmt.Exec(nil)
	if err != nil {
		fmt.Println("Error query")
		return nil, err
	}

	if cursor, ok := stmt.Pars[0].Value.(go_ora.RefCursor); ok {
		defer cursor.Close()
		rows, err := cursor.Query()
		if err != nil {
			return nil, err
		}

		var (
			unit_id int64
			unit_name string
		)
		unit := models.Unit{}
		values := make([]driver.Value, 2)

		for {
			err = rows.Next(values)
			// check for error and if == io.EOF break
			if err == io.EOF {
				break
			}

			if unit_id, ok = values[0].(int64); !ok {
				return nil, errors.New("Not have value unit_id")
			}

			if unit_name , ok = values[1].(string); !ok {
				return nil, errors.New("Not have value unit_name ")
			}

			unit.unit_id = unit_id
			unit.unit_name = unit_name 
			units = append(units, unit)
		}
	}

	return units, nil

when I run I get a run forever nonstop. Do I add input AddParam wrong? @sijms

[sijms]

err = rows.Next(values)
// check for error and if == io.EOF break
if err == io.EOF {
break
}

here add check for other errors

if err != nil {
    if err == io.EOF {
        break
    }
    fmt.Println(err)
    break
}

[sijms]

did you get correct output?

[hadeshunter]

Yes, my procedure return two columns which are unit_id number and unit_name nvarchar2.
If there only output sys_refcursor without any input condition then it work well and return correct output.
But when I add condition input such as i_unitID IN number then it just run. I think there is issue in the line AddParam
stmt.AddParam("1", 41, 100, go_ora.Input) because _, err := stmt.Exec(nil) run forever and every thing such as println("This steps") below that line not show.
When change code check error to

if err != nil {
    if err == io.EOF {
        break
    }
    fmt.Println(err)
    break
}

There is nothing happen. It just run forever.

[sijms]

I make modification that solve the problem
please test and inform me about the result