6 Replies Latest reply: Nov 16, 2012 9:53 PM by rp0428 RSS

    can not close cursor oracle when getProcedureColumns, getFunctionColumns

    885520

      Dear all,
      i do unit-test to get all properties of one procedure, function Oracle.
      follow my source-code. when i run main method, finish for loop and while sleep of Thread.
      i execute select query get current open cursor database(resultset java was closed, connection was close). i see all cursor was born by for loop can not close.

      datasource i used BoneCP ,"http://jolbox.com/".

      who can help me fix this bug.
      thanks.

      *my sql for check :

      select sum(a.value) total_cur, max(a.value) max_cur, s.username, s.program
      from v$sesstat a, v$statname b, v$session s
      where a.statistic# = b.statistic# and s.sid=a.sid
      and b.name = 'opened cursors current'
      and username = 'xxxxxx'
      and program <> 'SQL Developer'
      group by s.username, s.program order by 1 desc;

      *my java code
      public class UnitTest {
      static com.jolbox.bonecp.BoneCPDataSource dataSource;

      private static void init() {
      dataSource = new com.jolbox.bonecp.BoneCPDataSource();
      dataSource.setPartitionCount(1);
      dataSource.setDriverClass("oracle.jdbc.OracleDriver");
      dataSource.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl");
      dataSource.setUsername("xxxxx");
      dataSource.setPassword("yyyyyy");
      dataSource.setAcquireIncrement(1);
      dataSource.setMinConnectionsPerPartition(1);
      dataSource.setMaxConnectionsPerPartition(1);
      dataSource.setIdleMaxAgeInMinutes(1);
      dataSource.setIdleConnectionTestPeriodInMinutes(5);
      }

      private static void doTest() throws SQLException {
      Connection conn = dataSource.getConnection();
      DatabaseMetaData dbmd = conn.getMetaData();
      String schema = "SCHEMA";
      String catalog = "CATALOG";
      String procedure = "PROCEDURE_NAME";

      ResultSet rs = dbmd.getProcedureColumns(catalog, schema, procedure, null);
      if (rs.isBeforeFirst()) {
      while (rs.next()) {
      System.out.println(rs.getString("COLUMN_NAME") + " : " + rs.getInt("COLUMN_TYPE"));
      System.out.println(rs.getInt("DATA_TYPE") + " : " + rs.getString("TYPE_NAME"));
      System.out.println("--------------------------------------------");
      }
      }

      rs.close();
      conn.close();
      }

      public static void main(String[] args) throws SQLException, InterruptedException {
      init();
      for(int i = 0; i < 100; i++){
      doTest();
      }
      Thread.sleep(1000*60*2);
      }
      }

      Edited by: user12063694 on Nov 16, 2012 9:01 PM

        • 1. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
          rp0428
          Welcome to the forum!
          >
          i do unit-test to get all properties of one procedure, function Oracle.
          follow my source-code. when i run main method, finish for loop and while sleep of Thread.
          i execute select query get current open cursor database(resultset java was closed, connection was close). i see all cursor was born by for loop can not close.
          >
          When you post a JDBC question you need to provide your 4 digit Oracle version, JDBC jar name and version and JDK version.

          Also your posted query for checking open cursors includes this line
          and program 'SQL Developer'
          SQL Developer could not have been used to execute the Java code you posted so why are you checking open cursors for sql developer?

          There is nothing in the code you posted that would have set 'program' to 'SQL Developer'. Please clarify this.
          • 2. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
            885520
            i using oracle database oracle_11g.2.0.1.0, ojdbc6_g(11.2.0.3) and jdk1.6.0_30.
            my quey have " program <> 'SQL Developer' ". because i open SQL Developer then in my query i filter all cursor open by SQL Developer.
            you can run my code for check it.
            • 3. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
              rp0428
              >
              my quey have " program 'SQL Developer' ". because i open SQL Developer then in my query i filter all cursor open by SQL Developer.
              >
              I understand that. But you did not respond to my question
              >
              SQL Developer could not have been used to execute the Java code you posted so why are you checking open cursors for sql developer?
              >
              Your query would only check the cursors that sql developer has open. Sql developer is not executing your java class so your query is not checking the open cursors of your java class.

              I did execute your Java class using Netbeans and there are no open cursors when the class is finished. I used an OracleDataSource and only set these values:
              dataSource.setDriverType("oracle.jdbc.OracleDriver");
              dataSource.setPassword("tiger");
              dataSource.setUser("SCOTT");
              dataSource.setURL(myUrl);
              The query count rarely gets above 2. If I comment out the close statements
              //rs.close();
              //conn.close();
              the query count will get as high as 60 or 70 but almost immediately drops down to 2-4 or so. That is because each loop iteration resuses the same result set and so the older result set gets garbage collected which results in it being closed.

              In short, I can't reproduce your problem.
              • 4. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
                885520
                sorry,
                my query "program &lt;&gt; 'SQL Developer' ".
                execute my Java class while Thread sleep, you run my query, you will see cursor open by java can not close.

                Edited by: user12063694 on Nov 16, 2012 7:46 PM

                Edited by: user12063694 on Nov 16, 2012 9:00 PM

                Edited by: user12063694 on Nov 16, 2012 9:00 PM
                • 5. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
                  885520
                  i execute sql query and result :

                  value osuser username sid program status machine
                  102     X61      XXXXX     15     JDBC Thin Client     INACTIVE     X61-PC
                  • 6. Re: can not close cursor oracle when getProcedureColumns, getFunctionColumns
                    rp0428
                    >
                    my query "program <> 'SQL Developer' ".
                    >
                    That makes more sense. I forgot that the jive forum software eats the not equals sign. You should always use <>.
                    >
                    not, execute your Java class while Thread sleep, you run my query, you will see cursor open by java can not close.
                    >
                    Did you read my entire reply? I did execute your Java class. Though I don't know what you mean by 'execute your Java class while Thread sleep'. The sleep is AFTER the loop.

                    I do not see the cursors still open after the code finishes. See the results I posted above. I am using Oracle 11.2.0.1.0 Java 1.6 and OJDBC6.jar