调用Oracle存储过程sp_stage_target后无记录插入相关表的问题求助

Let's break down why your stored procedure sp_stage_target isn't inserting any records into TARGET_TAB, reject_tab, or SUMMARY_TAB, and fix the issues step by step:

Key Issues Identified #

1. Missing reject_tab Table #

Your provided DDL doesn't include a CREATE TABLE statement for reject_tab. When the procedure reaches the insert into reject_tab... line, it throws a "table or view does not exist" error. Since there's no exception handling, the entire procedure aborts immediately, and all uncommitted DML operations are rolled back—this is the most direct reason no records appear in your tables.

2. Uninitialized lv_RejectedCount Variable #

lv_RejectedCount is only assigned a value if the reference table check passes. If for any reason that check fails (even though your reference tables have data now, the logic has a gap), the variable stays NULL. The condition lv_RejectedCount <= lv_threshold_cnt evaluates to UNKNOWN when comparing NULL to a number, so the merge logic for TARGET_TAB never runs.

3. No Exception Handling #

Without error trapping, any runtime error (like the missing table) causes the procedure to crash before reaching the final commit. All intermediate inserts/merges are rolled back, leaving your target tables empty.

4. Uninitialized lv_status Variable #

If the reference table check fails, lv_status remains NULL, so inserting into SUMMARY_TAB would leave the process_status column empty—this doesn't align with your tracking requirements.

Fixed Code #

First, add the missing reject_tab table:

CREATE TABLE reject_tab (
    E_ID NUMBER(10),
    E_NAME VARCHAR2(30),
    E_LOC VARCHAR2(30),
    VALIDATION_STATUS varchar2(30),
    validation_msg varchar2(30)
);

Then, use this revised stored procedure with fixes for all the above issues:

create or replace procedure sp_stage_target(iv_req_id IN sys.OdciNumberList,ov_err_msg OUT varchar2) is
    lv_succ_rec number(30) := 0;
    lv_fail_rec number(30) := 0;
    lv_count_ref number(10);
    lv_count_ref2 number(10);
    lv_threshold_cnt number(10) := 5;
    lv_RejectedCount number(10) := 0; -- Initialize to avoid NULL comparison
    lv_status varchar2(30) := 'Failed'; -- Default to failed state
begin
    /* Validate reference tables exist */
    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 tab_ref !!Cannot proceed';
        return; -- Exit early if reference table is empty
    elsif lv_count_ref2 = 0 then
        ov_err_msg := 'Records are not present in tab_ref_2 !!Cannot proceed';
        return;
    else
        dbms_output.put_line('Data are present into reference tables');
        -- Mark invalid staging records
        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' 
                                    when LENGTH(e_id) > 4 then 'Id is longer than expected' 
                                    else null
                                  end
        where e_id is null OR LENGTH(e_id) > 4;
        
        lv_RejectedCount := SQL%ROWCOUNT;
        lv_status := 'Success'; -- Update status if references are valid
    end if;
    
    -- Process valid records if reject count is under threshold
    if lv_RejectedCount <= lv_threshold_cnt then
        dbms_output.put_line('Processing valid records');
        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;
    else
        lv_status := 'Failed';
        ov_err_msg := 'Rejected records count exceeds threshold of ' || lv_threshold_cnt;
    end if;
    
    -- Insert rejected records
    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;
    
    -- Log summary
    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 successfully';
    commit;
    
-- Catch and handle errors to avoid silent failures
EXCEPTION
    WHEN OTHERS THEN
        ov_err_msg := 'Error occurred: ' || SQLERRM;
        ROLLBACK; -- Rollback all changes on error
end;

What Changed? #

• Added the missing reject_tab table definition
• Initialized variables to prevent NULL comparison issues
• Added early exit logic if reference tables are empty
• Added exception handling to catch errors, rollback changes, and return meaningful error messages
• Updated status tracking to ensure SUMMARY_TAB always gets a valid process_status
• Clarified the validation message logic for staging records

After applying these fixes, your procedure should correctly populate TARGET_TAB, reject_tab, and SUMMARY_TAB when called with sys.OdciNumberList(1,2).

内容的提问来源于stack exchange，提问作者Vicky