DSXchange

Hi ,

I am firing a query in database and its working , but when the same query I am using in Datastage Connector Stage.
It is showing a error as :-

An exception has occured while trying to recieve the response from handler: An exception was recieved from handler: While parsing table name CDL_LND.CDL_INSTALMENT_BOOKOUTERCDL_LND.CDL_FIX_GNL_RECORDS , the connector detected an unexpected character at position 39.


I have checked the metadata of the columns , double checked for the special characters ... but does not find issue. The query is mentioned as :-

Query
-------
SELECT
CDL_INSTALMENT_BOOK.PK_ID,
CDL_INSTALMENT_BOOK.KEY,
CDL_INSTALMENT_BOOK.EXTRACT_DATE,
CDL_INSTALMENT_BOOK.BRAN,
CDL_INSTALMENT_BOOK.CLIENT,
CDL_INSTALMENT_BOOK.REF,
CDL_INSTALMENT_BOOK.NO,
CDL_INSTALMENT_BOOK.DEPOSIT,
CDL_INSTALMENT_BOOK.CHARGES,
CDL_INSTALMENT_BOOK.INST,
CDL_INSTALMENT_BOOK.REPNO,
CDL_INSTALMENT_BOOK.LOAN,
CDL_INSTALMENT_BOOK.OWED,
CDL_INSTALMENT_BOOK.SCHEME,
CDL_INSTALMENT_BOOK.REFERENCE,
CDL_INSTALMENT_BOOK.STATUS,
CDL_INSTALMENT_BOOK.PAY,
CDL_INSTALMENT_BOOK.LDATE,
CDL_INSTALMENT_BOOK.PLESS,
CDL_INSTALMENT_BOOK.BXDATE,
CDL_INSTALMENT_BOOK.BANKNAME,
CDL_INSTALMENT_BOOK.BANKACC,
CDL_INSTALMENT_BOOK.BANKCODE,
CDL_INSTALMENT_BOOK.CARDNO,
CDL_INSTALMENT_BOOK.CARDSTART,
CDL_INSTALMENT_BOOK.CARDEND,
CDL_INSTALMENT_BOOK.CARDISSUE,
CDL_FIX_GNL_RECORDS.PTYPE AS PTYPE,
CDL_HH_VAR_POLCY_RECORDS.SCHEME AS CCCH,
CDL_PRVT_CAR_VAR_POLCY_RECORDS.SCHEME AS CCCP
FROM
CDL_LND.CDL_INSTALMENT_BOOK
LEFT OUTER JOIN
CDL_LND.CDL_FIX_GNL_RECORDS CDL_FIX_GNL_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_FIX_GNL_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_FIX_GNL_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_PRVT_CAR_VAR_POLCY_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_PRVT_CAR_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_PRVT_CAR_VAR_POLCY_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_HH_VAR_POLCY_RECORDS
ON
( CDL_INSTALMENT_BOOK.CLIENT = CDL_HH_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_HH_VAR_POLCY_RECORDS.REF)

Guessing it is the fact that "CDL_INSTALMENT_BOOKOUTERCDL_LND" is 31 characters... could have sworn the limit was 30. What version of Oracle is this - server and client?

This is the Oracle client .....

I have put the same query in Oracle Enterprise stage .... and it is working fine....Not understanding what was the problem with connector Stage?

Doesn't answer my question at all, but fine. Connectors are new and still... buggy. Ping your official support provider and see if there is a patch.

Chulett is right. Please check with your official support partners.

Connectors are buggy. We are also facing so many issues with the OCI Connector Stage.

Check too whether your other client is set to automatically truncate to legal names.

The issue is resolved , there was a small bug in the Query... I have not mentioned the alias of CDL_INSTALMENT_BOOK table marked in Bold Letters. I was not able to diagnose because the query was working in database + Oracle Enterprise stage.

Thanks for all the inputs.

The query is given below:-

Query
-------
SELECT
CDL_INSTALMENT_BOOK.PK_ID,
CDL_INSTALMENT_BOOK.KEY,
CDL_INSTALMENT_BOOK.EXTRACT_DATE,
CDL_INSTALMENT_BOOK.BRAN,
CDL_INSTALMENT_BOOK.CLIENT,
CDL_INSTALMENT_BOOK.REF,
CDL_INSTALMENT_BOOK.NO,
CDL_INSTALMENT_BOOK.DEPOSIT,
CDL_INSTALMENT_BOOK.CHARGES,
CDL_INSTALMENT_BOOK.INST,
CDL_INSTALMENT_BOOK.REPNO,
CDL_INSTALMENT_BOOK.LOAN,
CDL_INSTALMENT_BOOK.OWED,
CDL_INSTALMENT_BOOK.SCHEME,
CDL_INSTALMENT_BOOK.REFERENCE,
CDL_INSTALMENT_BOOK.STATUS,
CDL_INSTALMENT_BOOK.PAY,
CDL_INSTALMENT_BOOK.LDATE,
CDL_INSTALMENT_BOOK.PLESS,
CDL_INSTALMENT_BOOK.BXDATE,
CDL_INSTALMENT_BOOK.BANKNAME,
CDL_INSTALMENT_BOOK.BANKACC,
CDL_INSTALMENT_BOOK.BANKCODE,
CDL_INSTALMENT_BOOK.CARDNO,
CDL_INSTALMENT_BOOK.CARDSTART,
CDL_INSTALMENT_BOOK.CARDEND,
CDL_INSTALMENT_BOOK.CARDISSUE,
CDL_FIX_GNL_RECORDS.PTYPE AS PTYPE,
CDL_HH_VAR_POLCY_RECORDS.SCHEME AS CCCH,
CDL_PRVT_CAR_VAR_POLCY_RECORDS.SCHEME AS CCCP
FROM
CDL_LND.CDL_INSTALMENT_BOOK CDL_INSTALMENT_BOOK
LEFT OUTER JOIN
CDL_LND.CDL_FIX_GNL_RECORDS CDL_FIX_GNL_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_FIX_GNL_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_FIX_GNL_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_PRVT_CAR_VAR_POLCY_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_PRVT_CAR_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_PRVT_CAR_VAR_POLCY_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_HH_VAR_POLCY_RECORDS
ON
( CDL_INSTALMENT_BOOK.CLIENT = CDL_HH_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_HH_VAR_POLCY_RECORDS.REF)

I was facing same issue with my query where I was using INNER JOIN.

SELECT
count(*)
FROM EPIMART.MUT_FUD_TRANS_FACT_0_V MFTF
INNER JOIN
EPIMART.SECURITIES_0_V SECURITIES
ON MFTF.SECURITIES_KEY = SECURITIES.SECURITIES_KEY
INNER JOIN
EPIMART.SECURITIES_0_V TO_SECURITIES
ON MFTF.TO_SECURITIES_KEY = TO_SECURITIES.SECURITIES_KEY
INNER JOIN EPIMART.TRANS_TYPE_METHOD_0_V TTM
ON MFTF.TRANS_TYPE_METHOD_KEY = TTM.TRANS_TYPE_METHOD_KEY
WHERE MFTF.TRANSTYPE_KEY = 20188
AND TRUNC(MONTHS_BETWEEN(TO_DATE(\'2010-12-13\',\'YYYY-MM-DD\'),MFTF.DATE_KEY)) <= 13;


It worked after I replaced INNER JOIN with JOIN.