Блог

 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) gt; 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 lt;= 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;  

Когда я вызываю процедуру, я не получаю результатов. В идеале он должен вставлять записи из промежуточной таблицы в target_tab, reject_tab и summary_tab. Но не получаю никаких записей. Кто-нибудь может помочь с этим? Я запрашиваю процедуру вызова только в одном сеансе.

Спасибо за тестовый случай; его редко можно увидеть.

Насколько я могу судить, ваша процедура работает. Хотя вы не опубликовали reject_table описание, поэтому я прокомментировал этот фрагмент кода вне процедуры, но … остальное, похоже, в порядке.

 SQLgt; 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.  SQLgt; select * From target_tab;   E_ID E_NAME E_LOC ---------- ---------- ------------------------------  3 C CC  1 A AA  2 B BB  SQLgt; select * from summary_tab;  TOT_RECORDS SUCCESS_RECORDS FAILED_RECORDS PROCESS_STATUS ----------- --------------- -------------- ------------------------------  6 3 3 Success  6 3 3 Success  SQLgt;  

Как вы выполняете процедуру вызова? Ваши результаты зависят от 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.