SQL statement ORA lookup

[Klaus Schaefer]

Hi folks,

I am in the process of redesigning server jobs to parallel. I have a lookup to an ORA-DB in the old server job that has is user defined sql:

"SELECT :1 TEXT, REGEXP_SUBSTR(REGEXP_REPLACE(UPPER(:1), '[^a-zA-Z0-9]', '', 1, 0), '[BK][0-9]{1,8}') TRANSAKTIONSCODE FROM dual"

and runs fine.

Now, using that same statement in an ORA-Connector stage in PX aborts with the following error messages:

"LKP_ORA_dual,0: The following SQL statement failed: SELECT :1 TEXT, REGEXP_SUBSTR(REGEXP_REPLACE(UPPER(:1), '[^a-zA-Z0-9]', '', 1, 0), '[BK][0-9]{1,8}') TRANSAKTIONSCODE FROM dual.

LKP_ORA_dual,0: The OCI function OCIStmtExecute returned status -1. Error code: 1.008, Error message: ORA-01008: not all variables bound. (CC_OraStatement::executeSelect, file CC_OraStatement.cpp, line 2.893)"

In both cases the attributes TEXT and TRANSAKTIONSCODE are defined identical.
Any idea on how to get this statement being executed with PX?

Thanks a lot in advance...
Klaus

[jerome_rajan]

Try replacing ':1' with the ORCHESTRATE.firstcolname reference.

[Klaus Schaefer]

The solution is: It has to be a "sparse" lookup in PX. And the lookup SQL then looks e.g. like this:

"SELECT ORCHESTRATE.WOTEXT10 TEXT, REGEXP_SUBSTR(REGEXP_REPLACE(UPPER(ORCHESTRATE.WOTEXT10), '[^a-zA-Z0-9]', '', 1, 0), '[BK][0-9]{1,8}') TRANSAKTIONSCODE FROM dual"

ORCHESTRATE.WOTEXT10 being the reference to the field of the input link.

Regards
Klaus