0

I'm attempting to use JDBC to call a SQL query in Oracle that uses bind variables with the :varName syntax. The query was written for interactive use and looks like this:

select * from foo where bar = :mybar;

If I could, I would just change the query to use JDBC standard "?" symbols to represent the variable, and use setString(1, "x") to set them. Unfortunately, I cannot change the text of the query as it has been validated for use in medical applications.

I attempted to wrap it in additional code that sets the value for :mybar in approximately the same fashion as an interactive user, following examples from Oracle, but I'm getting the JDBC error:

java.sql.SQLException: Missing IN or OUT parameter at index:: 1.

The wrapped version produces a string that looks like this

variable mybar VARCHAR

begin

:mybar := 'x'

select * from foo where bar = :mybar;

end

The Oracle Driver is a V12 driver loaded via:

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1.0</version>
</dependency>

This is my java code:

try(Connection conn = getConnection()) {
    PreparedStatement stmt = stmt(conn, study, start, end);
    ResultSet resultSet = stmt.executeQuery();
    return Table.read().db(resultSet);
}

...

private PreparedStatement stmt(Connection connection, String study, LocalDate start, LocalDate end) throws SQLException {
    String query = wrapValidatedQuery(study, start, end);
    return connection.prepareStatement(query);
}

The wrapValidatedQuery() method returns the query as shown above.

This (below) is a standalone version that should be executable with changes to the db params.

public static void main(String[] args) throws Exception {
    String username = "name";
    String password = "pwd";
    Connection conn = null;
    Statement stmt;
    int port = 1521;
    String hostname = "host";
    String dbName = "db_name";
    String jdbcUrl = "jdbc:oracle:thin:@" + hostname + ":" + port + "/" + dbName;

    try {
        String driver = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driver).newInstance();
        conn =  DriverManager.getConnection(jdbcUrl, username, password);
        stmt = conn.createStatement();

        String query =
                "    VARIABLE mybar VARCHAR;\n" +
                        "    begin;\n" +
                        "    :mybar := 'x'; \n" +
                        "    select * from foo where bar = :mybar;\n" +
                        "    end;";
        ResultSet rs = stmt.executeQuery(query);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
    throw new RuntimeException("Didn't get results.");
}

Is there a solution that will work without changing the query?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
L. Blanc
  • 2,150
  • 2
  • 21
  • 31
  • 1
    "IN or OUT parameter" does not match with "call a query". It looks like to call a procedure rather than a query. Please show use your Java code. – Wernfried Domscheit Dec 18 '20 at 16:56
  • 1
    Your original query should work - at least, without the trailing semicolon. I've just used that in a small test harness and and it returned matching rows as expected. The driver version might make a difference, I suppose. But it looks like something else is wrong - and Wernfried's comment might indicate where to look. It would probably be helpful to include your Java code, preferably as an [mcve]. – Alex Poole Dec 18 '20 at 17:19
  • Well... actually, your second 'wrapped' version does throw that error, bur referring to index 2. You can't use `variable` because that's a SQL\*Plus etc. client command, and your anonymous PL/SQL block is missing semicolons and something to select *into*. None of that should be necessary though; so show us the code and the error for the simple version. – Alex Poole Dec 18 '20 at 17:30
  • The ":varName syntax" is not a valid JDBC syntax, but a different technology. If you want to run the query using JDBC, you need to adhere to the JDBC standard. That is: `select * from foo where bar = ?`. – The Impaler Dec 18 '20 at 17:38
  • 1
    Oracle drivers support that though, and even allow named references - [see the FAQ](https://www.oracle.com/uk/database/technologies/faq-jdbc.html#Q42). – Alex Poole Dec 18 '20 at 17:51
  • You should be able to use the original query syntax via [Spring JDBC template](https://www.baeldung.com/spring-jdbc-jdbctemplate) which supports named parameters in SQL queries (see section 3.2 from the linked tutorial). – Mick Mnemonic Dec 18 '20 at 21:33
  • Please provide a [mre] of your Java code that produces the error, and post the full exception stacktrace. – Mark Rotteveel Dec 19 '20 at 17:04
  • `VARIABLE mybar VARCHAR;` is not valid in PL/SQL You need a `DECLARE`block: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-language-fundamentals.html#GUID-568AC23F-1BC3-444E-855E-BF2EC4EEB14B –  Dec 19 '20 at 22:23

0 Answers0