Symptom

Using Oracle database version 10.2.0.3 with JDBC driver 10.2.0.3, you encounter exceptions and SQL problems for database select/update/insert operations on the PegaRULES Database in various scenarios, particularly during rule resolution:

ORA-01460: unimplemented or unreasonable conversion requested

ORA-01461 can bind a LONG value only for insert into a LONG column

Details

Here’s a partial sample of what an ORA-01460 error might look like:

[2/3/10 11:15:10:466 EST] 0000001d SystemOut O 11:15:10,465 [ WebContainer : 1] ( engine.database.DatabaseImpl) ERROR server_name|server_IP_address - There was a problem opening a database instance (class: Rule-Declare-Expressions): Database-General Problem getting candidates for Rule Resolution 1460 72000 ORA-01460: unimplemented or unreasonable conversion requested

com.pega.pegarules.pub.database.DatabaseException: Database-General Problem getting candidates for Rule Resolution 1460 72000 ORA-01460: unimplemented or unreasonable conversion requested

From: (HAB97D1C456EAE3053CA9BCCC7CF08695: server_IP_address)

SQL: select pzInsKey,pyRuleStarts,pyRuleSet,pyRuleSetVersion,pyClassName,pyRuleAvailable,pyRuleEnds, pyCircumstanceProp,pyCircumstanceVal,pyCircumstanceDateProp,pyCircumstanceDate,pyBaseRule, pyCircumstanceType,pySortDateCircumWithinRSMajor from pr4_rule where pxObjClass = ? and pxInsId = ?

SQL Inserts:
<Rule-Declare-Expressions><!.PYRECENTRULESLIMIT!.PYPREFERENCES.PYNAVIGATIONPREFS>
Caused by SQL Problems. Problem #1, SQLState 72000, Error code 1460: java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

Analysis

The Oracle Technology Network (OTN) Discussion Forum shows several posts regarding known issues with Oracle database and JDBC drivers 10.2.0.3, including this useful post (September 2, 2008):

Bug 6085625 ORA-01483 INVALID LENGTH FOR DATE OR NUMBER BIND VARIABLE
This bug was introduced in RDBMS 10.2.0.3 (Environment: all).
Although the bug is filed for the error:
* ORA-01483 invalid length for DATE or NUMBER bind variable
duplicate bugs report other errors, three of which are:
* ORA-01460: unimplemented or unreasonable conversion requested
* ORA-01461 can bind a LONG value only for insert into a LONG column
* ORA-01722 invalid number Solution: either apply patch or upgrade to 10.2.0.4.
Related resources:
* Bug No. 6085625 - ORA-01483: INVALID LENGTH FOR DATE OR NUMBER BIND VARIABLE * Bug No. 2998709 - ORA-01461: CAN BIND A LONG VALUE ONLY FOR INSERT INTO A LONG COLUMN
* Bug No. 1400539 - GETTING ORA-1461 WHEN INSERTING INTO A VARCHAR FIELD
* Note: 465497.1 - After Upgrading Database and JDBC from 10.2.0.2 to 10.2.0.3 Intermittent ORA-01483 Errors Occur

Solution

Upgrade both your JDBC Driver and Oracle Database to Version 10.2.0.4.