Select multirows from ORACLE table function in JAVA code

Question

In ORACLE(11g), in some package I have a function that returning table:

SELECT * FROM TABLE( my_PKG.fnc_myList());

So it works perfectly in Oracle SQL Developer tool, for example. I got rows from the target table in Query Result of SQL Developer. Question: Will it work from JAVA (8) code? I tried the code below:

        con = DriverManager.getConnection(...);
        String SQLQ = "{SELECT * FROM TABLE( my_PKG.fnc_myList());}";
        Statement st =con.createStatement();
        rs=st.executeQuery(SQLQ);
        while (rs.next()) {
            int id = rs.getInt(0);
            String name = rs.getString(1);
            ....
        }

But got the error:

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

Am I wrong somehwere else or it couldn't work at all through JDBC driver?

Answer 1

You should neither use the braces nor the semi-colon. The braces are sometimes used if only a stored procedure is called. But you have a SELECT statement (even if it contains a function). And the semi-colon is only used in PL/SQL or in tools like SQL developer to separate statements:

con = DriverManager.getConnection(...);
    String SQLQ = "SELECT * FROM TABLE( my_PKG.fnc_myList())";
    Statement st =con.createStatement();
    rs=st.executeQuery(SQLQ);
    while (rs.next()) {
        int id = rs.getInt(0);
        String name = rs.getString(1);
        ....
    }