Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Insertion while executing Procedure (Oracle 12c)
Duplicate Insertion while executing Procedure [message #675239] Mon, 18 March 2019 05:34 Go to next message
rajmat483
Messages: 3
Registered: March 2019
Junior Member
Hi,
When I execute the below query, I get the expected result. However, when I schedule a job to populate a table using the same query, the table gets populated with the same records twice, thrice etc. as many times as the job runs.

The query is:


SELECT
    to_timestamp((TO_CHAR(SYSDATE,'DD-MON-RR')|| ' '|| TO_CHAR(m.contact_date,'HH:MI:SS AM')), 'DD-MON-RR HH:MI:SS AM') AS contact_date,
    m.interactivechannelname,
    m.interactionpointname,
    nvl(m.channel_type,'UNKNOWN')channel_type,
    m.cbu,
    m.segment,
    m.visits todays_visits,
    nvl(y.visits,0) yesterdays_visits,
    m.visits - nvl(y.visits,0) today_vs_yesterday_visits_diff,
    nvl(w.visits,0) last_6_days_visits,
    m.visits - nvl(w.visits,0) today_vs_6days_visits_diff,
    m.accepts todays_accepts,
    nvl(y.accepts,0) yesterdays_accepts,
    m.accepts - nvl(y.accepts,0) today_vs_yesterday_accepts,
    nvl(w.accepts,0) last_6_days_accepts,
    m.accepts - nvl(w.accepts,0) today_vs_6days_accepts,
    CASE
            WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN 'YES'
            WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN 'YES'
            WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN 'YES'
            WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN 'YES'
        END
    AS alert_msg,
    CASE
            WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN c.alarm_text
            WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN c.alarm_text
            WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN c.alarm_text
            WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN c.alarm_text
        END
    AS alarm_text
FROM
    mccm_ops_monitoring_1_days m
    LEFT JOIN mccm_ops_monitoring_pre_days y ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(y.contact_date,'HH24:MI:SS AM')
                                                AND m.interactivechannelname = y.interactivechannelname
                                                AND m.interactionpointname = y.interactionpointname
                                                AND nvl(m.channel_type,'UNKNOWN') = nvl(y.channel_type,'UNKNOWN')
                                                AND m.cbu = y.cbu
                                                AND m.segment = y.segment
    LEFT JOIN mccm_ops_monitoring_6_days w ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(w.contact_date,'HH24:MI:SS AM')
                                              AND m.interactivechannelname = w.interactivechannelname
                                              AND m.interactionpointname = w.interactionpointname
                                              AND nvl(m.channel_type,'UNKNOWN') = nvl(w.channel_type,'UNKNOWN')
                                              AND m.cbu = w.cbu
                                              AND m.segment = w.segment
    LEFT JOIN alarm_control_table c ON m.interactivechannelname = c.interactivechannelname
                                 AND m.interactionpointname = c.interactionpointname
                                 AND nvl(m.channel_type,'UNKNOWN') = nvl(c.channel_type,'UNKNOWN')
                                 AND m.cbu = c.cbu
                                 AND m.segment = c.segment
ORDER BY
    1 ;

And the Procedure is:


CREATE OR REPLACE PROCEDURE mccm_alarm_notification
    AS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE KPI.ALARM_NOTIFICATION';
    INSERT /*+APPEND+*/ INTO alarm_notification
        SELECT
            to_timestamp( (TO_CHAR(SYSDATE,'DD-MON-RR')
                             || ' '
                             || TO_CHAR(m.contact_date,'HH:MI:SS AM') ),'DD-MON-RR HH:MI:SS AM') AS contact_date,
            m.interactivechannelname,
            m.interactionpointname,
            nvl(m.channel_type,'UNKNOWN') channel_type,
            m.cbu,
            m.segment,
            m.visits todays_visits,
            nvl(y.visits,0) yesterdays_visits,
            m.visits - nvl(y.visits,0) today_vs_yesterday_visits_diff,
            nvl(w.visits,0) last_6_days_visits,
            m.visits - nvl(w.visits,0) today_vs_6days_visits_diff,
            m.accepts todays_accepts,
            nvl(y.accepts,0) yesterdays_accepts,
            m.accepts - nvl(y.accepts,0) today_vs_yesterday_accepts,
            nvl(w.accepts,0) last_6_days_accepts,
            m.accepts - nvl(w.accepts,0) today_vs_6days_accepts,
            CASE
                    WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold
                         OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN 'YES'
                    WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold
                         OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN 'YES'
                    WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold
                         OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN 'YES'
                    WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold
                         OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN 'YES'
                END
            AS alert_msg,
            CASE
                    WHEN ( m.visits - y.visits ) / 100 > c.prev_day_visit_threshold
                         OR ( y.visits - m.visits ) / 100 > c.prev_day_visit_threshold THEN c.alarm_text
                    WHEN ( m.visits - w.visits ) / 100 > c.last_6_days_visit_threshold
                         OR ( w.visits - m.visits ) / 100 > c.last_6_days_visit_threshold THEN c.alarm_text
                    WHEN ( m.accepts - y.accepts ) / 100 > c.prev_day_accept_threshold
                         OR ( y.accepts - m.accepts ) / 100 > c.prev_day_accept_threshold THEN c.alarm_text
                    WHEN ( m.accepts - w.accepts ) / 100 > c.last_6_days_accept_threshold
                         OR ( w.accepts - m.accepts ) / 100 > c.last_6_days_accept_threshold THEN c.alarm_text
                END
            AS alarm_text
        FROM
            mccm_ops_monitoring_1_days m
            LEFT JOIN mccm_ops_monitoring_pre_days y ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(y.contact_date,'HH24:MI:SS AM')
                                                        AND m.interactivechannelname = y.interactivechannelname
                                                        AND m.interactionpointname = y.interactionpointname
                                                        AND nvl(m.channel_type,'UNKNOWN') = nvl(y.channel_type,'UNKNOWN')
                                                        AND m.cbu = y.cbu
                                                        AND m.segment = y.segment
            LEFT JOIN mccm_ops_monitoring_6_days w ON TO_CHAR(m.contact_date,'HH24:MI:SS AM') = TO_CHAR(w.contact_date,'HH24:MI:SS AM')
                                                      AND m.interactivechannelname = w.interactivechannelname
                                                      AND m.interactionpointname = w.interactionpointname
                                                      AND nvl(m.channel_type,'UNKNOWN') = nvl(w.channel_type,'UNKNOWN')
                                                      AND m.cbu = w.cbu
                                                      AND m.segment = w.segment
            LEFT JOIN alarm_control_table c ON m.interactivechannelname = c.interactivechannelname
                                               AND m.interactionpointname = c.interactionpointname
                                               AND nvl(m.channel_type,'UNKNOWN') = nvl(c.channel_type,'UNKNOWN')
                                               AND m.cbu = c.cbu
                                               AND m.segment = c.segment;

COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001,'An error was encountered refreshing stage table- '
                                        || sqlcode
                                        || ' -ERROR- '
                                        || sqlerrm);
