ORA-28112: failed to execute policy function

[kogan69]

Hi!
Another trouble :)
I'm calling the same stored procedure for a number of times. The first 374 runs are OK, but on the run 375 it fails with ORA-28112
Another effect that I observe, is that the memory usage grows constantly. I've eliminated all of my code to isolate the case and made sure that only the driver's code is running. The memory leakage continued...
I've suspected that the cursors are not getting released and found out in debugger that the line stmt.go#193 (C.OCIHandleFree(unsafe.Pointer(ocistmt), C.OCI_HTYPE_STMT)) isn't called indeed...
It's just my speculation, but may be this is a reason for this effect?

Here's the code that eventually fails:

func LoadUState(guid string) (err error) {
	session, err := SessionPool.Get()
	defer SessionPool.Put(session)
	if err != nil {
		panic(err)
	}
	stmt, err := session.Prep("call sp_get_user_state(:i_user_guid, :o_cur_users,:o_cur_auth_users,:o_cur_acct_accounts,:o_cur_settings)")

        defer stmt.Close()
	if err != nil {
		return err
	}
	usersCur := &ora.Rset{}
	authUsersCur := &ora.Rset{}
	accountsCur := &ora.Rset{}
	settingsCur := &ora.Rset{}

	_, err = stmt.Exe(guid, usersCur, authUsersCur, accountsCur, settingsCur)

	if err != nil {
		return err
	}

	
	usersCur.NextRow()
	authUsersCur.NextRow()
	accountsCur.NextRow()
	settingsCur.NextRow()

	return nil
}

With kind regards,
LK

[kogan69]

Well, I think I've manage to create a test that reproduces it completely and ends up with:
panic: Stmt.exeC Env.ociError ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-06512: at "PARAGONEX.SP_LOB_TEST", line 4

which kinda confirms my hunch regarding dangling cursors.
Here's the test:

package main

import (
	_ "gopkg.in/rana/ora.v4"
	"fmt"
	"gopkg.in/rana/ora.v4"
)

func main() {

	var createTableQry string = `
	begin
	  begin
		execute immediate 'drop table lob_test';
	  exception
		when others then
		  null;
	  end;
	  execute immediate 'create table lob_test (c clob)';
	  execute immediate 'insert into lob_test values (''Hello'')';
	  execute immediate 'insert into lob_test values (''world!'')';
	  commit;
	exception
	  when others then
		null;
	end;`

	dbConnectionString := "u/p@h:1521/DB"
	SessionPool, err := ora.NewPool(dbConnectionString, 32)

	session, err := SessionPool.Get()
	if err != nil {
		panic(err)
	}

	_, err = session.PrepAndExe(createTableQry)

	if err != nil {
		panic(err)
	}

	var createSpQry string = `
	CREATE OR REPLACE PROCEDURE sp_lob_test(o_cur_lob OUT SYS_REFCURSOR,
                                            o_cur_date OUT SYS_REFCURSOR) AS
BEGIN
 OPEN o_cur_lob FOR
  SELECT * FROM lob_test;

 OPEN o_cur_date FOR
 select sysdate from dual;
end sp_lob_test;`

	_, err = session.PrepAndExe(createSpQry)

	if err != nil {
		panic(err)
	}

	SessionPool.Put(session)

	qry := "call sp_lob_test(:o_cur_lob, :o_cur_date)"

	for i := 0; i < 1000000; i++ {

		session, err := SessionPool.Get()
		if err != nil {
			panic(err)
		}

		stmt, err := session.Prep(qry)

		if err != nil {
			panic(err)

		}

		c1 := &ora.Rset{}
		c2 := &ora.Rset{}

		_, err = stmt.Exe(c1, c2)

		if err != nil {
			panic(err)
		}
		fmt.Printf("iteration #%d\n", i)
		fmt.Println(c1)
		fmt.Println(c2)

		stmt.Close()
		SessionPool.Put(session)
	}

}

[tgulacsi]

