3 Replies Latest reply: Aug 5, 2012 1:09 PM by rp0428 RSS

    how stored procedure get executed when called from java

    929270

      When we create a stored procedure or function in oracle, it is compiled and stored there. From java when we call them no compilation is performed its a simple call. When a function or stored procedure is invoked from multiple instance of java objects(for a single session), does the stored procedure clone it self so that it can be called by different java objects which I think not possible at all. In such cases when one java object is interacting with the stored procedure, does other java objects go on a wait state. What happens if sessions are different.
      Please help.

        • 1. Re: how stored procedure get executed when called from java
          rp0428
          >
          When we create a stored procedure or function in oracle, it is compiled and stored there. From java when we call them no compilation is performed its a simple call. When a function or stored procedure is invoked from multiple instance of java objects(for a single session), does the stored procedure clone it self so that it can be called by different java objects which I think not possible at all. In such cases when one java object is interacting with the stored procedure, does other java objects go on a wait state. What happens if sessions are different.
          >
          For a single session/connection a procedure/function is invoked using a connection and statement instance the same procedure/function can't be invoked simutaneously by multiple statements.

          Each DB connection is a separate session so multiple connections/statements CAN invoke the same procedure/function. For each session Oracle allocates space in the PGA and each session gets their own set of instance variables. So these executions may happen in parallel but use different instance data.
          • 2. Re: how stored procedure get executed when called from java
            929270
            does oracle creates multiple instance of a particular stored procedure for different sessions or connections. if not then there might be some kind of waiting principle followed.. what happens exactly
            • 3. Re: how stored procedure get executed when called from java
              rp0428
              >
              does oracle creates multiple instance of a particular stored procedure for different sessions or connections. if not then there might be some kind of waiting principle followed.. what happens exactly
              >
              What happens exactly is detailed in Chap. 14 Memory Architecture ni the Database concepts doc
              http://docs.oracle.com/cd/E14072_01/server.112/e10713/memory.htm#i21266

              The code is shared but the data isn't (see the lone exception to this below).

              See the section on the Library Cache
              >
              Program Units and the Library Cache
              The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to as program units.

              The database processes program units similarly to SQL statements. For example, the database allocates a shared area to hold the parsed, compiled form of a PL/SQL program. The database allocates a private area to hold values specific to the session that runs the program, including local, global, and package variables, and buffers for executing SQL. If multiple users run the same program, then each user maintains a separate copy of his or her private SQL area, which holds session-specific values, and accesses a single shared SQL area.
              >
              The exception is when code uses the SERIALLY_REUSABLE pragma. In that case the memory for package state is in the SGA and users do not have their own copy in their UGA.

              See the SERIALLY_REUSABLE pragma in the PL/SQL Language doc
              http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/seriallyreusable_pragma.htm
              >
              The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

              Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, the database generates an error.