Regarding Store Procedure in Golang to Oracle DB

[Nisarg Desai]

Hi There Golang-nuts. 

I am seeking help for my go lang project.

I am having an oracle server and in that there are many Db's Now I am createing a store procedure which has almost 20 parameters which are used multiple times in one single query. 

Now I checked GO does not support reuse of same parameters so if I have already used order_id as a parameter while passing query to be executed I can not use the order_id parameter anywhere else in the query. 

So I created a store procedure which would be easier but now in oracle while running the store procedure I am running it as follows 

    var rows *sql.Rows

    _, err := r.db.QueryxContext(

        ctx,

        `BEGIN

        PSIGWAY.get_transactions(

            :1, :2, :3, :4, :5, :6, :7, :8, :9, :10,

            :11, :12, :13, :14, :15, :16, :17, :18,

            :19, :20

        );

     END;`,

        request.StoreID,

        request.DateFrom,

        request.DateTo,

        request.Status,

        request.Action,

        request.AuthCode,

        request.OrderID,

        request.UserId,

        request.CustomerIP,

        request.CardType,

        request.CardSpan,

        request.DateFilterType,

        request.AmountFilterType,

        request.Amount,

        request.BillingName,

        request.BillingPostalCode,

        request.BillingProvince,

        Requestpage,

        "GetTransactions",

        sql.Out{Dest: &rows}, // 🔥 THIS IS THE KEY

    )

    if err != nil {

        return response, err

    }

    defer rows.Close()

    for rows.Next() {

        var o models.OrderSummary

        err := rows.Scan(

            &o.IMID,

            &o.OrderID,

            &o.TrxID,

            &o.Amount,

            &o.Name,

            &o.Card,

            &o.UserId,

            &o.Date,

            &o.Action,

            &o.Result,

            &o.ReturnCode,

            &o.External_Reason,

            &o.IDEBITISSNAME,

            &o.IDEBITISSCONF,

            &o.ACQCONFCODE,

        )

        if err != nil {

            return response, err

        }

        orders = append(orders, o)

    }

    if err := rows.Err(); err != nil {

        return response, err

    }

    // Post-processing

    for i := range orders {

        transType := "IOP_TRANSACTIONS"

        if orders[i].TrxID > "2000000000000000000" {

            transType = "CC_TRANSACTIONS"

        }

        orders[i].ActionText = utils.GetActionText(orders[i].Action, transType)

        orders[i].ResultText = utils.GetResultText(orders[i].Result, transType)

        orders[i].ReturnCodeText = utils.GetReturnCodeText(

            orders[i].Result,

            orders[i].TrxID,

            orders[i].ReturnCode,

            orders[i].IDEBITISSNAME,

            orders[i].IDEBITISSCONF,

            orders[i].ACQCONFCODE,

            orders[i].External_Reason,

        )

    }

    response.TotalResults = len(orders)

    response.Orders = orders

but it throws me an error reflect: call of reflect.Value.Type on zero Value
/usr/local/go/src/reflect/value.go:2474 (0x4c034d)
        Value.abiTypeSlow: panic(&ValueError{"reflect.Value.Type", Invalid})
/usr/local/go/src/reflect/value.go:2462 (0x4c021c)
        Value.typeSlow: return toRType(v.abiTypeSlow())
/usr/local/go/src/reflect/value.go:2457 (0x6fc1fb)
        Value.Type: return v.typeSlow()
/home/ndesai/go/pkg/mod/github.com/godror/god...@v0.49.4/stmt.go:3409 (0x6fc1ab)
        (*conn).getStructObjectType: rvt := rv.Type()
/home/ndesai/go/pkg/mod/github.com/godror/god...@v0.49.4/stmt.go:1465 (0x6e9393)
        (*statement).bindVarTypeSwitch: if ot, err := st.conn.getStructObjectType(ctx, value, ""); err != nil { Now it is because of the sq.out but not sure How to solve this issue and get it running I have spent almost 3 weeks on this please help me with this and help me resolve it Thank you.

[Jason E. Aten]

Hi Nisarg, 

it helps to have a runnable reproducer. Please post a link to a minimal reproducer in the https://go.dev/play/ (the Go playgound) or a

github repo.

Best,

Jason

[Nisarg Desai]

Hi Jason, 

I can not give you the whole runnable code 

But here is the whole snippet that I am working with along with  the SQL query for ORACLE DB 

Link :    https://go.dev/play/p/z9KeuWFzsB2

this is just the snippet. All I want to know is 2 things 
1.  I have a multiple single parameters( for eg. Order_id used multiple times inside a query  ) Now while keeping the query inside the code itself and just passing the single oreder_id variable for all the Order_id parameters inside the query is it possible in GO lang. If Yes then how except using store procedure because I didn't find any solution to that. 

2. the above issue can be solved by Store procedure which I know. But the issue is my database is Oracle and it has to have the "SYS_REFCURSOR" which when I am passing as an out parameter in the parameters of the store procedure Inside repository code it throws the error of something that I don't understand.

reflect: call of reflect.Value.Type on zero Value
/usr/local/go/src/reflect/value.go:2474 (0x4c034d)
        Value.abiTypeSlow: panic(&ValueError{"reflect.Value.Type", Invalid})
/usr/local/go/src/reflect/value.go:2462 (0x4c021c)
        Value.typeSlow: return toRType(v.abiTypeSlow())
/usr/local/go/src/reflect/value.go:2457 (0x6fc1fb)
        Value.Type: return v.typeSlow()
/home/ndesai/go/pkg/mod/github.com/godror/god...@v0.49.4/stmt.go:3409 (0x6fc1ab)
        (*conn).getStructObjectType: rvt := rv.Type()
/home/ndesai/go/pkg/mod/github.com/godror/god...@v0.49.4/stmt.go:1465 (0x6e9393)

        (*statement).bindVarTypeSwitch: if ot, err := st.conn.getStructObjectType(ctx, value, ""); err != nil { This was the error that I am receiving

Let me know if I am doing something wrong or what is the correct way to move forward with it.

[Brian Candler]

> Now I checked GO does not support reuse of same parameters

Where or how did you check this?

> so if I have already used order_id as a parameter while passing query to be executed I can not use the order_id parameter anywhere else in the query. 

You haven't given an example of a query that doesn't work in the way you expect. For example:

insert into foo(a, b, c) values (:order_id, :order_id, :order_id);

What happens? If you do find this doesn't work, it's possibly a limitation with Oracle and not with Go. See:

https://github.com/launchbadge/sqlx/issues/951

However, Python's Oracle binding implies this should work:

https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#duplicate-bind-variable-placeholders

As for the stored procedure:

>         sql.Out{Dest: &rows}, // 🔥 THIS IS THE KEY

You haven't show what parameter :20 is being used for, or why you need to pass this in the first place. Nor have you shown which Oracle client library you're using.

The best thing to do would be to write a fresh, minimal, standalone program which demonstrates the problem you are trying to solve - with an associated dummy stored procedure. Then (a) you don't need to share the code you are working on, (b) someone else can use it to reproduce your problem, and (c) in the process of boiling it down to a reproducer, it's quite common that you find the solution.

Good luck!