END;


Please advise and help on avoiding this duplication.
Let me know if any other details are required.

Thanks!
Re: Duplicate Insertion while executing Procedure [message #675240 is a reply to message #675239] Mon, 18 March 2019 05:58 Go to previous messageGo to next message
John Watson
Messages: 8124
Registered: January 2010
Location: Global Village
Senior Member
Were the answers to your previous question of any help? You never replied to any of them.
http://www.orafaq.com/forum/m/675063/#msg_675063

If you run the same query several times, would you not expect the same result?
Re: Duplicate Insertion while executing Procedure [message #675241 is a reply to message #675239] Mon, 18 March 2019 06:00 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
Oracle is doing exactly as you have told it to do.
Problem Exists Between Keyboard And Chair.
If you don't want duplicate data then run the job only once a day.
Re: Duplicate Insertion while executing Procedure [message #675244 is a reply to message #675239] Mon, 18 March 2019 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First of all, read WHEN OTHERS and remove it from your procedure.

Re: Duplicate Insertion while executing Procedure [message #675245 is a reply to message #675244] Mon, 18 March 2019 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13781
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're going to have to explain in more detail what is going on.
At the end of the day you've got a truncate followed by an insert/select.

If you're ending up with different numbers of rows each time you run it then either:
a) the select returns different numbers of rows each time
b) some other code is also modifying the table.

If it's a then that'll have to be because the data in the tables being queried has changed between procedure calls.

We know nothing about your tables and data.
We know nothing about what that query is actually returning

If you want us to help you work out what the problem is then you're going to have to explain those things to us.
Post a Test case - create table and insert statements so we can recreate your tables and data, along with expected output.
Re: Duplicate Insertion while executing Procedure [message #675398 is a reply to message #675245] Wed, 27 March 2019 07:19 Go to previous message
Bill B
Messages: 1927
Registered: December 2004
Senior Member
What schema are you running your procedure. You are attempting to truncate a table in the "KPI" schema, however truncate is a DDL statement but something like "delete from KPI.ALARM_NOTIFICATION" is a DML and is allowed if an insert privilege was granted on the table to your schema.
Previous Topic: existing state of package - Invalidated
Next Topic: how to replace date column
Goto Forum:
  


Current Time: Wed Jan 22 20:19:30 CST 2020