Comparision of the timestamp in procedure not working

Question

I have written a following query in my procedure which is not inserting the records in the TEST table. The KPI definition table has the following record:

KPI_DEF_ID  KPI_FREQUENCY KPI_FREQ_TIME_UNIT  EVENT_ID
1000136     30            MINUTE                10028   
1000137     50            MINUTE                10028

I have a application in which user want to get the records depending on the timestamp. So user can enter in the application to get the records for example older than 30 min and newer than 24 hour. And the timestamp is changable. The older than timestamp comes from the KPI DEFINITION table and which is stored in the column KPI_FREQUENCY and KPI_FREQUENCY_UNIT and it can be changabler. And the newer than timestamp is fixed and i stored it in varaible LAST_OLDER_VAL and LAST_OLDER_VAL_UNIT. I used the insert using select query to store the records in table but its not working.

         create or replace PROCEDURE "EXT_TEST" AS 

LAST_WF_ID Number := 0;
--LAST_UNIT NUMBER:=10;
--LAST_UNIT_VAL VARCHAR2(20);
LAST_OLDER_VAL NUMBER := 24;
LAST_OLDER_VAL_UNIT VARCHAR2(10) := 'HOUR';

CURSOR WF_WORKFLOW_CUR IS
Select KPI_DEF_ID,KPI_FREQUENCY,KPI_FREQ_TIME_UNIT,EVENT_ID,BUSINESS_CHECK_PERIOD_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION where EVENT_ID=10028;
BEGIN

--DBMS_OUTPUT.PUT_LINE('LAST_UNIT - ' || LAST_UNIT);
--DBMS_OUTPUT.PUT_LINE('LAST_UNIT_VAL - ' || LAST_UNIT_VAL);

-- removed, retrieve  a new START_ID from source first, don't use the last id.
--SELECT LAST_TASK_ID INTO LAST_WF_ID FROM CAPTURING where DB_TABLE='TEMP_WF_WORKFLOW';

FOR WF_WORKFLOW_ROW IN WF_WORKFLOW_CUR
LOOP

--select MIN(ID) INTO LAST_WF_ID from WF_WORKFLOW@FONIC_RETAIL WF where WF.START_DATE > sysdate - numtodsinterval ( WF_WORKFLOW_ROW.KPI_FREQUENCY, WF_WORKFLOW_ROW.KPI_FREQ_TIME_UNIT );

Insert into TEST(ID,NAME,SUBSCRIPTION_ID,START_DATE,STATUS_ID,ACCOUNT_ID,END_DATE)
Select DISTINCT(WF.ID),WF.NAME,WF.SUBSCRIPTION_ID,WF.START_DATE,WF.STATUS_ID,WF.ACCOUNT_ID,WF.END_DATE
from WF_WORKFLOW@FONIC_RETAIL WF where WF.STATUS_ID = 0 and WF.NAME = 'SIGNUP_MOBILE_PRE_PAID'
and WF.START_DATE > SYSDATE - numtodsinterval ( LAST_OLDER_VAL, LAST_OLDER_VAL_UNIT
and WF.START_DATE < SYSDATE - numtodsinterval ( WF_WORKFLOW_ROW.KPI_FREQUENCY, WF_WORKFLOW_ROW.KPI_FREQ_TIME_UNIT ));

DBMS_OUTPUT.PUT_LINE('WF_WORKFLOW_ROW.KPI_FREQUENCY - ' || WF_WORKFLOW_ROW.KPI_FREQUENCY);
DBMS_OUTPUT.PUT_LINE('WF_WORKFLOW_ROW.KPI_FREQ_TIME_UNIT - ' || WF_WORKFLOW_ROW.KPI_FREQ_TIME_UNIT);

END LOOP;

END EXT_TEST;

Answer 1

You're currently looking for a start date that is older than 24 hours and newer than 30 minutes. Which is impossible, and not what you stated you needed, so that isn't what you mean really. Looks like you just have your < and > comparisons the wrong way around:

...
and WF.START_DATE > SYSDATE - numtodsinterval ( LAST_OLDER_VAL, LAST_OLDER_VAL_UNIT )
and WF.START_DATE < SYSDATE - numtodsinterval ( WF_WORKFLOW_ROW.KPI_FREQUENCY, WF_WORKFLOW_ROW.KPI_FREQ_TIME_UNIT );

Not directly relevant, but I'm not sure why you're using a loop for this, rather than a single insert ... select which joins WF_WORKFLOW_CUR and WF_WORKFLOW@FONIC_RETAIL. Or really why you'd use a stored procedure at all.