获取最后插入的ID（GO + oracle）

Question

I need to insert two records in Oracle database from my go application using sqlx (https://github.com/jmoiron/sqlx) with go-oci8 (https://github.com/mattn/go-oci8) driver. Second record references previous one by foreign key. So I need to have primary key of first record (it is assigned from sequence with before insert trigger on first table) before I can insert second record.

So I experimented to get last inserted id:

create table t(x int primary key);
create sequence x_seq;

LastInsertId failed for me:

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _ "github.com/mattn/go-oci8"
)

func main(){
    db, err := sqlx.Connect("oci8", "integr/integr@localhost:49161/xe")
    if err != nil {
       fmt.Println(err)
    }
    sql := "insert into t values(x_seq.nextval)"
    r, err := db.Exec(sql)
    if err != nil {
       fmt.Println(err)
    }
    fmt.Println(r.RowsAffected())
    fmt.Println(r.LastInsertId())
}

Output:

1 <nil>
0 LastInsertId not supported

Then I tried to make stored function that inserts record and returns primary key.

create function f(x int) return int as
v int;
begin
    insert into t values(x)
    returning x into v;
    return v;
end;

But I failed to find a way to get its result. PostgreSQL style select fails:

SQL> select f(9) from dual;
select f(9) from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "INTEGR.F", line 1

And OCI8 style variable binding does not work:

package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _ "github.com/mattn/go-oci8"
)

func main(){
    db, err := sqlx.Connect("oci8", "integr/integr@localhost:49161/xe")
    if err != nil {
       fmt.Println(err)
    }
    sql := sqlx.Rebind(sqlx.NAMED,"begin ? := f(?); end;")
    var a int
    _, err = db.Exec(sql, a, 333)
    if err != nil {
       fmt.Println(err)
    }
    fmt.Println(sql)
    fmt.Println(a)
}

Output:

begin :arg1 := f(:arg2); end;
0

How to get last inserted ID, or how to get value from stored funcion in Go from Oracle?

Answer 1

Hi I Don't know about go but I know about oracle return query

Same problem I had with PHP and I solve it using this query

insert into table (field1,field2,field3)
values (val1,val2,val3) return primaryfield_id into :xx

It will automatically return value in your Go variable (I guess ) please try this