调用过程之后，不会将记录插入到相应的表中

Question

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)
);

存储过程：

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;

调用程序：

declare
err_msg varchar2(4000);
begin
sp_stage_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;

当我调用一个过程时，我没有得到结果。理想情况下，它应该将记录从staging插入到target_tab、reject_tab和summary_tab。但是没有记录。有人能帮忙吗？我只是在同一会话中查询调用过程。

Answer 1

感谢您的测试用例；它很少出现。

据我所知，您的程序有效。虽然，你没有发布reject_table的描述，所以我在过程中评论了这段代码，但是-其余的似乎都可以。

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>

Answer 2

您是如何运行调用过程的？您的结果取决于DBMS_OUTPUT。但它实际上并没有生成任何输出，而是在缓冲区中创建客户端(这里是您的调用过程(可以处理或不处理的输出。如果通过SQL*Plus运行调用过程，请确保在该过程之前有set serveroutput on。如果通过另一个api，您可以在开始后尝试dbms_output.enable。

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;
/

遗憾的是，并非所有工具都支持dbms_output。