Forum Stats

  • 3,768,004 Users
  • 2,252,739 Discussions
  • 7,874,407 Comments

Discussions

OGG: SQLEXEC SP Procedure and Stadalone procedure do not work

User_PPR7Y
User_PPR7Y Member Posts: 2 Green Ribbon

Hello Experts,

We are using OGG is 12C and database is "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.7.0.0.0"

We have following code in lower region:

MAP RXOWNER.RXP_PRESCRIPTION_FILL, TARGET RXDW_RT_DATA.RXP_PRESCRIPTION_FILL_CURR,&

COLMAP (USEDEFAULTS,[email protected](),IS_PROCESSED='N',RXDW_CRTE_BY='GGADMIN'),&

COMPARECOLS(ON UPDATE KEYINCLUDING(LAST_UPDATED_DATE),ON DELETE KEY),&

FILTER (@STREQ(FILL_STATUS, '7')),&

SQLEXEC (SPNAME RXDW_RT_DATA.SP_GG_RXP_IMM_PRESC_FILL_CURR, ID SP_GG_RXP_IMM_PRESC_FILL_CURR, ON INSERT, AFTERFILTER, EXEC TRANSACTION, ERROR RAISE),&

SQLEXEC (SPNAME RXDW_RT_DATA.SP_GG_RXP_IMM_PRESC_FILL_CURR, ID SP_GG_RXP_IMM_PRESC_FILL_CURR, ON UPDATE, AFTERFILTER, EXEC TRANSACTION, ERROR RAISE),&

RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT,USEMAX(LAST_UPDATED_DATE))),&

RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT,USEMAX(LAST_UPDATED_DATE))),&

RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT,OVERWRITE)),&

RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT,DISCARD)),&

RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT,DISCARD));

MAP RXDW_RT_DATA.RXP_PRESCRIPTION_FILL_CURR #exception_handler();


SQLEXEC 'rxdw_srvc.prc_temp1' EVERY 90 SECONDS


--SQLEXEC "call rxdw_srvc.prc_temp1('X')" EVERY 30 SECONDS

--SQLEXEC 'call rxdw_srvc.prc_temp2()' EVERY 100 SECONDS

--SQLEXEC 'CALL rxdw_srvc.prc_temp1()' EVERY 5 MINUTES


------------ Below are observation

1) the stored procedure P_GG_RXP_IMM_PRESC_FILL_CURR available under MAP statement does not work or getting executed.

My objective is: whenever any DML (I/U/D) happens on source table RXOWNER.RXP_PRESCRIPTION_FILL, this procedure SP_GG_RXP_IMM_PRESC_FILL_CURR should get executed automatically based on MAP statement. Each and every transactions

The filter criteria is working "FILTER (@STREQ(FILL_STATUS, '7')),&"


2) I am trying to schedule standalone procedure every X minutes or seconds using SQLEXEC. I tried with all permutations and combination using SQLEXEC 'CALL ...' etc. single / double quote, etc. But it gives me "Literal Does not match format string". You can see commented code as well.

The objective here is to execute this procedure rxdw_srvc.prc_temp1 every X minutes or seconds automatically - something like auto scheduler

Comments

  • ORASCN
    ORASCN Member Posts: 1,824 Gold Trophy

    Hello,

    We are using OGG is 12C and database is "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.7.0.0.0"

    Oracle GoldenGate 12c does not support Oracle Database 19c. Please check the certification matrix. The minimum version of Oracle GoldenGate required to support Oracle Database 19c is OGG 19c.

    Regards,

    Veera

  • User_PPR7Y
    User_PPR7Y Member Posts: 2 Green Ribbon

    Hi Veera,

    The OGG is working fine with above versions.

    It is just point 1 & 2 not working.

  • ORASCN
    ORASCN Member Posts: 1,824 Gold Trophy

    Hi ,

    When it is not meeting the pre-requisites or certifications, we cannot dig further in to the issue. This is not recommended as well.

    Please re-install the OGG with correct database version, so that we can troubleshoot further.

    Regards,

    Veera