This discussion is archived
8 Replies Latest reply: May 3, 2012 6:54 AM by 849087 RSS

Missing Defines Error in Simple Java Stored Procedure

434510 Newbie
Currently Being Moderated
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.class.version=48.0
java.home=C:\j2sdk1.4.2_04\jre
java.vendor=Sun Microsystems Inc.
java.version=1.4.2_04
os.arch=x86
os.name=Windows XP
os.version=5.1
In getConnection
Executing outside of the DB
Driver Name = Oracle JDBC driver
Driver Version = 10.1.0.2.0
column count=1
column name=TEST
column type=1
TEST

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

java.class.path=
java.class.version=46.0
java.home=/space/oracle/javavm/
java.vendor=Oracle Corporation
java.version=1.4.1
os.arch=sparc
os.name=Solaris
os.version=5.8
In getConnection
We are executing inside the database
Driver Name = Oracle JDBC driver
Driver Version = 10.1.0.2.0
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(){
     System.out.println("java.class.path="+System.getProperty("java.class.path"));
     System.out.println("java.class.version="+System.getProperty("java.class.version"));
     System.out.println("java.home="+System.getProperty("java.home"));
     System.out.println("java.vendor="+System.getProperty("java.vendor"));
     System.out.println("java.version="+System.getProperty("java.version"));
     System.out.println("os.arch="+System.getProperty("os.arch"));
     System.out.println("os.name="+System.getProperty("os.name"));
     System.out.println("os.version="+System.getProperty("os.version"));
}

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();
}else{
     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[]){     
     test();     
}


public static void test() {   
     printProperties();
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));
     
     if(rs.next()){
          System.out.println(rs.getString(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());
     ex.printStackTrace();
} finally {
try{
if (connection != null || !connection.isClosed())
connection.close(); // Close the database connection
} catch(SQLException ex){

ex.printStackTrace();
}
}
}


}

Message was edited by:
jason_mac
  • 1. Re: Missing Defines Error in Simple Java Stored Procedure
    Avi Abrami Oracle ACE
    Currently Being Moderated
    Jason,
    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,
    Avi.
  • 2. Re: Missing Defines Error in Simple Java Stored Procedure
    434510 Newbie
    Currently Being Moderated
    Thanks for the suggestion but the exception is being thrown from the line:

    System.out.println(rs.getString(1));

    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 Oracle ACE
    Currently Being Moderated
    Jason,
    Works for me on Oracle 10.1.0.3 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()) {
            System.out.println(rs.getString(1));
          }
        }
        finally {
          if (rs != null) {
            try {
              rs.close();
            }
            catch (SQLException sqlEx) {
              System.err.println("Error ignored. Failed to close result set.");
            }
          }
          if (ps != null) {
            try {
              ps.close();
            }
            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,
    Avi.
  • 4. Re: Missing Defines Error in Simple Java Stored Procedure
    452175 Newbie
    Currently Being Moderated
    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
    482423 Newbie
    Currently Being Moderated
    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.

    Thanks!
  • 6. Re: Missing Defines Error in Simple Java Stored Procedure
    321109 Newbie
    Currently Being Moderated
    Ciao Jason

    Did you have any luck solving this problem?

    My JSP has the same behaviour, after updating from 10.1.0.2 to 10.1.0.3.

    thanks
    Roberto
  • 7. Re: Missing Defines Error in Simple Java Stored Procedure
    540850 Newbie
    Currently Being Moderated
    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
    849087 Newbie
    Currently Being Moderated
    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.