Hi dev team, This is not an issue but can you provide an example where ora.v3 exec

alternative to calling stored procedure without using OUT parameter SYS_REFCURSOR to retrieve results about ora HOT 14 CLOSED

rana commented on May 30, 2024

alternative to calling stored procedure without using OUT parameter SYS_REFCURSOR to retrieve results

from ora.

Comments (14)

tgulacsi commented on May 30, 2024

a) shovel all data in an OUT VARCHAR2(32767) parameter (or CLOB),
b) use my not-yet-merged ("slice" branch) changes to return a TYPE xx_tab_typ VARCHAR2(32767) INDEX BY PLS_INTEGER
c) help us speed up Rset.Next: provide a repro, or profile it yourself!

from ora.

crisarceramos commented on May 30, 2024

Thanks tgulacsi. Just to be clear with a), u mean to change SYS_REFCURSOR to VARCHAR2(32767) right? But inside stored proc, how do u store result of a SELECT query in a VARCHAR2 ?

from ora.

tgulacsi commented on May 30, 2024

"SELECT a||';'||b||';'", or create an XML with the built-in methods ... I haven't said it's a nice solution - just a barely workable one...

from ora.

tgulacsi commented on May 30, 2024

How slow is it? Do you retrieve a lot of simple (one small field, like a VARCHAR2(1)) rows?
Maybe the conversion is slow? What are the data types?

from ora.

crisarceramos commented on May 30, 2024

i get this execution timestamp below. i already modified the SP to return Varchar2 and get that from a string variable in golang. unfortunately, still slow. but when i execute SP on sqldev, i get fast result. not sure why. SP constantly return 30+ rows and 4 columns.

executing sp:20160217132405
executing complete:20160217132735

from ora.

tgulacsi commented on May 30, 2024

What are these numbers? 3 minutes difference?

Can you share code?

from ora.

crisarceramos commented on May 30, 2024

yes 3mins. sometimes 5mins. not sure what's going on honestly. below is the golang code and SP code being called. also, the SP is actually dynamic but generates the below SQL code which is actually simple.