I know this problem (cursors aren't closed everytime), but couldn't find the solution, yet. kogan69 <[email protected]> ezt írta (időpont: 2017. nov. 14., K, 16:47):

…

Well, I think I've manage to create a test that reproduces it completely and ends up with: panic: Stmt.exeC Env.ociError ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded ORA-01000: maximum open cursors exceeded ORA-06512: at "PARAGONEX.SP_LOB_TEST", line 4 which kinda confirms my hunch regarding dangling cursors. Here's the test: package main import ( _ "gopkg.in/rana/ora.v4" "fmt" "gopkg.in/rana/ora.v4" ) func main() { var createTableQry string = ` begin begin execute immediate 'drop table lob_test'; exception when others then null; end; execute immediate 'create table lob_test (c clob)'; execute immediate 'insert into lob_test values (''Hello'')'; execute immediate 'insert into lob_test values (''world!'')'; commit; exception when others then null; end;` dbConnectionString := ***@***.***:1521/DB" SessionPool, err := ora.NewPool(dbConnectionString, 32) session, err := SessionPool.Get() if err != nil { panic(err) } _, err = session.PrepAndExe(createTableQry) if err != nil { panic(err) } var createSpQry string = ` CREATE OR REPLACE PROCEDURE sp_lob_test(o_cur_lob OUT SYS_REFCURSOR, o_cur_date OUT SYS_REFCURSOR) AS BEGIN OPEN o_cur_lob FOR SELECT * FROM lob_test; OPEN o_cur_date FOR select sysdate from dual; end sp_lob_test;` _, err = session.PrepAndExe(createSpQry) if err != nil { panic(err) } SessionPool.Put(session) qry := "call sp_lob_test(:o_cur_lob, :o_cur_date)" for i := 0; i < 1000000; i++ { session, err := SessionPool.Get() if err != nil { panic(err) } stmt, err := session.Prep(qry) if err != nil { panic(err) } c1 := &ora.Rset{} c2 := &ora.Rset{} _, err = stmt.Exe(c1, c2) if err != nil { panic(err) } fmt.Printf("iteration #%d\n", i) fmt.Println(c1) fmt.Println(c2) stmt.Close() SessionPool.Put(session) } } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#233 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAPoSm7pgFnnC5T1ij1CAGVD0ii6IBWOks5s2bYrgaJpZM4QdMF5> .

[kogan69]

What’s the problem, exactly? May be I can suggest my two cents, man? Cheers, LK

…

On Nov 14, 2017, at 6:09 PM, Tamás Gulácsi ***@***.***> wrote: I know this problem (cursors aren't closed everytime), but couldn't find the solution, yet. kogan69 ***@***.***> ezt írta (időpont: 2017. nov. 14., K, 16:47): > Well, I think I've manage to create a test that reproduces it completely > and ends up with: > panic: Stmt.exeC Env.ociError ORA-00604: error occurred at recursive SQL > level 1 > ORA-01000: maximum open cursors exceeded > ORA-01000: maximum open cursors exceeded > ORA-06512: at "PARAGONEX.SP_LOB_TEST", line 4 > > which kinda confirms my hunch regarding dangling cursors. > Here's the test: > > package main > > import ( > _ "gopkg.in/rana/ora.v4" > "fmt" > "gopkg.in/rana/ora.v4" > ) > > func main() { > > var createTableQry string = ` > begin > begin > execute immediate 'drop table lob_test'; > exception > when others then > null; > end; > execute immediate 'create table lob_test (c clob)'; > execute immediate 'insert into lob_test values (''Hello'')'; > execute immediate 'insert into lob_test values (''world!'')'; > commit; > exception > when others then > null; > end;` > > dbConnectionString := ***@***.***:1521/DB" > SessionPool, err := ora.NewPool(dbConnectionString, 32) > > session, err := SessionPool.Get() > if err != nil { > panic(err) > } > > _, err = session.PrepAndExe(createTableQry) > > if err != nil { > panic(err) > } > > var createSpQry string = ` > CREATE OR REPLACE PROCEDURE sp_lob_test(o_cur_lob OUT SYS_REFCURSOR, > o_cur_date OUT SYS_REFCURSOR) AS > BEGIN > OPEN o_cur_lob FOR > SELECT * FROM lob_test; > > OPEN o_cur_date FOR > select sysdate from dual; > end sp_lob_test;` > > _, err = session.PrepAndExe(createSpQry) > > if err != nil { > panic(err) > } > > SessionPool.Put(session) > > qry := "call sp_lob_test(:o_cur_lob, :o_cur_date)" > > for i := 0; i < 1000000; i++ { > > session, err := SessionPool.Get() > if err != nil { > panic(err) > } > > stmt, err := session.Prep(qry) > > if err != nil { > panic(err) > > } > > c1 := &ora.Rset{} > c2 := &ora.Rset{} > > _, err = stmt.Exe(c1, c2) > > if err != nil { > panic(err) > } > fmt.Printf("iteration #%d\n", i) > fmt.Println(c1) > fmt.Println(c2) > > stmt.Close() > SessionPool.Put(session) > } > > } > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <#233 (comment)>, or mute > the thread > <https://github.com/notifications/unsubscribe-auth/AAPoSm7pgFnnC5T1ij1CAGVD0ii6IBWOks5s2bYrgaJpZM4QdMF5> > . > — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[tgulacsi]

Only from ORA-01000 errors. I've tried to reproduce it (z_db_test.go, z_session_test.go, Test_open_cursor, Test_open_cursor_db), without success. Maybe if you can tweak any of those tests to reproduce the error, the fix would be easier.

[kogan69]

Tamas, The code I’ve sent reproduces this like a charm :)

…

On 14 Nov 2017, at 22:00, Tamás Gulácsi ***@***.***> wrote: Only from ORA-01000 errors. I've tried to reproduce it (z_db_test.go, z_session_test.go, Test_open_cursor, Test_open_cursor_db), without success. Maybe if you can tweak any of those tests to reproduce the error, the fix would be easier. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#233 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ALjeJ9zDcZpxz6cAwj_PrhsLDEB4JxIwks5s2fFxgaJpZM4QdMF5>.

[tgulacsi]

I suspect that the returned cursor are never closed - even if I call Exhaust on it.

[kogan69]

I guess some more RTFM is required... Did you use my code to reproduce this sucker? Cheers, LK

…

On Nov 14, 2017, at 11:35 PM, Tamás Gulácsi ***@***.***> wrote: I suspect that the returned cursor are never closed - even if I call Exhaust on it. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[tgulacsi]

Yes, see TestIssue233 in z_session_test.go

[kogan69]

If you change session.close to testSesPool.Put(session) it will fail even much faster.. :(((
However, in both cases it fails on the same iteration. In my case '#1495' ...
Which means, and correct me if I'm wrong, that closing of session still not enough to get rid of the open cursors within the OCI library related to that session....

[kogan69]

Hi!
In bndRset.bind a handle is allocated for result set.
But I could not trace in the code where this handle is released. At least, Rset.close does not do that.
Tamas, may be this is the problem?

[tgulacsi]

I think yes, and no. My instinct says that the problem is that there's no explicit close for the statement and rset, but close of Rset's close its Stmt, if there's no more opened Rset.
So this is a big mess for me.

I've moved the changes to "issue233" branch.

[kogan69]

Tamas,
I truly appreciate your effort! You've done one hell of the job on this project and AFAIK this is the only solid attempt to give us enterprise Oracle slaves :) a way to bring Go to the corporate world.
#223 is just a total No Go for us, since 80% of our Oracle code is implemented as a stored procedures that return cursors...
I'd really love to contribute to this project since it's of paramount value in the success of Go in the enterprise, so if you need any help, please, just let me know...

BTW, I've observed, that if you return cursors from prepared statement executed from the driver, rather than calling SP, the behaviour is different...

With kind regards,
LK

[tgulacsi]

gopkg.in/goracle.v2 is waay simpler, uses only database/sql and supports returning cursors (as driver.Rows now, but may get better in the future - chime in in https://groups.google.com/forum/#!topic/golang-sql/HNvcgScdyt8). kogan69 <[email protected]> ezt írta (időpont: 2017. nov. 17., P, 6:50):

…

Tamas, I truly appreciate your effort! You've done one hell of the job on this project and AFAIK this is the only solid attempt to give us enterprise Oracle slaves :) a way to bring Go to the corporate world. #223 <#223> is just a total No Go for us, since 80% of our Oracle code is implemented as a stored procedures that return cursors... I'd really love to contribute to this project since it's of paramount value in the success of Go in the enterprise, so if you need any help, please, just let me know... With kind regards, LK — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#233 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAPoSvNwDx6Wa4RGvLotyolpCG4rInetks5s3R65gaJpZM4QdMF5> .

[kogan69]

I see. So what's the point of v4?
And another questions is if v2 is "bugs free" ? :)))
I've also saw you change in issue223 branch. Did it resolve the issue?

[tgulacsi]

rana/ora.v4 is older than goracle.v2 goracle.v1 is older than anything, it's a port of Python's cx_Oracle. goracle.v2 is a rewrite of a driver from scratch for go1.9 as that allowed Out params, for example. (BTW goracle.v2 is based on ODPI-C which is a wrapper of the Oracle OCI C library, written by Anthony Tuininga, the creator of cx_Oracle) kogan69 <[email protected]> ezt írta (időpont: 2017. nov. 17., P, 9:43):

…

I see. So what's the point of v4? And another questions is if v2 is "bugs free" ? :))) — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#233 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAPoSpI5ngdGryT-C4K5yJf8TFG5XTj_ks5s3UcngaJpZM4QdMF5> .

[kogan69]

Tamas, many thanks!
I'll try goracle.v2.
Hopefully, everything is OK there.
I'll tell you upon I'll finish the testing

[vamkaxifa]

@kogan69 Hi, I'm glad that there is someone run into this memory problem too! ^.^ I post it on #197. we are in same situation.... If you got any progress, please do let me know, I'm really appreciate.

[vamkaxifa]

@tgulacsi Hi，I'm still looking forward to #197 , I'd really love to contribute to this project too, so if you need any help, please, just let me know too...