You cannot execute a query using the % field.(SBL-DAT-00401)

Found a really annoying oracle limitation today. One requirement involved querying in a BC Field which had a column of type long behind it. I didn’t think twice and implemented this with a little bit of scripting. The script compile and ran fine on my local machine. But once I checked in and got it compiled, it got this error

 

You cannot execute a query using the %1 field.(SBL-DAT-00401)

 

And I found this from supportweb:

 

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=539572.1&h=Y

 

The Oracle RDBMS does not support queries on LONG columns - Siebel Technical Support testing just confirmed that this restriction that is documented for Oracle 8 already still exists in Oracle 10.

http://download-east.oracle.com/docs/cd/A58617_01/server.804/a58241/ch5.htm

Restrictions on LONG and LONG RAW Data
Although LONG (and LONG RAW; see below) columns have many uses, their use has some restrictions:
- Only one LONG column is allowed per table.
- LONG columns cannot be indexed.
- LONG columns cannot appear in integrity constraints.
- LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements. ...

 

But I had a perfect working code on my local db. How could that be ? The next line in supportweb explained this.

 

The SQLAnywhere Database Engine that is used for the Siebel local database does not have this restrition, so the query is working there.

 

Great !! Now I have to find another way out. Any suggestions ?