4 Replies Latest reply on Apr 30, 2007 7:19 AM by Billy~Verreynne

    Error ORA - 03127

    569125
    569125 Apr 30, 2007 6:49 AM
      Hi,

      I am facing error ora-03127 no new operations allowed untill the active operations end while selecting from table, after updating data in clob columns.

      Table structure is as follows :

      prj_operations_plan
      ---------------------------
      plan_id varchar2(30)
      oper_id varcahr2(30)
      updt_no number(3)
      text clob
      sd_var sd_obj

      * Here I am appending some texts to the "text" clob data.
      * Proc is executed without any error.
      * I got all texts appended in "text" column.

      But when I have fired select query in table like ( in plsql developers's sql window)

      select * from prj_operations_plan;

      It gives me above mentioned error.

      Kindly help.

      Regards,
      Rushang.

      Message was edited by:
      Rush
      • 2490 Views
      • Tags:
        • 1. Re: Error ORA - 03127
          569125
          569125 Apr 30, 2007 7:00 AM (in response to 569125)
          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.

          Regards,
          Rushang Kansara
          • 2. Re: Error ORA - 03127
            Billy~Verreynne
            Billy~Verreynne Apr 30, 2007 7:00 AM (in response to 569125) 
            03127, 00000, "no new operations allowed until the active operation ends"
            
// *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.
            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.

            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.
            • 3. Re: Error ORA - 03127
              569125
              569125 Apr 30, 2007 7:09 AM (in response to Billy~Verreynne)
              Thanks Billy...
              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?

              Regards,
              Rushang
              • 4. Re: Error ORA - 03127
                Billy~Verreynne
                Billy~Verreynne Apr 30, 2007 7:19 AM (in response to 569125)
                > 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.