Driver doesn't support sys_refcursor parameter about go-ora HOT 13 CLOSED

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

from go-ora.

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

from go-ora.

if your problem solved please close the issue

from go-ora.

if your problem solved please close the issue

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

from go-ora.

@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
 }

from go-ora.

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

from go-ora.

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?

from go-ora.

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

from go-ora.

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

from go-ora.

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
}

from go-ora.

did you get correct output?

from go-ora.

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.

from go-ora.

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

from go-ora.