Variable in list with Oracle JDBC and RDBMS

참고 : http://theblasfrompas.blogspot.com/2008/02/variable-in-list-with-oracle-jdbc-and.html

I always wanted to create a variable in list query using a single parameter and found that it wasn't possible with JDBC.

SELECT * FROM DEPT WHERE like DEPTNO IN (?);

Then I stumbled upon this on steve's blog, which he shows how he did it in ADF BC using a SQL function.

http://radio.weblogs.com/0118231/stories/2004/09/23/notYetDocumentedAdfSampleApplications.html
126. Using Comma-Separated String Bind for Variable IN List [10.1.3.3] 10-JAN-2008

So his my simple JDBC program method showing how it works, using the DEPT table here.

public void run () throws SQLException
{
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rset = null;
  String queryInList =
    "SELECT DEPTNO, " +
    "       DNAME, " +
    "       LOC " +
    "FROM DEPT " +
    "WHERE DEPTNO IN " +
    "(SELECT * FROM TABLE(CAST(in_number_list(?) as num_table)))";

  try
  {
    conn = getConnection();
    stmt = conn.prepareStatement(queryInList);
    stmt.setString(1, "10, 20, 30");
    rset = stmt.executeQuery();
 
    while (rset.next())
    {
      System.out.println("Dept [" + rset.getInt(1) + ", " +
                         rset.getString(2) + "]");
    }
  }
  catch (SQLException e)
  {
    System.out.println("SQLException occurred");
    e.printStackTrace();
  }
  finally
  {
    if (rset != null)
      rset.close();

    if (stmt != null)
      stmt.close();
   
    if (conn != null)
      conn.close();
  }
}

So the output is as follows showing that only records 10, 20, 30 will be returned.

Dept [10, ACCOUNTING]
Dept [20, RESEARCH]
Dept [30, SALES]

Note: SQL being used is as follows

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
  RETURN num_table
AS
  l_tab   num_table := num_table();
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;
/
show errors