Monday, 4 February 2008

Variable in list with Oracle JDBC and RDBMS

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


No comments: