A question about PreparedStatement and ORA-03115 error

[dariyoosh za]

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,

[Gregg Bolinger]

Google turns up a ton of good info about this error message

ORA-03115: unsupported network datatype or representation

[dariyoosh za]

Gregg Bolinger wrote:Google turns up a ton of good info about this error message

Hello there,

Thanks for your reply. I have been googling since yesterday, but I have not found any thread among HOWTOs and several forums (including this one) that explains my problem. That's why I posted here to get some help specifically about my class connection to the database.

Regards,

[Gregg Bolinger]

Well, I don't know if this is the reason or not but you are supplying the query twice.

PreparedStatement prepared_statement = connection.preparedStatement(query_text);
ResultSet query_result = prepared_statement.executeQuery(query_text);

Try just doing

ResultSet query_result = prepared_statement.executeQuery();

Also, on a side note, in java we use camel casing for variable names, not underscores. Your code would follow better conventions if it looked like

ResultSet queryResult = preparedStatement.executeQuery();

[dariyoosh za]

Gregg Bolinger wrote:Well, I don't know if this is the reason or not but you are supplying the query twice.

PreparedStatement prepared_statement = connection.preparedStatement(query_text);
ResultSet query_result = prepared_statement.executeQuery(query_text);

Try just doing

ResultSet query_result = prepared_statement.executeQuery();

Also, on a side note, in java we use camel casing for variable names, not underscores. Your code would follow better conventions if it looked like

ResultSet queryResult = preparedStatement.executeQuery();

Hello there,

Thank you very much for your help that solved my problem !!. In fact I had made two mistakes:

First error, I had used '?' also for column names, which is not accepted as the SQL query has to be precompiled, therefore all the columns must be known before the compilation.

Second error (which you pointed out): Instead of writing

ResultSet query_result =  prepared_statement.executeQuery(query_text);

I had to write:

ResultSet query_result =  prepared_statement.executeQuery();

I tested with the following

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();

And it works pretty well now!


Thank you very much for your help and for the time you spent for my problem

Kind Regards,