[edited - to add single quote escape method]
Please note that you hare asking about what is strictly a REPLICATE question, in the ENTERPRISE MANAGER FORUM. That forum is frequented by some Replicate users, but not many I suspect. Please consider jumping over to the appropriate REPLICATE forum where you will get a (10x? ) larger audience.
Let's first all realize that the (source|target)_lookup functions are designed for simple singleton queries which Replicate constructs on the fly from column, table, conditional parts. What is we are doing here is essentially SQL injection hijacking the return value (column) expression to stuff it with the entire query and making the Replicate provide remainder a mere comment. (TTL,'SCHM','TBL','EXP','COND'). This is similar to the provided example.
- The referenced example appears to be SQL Server where arguments substitution points are indicated with a question mark(s). The need here is for Oracle syntax which as per Replicate Userguide requires a colon followed by a number. That number appears to be missing.
- The desired query seems to want to include a piece of string "Client actif" as literal comparison test value. Fine. However, as per error message line that text come over as a double-quoted string which in Oracle SQL is interpreted as a column name is it not?
- To provide a fixed literal you somehow have add a single quotes to the whole single quoted expression . That does not appear to be documented, best i checked. I tested using a double-single quote and the transformation:
source_lookup('NO_CACHING','x','y',' ''quote test'' from dual --', 'z')
Worked for me. You try! ... look in the error message for result (if it fails) and report back please. To 'see' the result I had to made it fail deliberately providing a nonexistent table xxx and my deliberate error was: "\ Failed to get data lookup statement for table x.y \ Error in executing data lookup statement 'SELECT 'quote test' from XXX -- FROM "x"."y" WHERE z' \ ORA-00942: table or view does not exist "
- You could also try to provide that 'Client actif' as a (fixed) condition-parameter argument string and provide it with :1 or :2 in the expression as a first or second parameter.
- Probably not this time, but sometimes, you need build the expression by 'gluing' morsels together.
for example instead of:
'... AD.ROW_ID=:1 --', 'z', $ROW_ID)
you could try:
'... AD.ROW_ID= ''' || $ROW_ID || ''' --', 'z')
or with numeric ROW_ID:
'... AD.ROW_ID= ' || $ROW_ID || ' --', 'z')
>> Would it be possible to use this sql for both CDC and Full Load tasks ?
Not until you fix it. :-). Then it will work for full-load and cdc as all transformations do always unless they have additional magic to distinguish between the two (like looking at AR_H_TRANSACTION_ID)
Good luck. Let us know? Hein.
