Call function with sys_refcursor return

[ergoz]

Hello. I have a problem. In oracle we have a package with function, that receive 2 varchar params and return sys_refcursor. I need to get returned cursor and get data from it.

In java we use:

func = connect.prepareCall("{call ? := Pkg_Name.Oper_Process(?,?)}");
func.registerOutParameter(1, OracleTypes.CURSOR);
func.setString(2, param.getProjectName());
func.setString(3, param.getReportName());
func.execute();
rs = (ResultSet) func.getObject(1);
listResult = Oracle.getResultSet.getEntitiesFromResultSet(rs);

But i cant understand how to make correct the same with rana/ora with direct driver usage (i understand that database/sql not support this). I found examples only for out params in procedure, and tries to make same for function. We cant change funtion to procedure, or make any changes in db.

We cant use this

select Pkg_Name.Process(?,?) from dual;

Oracle not allow us to use it in our situation. -

 ORA-14551: cannot perform a DML operation inside a query

I tries this code:

package main

import (
    "fmt"

    "gopkg.in/rana/ora.v3"
)

func main() {
    // example usage of the ora package driver
    // connect to a server and open a session
    env, err := ora.OpenEnv(nil)
    defer env.Close()
    if err != nil {
        panic(err)
    }
    srvCfg := ora.SrvCfg{Dblink: "127.0.0.1:1521/ORCL"}
    srv, err := env.OpenSrv(&srvCfg)
    defer srv.Close()
    if err != nil {
        panic(err)
    }
    sesCfg := ora.SesCfg{
        Username: "usrlogin",
        Password: "pass",
    }
    ses, err := srv.OpenSes(&sesCfg)
    defer ses.Close()
    if err != nil {
        panic(err)
    }

    ses.Cfg().StmtCfg.Rset.SetBlob(ora.Bin)
    ses.Cfg().StmtCfg.Rset.SetClob(ora.S)

    tx1, err := ses.StartTx()
    if err != nil {
        panic(err)
    }

    _, err = ses.PrepAndExe("begin delete from tmp_reporter; end;")
    if err != nil {
        panic(err)
    }

    _, err = ses.PrepAndExe("CALL Pkg_Name.Set_Param(:1, :2)", "pID", "123321")
    if err != nil {
        panic(err)
    }

    rset := &ora.Rset{}
    _, err = ses.PrepAndExe("BEGIN :1 := Pkg_Name.Execute(:2, :3); END;", rset, "test", "message")
    if err != nil {
        panic(err)
    }

    if rset.Err != nil {
        panic(rset.Err)
    }

    fmt.Println(rset.IsOpen()) //it prints false
    fmt.Println(rset.ColumnNames) /int prints []
    if rset.IsOpen() {
        for rset.Next() {
            fmt.Println(rset.ColumnNames)
        }
        if rset.Err != nil {
            panic(rset.Err)
        } else {
            fmt.Println("No Panic...")
        }
        fmt.Println(rset.Len())
    } else {
        fmt.Println("CLOSED BEFORE...")  // last printed line in output
    }
    tx1.Commit()
}

But it when i run this file it prints to console:

false
[]
CLOSED BEFORE...

This is maximum that i can make, but i cant understand why rset.IsOpen() returns false, i think it is my common problem....

Help me please, tell me where i am wrong. Maybe rana/ora not supports And thanks to all contributors to this repo, and thank you @rana, this oracle driver is the best. And sorry for bad English :) Best regards!

[tgulacsi]

Use Prep, Exe separately! As the PrepAndExe's comment says, it closes the statement on return, so the returned resultset is closed, too.

Sth along the lines of

  stmt, err := ses.Prep(...)
  if err != nil {
    ...
  }  
  defer stmt.Close()
  var rset ora.Rset
  if _, err := stmt.Exe(&rset, "pID", "123321"); err != nil {
    ...
  }
  ...

By the way, MAYBE you can use the database/sql interface, with

  stmt, err := db.Prepare("BEGIN :1 := ... END;")
  defer stmt.Close()
  var rset ora.Rset
  var out interface{} = &rset
  _, err := stmt.Exec(out, "pID", "123321")

