Java Oracle jdbc COLUMN statement

Question

The problem is following By executing the below queries in SQL Plus, everything is working perfect:

column firstname new_value v_firstname
select firstname from tbcustomer where customer_id = 111
select '&v_firstname', wrk.* from tbwork where customer_id = 111

But when when i tried to execute these queries from Java program, i get java.sql.SQLException: ORA-00900: invalid SQL statement on the first SQL query

        Connection connection = null;
    Statement stat = null;
    String query = "column due_date new_value v_due_date";
    try {

        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverName);
        connection = DriverManager.getConnection(url, username, password);
        stat = connection.createStatement();
        boolean res_num = stat.execute(query);

    } catch (ClassNotFoundException e) {
        // Could not find the database driver
    } catch (SQLException e) {
        e.printStackTrace();
    }

Now the question is how to overcome this error and execute first query or do you have any other solution to define variable on the oracle session and use it in other SQL statements. For instance third query is one of the many queries that i need to execute and all of them will have same first name field

Answer 1

column is a SQL*Plus command. It is not valid in SQL or PL/SQL so you cannot use it in a Java application. Substitution variables like &v_firstname are also a SQL*Plus construct-- they are not valid in SQL or PL/SQL so you cannot use them in a Java application.

If your goal is to get the firstname from tbcustomer and all the columns from tbwork in a single query, you would need to join the two tables. Assuming that both tables have a customer_id column and that is how the two tables are supposed to be joined

SELECT cust.firstname,
       work.*
  FROM tbcustomer cust
       JOIN tbwork work ON (cust.customer_id = work.customer_id)
 WHERE cust.customer_id = 111

Assuming that you will be executing this query for multiple customer_id values, however, 111 should be a bind variable instead of a literal and your Java code should be using a PreparedStatement to prepare the SQL statement, then binding a value like 111 using the setInt method before executing the query.

If you want to break this into two database calls, you can simply do something like

PreparedStatement stmtGetFirstName = connection.prepareStatement("select firstname from tbcustomer where customer_id = ?");
stmtGetFirstName.setInt( 1, 111 );
ResultSet rsGetFirstName = stmtGetFirstName.executeQuery();
String firstName = rsGetFirstName.getString();

PreparedStatement stmtGetWork = connection.prepareStatement("select ?, work.* from tbwork where customer_id = ?");
stmtGetWork.setString( 1, firstName );
stmtGetWork.setInt( 2, 111 );
ResultSet rsGetWork = stmtGetWork.executeQuery();

If you can guarantee that all 600 million executions will occur in the same database session, you could use a database context. You would need to create the context and the package it uses in the database

SQL> create or replace context fname_ctx using scott.pkg_get_fname;

Context created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace package pkg_get_fname
  2  is
  3    procedure set_fname( p_customer_id in number );
  4    function get_fname return varchar2;
  5* end;
SQL> /

Package created.

SQL> create or replace package body pkg_get_fname
  2  is
  3    procedure set_fname( p_customer_id in number )
  4    as
  5    begin
         -- Obviously, you'd get the data here from your table rather than hard-coding 'Bob'
  6      dbms_session.set_context( 'fname_ctx', 'fname', 'Bob' );
  7    end;
  8
  9    function get_fname
 10      return varchar2
 11    is
 12      l_fname varchar2(100);
 13    begin
 14      l_fname := sys_context( 'fname_ctx', 'fname' );
 15      return l_fname;
 16    end;
 17  end;
 18  /

Package body created.

From Java, you could then call pkg_get_fname.set_fname(111) to set the context and use the function pkg_get_fname.get_fname in your query.

It seems odd, however, to be concerned about performance and to be planning to execute 600 million queries from Java against the database. That's going to involve a ton of round-trips over the network between the middle tier and the database server-- if you're really concerned about performance, you'd push that work to stored procedures in the database to eliminate the network round-trips. And the fact that you're executing them so many times makes me suspect that you're doing a lot of row-by-row processing rather than letting the database do set-based operations. That's also going to be a major source of poor performance. Plus, databases are born to join, so it's pretty unusual for a simple join like this to add appreciably to the cost of a query assuming that proper indexes are in place.