Need to implement PL/SQL cursor in Talend

[Anonymous]

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

[Anonymous]

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