2 Replies Latest reply: Mar 28, 2006 12:25 PM by user447485 RSS

    java.sql.SQLException: ORA-00911: invalid character

    user447485
      I am trying to extract results from multiple resultsets as per the following article:

      http://www.javaworld.com/javaworld/jw-02-2000/jw-02-ssj-jdbc2.html

      My code runs fine and get the results if I connect to MS SQL SERVER2000. I am getting the following error, If I connect to Oracle 9i server:

      Exception in thread "main" java.sql.SQLException: ORA-00911: invalid character

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:
      830)
      at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav
      a:2391)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
      nt.java:2672)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
      edStatement.java:589)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
      ement.java:656)
      at MultipleResultsets.processStatement(MultipleResultsets.java:28)
      at SimpleSQLMultipleResultsets.main(SimpleSQLMultipleResultsets.java:14)

      The code I have in my main() method is as follows:

      public static void main(String[] args) throws Exception
      {
      Connection conn = setupConnection();

      // Create a SQL query returning multiple ResultSets
      String sql = "select * from tblusers; select username from tblusers";

      PreparedStatement stmnt = conn.prepareStatement(sql);

      processStatement(stmnt);

      // Close all database resources
      stmnt.close();
      conn.close();
      }

      SQLServer 2000 is able to read the String sql and is able to treat it as two select statements separated by ';' and executing both the statements and return the results. But, Oracle server is throwing an error message saying invalid character. How can I separate multiple sql statements?

      My stored procedure on SQLServer 2000 is as follows:

      CREATE PROCEDURE reverseSelectionProcedure AS
      BEGIN
      select username from tblusers;
      select * from tblusers;
      END
      GO

      The code I have in my main() method using the above stored procedure is as follows:

      public static void main(String[] args) throws Exception
      {
      Connection conn = setupConnection();

      // Create a SQL query calling a stored procedure returning multiple ResultSets
      String sql = "{call reverseSelectionProcedure()}";

      CallableStatement stmnt = conn.prepareCall(sql);

      processStatement(stmnt);

      // Close all database resources
      stmnt.close();
      conn.close();
      }

      If somebody help me create a stored procedure to have those simple selects on Oracle, I can try testing the second method. I don't know to create stored procedure on Oracle. I think I have to create package and other objects to create a stored procedure.

      Any help is greatly appreciated.
      Thanks.
      -NN