0

I need to use a global temporary table as proxy to read a BLOB column from a database link in an application I've inherited. Using ON COMMIT DELETE ROWS I wasn't able to read my values back (as if every query was being ran in a different connection from pool) so I eventually settled with:

CREATE GLOBAL TEMPORARY TABLE TMP_FOO (
    ID VARCHAR2(64 BYTE) NOT NULL ENABLE, 
    FOO BLOB NOT NULL ENABLE, 
    CONSTRAINT TMP_FOO_PK PRIMARY KEY (ID) ENABLE
) ON COMMIT PRESERVE ROWS;

While this works, I'm now repeatedly getting:

weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool foods to allocate to applications, please increase the size of the pool and retry

WebLogic console certainly shows 15 active connections. But I am the only user!

What am I doing wrong that's preventing connections from being reused?

// Custom method that returns java.sql.Connection from javax.naming.InitialContext.lookup(String)
Connection conn = ds.getConnection();
PreparedStatement st = null;
ResultSet rs = null;
String sql;
Blob foo;

try {
    sql = "INSERT INTO TMP_FOO (ID, FOO) SELECT ?, FOO FROM REMOTE_TABLE@DBLINK WHERE REMOTE_ID = ?";
    st = conn.prepareStatement(sql);
    st.setString(1, "Random UUID here");
    st.setString(2, "20");
    System.out.println("Rows inserted: " + st.executeUpdate());

    sql = "SELECT FOO FROM TMP_FOO WHERE ID = ?";
    st = conn.prepareStatement(sql);
    st.setString(1, "Random UUID here");
    rs = st.executeQuery();
    if (!rs.next()) {
        throw new RuntimeException("Not found");
    }
    foo = rs.getBlob("foo");
} catch(Exception e) {
    // Final code will do something meaningful here
    System.out.println("[ERROR] " + e.getMessage());
} finally {
    conn.rollback();
}
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • 2
    You are not closing the connection, so it is never put back into the pool –  Feb 04 '20 at 07:42
  • @a_horse_with_no_name Oh my... That was it. (BTW, did `ON COMMIT DELETE ROWS` not work for me for some similar rookie mistake?) – Álvaro González Feb 04 '20 at 07:50
  • @a_horse_with_no_name Never mind... I totally forgot about checking whether autocommit is enabled by default (it is). Thank you very much. – Álvaro González Feb 04 '20 at 08:15

0 Answers0