Need to implement PL/SQL cursor in Talend

Anonymous — Thu, 22 Oct 2015 21:17:25 GMT

Hi ,

I'm new to Talend and I need to implement the PL/SQL cursor in Talend studio by creting a job.Can anyone please let me know if there are any transformations that are available in Talend to implement the PL/SQL cursor.

Below is the sample that I need to work on in Talend studio for PL/SQL cursor.

For each ZIP_ST_ABBR_CDE from distinct rows from #1 above, compare to ST_ABBR in MDMGEO.T_STATE. If row found, continue. Else, bypass record and read next.

For each key of DTL_ZIP_CDE, PRFRD_LST_LINE_CITY_ST_KEY_NAM and ZIP_ST_ABBR_CDE from distinct rows from #1 above, compare ZIP_CLSFN_CDE and match to ZIP_CDE_TYP_CDE in the ZIP_CITY_STATE cursor below. Exception: if ZIP_CLSFN_CDE = spaces, change to ‘N’ prior to the compare:
•    ZIP_CITY_STATE Cursor
DECLARE ZIP_CITY_STATE CURSOR FOR
SELECT ZZ.ZIP_CDE
     , CC.CITY_NAM
     , CC.ST_ABBR
     , ZZ.ZIP_CDE_TYP_CDE
     , ZZ.ZIP_CDE_STRT_DTE
FROM MDMGEO.T_CITY         CC
   , MDMGEO.T_ZIP_CITY     ZC
   , MDMGEO.T_ZIP_CODE     ZZ
WHERE CC.CITY_GNRTD_ID    = ZC.CITY_GNRTD_ID
AND ZZ.ZIP_CDE          = ZC.ZIP_CDE
AND ZZ.ZIP_CDE_STRT_DTE = ZC.ZIP_CDE_STRT_DTE
AND CURRENT DATE BETWEEN ZZ.ZIP_CDE_STRT_DTE
AND                      ZZ.ZIP_CDE_END_DTE
AND CURRENT DATE BETWEEN ZC.ZIP_CITY_STRT_DTE
AND                      ZC.ZIP_CITY_END_DTE
AND ZZ.ZIP_CDE          = DTL_ZIP_CDE
AND CC.CITY_NAM         =       PRFRD_LST_LINE_CITY_ST_KEY_NAM
AND CC.ST_ABBR          = ZIP_ST_ABBR_CDE

For matching keys, for each mismatch between ZIP_CLSFN_CDE and ZIP_CDE_TYP_CDE, write a record to the Record Type Changes output:
RTYP-CHANGE-RECORD
    RTYP-ZIP-CDE     (from cursor)
    RTYP-CITY       (from cursor)
    RTYP-STATE      (from cursor)
    RTYP-RECORD-TYPE   (from USPS record type)
    RTYP-STRT-DATE (from cursor)
    RTYP-OLD-TYPE   (from cursor)

For unmatched keys, if the key is found in the cursor but not in the distinct rows from #1 above, these are USPS deletions. Write a record to the USPS Deletions output:
UNIVERSE-CHANGE-RECORD
   UNIV-ZIP-CDE
   UNIV-CITY
   UNIV-STATE
   UNIV-RECORD-TYPE
   UNIV-STRT-DATE

For unmatched keys, if the key is found in the distinct rows from #1 above but not in the cursor, these are USPS additions. Write a record to the USPS Additions output:

USPS-CHANGE-RECORD
    USPS-ZIP
    USPS-CITY
    USPS-STATE
    USPS-RECORD-TYPE

Thanks,
Yugandhar

Re: Need to implement PL/SQL cursor in Talend

Anonymous — Thu, 05 Nov 2015 05:58:30 GMT

Hi
You can create stored procedure and call the SP with tOracleSP component, use tParseRecordSet to parse the result set after tOracleSP.
Best regards
Shong

topic Need to implement PL/SQL cursor in Talend in Talend Studio

Need to implement PL/SQL cursor in Talend

Re: Need to implement PL/SQL cursor in Talend