Problem using CHAR column data type
701437Mar 16 2010 — edited Mar 16 2010Hi,
Using JDeveloper version 11.1.1.2.0, with Oracle Database 11g.
Our DBA recently advised us to change DB columns with fixed length text from VARCHAR2 to CHAR in order to improve the database's performance. After this change we also had to update all the related entities and view objects already created, and it's in this point where we have found some problems.
In most of the updated business components there wasn't any problem at all: the entities' "Synchronize with database" option worked fine and both database and Java types switched to Char.
However, in some cases the Java type remained to String. If we leave them like this, the basic rows and data operations are correctly executed, but if we try to change the Java type to Char through the attribute's "Change type" option, we get the following error when commiting a new row:
oracle.jbo.AttributeLoadException: JBO-27021: Failed to load custom data type value at index 5 with java object of type oracle.jbo.domain.Char due to java.lang.NullPointerException.
.....
Caused by: java.lang.NullPointerException
at oracle.jbo.domain.Char$1facClass.createDatum(Char.java:136)
at oracle.jbo.server.OracleSQLBuilderImpl.doLoadFromStatement(OracleSQLBuilderImpl.java:1462)
... 43 more
We have checked several times all the attribute types in the entity and view object, especially the reported index, but it keeps showing the error even after executing the "Clear all" command" and restarting JDeveloper. We have found that this Char attribute belongs to a Foreign Key, and that if twe try to inform a not valid value, the error changes completely and we get the typical Foreign Key error
(oracle.jbo.DMLConstraintException) JBO-26048: Constraint "SAM_DONOR_TEST_STATUS_SAM_FK3" is violated during post operation "Insert" using SQL statement
Moreover, if we change the Char attributes back to String (keeping CHAR as database type), the previous error disappears and new rows can be commited.
Despite of that, we can't leave CHAR attributes as String because we have a small problem with them. If this attribute has a list of values renedered as a combobox, and it is used in a View Criteria search form with mandatory fields, the search form validation throws an exception until the Java type of this attribute is changed to Char.
oracle.jbo.AttrValException: JBO-27036: At least one of the following attributes Maildoc #, Maildoc Status, QaReviewDate, VerificationDate, CreationDate, SendingDate is required.
And then we have another error that happens just getting the database rows without minding the Java type (String or Char):
VO_RO_LabCodeForSampleImpl voLfS = getVO_RO_LabCodeForSample1();
voLfS.clearCache();
voLfS.setUnitControlNumberParam(unitControlNumberParam);
voLfS.setTestGroupCodeParam(testGroupCodeParam);
voLfS.executeQuery();
if (voLfS.getEstimatedRowCount() == 0) {
return SAM_SMI4_E03_EXCEPTION;
}
Row r = voLfS.first();
Debugging it, the voLfS.getEstimatedRowCount() method returns 1, but when executing voLfS.first(), we get the following error:
oracle.jbo.AttributeLoadException: JBO-27021: Error loading the value of custom data type at index 4 with Java object of type oracle.jbo.domain.Char due to java.sql.SQLException.
.....
at com.grifols.gds.webapp.samples.service.AM_TestResultsImpl.processReceivedTestResult(AM_TestResultsImpl.java:405)
at com.grifols.gds.webapp.samples.service.AM_TestResultsImpl.receiveTestResults(AM_TestResultsImpl.java:486)
at com.grifols.gds.webapp.samples.service.AM_TestResultsImplTest.testReceiveTestResults(AM_TestResultsImplTest.java:82)
.....
Caused by: java.sql.SQLException: Index of column not valid
The trace line at com.grifols.gds.webapp.samples.service.AM_TestResultsImpl.processReceivedTestResult(AM_TestResultsImpl.java:405) is the Row r = voLfS.first(); row. If we run the Application Module, open this View Object and inform the same values to the bind variables, it simply doesn't return any row; and if we execute the ViewObject's select directly in database informing the same values, we get one row.
We have spent lots of hours checking every attribute, entity and view object, but we can't find anything that explains these errors (i.e. different uses of CHAR columns). So, we need to find the appropriate way of managing CHAR columns in ADF.
We have the complete stack traces for all the Exceptions, if you think they could be helpfully, I can post them.
Any help will be appreciated.