Oracle中UTL_FILE导入含表头CSV的问题及表头匹配需求

Got it, let's tackle your CSV import issues step by step. You're dealing with auto-generated files that can't be edited manually, so we need to build all the logic right into your stored procedure—skipping the header, stripping unwanted double quotes, and adding a safety check to ensure the CSV header matches your target table structure.

Key Fixes We'll Implement #

• Skip and validate the header: Read the first line, confirm it matches your test table columns, then move to data rows.
• Strip double quotes: Clean up quoted fields without breaking values that have commas inside quotes (like "Smith, Jane").
• Robust error handling: Ensure the file gets closed properly if anything goes wrong.

Modified Stored Procedure #

create or replace procedure load_file_new(p_FileDir in varchar2, p_FileName in varchar2) as
    v_FileHandle utl_file.file_type;
    v_NewLine varchar2(2000);
    v_header_line varchar2(2000);
    v_a varchar2(100);
    v_b varchar2(100);
    v_c varchar2(100);
    v_d varchar2(100);
    -- Match this to your test table's column order/names
    v_expected_header varchar2(2000) := 'a,b,c,d';
begin
    -- Open the file with maximum line size support
    v_FileHandle := utl_file.fopen(p_FileDir, p_FileName, 'r', 32767);

    -- Read and validate the header line
    utl_file.get_line(v_FileHandle, v_header_line);
    -- Clean quotes from header in case CSV uses quoted column names (e.g., "a","b")
    v_header_line := regexp_replace(v_header_line, '^"|"$', '', 1, 0, 'm');
    v_header_line := regexp_replace(v_header_line, '","', ',', 1, 0, 'm');
    
    -- Stop import if header doesn't match target table
    if lower(v_header_line) != lower(v_expected_header) then
        utl_file.fclose(v_FileHandle);
        raise_application_error(-20001, 'CSV header mismatch. Expected: ' || v_expected_header || ' | Got: ' || v_header_line);
    end if;

    -- Process each data line
    loop
        begin
            utl_file.get_line(v_FileHandle, v_NewLine);
        exception 
            when no_data_found then 
                exit; -- Exit loop when end of file is reached
        end;

        -- Extract fields and strip leading/trailing double quotes
        v_a := regexp_replace(regexp_substr(v_NewLine, '("[^"]*"|[^,]+)', 1, 1), '^"|"$', '');
        v_b := regexp_replace(regexp_substr(v_NewLine, '("[^"]*"|[^,]+)', 1, 2), '^"|"$', '');
        v_c := regexp_replace(regexp_substr(v_NewLine, '("[^"]*"|[^,]+)', 1, 3), '^"|"$', '');
        v_d := regexp_replace(regexp_substr(v_NewLine, '("[^"]*"|[^,]+)', 1, 4), '^"|"$', '');

        -- Your existing merge logic, now using clean, quote-free data
        merge into test using dual on (a = v_a)
        when not matched then 
            insert (a, b, c, d) values (v_a, v_b, v_c, v_d)
        when matched then 
            update set b = v_b, c = v_c, d = v_d where a = v_a;
    end loop;

    utl_file.fclose(v_FileHandle);
    commit;
exception
    when others then
        -- Ensure file is closed if an error occurs to avoid locks
        if utl_file.is_open(v_FileHandle) then
            utl_file.fclose(v_FileHandle);
        end if;
        raise; -- Re-throw the error to preserve debugging info
end load_file_new;

What Changed & Why #

1. Header Validation

   • We first read the header line, clean any quotes from it, then compare it to your expected column list. If they don't match, the procedure throws an error immediately—no bad data gets imported.
   • Use lower() to make the comparison case-insensitive (adjust if you need strict case matching).

2. Quote Stripping

   • The regexp_replace(..., '^"|"$', '') removes leading and trailing double quotes from each field. The original regexp_substr already handles fields with commas inside quotes, so this just cleans up the quote marks without breaking valid values.

3. Error Handling

   • Added an exception block to guarantee the file is closed if any error happens (like invalid data or missing file), preventing locked files that can cause future import failures.

Quick Notes #

• Update v_expected_header if your test table has different column names or a different order.
• If your CSV has fields with embedded newlines, utl_file.get_line won't handle that—you'd need to use utl_file.get_raw to parse multi-line fields, but that's a more niche edge case.
• Double-check that your Oracle user has UTL_FILE privileges on the directory specified in p_FileDir.

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