I have written a stored procedure where based on the SAP_ID, I will update the data in the table, if the data for that SAP_ID already exists, if not, then I INSERT it.
This is my query:
PROCEDURE INSERT_PF_EXCEL_DATA_IPCOLO (
p_political_state_name IN NVARCHAR2,
p_political_state_code IN NVARCHAR2,
p_rfcdate IN NVARCHAR2,
p_rfs_date IN NVARCHAR2,
p_rfe1_date IN NVARCHAR2,
p_site_drop_date IN NVARCHAR2,
p_ip_colo_siteid IN NVARCHAR2,
p_mw_installed IN NVARCHAR2,
p_dg_nondg IN NVARCHAR2,
p_eb_noneb IN NVARCHAR2,
p_id_od_countchange IN NVARCHAR2,
p_id_od_changeddate IN NVARCHAR2,
p_rrh_countchange IN NVARCHAR2,
p_rrh_changeddate IN NVARCHAR2,
p_tenancy_countchange IN NVARCHAR2,
p_tenancy_changeddate IN NVARCHAR2,
p_sap_id IN NVARCHAR2,
p_created_by IN NVARCHAR2,
p_rfs_date_5g IN NVARCHAR2,
p_drop_date_5g IN NVARCHAR2,
p_olt_count IN NUMBER,
p_olt_change_date IN NVARCHAR2,
p_diesel_downtime_minutes IN NVARCHAR2,
p_overall_infra_outage_minutes IN NVARCHAR2,
p_diesel_downtime_min_my IN NVARCHAR2,
p_overall_infra_outage_min_my IN NVARCHAR2,
p_is5gpresent IN NVARCHAR2,
p_is_site_dropped IN NVARCHAR2,
p_status OUT NVARCHAR2,
p_message OUT NVARCHAR2
) AS
t_cnt NUMBER;
vcnt_sapid NUMBER;
v_ipcolobilling VARCHAR2(25) := 'IpColoBilling';
BEGIN
-- DELETE FROM APP_LOG WHERE APP_NAME = 'IpColoBilling' AND TRUNC(LOG_TIME) = TRUNC(SYSDATE - 2);
DELETE FROM app_log
WHERE
app_name = v_ipcolobilling
AND ( log_time ) >= trunc(sysdate - 2)
AND log_time < trunc(sysdate - 1);
COMMIT;
SELECT count(sap_id) INTO vcnt_sapid FROM temp_ipcolo_billing_mst
WHERE
sap_id = p_sap_id;-- AND CREATED_DATE = SYSDATE;
IF vcnt_sapid > 0 THEN
UPDATE temp_ipcolo_billing_mst
SET
political_state_name = p_political_state_name,
political_state_code = p_political_state_code,
rfcdate = TO_DATE(p_rfcdate, 'DD-MM-YYYY'), -- Truncates to the day
rfs_date = TO_DATE(p_rfs_date, 'DD-MM-YYYY'),-- TRUNC(), -- Removed invalid format 'dd-MM-yyyy'
rfe1_date = TO_DATE(p_rfe1_date, 'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
site_drop_date = TO_DATE(p_site_drop_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
ip_colo_siteid = p_ip_colo_siteid,
mw_installed = p_mw_installed,
dg_nondg = p_dg_nondg,
eb_noneb = p_eb_noneb,
id_od_countchange = p_id_od_countchange,
id_od_changeddate = TO_DATE(p_id_od_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
rrh_countchange = p_rrh_countchange,
rrh_changeddate = TO_DATE(p_rrh_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
tenancy_countchange = p_tenancy_countchange,
tenancy_changeddate = TO_DATE(p_tenancy_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
created_by = p_created_by,
rfs_date_5g = TO_DATE(p_rfs_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
drop_date_5g = TO_DATE(p_drop_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
olt_count = p_olt_count,
olt_change_date = TO_DATE(p_olt_change_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
diesel_downtime_minutes = p_diesel_downtime_minutes,
overall_infra_outage_minutes = p_overall_infra_outage_minutes,
diesel_downtime_min_my = p_diesel_downtime_min_my,
overall_infra_outage_min_my = p_overall_infra_outage_min_my,
is5gpresent = p_is5gpresent,
is_site_dropped = p_is_site_dropped,
LAST_UPDATED_DATE = SYSDATE
WHERE
sap_id = p_sap_id;
p_message := 'SUCCESS';
COMMIT;
ELSE
INSERT INTO temp_ipcolo_billing_mst (--TEMP_IPCOLO_BILLING_MST
id,
political_state_name,
political_state_code,
rfcdate,
rfs_date,
rfe1_date,
site_drop_date,
ip_colo_siteid,
mw_installed,
dg_nondg,
eb_noneb,
id_od_countchange,
id_od_changeddate,
rrh_countchange,
rrh_changeddate,
tenancy_countchange,
tenancy_changeddate,
sap_id,
created_by,
rfs_date_5g,
drop_date_5g,
olt_count,
olt_change_date,
diesel_downtime_minutes,
overall_infra_outage_minutes,
diesel_downtime_min_my,
overall_infra_outage_min_my,
is5gpresent,
is_site_dropped
) VALUES (
incr_id_ipcolo_temp.NEXTVAL,
p_political_state_name,
p_political_state_code,
to_date(p_rfcdate, 'dd-MM-yyyy'),
to_date(p_rfs_date, 'dd-MM-yyyy'),
to_date(p_rfe1_date, 'dd-MM-yyyy'),
to_date(p_site_drop_date, 'dd-MM-yyyy'),
p_ip_colo_siteid,
p_mw_installed,
p_dg_nondg,
p_eb_noneb,
p_id_od_countchange,
to_date(p_id_od_changeddate, 'dd-MM-yyyy'),
p_rrh_countchange,
to_date(p_rrh_changeddate, 'dd-MM-yyyy'),
p_tenancy_countchange,
to_date(p_tenancy_changeddate, 'dd-MM-yyyy'),
p_sap_id,
p_created_by,
to_date(p_rfs_date_5g, 'dd-MM-yyyy'),
to_date(p_drop_date_5g, 'dd-MM-yyyy'),
p_olt_count,
to_date(p_olt_change_date, 'dd-MM-yyyy'),
p_diesel_downtime_minutes,
p_overall_infra_outage_minutes,
to_date(p_diesel_downtime_min_my, 'dd-MM-yyyy'),
to_date(p_overall_infra_outage_min_my, 'dd-MM-yyyy'),
p_is5gpresent,
p_is_site_dropped
);
p_message := 'SUCCESS';
END IF;
EXCEPTION
WHEN OTHERS THEN
p_status := sqlerrm;
p_message := 'ERROR';
END INSERT_PF_EXCEL_DATA_IPCOLO;
Where am I going wrong?
You never explained what you meant by saying that procedure you wrote doesn't work. How did you check it? Did you run it? Using which data? Did you check value of OUT parameters?
Anyway: instead of checking whether row already exists (or not) and writing separate update and insert statements, consider using merge instead; it is also known as "upsert" as it combines update with insert, and is designed for problems you described.
Here's a simplified example (I didn't feel like creating all those parameters or table columns), but - it should illustrate what I'm trying to say.
Sample table and sequence:
SQL> create table temp_ipcolo_billing_mst
2 (id number,
3 political_state_name varchar2(10),
4 political_state_code varchar2(10),
5 rfcdate date);
Table created.
SQL> create sequence incr_id_ipcolo_temp;
Sequence created.
Procedure; it removed logging part of code - include it back, if you need it. Basically, it does the same job as your procedure by checking whether P_SAP_ID row exists in the target table or not:
merge will update that rowHere's its code:
SQL> create or replace procedure insert_pf_excel_data_ipcolo
2 (p_sap_id in number,
3 p_political_state_name in nvarchar2,
4 p_political_state_code in nvarchar2,
5 p_rfcdate in nvarchar2,
6 p_message out nvarchar2)
7 as
8 begin
9 merge into temp_ipcolo_billing_mst a
10 using (select p_sap_id as sap_id,
11 p_political_state_name as political_state_name,
12 p_political_state_code as political_state_code,
13 to_date(p_rfcdate, 'dd-mm-yyyy') as rfcdate
14 from dual
15 ) b
16 on (b.sap_id = a.id)
17 when matched then update set
18 a.political_state_name = b.political_state_name,
19 a.political_state_code = b.political_state_code,
20 a.rfcdate = b.rfcdate
21 when not matched then insert
22 (id,
23 political_state_name,
24 political_state_code,
25 rfcdate)
26 values
27 (incr_id_ipcolo_temp.nextval,
28 b.political_state_name,
29 b.political_state_code,
30 b.rfcdate
31 );
32
33 p_message := 'SUCCESS';
34 exception
35 when others then
36 p_message := 'ERROR: ' || sqlerrm;
37 end;
38 /
Procedure created.
Let's test it: the first example runs OK and inserts a row:
SQL> set serveroutput on
SQL> declare
2 l_msg varchar2(200);
3 begin
4 insert_pf_excel_data_ipcolo
5 (p_sap_id => 1,
6 p_political_state_name => 'Pol. name',
7 p_political_state_code => 'PNC',
8 p_rfcdate => '06-01-2025',
9 p_message => l_msg);
10 dbms_output.put_line(l_msg);
11 end;
12 /
SUCCESS
PL/SQL procedure successfully completed.
SQL> select * from temp_ipcolo_billing_mst;
ID POLITICAL_ POLITICAL_ RFCDATE
---------- ---------- ---------- ---------
1 Pol. name PNC 06-JAN-25
This example fails because of invalid date value (and returns error message):
SQL> declare
2 l_msg varchar2(200);
3 begin
4 insert_pf_excel_data_ipcolo
5 (p_sap_id => 1,
6 p_political_state_name => 'Pol. name',
7 p_political_state_code => 'PNC',
8 p_rfcdate => '99-01-2025',
9 p_message => l_msg);
10 dbms_output.put_line(l_msg);
11 end;
12 /
ERROR: ORA-01847: day of month must be between 1 and last day of month
PL/SQL procedure successfully completed.
The final example updates existing row:
SQL> declare
2 l_msg varchar2(200);
3 begin
4 insert_pf_excel_data_ipcolo
5 (p_sap_id => 1,
6 p_political_state_name => 'Pol. nameA',
7 p_political_state_code => 'PNC_A',
8 p_rfcdate => '25-08-2025',
9 p_message => l_msg);
10 dbms_output.put_line(l_msg);
11 end;
12 /
SUCCESS
PL/SQL procedure successfully completed.
SQL> select * from temp_ipcolo_billing_mst;
ID POLITICAL_ POLITICAL_ RFCDATE
---------- ---------- ---------- ---------
1 Pol. nameA PNC_A 25-AUG-25
SQL>
So, yes - it works. Have a closer look at it and - if you find it useful - rewrite your code so that it does the same as my sample procedure.