以下代码基于 AskTom 的热门话题，Export to CSV

我确实在内部循环中使用一些代码修改了原始脚本，以处理嵌入的逗号和引号字符。

我无法让它在 Oracle 8i 上编译。

我使用 DBA Studio 设置 utl_file_dir 的值。然后我使用 SQL*Worksheet 运行脚本。报错说明第46行是问题所在。

    CREATE OR REPLACE PROCEDURE dump_table_to_csv 
            ( p_tname IN VARCHAR2, 
            p_dir   IN VARCHAR2, 
            p_filename IN VARCHAR2 ) 
    IS 
 
    l_output        utl_file.file_type; 
    l_theCursor     INTEGER DEFAULT dbms_sql.open_cursor; 
    l_columnValue   VARCHAR2(4000); 
    l_status        INTEGER; 
    l_query         VARCHAR2(1000) DEFAULT 'select * from ' || p_tname; 
    l_colCnt        NUMBER := 0; 
    l_separator     VARCHAR2(1); 
    l_descTbl       dbms_sql.desc_tab; 
    l_quote    VARCHAR2(1); 
 
    BEGIN 
            l_output := utl_file.fopen( p_dir, p_filename, 'w', 32760 ); 
            -- adjust date so the format is compatible with the target system 
            -- in this case, PostgreSQL 9 
            EXECUTE IMMEDIATE 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' '; 
            -- the original version used the "rr" moniker for year. 
            -- execute immediate 'alter session set nls_date_format=''rr-mm-dd hh24:mi:ss'' '; 
 
            -- set up first row with column names 
            dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native ); 
            dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 
 
            FOR i IN 1 .. l_colCnt LOOP 
                    utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' ); 
                    dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 ); 
                    l_separator := ','; 
            END LOOP; 
 
            utl_file.new_line( l_output ); 
            l_status := dbms_sql.EXECUTE(l_theCursor); 
 
            -- iterate through the data, for each row check each column 
            WHILE ( dbms_sql.fetch_rows(l_theCursor) > 0 ) LOOP 
                    l_separator := ''; 
 
                    FOR i IN 1 .. l_colCnt LOOP 
                            dbms_sql.column_value( l_theCursor, i, l_columnValue ); 
                            -- 
                            -- if the separator or quote is embedded in the value then enclose in double-quotes 
                            IF INSTR(l_columnValue, ',') != 0 OR INSTR(l_columnValue, '"') THEN 
                                    l_quote := '"'; 
                                    -- double any/all embedded quotes 
                                    l_columnValue := REPLACE(l_columnValue,'"','""'); 
                            ELSE 
                                    l_quote := ''; 
                            END IF; 
                            l_columnValue := l_separator || l_quote || l_columnValue || l_quote; 
                            -- 
                            -- write the value to disk 
                            utl_file.put( l_output, l_separator || l_columnValue ); 
                            l_separator := ','; 
                    END LOOP; 
 
                    utl_file.new_line( l_output ); 
            END LOOP; 
 
            dbms_sql.close_cursor(l_theCursor); 
            utl_file.fclose( l_output ); 
 
            EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' '; 
 
    EXCEPTION 
            WHEN OTHERS THEN 
                    EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-yy'' '; 
                    RAISE; 
    END; 

请您参考如下方法:

第 46 行中的第二个 instr() 调用缺少 != 0:

if instr(l_columnValue, ',') != 0 or instr(l_columnValue, '"') then 

应该是:

if instr(l_columnValue, ',') != 0 or instr(l_columnValue, '"') != 0 then