and use rset as before - so after all, this is the same as the native usage, maybe not worth the difference.

[ergoz]

@tgulacsi Tamás THANK YOU!!! It is awesome, it is works!!! (i use your first example)

[ergoz]

@tgulacsi sorry for disturb, second variant not works with database/sql:

    tx, errTx := db.Begin()

    stmt, errTx := tx.Prepare("begin :1 := Pkg_Name.Execute(:2, :3); end;")
    if errTx != nil {
        tx.Rollback()
        fmt.Println(errTx)
    }
    var rset ora.Rset
    var result interface{} = &rset
    _, errTx := stmt.Exec(result, "test", "message")
    if errTx != nil {
        tx.Rollback()
        fmt.Println(errTx)
    }
    stmt.Close()

I receive error:

sql: converting Exec argument #0's type: unsupported type ora.Rset, a struct

if i try:

    var rset ora.Rset
    _, errTx := stmt.Exec(&rset, "test", "message")
    if errTx != nil {
        tx.Rollback()
        fmt.Println(errTx)
    }

I have same error

Can we fix it, because database/sql usage is better for me :smile: i like its connection pool :smile: Maybe you have any ideas? :smile:

[tgulacsi]

Try

    stmt, errTx := tx.Prepare("begin :1 := Pkg_Name.Execute(:2, :3); end;")
    if errTx != nil {
        tx.Rollback()
        fmt.Println(errTx)
    }

    var result interface{}

    Row, errTx := stmt.Exec(result, args.ProjectName, args.ReportName)
    if errTx != nil {
        tx.Rollback()
        fmt.Println(errTx)
    }
    fmt.Println(Row)
    fmt.Println(result)

    rset := result.(*ora.Rset)

    stmt.Close()

ErgoZ Riftbit Vaper notifications@github.com ezt írta (időpont: 2016. nov. 30., Sze, 13:47):

@tgulacsi https://github.com/tgulacsi sorry for disturb, second variant not works with database/sql:

stmt, errTx := tx.Prepare("begin :1 := Pkg_Name.Execute(:2, :3); end;") if errTx != nil { tx.Rollback() fmt.Println(errTx) } Row, errTx := stmt.Exec(result, args.ProjectName, args.ReportName) if errTx != nil { tx.Rollback() fmt.Println(errTx) } fmt.Println(Row) fmt.Println(result) stmt.Close()

I receive error:

sql: converting Exec argument #0's type: unsupported type ora.Rset, a struct

Can we fix it, because database/sql usage is better for me 😄 i like its connection pool 😄 Maybe you have any ideas? 😄

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/rana/ora/issues/136#issuecomment-263865183, or mute the thread https://github.com/notifications/unsubscribe-auth/AAPoSjYlXaa_ru-RFZmxNaz1VLgs8AV5ks5rDXBZgaJpZM4K_902 .

[ergoz]

after Exec errTx have an error:

DrvStmt.Exec Stmt.exe Env.ociErrorNL ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Line with rset := result.(*ora.Rset) not executes

[tgulacsi]

Maybe a

var result interface{} = new(ora.Rset)
Row, errTx := stmt.Exec(result, args.ProjectName, args.ReportName)
...

could help, I haven't tested - it's not easy to pass in not canonicalized types to database/sql - see database/sql/driver/#Value

[ergoz]

@tgulacsi another error :smile:

sql: converting Exec argument #0's type: unsupported type ora.Rset, a struct

[tgulacsi]

I have no more ideas - use ora, directly.

ErgoZ Riftbit Vaper notifications@github.com ezt írta (időpont: 2016. dec. 5., H 12:57):

@tgulacsi https://github.com/tgulacsi another error 😄

sql: converting Exec argument #0's type: unsupported type ora.Rset, a struct

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/rana/ora/issues/136#issuecomment-264836719, or mute the thread https://github.com/notifications/unsubscribe-auth/AAPoSg9NczTOcyZ_BN6EvNOlBaE4k-0aks5rE_xDgaJpZM4K_902 .