This discussion is archived
6 Replies Latest reply: Nov 16, 2012 7:53 PM by rp0428 RSS

can not close cursor oracle when getProcedureColumns, getFunctionColumns

885520 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points