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
SQL statement ORA lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 94
- Joined: Wed May 08, 2002 8:44 am
- Location: Germany
- Contact:
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Try replacing ':1' with the ORCHESTRATE.firstcolname reference.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Participant
- Posts: 94
- Joined: Wed May 08, 2002 8:44 am
- Location: Germany
- Contact:
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
"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