This content has been marked as final. Show 4 replies
also i want to add that object type field i get the value error.
I have another proc which executes and gives me result without any errors but it does not contain any object field.
If anybody know about this error, please explain here.
03127, 00000, "no new operations allowed until the active operation ends"This error results from making a call to the SQL Engine, with a SQL Statement/Database Connection Handle, that is already busy servicing/executing a call.
// *Cause: An attempt was made to execute a new operation before the active
// non-blocking operation completed or a new operation was attempted
// before all the pieces of a column were inserted or fetched.
// *Action: Execute the new operation after the non-blocking operation
// completes. If piecewise binds/defines were done, execute the new
// operation after all the pieces have been inserted or fetched.
SQL & Database Handles are not multi-thread capable. It is serialised. You can only pass a single call at a time using a SQL Statement Handle. You can create multiple statement handles per Database Connection Handle. But this in turn can only execute a single statement handle at a time.
You have to wait for that call to be serviced and a response code to be returned from the SQL engine for that call. Only then, can you pass another call to that Database Connection Handle, using the same SQL Statement Handle or a different SQL Statement Handle.
You have to wait for that call to be serviced and a response code to be returned from the SQL engine for that call.But Is there any way to know how long I have to wait?
> But Is there any way to know how long I have to wait?
OCI calls are blocking call - it will return when it is done.
With "waiting for the call to complete" I referred primarily to not using the database connection handle until that call is done.
For example, your Java program opens a database connection to Oracle and gets a connection handle. You spawn 2 threads. These read data from source source (network/file/whatever) and then fire a SQL INSERT statement over that database connection handle.
At the other end of that connection handle, is a database server process servicing that connection (aka Oracle client session). That process can only perform a single instruction at a time. It is not multi-threaded. It is serialised.
If your threads 1 and 2 at the same time attempt an INSERT, the first thread will succeed. The second thread will get this ORA-03127 error as the server process in Oracle is busy with the first thread's INSERT statement. Only when that is done, then can it service a new call.
The problem in this example I've described is that the database connection/session handle is treated as if it is multi-thread capable. It is not. Each thread will need its own connection handle and its own Oracle server process to service it.