CREATE TABLE STAGING_tab
(
E_ID NUMBER(10),
E_NAME VARCHAR2(30),
E_LOC VARCHAR2(30),
VALIDATION_STATUS varchar2(30),
validation_msg varchar2(30),
req_id number(10)
);
insert into staging_tab values(1,'A','AA',null,null,1);
insert into staging_tab values(2,'B','BB',null,null,1);
insert into staging_tab values(3,'C','CC',null,null,1);
insert into staging_tab values(null,'D','DD',null,null,2);
insert into staging_tab values(null,'E','EE',null,null,2);
insert into staging_tab values(null,'F','GG',null,null,2);
CREATE TABLE tab_ref
(
ref_id number(10),
ref_name varchar2(30)
);
insert into tab_ref values(1,'aa');
insert into tab_ref values(2,'bb');
insert into tab_ref values(3,'cc');
insert into tab_ref values(4,'dd');
CREATE TABLE tab_ref_2
(
ref_id number(10),
ref_name varchar2(30)
);
insert into tab_ref_2 values(1,'ee');
insert into tab_ref_2 values(2,'ff');
insert into tab_ref_2 values(3,'gg');
insert into tab_ref_2 values(4,'hh');
CREATE TABLE SUMMARY_TAB
(
TOT_RECORDS NUMBER(10,0),
SUCCESS_RECORDS NUMBER(10,0),
FAILED_RECORDS NUMBER(10,0),
process_status varchar2(30)
);
CREATE TABLE TARGET_TAB
(
E_ID NUMBER(10,0),
E_NAME VARCHAR2(30),
E_LOC VARCHAR2(30)
);
Stored procedure :
create or replace procedure sp_stage_target(iv_req_id IN sys.OdciNumberList,ov_err_msg OUT varchar2) is
lv_succ_rec number(30);
lv_fail_rec number(30);
lv_count_ref number(10);
lv_count_ref2 number(10);
lv_threshold_cnt number(10);
lv_RejectedCount number(10);
lv_status varchar2(30);
begin
lv_succ_rec := 0;
lv_fail_rec := 0;
lv_threshold_cnt := 5;
/*First checking whether data is present in reference table or not.
If data is not present then process should stop*/
select count(1) into lv_count_ref from tab_ref;
select count(1) into lv_count_ref2 from tab_ref_2;
if lv_count_ref = 0 then
ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';
elsif lv_count_ref2 = 0 then
ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';
else
dbms_output.put_line('Data are present into reference tables');
merge into staging_tab d
using (
select 'Fail' as validation_status, t.column_value as req_id
from table(iv_req_id) t
) s
on (d.req_id = s.req_id)
when matched then
update set
d.validation_status = s.validation_status
, d.validation_msg = case
when e_id is null then 'Id is not present'
else 'Id is longer than expected'
end
where e_id is null OR LENGTH(e_id) > 4;
lv_RejectedCount := SQL%ROWCOUNT;
end if;
--If rejected count is less than lv_threshold_cnt i.e 5
--then success records will go in target_tab and failed records will go in reject_tab
if lv_RejectedCount <= lv_threshold_cnt then
lv_status := 'Success';
dbms_output.put_line('Success');
merge into target_tab t
using (
select e_id, e_name, e_loc
from staging_tab
where validation_status is null and req_id in (select column_value from table(iv_req_id))
) s
on (t.e_id = s.e_id)
when matched then
update set
t.e_name = s.e_name,
t.e_loc = s.e_loc
when not matched then
insert (t.e_id,t.e_name,t.e_loc)
values (s.e_id,s.e_name,s.e_loc);
lv_succ_rec := SQL%ROWCOUNT;
end if;
insert into reject_tab(e_id, e_name, e_loc, validation_status,validation_msg)
select e_id, e_name, e_loc, validation_status,validation_msg
from staging_tab
where validation_status = 'Fail' and req_id in (select column_value from table(iv_req_id));
lv_fail_rec := SQL%ROWCOUNT;
--In Summary table keeping track of all the records i.e success record, failed records
dbms_output.put_line('Inserting into Summary table');
insert into summary_tab(tot_records, success_records, failed_records, process_status)
values (lv_succ_rec lv_fail_rec, lv_succ_rec, lv_fail_rec, lv_status);
ov_err_msg := 'Procedure completed succesfully';
commit;
end;
Calling Procedure :
declare
err_msg varchar2(4000);
begin
sp_stage_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;
When I am calling a procedure I am not getting the results. Ideally, it should insert the records from staging to target_tab, reject_tab, and summary_tab. But getting no records. Can someone help with this? I am querying the calling procedure in the same session only.
CodePudding user response：
Thank you for test case; it is rarely seen.
As far as I can tell, your procedure works. Though, you didn't post reject_table description so I commented that piece of code out of the procedure, but - the rest seems to be OK.
SQL> declare
2 err_msg varchar2(4000);
3 begin
4 sp_stage_target(sys.OdciNumberList(1,2),err_msg);
5 dbms_output.put_line(err_msg);
6 end;
7 /
Data are present into reference tables
Success
Inserting into Summary table
Procedure completed succesfully
PL/SQL procedure successfully completed.
SQL> select * From target_tab;
E_ID E_NAME E_LOC
---------- ---------- ------------------------------
3 C CC
1 A AA
2 B BB
SQL> select * from summary_tab;
TOT_RECORDS SUCCESS_RECORDS FAILED_RECORDS PROCESS_STATUS
----------- --------------- -------------- ------------------------------
6 3 3 Success
6 3 3 Success
SQL>
CodePudding user response：
How are you running he Calling Procedure? Your are depending on DBMS_OUTPUT for results. But it does not actually generate any output, it creates and in buffer which the client (here your calling procedure) can process or not. If running Calling Procedure through SQL*Plus make sure you have set serveroutput on before the procedure. If through another api you can try dbms_output.enable just after begin.
SET SERVEROUTPUT ON --- THIS
declare
err_msg varchar2(4000);
begin
dbms_output.enable; -- OR THIS
sp_stage_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;
/
Unfortunately not all tools support dbms_output.