JDBC Interview Questions - FAQs

JDBC - Frequently Asked Questions or Interview Questions(FAQs)

41. Why does executing the PreparedStatement class cause a "TRUNC fails: ORA-00932: inconsistent datatypes" error?

According to Oracle Metalink Bug Database Doc ID: 144784.1, in the absence of explicit data typecasting, OCI assumes that a bind variable is a CHAR data type. If the SQL statement intends to use the bind variable as a DATE data type, but OCI thought it was a CHAR, the SQL parser will have a conflict in data types. The fix is to explicitly use data conversion functions to convert the bind variables in the problem queries. For example, a select string of

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(?)"; 

should be changed to:

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(TO_DATE(?))";

42. How do I create and update Oracle Blob fields?

The following code sample shows how to create and update Oracle Blob fields.

public void insert() throws SQLException {
  try {

    // Connect to the database using WebLogic JDBC connection pool
    Hashtable ht = new Hashtable();
    ht.put(Context.INITIAL_CONTEXT_FACTORY,
      "weblogic.jndi.WLInitialContextFactory");
    ht.put(Context.PROVIDER_URL, "t3://localhost:7001");
    InitialContext ctx = new InitialContext(ht);
    javax.sql.DataSource ds = (javax.sql.DataSource)
      ctx.lookup("java:comp/env/jdbc/DSName");
    Connection conn = ds.getConnection();

    // This is necessary in any case where you are doing
    // a select for update. Not doing this will result in a ORA-1002
    conn.setAutoCommit (false);

    BLOB blob = null;

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Drop the table if it exists
    try {
      stmt.execute ("drop table ImageTable");
      System.out.println("Table droped ...");
    }
    catch (SQLException e) {
      System.out.println("Table does not exist");
    }

    // Create the table
    stmt.execute ("create table ImageTable (column1 varchar2(20),
      image BLOB)");
    System.out.println("Table created ...");

    // create a blob entry in the table
    stmt.execute("insert into ImageTable values ('one', empty_blob())");
    stmt.execute("commit");
    System.out.println("inserted empty blob");
    String cmd = "select * from ImageTable for update";
    ResultSet rset = stmt.executeQuery(cmd);
    if (rset.next())  {
          blob = ((OracleResultSet)rset).getBLOB(2);
          System.out.println("got blob reference");
    }

    else System.out.println("no row to get!!!!");

    rset.close();

    blob = readFromFile();

    cmd = "update ImageTable set image = ? where column1 = 'one'";
    PreparedStatement pstmt = conn.prepareStatement(cmd);
    pstmt.setBlob(1, blob);
    pstmt.execute();
    stmt.execute("commit");
    System.out.println("blob updated");

    blob = null;

    cmd = "select * from ImageTable for update";
    rset = stmt.executeQuery(cmd);
    if (rset.next())  {
      System.out.println("get blob");
      blob = ((OracleResultSet)rset).getBLOB(2);
      // do something with blob

    }

    else
      System.out.println("no row to get (2)!!!!");

  }

  catch (SQLException sqle) {
    System.out.println("SQL Exception occured: " + sqle.getMessage());
    sqle.printStackTrace();

  }

  catch(FileNotFoundException e) {
    System.out.println("File Not Found");

  }

  catch (IOException ioe) {
    System.out.println("IO Exception" + ioe.getMessage());

  }

  catch (Exception ioe) {
    System.out.println("Exception" + ioe.getMessage());

  }

}

43. How do I enlist an Oracle XAResource?

This code will only work on the server side. It cannot be run in a client. Also note that enlistment is generally done transparently for JDBC resources that implement XAResource.

// Here is the XAResource for oracle
String URL = "jdbc:oracle:thin:@DbmsHost:DbmsPort:DbmsName";
DriverManager.registerDriver(new OracleDriver());

// Create XA Connection
OracleXADataSource oxds1 = new OracleXADataSource();
oxds1.setURL(URL);
oxds1.setUser("scott");
oxds1.setPassword("tiger");
javax.sql.XAConnection pc1  = oxds1.getXAConnection();
m_oracleResource = pc1.getXAResource ();

m_oracleConnection = pc1.getConnection();

// Here is the source code for getting the TM.
Context ctx = null;
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
// Parameters for the WebLogic Server.
// Substitute the correct hostname, port number
// user name, and password for your environment:
env.put(Context.PROVIDER_URL, "t3://localhost:7001");
env.put(Context.SECURITY_PRINCIPAL, "system");
env.put(Context.SECURITY_CREDENTIALS, "managers");
ctx = new InitialContext(env);
m_tManager =
(TransactionManager)ctx.lookup("javax.transaction.TransactionManager");

//Here is the source code for registering the TM.
m_tManager.begin();
m_transaction = m_tManager.getTransaction();
weblogic.transaction.TransactionManager weblogicTManager =
((weblogic.transaction.TransactionManager) m_tManager);
weblogicTManager.registerStaticResource("oracle",m_oracleResource);

// enlist the resources with the transaction
m_transaction.enlistResource(m_oracleResource);

44. Why do I get "ORA-00600" ?

This error generally means that version of Oracle server is newer than version of the driver you are using. In case you are using the Oracle thin driver, you will need to download the latest ojdbc14.jar from Oracle and put it at the beginning of your CLASSPATH (and possibly update any scripts that start the server, such as startweblogic.cmd, since they override the CLASSPATH).

45. Why do I get "ORA-24327" ?

This error generally means that the environment ORACLE_HOME is not set or is set incorrectly or the D_LIBRARY_PATH or PATH does not include the right dynamic link libraries. It can also indicate a mismatch when trying to use weblogic.jdbc.oci.Driver with an earlier or later version of the Oracle client software than is supported. In that case, try to use the Oracle Thin driver instead.

46. Why do I get "java.sql.SQLException: getOBJECT is not supported by the WebLogic JDBC Driver"?

When using the WebLogic JDBC connection pool and weblogic.jdbc.vendor.oracle.OracleResultSet, the error is returned (where OBJECT is the name of some Oracle object). It implies that this feature is not supported by WebLogic Server JDBC because the object type is not serializable. There are two alternatives.

• You can switch to using the Oracle thin driver directly. That means that you will get a connection directly to the database using the Thin driver instead of getting the connection from a pool of JDBC connections. That means that you lose all advantages of using the WebLogic Server JDBC subsystem, such as transactions, connection pooling, and caching of prepared statements.
• BEA recommends moving your processing to a stored procedure.
• 
  

47. Why do I get an "ORA-01453" when I use SET TRANSACTION?

When using Oracle, the message "java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction" may be logged. This is due to a limitation in the Oracle interfaces, starting in Oracle 8.1.7. WebLogic tries to minimize the problem by not calling SET TRANSACTION if the mode already matches the current state.

Source : www.javabeat.net