Cursor-limit problem on bulk insert.

[zicaty]

I used the following code to test performance [Use p(1000), for example]. But after some successful operations, server rejects further insertions with ORA-01000 error.
Please tell me what is wrong. Thanks.

t.erl:

-module(t).
-export([p/1,p_p/2]).

p(N) ->
application:start(erloci),
OciPort = erloci:new([{logging, false}]),
Tns= <<"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.143.132.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=bimstest)))">>,
User= <<"bill">>,
Pswd= <<"bill123">>,
OciSession = OciPort:get_session(Tns, User, Pswd),
process_flag(trap_exit, true),
p_create(N,OciSession),
p_exit(N).

p_create(0,_) ->
ok;
p_create(N,OciSession) ->
spawn_link(?MODULE,p_p,[N,OciSession]),
p_create(N-1,OciSession).

p_p(N,OciSession) ->
S=iolist_to_binary(["insert into test_2 values(",integer_to_list(N),",",integer_to_list(N),")"]),
Stmt=OciSession:prep_sql(S),
io:format("~p |-~p--| pn",[S,Stmt,Stmt:exec_stmt()]),
case Stmt:close() of
ok ->
io:format("Statement close ok!~n");
Err ->
io:format("Statement close err:pn",[Err])
end.

p_exit(0) ->
ok;
p_exit(N) ->
receive
{'EXIT',_FromPid,_Reason} ->
p_exit(N-1);
_ ->
p_exit(N)
end.

[c-bik]

Its seems your statements are not being closed fast enough. I can suggest the following:

1. check the current setting of open_cursors in server with select name, value from v$parameter where name = 'open_cursors'; by logging into server as sysdba (default current is too low https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137)
2. Try p(1000) again after increasing it to 1000 http://www.dba-oracle.com/sf_ora_01000_maximum_open_cursors_exceeded.htm
3. You may experimentally find a lower value of N in alter system set open_cursors=N... (p(M)) for N < M. But that will depend on the latency between Stmt:exec_stmt/0 <-> Stmt:close/0. Which IMHO may be difficult to formulate.

Please write back if you are still experiencing problem.

Good luck

[c-bik]

@zicaty how did it go? please let us know if you had to solve it differently. Can you please close this issue if it is resolved for you?

[c-bik]

Gave it a week to receive some kind of response. Closing!