14 Replies Latest reply on Apr 16, 2009 7:24 PM by dariyoosh

    A question about PreparedStatement and ORA-03115 error

    dariyoosh
      Dear all,

      I have an issue with JDBC and I would appreciate if you could kindly give me a hand.

      I'm using:

      - Oracle Database 11g Enterprise (Release 11.1.0.6.0)
      - JDBC thin driver version: ( 11.1.0.7.0-Production)
      - JDK 1.6
      - Operating system: Linux (Ubuntu 8.10)

      Here is my code
      import java.sql.*;
      
      public class Main
      {
          public static void main(String[] args)
          {        
              String dbURL = "jdbc:oracle:thin:@localhost:1521:mydatabase";
              String username = "scott";
              String user_password = "tiger";
              
              try
              {
                  Connection connection = DriverManager.getConnection(dbURL, username, user_password);
       
                  String query_text = "SELECT * FROM mytable";
                  Statement statement = connection.createStatement();
                  ResultSet query_result = statement.executeQuery(query_text);
      
                  connection.close();
              }
              catch (SQLException e)
              {
                  for (Throwable t: e)
                      t.printStackTrace();
              }
          }
      }
      This works pretty well without any problem. But when I want to use PreparedStatement instead of Statement I receive the ORA-03115 error message, that is, when I replace the Statement with PreparedStatement in the following way:
      String query_text =
              "SELECT first_name, ?, id, ?, job_title, salary  FROM mytable "+
              "WHERE id IN ('id14', ?, 'id17', 'id18')";
                  
      PreparedStatement prepared_statement =  connection.preparedStatement(query_text);
      
      prepared_statement.setString(1, "last_name");
      prepared_statement.setString(2, "birthday");
      prepared_statement.setString(3, "id02");
      
      ResultSet query_result = prepared_statement.executeQuery(query_text);
      I get the following:
      java.sql.SQLException: ORA-03115: unsupported network datatype or representation
      
           at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
           at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
           at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
           at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
           at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
           at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
           at mysqlpackage.Main.main(Main.java:33)
      Therefore, right after
      ResultSet query_result = prepared_statement.executeQuery(query_text);
      the exception is thrown,

      why it works with Statement but not with PreparedStatement? I tested with several other queries, insert a new row, delete a row, everytime it works well, but when I want to use PreparedStatement instead of Statement, again I have this error message.

      Any idea?


      Thanks in advance,
      :)
        • 1. Re: A question about PreparedStatement and ORA-03115 error
          Joe Weinstein-Oracle
          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
          • 2. Re: A question about PreparedStatement and ORA-03115 error
            dariyoosh
            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
            Hello there,

            Thanks for your reply,

            Even if I write explicitly all the column names, and just use ? for user defined values, the query will still raise the same exception. For example:
            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);
            Will launch the same exception, so the problem is probably elsewhere.

            Regards,
            • 3. Re: A question about PreparedStatement and ORA-03115 error
              Joe Weinstein-Oracle
              Well, it may be elsewhere as well, but what I said is true, so we've
              gotten past one problem. 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...
              1 person found this helpful
              • 4. Re: A question about PreparedStatement and ORA-03115 error
                dariyoosh
                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.
                Yes, thanks for this remark, I didn't know that
                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...
                Yes, that was just an error while I was typing, the query is valid and I tested on both Oracle (SQL*Plus) and
                MySQL(shell) and it gives me the correct result. As I said if I use Statement it works, but from the moment I switch to PreparedStatement I get the error message.

                - When I run with Oracle it raises ORA-03115 error

                - When I run with MySQL it raises MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?'
                • 5. Re: A question about PreparedStatement and ORA-03115 error
                  428263
                  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.
                  1 person found this helpful
                  • 6. Re: A question about PreparedStatement and ORA-03115 error
                    dariyoosh
                    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.
                    Hello there,

                    Thanks for this remark, but again even with a uniform style it doesn't work. For example:
                    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);
                    And it generated the same exception,

                    Regards,
                    • 7. Re: A question about PreparedStatement and ORA-03115 error
                      Joe Weinstein-Oracle
                      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
                      • 8. Re: A question about PreparedStatement and ORA-03115 error
                        Joe Weinstein-Oracle
                        also tell us your drvier version and DBMS version... You may be using something very old/buggy...
                        • 9. Re: A question about PreparedStatement and ORA-03115 error
                          dariyoosh
                          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
                          Here is the table named: myenterprise
                          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);
                          Regards,
                          • 10. Re: A question about PreparedStatement and ORA-03115 error
                            dariyoosh
                            joe.weinstein@oracle.com wrote:
                            also tell us your drvier version and DBMS version... You may be using something very old/buggy...
                            I wrote this in my first post

                            - Oracle Database 11g Enterprise (Release 11.1.0.6.0)
                            - JDBC thin driver version: ( 11.1.0.7.0-Production)
                            - JDK 1.6
                            - Operating system: Linux (Ubuntu 8.10)
                            • 11. Re: A question about PreparedStatement and ORA-03115 error
                              Joe Weinstein-Oracle
                              Gotcha. OK, I did this:

                              c = dr.connect( URL, props);
                              System.out.println("The driver is " + c.getMetaData().getDriverVersion() );
                              System.out.println("The DBMS is " + c.getMetaData().getDatabaseProductVersion() );

                              String table = "CREATE TABLE foo( 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)) ";

                              Statement s = c.createStatement();
                              s.executeUpdate(table);

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

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

                              I got the exception:

                              The driver is 10.2.0.2.0
                              The DBMS is Oracle Database 11g Release 11.1.0.0.0 - Production
                              java.sql.SQLException: ORA-03115: unsupported network datatype or representation

                              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
                              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
                              at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
                              at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
                              at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java
                              :799)
                              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1037)
                              at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.ja
                              va:839)
                              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
                              at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1272)
                              at thin.main(thin.java:39)

                              This is some sort of bug, but it seems maybe like a DBMS issue!
                              Joe
                              1 person found this helpful
                              • 12. Re: A question about PreparedStatement and ORA-03115 error
                                dariyoosh
                                I found the answer!!

                                Here is my test
                                 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();
                                So instead of writing
                                ResultSet query_result =  prepared_statement.executeQuery(query_text);
                                I had to write
                                ResultSet query_result =  prepared_statement.executeQuery();
                                And now it works pretty well :)

                                Thank you all very much for your remarks which were quite important and informative for me

                                Kind Regards,
                                • 13. Re: A question about PreparedStatement and ORA-03115 error
                                  Joe Weinstein-Oracle
                                  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.
                                  • 14. Re: A question about PreparedStatement and ORA-03115 error
                                    dariyoosh
                                    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.
                                    Thanks again, for the time you spent with my problem.

                                    Regards,