JDBC Interview Questions - FAQs

JDBC - Frequently Asked Questions or Interview Questions(FAQs)

31. What causes an OCIW32.dll error?

You may see the following error message when using your JDBC driver for Oracle: "The ordinal 40 could not be loaded in the dynamic link library OCIW32.dll." This problem is caused by an out-of-date version of OCIW32.DLL in your system directory. Some programs install this file in the system directory in order to run. If you remove this file from the system directory you should no longer receive this error.

32. What type of object is returned by ResultSet.getObject() when using the WebLogic jDriver for Oracle?

WebLogic jDriver for Oracle always returns a Java object that preserves the precision of the data retrieved. It returns the following from the getObject() method:

• For columns of types NUMBER(n) and NUMBER(m,n): a Double is returned if the defined precision of the column can be represented by a Double; otherwise BigDecimal is returned.
• For columns of type NUMBER: Because there is no explicit precision, the Java type to return is determined based on the actual value in each row, and this may vary from row to row. An Integer is returned if the value has a zero-valued fractional component and the value can be represented by an integer.

For example, 1.0000 will be an integer. A long is returned for a value such as 123456789123.00000. If a value has a non-zero fractional component, a Double is returned if the precision of the value can be represented by a Double; otherwise a BigDecimal is returned.

33. How do I call Oracle stored procedures that take no parameters?

Try this:

  CallableStatement cstmt = conn.prepareCall("Begin procName;
      END;");
  cstmt.execute();

where procName is the name of an Oracle stored procedure. This is standard Oracle SQL syntax that works with any Oracle DBMS. You can also use the following syntax:

 CallableStatement cstmt = conn.prepareCall("{call procName};");
  cstmt.execute();

This code, which conforms to the Java Extended SQL specification, will work with any DBMS, not just Oracle.

34. How do I bind string values in a PreparedStatement?

Suppose you are trying to get the PreparedStatement class to bind Strings in a statement. The setString() method doesn't seem to work. Here is how you have set up the PreparedStatement:

  String pstmt = "select n_name from n_table where n_name LIKE '?%'";
  PreparedStatement ps = conn.prepareStatement(pstmt);
  ps.setString(1, "SMIT");
  ResultSet rs = ps.executeQuery();

The preceding code does not work because the complete value needs to be specified in a String (without using embedded quotes) and then bound to an unquoted question mark (?). Here is the corrected code:

  String matchvalue = "smit%";
  String pstmt = "select n_name from n_table where n_name LIKE ?";
  PreparedStatement ps = conn.prepareStatement(pstmt);

  ps.setString(1, matchvalue);
  ResultSet rs = ps.executeQuery();

35. Why do I get unexpected characters from 8-bit character sets in WebLogic jDriver for Oracle?

If you are using an Oracle database with an 8-bit character set on Solaris, make sure you set NLS_LANG to the proper value on the client. If NLS_LANG is not set, it defaults to a 7-bit ASCII character set, and tries to map characters greater than ASCII 128 to a reasonable approximation (for example, á, à, â would all map to a). Other characters are mapped to a question mark (?).

36. How do I learn what codesets are available in Oracle?

To find out what codesets you currently have available in Oracle, execute the following SQL query from SQLPlus at the command line:

 SQL> SELECT value FROM v$nls_valid_values
          WHERE parameter='CHARACTERSET';

The response lists all codesets currently installed on your system. This listing will look something like the following shortened list:

  VALUE
  -----------------------------------------------------------
  US7ASCII
  WE8DEC
  WE8HP
  US8PC437
  WE8EBCDIC37
  WE8EBCDIC500
  WE8EBCDIC285
  ...

If you want to constrain the value in the query to a specific codeset you are searching for, you can use a SQL query like the following:

 SQL> SELECT value FROM v$nls_valid_values 
          WHERE parameter='CHARACTERSET' and VALUE='AL24UTFFSS';

This would produce the following response if the codeset is installed:

  VALUE
  -------------------------------------------------------------
  AL24UTFFSS

You can use Oracle's installation tools to install additional codesets. Contact Oracle for more information.

37. How do I look up an "ORA" SQLException?

If your WebLogic jDriver for Oracle application produces an SQLException, you can look up the Oracle error by using the oerr command. For example, if you see the following SQLException:

  java.sql.SQLException: ORA-12536: TNS: operation would block

You can find the description of error ORA-12536 can be found with the command:

  > oerr ora 12536

38. What is error "ORA-6502?"

The default length of a string bound to an OUTPUT parameter of a CallableStatement is 128 characters. If the value you assign to the bound parameter exceeds that length, you will get this error.

You can adjust the length of the value of the bound parameter by passing an explicit length with the scale argument to the CallableStatement.registerOutputParameter() method.

39. Why do I get an error while trying to retrieve the text for ORA-12705?

This error occurs when you have not set the ORACLE_HOME environment variable properly. In order to use WebLogic jDriver for Oracle, the Oracle client software needs to be installed and ORACLE_HOME must be set.

You may also see this error message if you try to use WebLogic jDriver for Oracle's internationalization capabilities with a language/codeset combination that is not installed on your system. If you get the ORA-12705 error with the correct error text, then either you have set NLS_LANG improperly, or you do not have the right codesets installed on your system.

40. Why do I run out of resources during updates with Oracle's database link?

When you use Oracle's database link to update your database, you may get error "maximum number of temporary table locks exceeded" even if you close your result sets and statements when you finish.

The database link is an object in the local database that allows you to access tables, views, and so forth in a remote database. The database link is controlled by the Oracle server, so the driver has no control over its use of resources. The link appears to perform the commit (since other processes could see the records that were being created), but it doesn't free any resources until the connection is closed. The solution is to remove the database link and use the JDBC driver to do your selects, inserts, and updates.

Source : www.javabeat.net