A question about PreparedStatement and ORA-03115 error

joe.weinstein@oracle.com wrote:
The problem is that you cannot use prepared statement parameters
to supply any general substring of the SQL you want to execute.
In this case you are trying to supply column names. Parameters
are limited to single data values. The DBMS needs to be able to
compile your SQL and develop a query plan that can be re-executed
and accept parameter values. If you try to put general substrings in
as 'parameters' the DBMS may not be able to compile the basic SQL,
and certainly can't make a query plan. Depending on what columns
you want, the query may use a different index or none at all, etc.
Joe

Edited by: joe.weinstein@oracle.com on Apr 16, 2009 10:14 AM

String query_text =
              "SELECT first_name, last_name, id, birthday, job_title, salary "+
              "FROM r "+
              "WHERE id IN ('id17', ?, id18') ";

            PreparedStatement prepared_statement =  connection.prepareStatement(query_text);
            prepared_statement.setString(1, "id02");
            ResultSet query_result =  prepared_statement.executeQuery(query_text);

joe.weinstein@oracle.com wrote:
Well, it may be elsewhere as well, but what I said is true, so we've
gotten past one problem.

joe.weinstein@oracle.com wrote:
Now, is the SQL you just replied with
exact? If so, you missed one single-quote mark around the last
value in the IN list...

mchiocca wrote:
You are mixing named with unnamed parameters in your query (in the WHERE clause). In a PL/SQL procedure call, you are not allowed to mix named with unnamed parameters in a procedure call (e.g. BEGIN pkg.proc(x=>?, ?, z=>?); END ). They must all be named or all unnamed.

If a PreparedStatement behaves in the same manner as a PL/SQL procedure call, then that could explain why you're having a problem you see. You may want to try using unnamed parameters in your query "WHERE id IN (?, ?, ?)" and then use set*() to set the values of those parameters.

String query_text =
             "SELECT first_name, last_name, id, birthday, job_title, salary "+
              "FROM myenterprise "+
              "WHERE id = ? ";

PreparedStatement prepared_statement =  connection.prepareStatement(query_text);
prepared_statement.setString(1, "id02");

ResultSet query_result =  prepared_statement.executeQuery(query_text);

joe.weinstein@oracle.com wrote:
Show me the DDL of your table. I just ran this:

PreparedStatement p = c.prepareStatement("select DT_DATA1, DT_DATA2 FROM MYSOAK where DT_DATA1 IN ('foo', ? , 'bar')");
p.setString(1, "asd" );
p.executeQuery();

The table is CREATE TABLE MYSOAK ( DT_VARCHAR2 VARCHAR2(4000 BYTE), DT_DATA1 VARCHAR2(4000 BYTE) )

If that matters. The driver is 10.2.0.2.0
The DBMS is Oracle Database 11g Release 11.1.0.0.0 - Production

CREATE TABLE myenterprise(
first_name VARCHAR2(20),
last_name VARCHAR2(20),
id VARCHAR2(10) NOT NULL,
birthday DATE,
job_title VARCHAR2(30),
salary NUMBER(10,3),
CONSTRAINT MYENTERPRISE_PK PRIMARY KEY(id)
);

COLUMN first_name FORMAT a10;
COLUMN last_name FORMAT a10;
COLUMN birthday FORMAT a12;
COLUMN id FORMAT a5;
COLUMN job_title FORMAT a10;
COLUMN salary FORMAT 99999;


INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Jack', 'Robinson', 
               'id01', '12-JAN-1970', 'DBA', 7000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('George', 'Black', 
               'id02', '20-FEB-1960', 'DBA', 7200);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Frank', 'Lopardo', 
               'id03', '14-NOV-1972', 'DBA', 8000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Bernard', 'Brown', 
               'id04', '14-AUG-1966', 'DBA', 9000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Robert', 'Burkland', 
               'id05', '3-SEP-1967', 'DBA', 9500);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Dariyoosh', 'Talai, 
               'id06', '16-MAR-1981', 'Network AD', 4200);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Parviz', 'davoodi', 'id07', 
               '12-JAN-1971', 'Network AD', 4000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('James', 'Bound', 
               'id08', '14-NOV-1982', 'Network AD', 5000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Lord', 'Flesheart', 
               'id09', '13-DEC-1981', 'Network AD', 4700);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Robert', 'Gates', 
               'id10', '3-MAR-1979', 'Network AD', 6000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Donald', 'Robinson', 
               'id11', '13-OCT-1977', 'Network AD', 6300);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Cyrus', 'payvar', 'id12', 
               '12-JAN-1971', 'Network AD', 4000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Peter', 'Queen', 
               'id13', '13-JAN-1950', 'Manager', 11000);
               
INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Faramarz', 'Alborz', 
               'id14', '16-MAR-1950', 'Manager', 8000);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Michael', 'Bruce', 
               'id15', '3-DEC-1948', 'Manager', 9000);
               
INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Parvin', 'saremi', 
               'id16', '13-DEC-1950', 'Manager', 8200);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('Susan', 'Jackson', 
               'id17', '14-NOV-1949', 'Manager', 8700);

INSERT INTO myenterprise
     (first_name, last_name, id, birthday, job_title, salary)
          VALUES  ('James', 'Levec', 
               'id18', '3-DEC-1948', 'Manager', 9000);

joe.weinstein@oracle.com wrote:
also tell us your drvier version and DBMS version... You may be using something very old/buggy...

 String query_text =
              "SELECT first_name, last_name, id, birthday, job_title, salary "+
              "FROM myenterprise "+
              "WHERE id IN (?, ?, ?, ?) ";

            PreparedStatement prepared_statement = 
                    connection.prepareStatement(query_text);

            prepared_statement.setString(1, "id02");
            prepared_statement.setString(2, "id04");
            prepared_statement.setString(3, "id08");
            prepared_statement.setString(4, "id09");

            ResultSet query_result =  prepared_statement.executeQuery();

ResultSet query_result =  prepared_statement.executeQuery(query_text);

ResultSet query_result =  prepared_statement.executeQuery();

joe.weinstein@oracle.com wrote:
right! sorry I missed seeing that. Yes, the spec still says that a PreparedStatement
is a subclass of Statement, so it should implement executeQuery(String query)
but I don't know of any driver that really does. Yes, the argument-less executeQuery()
call is correct.