4 Replies Latest reply on Jan 24, 2020 1:17 AM by ODBC-to-JDBC

    Oracle 12c MAX statements

    ODBC-to-JDBC

      Hello members,

       

      I have worked on the ODBC side of database driver development and have a good understanding of Connections and statements in that space. With JDBC, I am a little at a loss when trying to get similar information on it. I am looking for some thing similar to the following for Oracle 12c and need help with it.

       

      The following is a good list of System limits and defaults for TimesTen, example it lists the "Maximum number of prepared PL/SQL statements per connection." has a default number.

      https://docs.oracle.com/database/121/TTREF/limit.htm#TTREF455

       

      With Oracle 12c I see some information similar to this where they say this number varies based on the number of caches statements. Is there a document that has a list of the system limits for the Oracle 12C version as well.

       

      Please help.

       

      Thanks

        • 1. Re: Oracle 12c MAX statements
          Nirmala Sundarappa-Oracle

          Can you refer to JDBC developer's guide? All JDBC resources are on www.oracle.com/jdbc

          • 2. Re: Oracle 12c MAX statements
            ODBC-to-JDBC

            Thanks for the reply. I did check the web page www.oracle.com/jdbc and could not find a list similar to the one given below.

             

            https://docs.oracle.com/database/121/TTREF/limit.htm#TTREF455

             

            I am looking for some document that specifies the maximum number of statements in the a connection. When using it, I can see it is certainly more than one, but can not find a document that or API to use to get that MAX number.

             

            I have also tried to call the API calls to get this information and get a 0 for them.

             

            DatabaseMetaData dbmd;

            // connect to the database

                 try{

                        dbmd = g_dbIAMConn.getMetaData();

                        System.out.println(dbmd.getDatabaseProductName());

                        System.out.println(dbmd.getDatabaseProductVersion());

                        System.out.println(dbmd.getDriverName());

                        System.out.println(dbmd.getDriverVersion());

                        System.out.println(dbmd.getCatalogs());

                        //System.out.println(dbmd.getCatalogTerm());

                        System.out.println(dbmd.getMaxConnections());

                        System.out.println(dbmd.getMaxStatementLength());

                        System.out.println(dbmd.getMaxStatements());

                    }

                    catch (SQLException ex) {

                        ex.printStackTrace();

                    }

             

            --------------------- O U T P U T ------------------------

            Oracle

            Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

            Oracle JDBC driver

            12.1.0.2.0

            oracle.jdbc.driver.ForwardOnlyResultSet@f8c1ddd

            0

            65535

            0

             

            Please help!!!

            • 3. Re: Oracle 12c MAX statements
              Nirmala Sundarappa-Oracle

              Can you check the JDBC Javadochttps://docs.oracle.com/en/database/oracle/oracle-database/19/jajdb/index.htmlfor more details? Check out the details here.

               

              setMaxStatements

              void setMaxStatements(int max) throws SQLException

              Specifies the value of the maxStatements property. This is the size of the statement cache used by both implicit and explicit caching. This value does not apply to the statement cache size of connections created by ImplicitStatementCache for which the cache size is set through a java.util.Properties object.

              Parameters:
              max - Requested size of the cache. If the existing cache size is less than max, statements will be purged to reduce the size.
              Throws:
              SQLException - if max < 0

               

              • 4. Re: Oracle 12c MAX statements
                ODBC-to-JDBC

                Thanks for the reply. Looks like setMaxStatements API is Deprecated..

                 

                I will take a look at it from the connection side of the settings to see if I can find anything. So, there are no Default system settings for these I guess?