IBM Support

DataStage OCI stage getting error ORA-01036: illegal variable name/number

Technote (troubleshooting)


Problem(Abstract)

When the OCI stage is being used as a lookup with user-defined sql - getting error ORA-01036: illegal variable name/number

Cause

Mainly affects customers upgrading from a previous version of DataStage 7. With 7.5.2 and 7.5.3 and Information Server version 8 - the user-defined sql now has to have a where clause.

Resolving the problem

Add where clause to user-defined sql.

Before: SELECT 1 as ID, IDS.POPID.NEXTVAL as POP_ID from Dual
After: SELECT 1 as ID, IDS.POPID.NEXTVAL as POP_ID from Dual where 1=:1


Document information

More support for: InfoSphere DataStage

Software version: 7.5, 8.0, 8.1, 8.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1430645

Modified date: 04 July 2011


Translate this page: