SQL statement ORA lookup
Posted: Fri Jan 23, 2015 5:26 am
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
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