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).

pcreate(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--| ~p~n",[S,Stmt,Stmt:exec_stmt()]), case Stmt:close() of ok -> io:format("Statement close ok!~n"); Err -> io:format("Statement close err:~p~n",[Err]) end.

p_exit(0) -> ok; p_exit(N) -> receive {'EXIT',_FromPid,_Reason} -> pexit(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!