Golang
`
func (oracleDB *oracleDB) executeOraSP(ses *ora.Ses) string {
if nil == ses {
ses = oracleDB.mSes
}

var strResult string
mStmt, err := ses.Prep("CALL " + *sp + "(:1, :PARAM1, :PARAM2, :PARAM3)")

if nil == err {
    //split param
    var params = strings.Fields(*spPar)

    if len(params) == 3 {
        t := time.Now()
        fmt.Println("executing sp:" + t.Format("20060102150405"))
        _, err := mStmt.Exe(&strResult, params[0], params[1], params[2])
        t = time.Now()
        fmt.Println("executing complete:" + t.Format("20060102150405"))
        if nil != err {
            oracleDB.mErr = err
        }
    } else {
        oracleDB.mErr = errors.New("Incomplete SP parameter!")
    }
} else {
    oracleDB.mErr = err
}

return strResult

SP
SELECT STAGELIST.Channel, NVL(STATLIST."Value", 0) AS "VALUE", STAGELIST."FLOAT", STAGELIST."UNIT" FROM (SELECT Stage AS Channel, 0 AS "Value", 0 AS "FLOAT", 'Count' AS "UNIT" FROM SOMETABLE WHERE 1=1 AND Stage LIKE 'SOMETEXT%' AND ACCESSGROUP like 'SOMETEXT%' GROUP BY Stage) STAGELIST LEFT OUTER JOIN (SELECT Stage AS Channel, COUNT(DISTINCT CORRELATIONID) AS "Value", 0 AS "FLOAT", 'Count' AS "UNIT" FROM SOMETABLE WHERE CreatedDate > (SYSDATE - 5/1440) AND Stage LIKE 'SOMETEXT%' GROUP BY Stage ) STATLIST ON (STAGELIST.Channel = STATLIST.Channel);

from ora.

tgulacsi commented on May 30, 2024

I see nothing esp. fishy with your code; can you add more logging? Sthg like

import "gopkg.in/rana/ora.v3/lg"

func init() {
        drvCfg := ora.NewDrvCfg()
        drvCfg.Log.Logger = lg.Log
        ora.SetDrvCfg(drvCfg)
}

func (oracleDB *oracleDB) executeOraSP(ses *ora.Ses) string {                                                                                                                                                                                 
    if ses == ses {                                                                                                                                                                                                                           
        ses = oracleDB.mSes                                                                                                                                                                                                                   
    }

    qry := "CALL " + *sp + "(:1, :PARAM1, :PARAM2, :PARAM3)"                                                                                                                                                                                  
    mStmt, err := ses.Prep(qry)                                                                                                                                                                                                               
    if err != nil {                                                                                                                                                                                                                           
        oracleDB.mErr = err                                                                                                                                                                                                                   
        return ""                                                                                                                                                                                                                             
    }                                                                                                                                                                                                                                         

    //split param                                                                                                                                                                                                                             
    var params = strings.Fields(*spPar)                                                                                                                                                                                                       

    if len(params) != 3 {                                                                                                                                                                                                                     
        oracleDB.mErr = errors.New("Incomplete SP parameter!")                                                                                                                                                                                
        return ""                                                                                                                                                                                                                             
    }                                                                                                                                                                                                                                         
    params := []interface{}{&strResult, params[0], params[1], params[2]}                                                                                                                                                                      
    t := time.Now()                                                                                                                                                                                                                           
    fmt.Printf("Start executing %q %#v at %s...\n", qry, params, t.Format("20060102_150405"))                                                                                                                                                 
    var strResult string                                                                                                                                                                                                                      
    _, err := mStmt.Exe(params...)                                                                                                                                                                                                            
    d := time.Since(t)                                                                                                                                                                                                                        
    if err != nil {                                                                                                                                                                                                                           
        fmt.Printf("execution error: %v\n", err)                                                                                                                                                                                              
        oracleDB.mErr = err                                                                                                                                                                                                                   
        return ""                                                                                                                                                                                                                             
    }                                                                                                                                                                                                                                         
    fmt.Printf("execution completed in %s, result: %q.", d, strResult)                                                                                                                                                                        
    return strResult                                                                                                                                                                                                                          
}

It is quite strange that a stored procedure call has anything more than a few milliseconds of overhead...

from ora.

crisarceramos commented on May 30, 2024

Thanks tgulacsi! Really appreciate your time and effort to help. Below is what I get from the logs.
It seems taking long on "[bndStringPtr.bind] StringPtr.bind(1)".

ORA I 2016/02/17 19:40:21.282303 util.go:179: [gopkg.in/rana/ora%2ev3.OpenEnv]
ORA I 2016/02/17 19:40:21.285075 env.go:299: E2 [Env.OpenSrv]
ORA I 2016/02/17 19:40:23.921919 srv.go:322: E2S1 [Srv.OpenSes]
ORA I 2016/02/17 19:40:25.201767 ses.go:637: E2S1S1 [Ses.Prep] CALL PROC_PRTG_HZ_KPI(:1, :PARAM1, :PARAM2, :PARAM3)
Start executing "CALL SP_NAME(:1, :PARAM1, :PARAM2, :PARAM3)" []string{"sometext", "sometext%", "sometext"} at 20160217_194025...
ORA I 2016/02/17 19:40:25.202519 stmt.go:1116: E2S1S1S1 [Stmt.exe]
ORA I 2016/02/17 19:40:25.202546 stmt.go:1129: E2S1S1S1 [Stmt.bind] Params 4
ORA I 2016/02/17 19:40:25.202718 stmt.go:1129: E2S1S1S1 [bndStringPtr.bind] StringPtr.bind(1) cap=1000 len=1 alen=0
ORA I 2016/02/17 19:49:04.079039 stmt.go:1129: E2S1S1S1 [bndStringPtr.setPtr] StringPtr.setPtr isNull=1051 alen=1000
execution completed in 8m38.876753193s, result: "\nSometext;0;0;Count\n".<nil>          <--the result is just a repeated like this. 1000+characters

Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;Count
Sometext;0;0;                           <--this result string is always truncated not sure why, maybe because of the cap??
ORA I 2016/02/17 19:49:04.080678 ses.go:635: E2S1S1 [Ses.close]
ORA I 2016/02/17 19:49:04.080708 stmt.go:1116: E2S1S1S1 [Stmt.close]
ORA I 2016/02/17 19:49:04.379058 srv.go:322: E2S1 [Srv.close]
ORA I 2016/02/17 19:49:04.380475 env.go:299: E2 [Env.Close]
Success: process exited with code 0.

from ora.

tgulacsi commented on May 30, 2024

Just to narrow things down:
which version of go?
which version (commit) of ora?
I'm asking this 'cause I've met some great slow-down with go 1.6.

Can you share the stored procedure's sekeleton (in and out types), maybe a test version which does reproduce the problem in your env?

from ora.

crisarceramos commented on May 30, 2024

go version go1.5.1 linux/amd64
for ora, i just perform go get -u all last week.
Linux: Elementary OS Freya

SP skeleton: I'll try to create a test SP. but below is skeleton

create or replace PROCEDURE PROC_NAME(THEOUTPUT OUT VARCHAR2, PARAM1 IN VARCHAR2, PARAM2 IN VARCHAR2, PARAM3 IN VARCHAR2) AS
--declare multiple VARCHAR2 variable containing select statements, at the end combine these based from IF-ELS and execute with below:

  EXECUTE IMMEDIATE STATQUERY
  BULK COLLECT
  INTO channels;
  FOR i IN channels.FIRST..channels.LAST
  LOOP
      TEMPSTR:=TEMPSTR||CHR(10)||channels(i).channeldata;
  END LOOP;

  THEOUTPUT:=TEMPSTR;
END;

from ora.

tgulacsi commented on May 30, 2024

Hi, I can't reproduce it on my test db.

What is the env where you run it? Is this the only program running?
Can you reproduce it on my test db? It's at test/[email protected]:49161 - it runs in a container, and is erased every hour.
If you can, then a reproduction case would be great help!
If you cannot, then maybe the differences between the DB envs can shed some light.

You could start with checking out the "issue59" branch

cd $GOPATH/src/gopkg.in/rana/ora.v3
git fetch origin
git checkout -b issue59 origin/issue59
go install
go test -run=Issue59

from ora.

tgulacsi commented on May 30, 2024

Ok, I've misled you, sorry.

database/sql is does not allow to use it with returning values by using pointers: it simply dereferences them...

So I've added a TestSessionCallPkg as an example.

from ora.

crisarceramos commented on May 30, 2024

thanks alot!

from ora.

alternative to calling stored procedure without using OUT parameter SYS_REFCURSOR to retrieve results about ora HOT 14 CLOSED

Comments (14)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent