8 Replies Latest reply on May 3, 2012 1:54 PM by 849087

    Missing Defines Error in Simple Java Stored Procedure

      Anyone have any suggestions on what might be causing the unusual behavior described below? Could it be a 10g java configuration issue? I am really stuck so I'm open to just about anything. Thanks in advance.

      I am writing a java stored procedure and am getting some SQLException's when executing some basic JDBC code from within the database. I reproduced the problem by writing a very simple java stored procedure which I have included below. The code executes just fine when executed outside of the database (10g). Here is the output from that execution:

      java.class.path=C:\Program Files\jEdit42\jedit.jar
      java.vendor=Sun Microsystems Inc.
      os.name=Windows XP
      In getConnection
      Executing outside of the DB
      Driver Name = Oracle JDBC driver
      Driver Version =
      column count=1
      column name=TEST
      column type=1

      When I execute it on the database by calling the stored procedure I get:

      java.vendor=Oracle Corporation
      In getConnection
      We are executing inside the database
      Driver Name = Oracle JDBC driver
      Driver Version =
      column count=1
      column name='TEST'
      column type=1
      MEssage: Missing defines
      Error Code: 17021
      SQL State: null
      java.sql.SQLException: Missing defines
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
      at oracle.jdbc.driver.OracleResultSetImpl.getString(Native Method)
      at OracleJSPTest.test(OracleJSPTest:70)

      Here is the Java code:

      // JDBC classes
      import java.sql.*;
      import java.util.*;

      //Oracle Extensions to JDBC
      import oracle.jdbc.*;
      import oracle.jdbc.driver.OracleDriver;

      public class OracleJSPTest {

      private static void printProperties(){

      private static Connection getConnection() throws SQLException {
           System.out.println("In getConnection");      
      Connection connection = null;
      // Get a Default Database Connection using Server Side JDBC Driver.
      // Note : This class will be loaded on the Database Server and hence use a
      // Server Side JDBC Driver to get default Connection to Database
      if(System.getProperty("oracle.jserver.version") != null){
                System.out.println("We are executing inside the database");
                //connection = DriverManager.getConnection("jdbc:default:connection:");                    
                connection = new OracleDriver().defaultConnection();
           System.out.println("Executing outside of the DB");
           DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
           connection = DriverManager.getConnection("jdbc:oracle:thin:@XXX.XXX.XXX.XX:XXXX:XXXX","username","password");
      DatabaseMetaData dbmeta = connection.getMetaData();
      System.out.println("Driver Name = "+ dbmeta.getDriverName());
      System.out.println("Driver Version = "+ dbmeta.getDriverVersion());
      return connection;

      public static void main(String args[]){     

      public static void test() {   
      Connection connection = null; // Database connection object

      try {
           connection = getConnection();
           String sql = "select 'TEST' from dual";
           Statement stmt = connection.createStatement();
      ResultSet rs = stmt.executeQuery(sql);     
           ResultSetMetaData meta = rs.getMetaData();     
           System.out.println("column count="+meta.getColumnCount());
           System.out.println("column name="+meta.getColumnName(1));
           System.out.println("column type="+meta.getColumnType(1));

      } catch (SQLException ex) { // Trap SQL Errors
           System.out.println("MEssage: " + ex.getMessage());
           System.out.println("Error Code: " + ex.getErrorCode());
           System.out.println("SQL State: " + ex.getSQLState());
      } finally {
      if (connection != null || !connection.isClosed())
      connection.close(); // Close the database connection
      } catch(SQLException ex){



      Message was edited by:
        • 1. Re: Missing Defines Error in Simple Java Stored Procedure
          Avi Abrami
          Sometimes the error message can be misleading. The only thing I can suggest (and I'm only guessing) is that you shouldn't try to "close()" the [database] "Connection" when using the default connection.

          In other words, if "We are executing inside the database", don't invoke the "close()" method of class "java.sql.Connection".

          Good Luck,
          • 2. Re: Missing Defines Error in Simple Java Stored Procedure
            Thanks for the suggestion but the exception is being thrown from the line:


            So the close() statement has not yet been executed and it is not throwing any exception.

            Here is some additional information on this problem. If I use a PreparedStatement with a query like "SELECT col1 FROM table1 where coln=?" and try to call setXXX(1, value) on the prepared statement, I get a SQLException: Invalid Column Index.
            • 3. Re: Missing Defines Error in Simple Java Stored Procedure
              Avi Abrami
              Works for me on Oracle running on Red Hat Enterprise Linux AS release 3 (Taroon).

              Java code:
              import java.sql.*;
               * Oracle Java Virtual Machine (OJVM) test class.
              public class OjvmTest {
                public static void test() throws SQLException {
                  Connection conn = DriverManager.getConnection("jdbc:default:connection:");
                  PreparedStatement ps = null;
                  ResultSet rs = null;
                  try {
                    ps = conn.prepareStatement("select 'TEST' from SYS.DUAL");
                    rs = ps.executeQuery();
                    if (rs.next()) {
                  finally {
                    if (rs != null) {
                      try {
                      catch (SQLException sqlEx) {
                        System.err.println("Error ignored. Failed to close result set.");
                    if (ps != null) {
                      try {
                      catch (SQLException sqlEx) {
                        System.err.println("Error ignored. Failed to close statement.");
              And my PL/SQL wrapper:
              create or replace procedure P_J_TEST as language java
              name 'OjvmTest.test()';
              And here is how I execute it in a SQL*Plus session:
              set serveroutput on
              exec dbms_java.set_output(2000)
              exec p_j_test
              Good Luck,
              • 4. Re: Missing Defines Error in Simple Java Stored Procedure
                Hi Jason!
                Done some experimenting with Java Stored Procedures, and figured out that if you close the connection the whole process/thread you are running in is terminated. So you should remove the close statement from your finally block, even if that is not the sollution to your originating problem.
                • 5. Re: Missing Defines Error in Simple Java Stored Procedure
                  Did you find a solution to the ora-17021 Missing Defines error?

                  I am getting the same error when running our app. We just upgraded to 10g. This error usually appears after letting the app idle for 15 min or more and then attempting to access the database.

                  • 6. Re: Missing Defines Error in Simple Java Stored Procedure
                    Ciao Jason

                    Did you have any luck solving this problem?

                    My JSP has the same behaviour, after updating from to

                    • 7. Re: Missing Defines Error in Simple Java Stored Procedure
                      I am having the same problem and no solution so far.

                      As mentioned, the problem is the indexing of result set columns or prepared statement parameters. Whe I use numeric indexes, like so:

                      String s = resultSet.getString(1);

                      I get an "invalid column index" message.
                      However, if I try the same using the column name:

                      String s = resultSet.getString("COLUMN_NAME");

                      I get the ORA-17021 "Missing Defines" error...
                      I am sure that the result set is present and has rows...
                      • 8. Re: Missing Defines Error in Simple Java Stored Procedure
                        The answer that can solve "Missing Defines" is here. Re: migrating application from oc4j into weblogic 10.3

                        I had the same problem, and I tried to solve using In the pool connection of WebLogic set property "Statement Cache Size" = 0.

                        I'm testing this and I hope this help us.