SQLLDR默认字符长度及其修复方法

我认为它们与内部相同,这也是缓冲区大小。

/********************************************************************************************************
    Name:       GEN_CTL_FILE

    Desc:       Generates a skeleton control file for loading data via SQL*Loader.

    Args:       tablename_in IN VARCHAR2, delim_in IN VARCHAR2 DEFAULT '|'

    Returns:    None.

    Usage:      utilities.gen_ctl_file('tablename');

    Notes:      Prints a skeleton control file.

                If a template for a fixed-length data file is desired, use 'FIXED' for the delim_in string.
                FIXED needs more work to put actual lengths in.  For now just placeholders.  

                Example usage:

                set serveroutput on;
                execute utilities.gen_ctl_file('test', utilities.TAB);

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1         6/6/2013    gary_w          - Created procedure.
   1.2         10/8/2013   gary_w          - Fixed decode statement. 
                                           - Added option to generate a fixed-length template.
   ************************************************************************************************************************/
  PROCEDURE GEN_CTL_FILE(tablename_in IN VARCHAR2, delim_in VARCHAR2 DEFAULT '|') IS
    ERRNULLTABLENAME     CONSTANT NUMBER        := -20103; -- User-defined error numbers and messages.
    ERRNULLTABLENAMEMSG  CONSTANT VARCHAR2(100) := 'A table name is required.';
    USAGE                CONSTANT VARCHAR2(100) := '*   USAGE: UTILITIES.GEN_CTL_FILE(tablename_in IN VARCHAR2, fieldsep_in VARCHAR2 DEFAULT ''|'')';
    v_delim                       VARCHAR2(20)  := NVL(delim_in, '|');

    CURSOR COL_CUR  IS
      SELECT COLUMN_NAME, 
      DECODE(COLUMN_ID, 1, ' ', ',') || RPAD(COLUMN_NAME, 32) || case upper(v_delim)
        when 'FIXED' then 'POSITION(99:99) '
        else NULL
      end|| DECODE(DATA_TYPE,
             'VARCHAR2', 'CHAR('||DATA_LENGTH||') NULLIF(' || COLUMN_NAME || '=BLANKS)',
             'FLOAT', 'DECIMAL EXTERNAL NULLIF(' || COLUMN_NAME || '=BLANKS)',
             'NUMBER', DECODE(                                                 DATA_PRECISION,
             0, 'INTEGER EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)',
             DECODE(DATA_SCALE, 0, 'INTEGER EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)', 'DECIMAL EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)')),
             'DATE', 'DATE "MM/DD/YYYY" NULLIF (' || COLUMN_NAME || '=BLANKS)',
             data_type)
               AS COL_DATA
      FROM  USER_TAB_COLUMNS
      WHERE TABLE_NAME = UPPER(tablename_in)
      ORDER BY COLUMN_ID;

  BEGIN

    IF tablename_in IS NULL THEN
      RAISE_APPLICATION_ERROR(ERRNULLTABLENAME, ERRNULLTABLENAMEMSG || CR || USAGE);
    END IF;

    DBMS_OUTPUT.PUT_LINE('--');
    DBMS_OUTPUT.PUT_LINE('-- NOTE - When using DIRECT=TRUE to perform block inserts to a table,');
    DBMS_OUTPUT.PUT_LINE('--        the table''s triggers will not be used! Plan accordingly to');
    DBMS_OUTPUT.PUT_LINE('--        manually perform the trigger actions after loading, if needed.');
    DBMS_OUTPUT.PUT_LINE('--');
    DBMS_OUTPUT.PUT_LINE('OPTIONS (DIRECT=TRUE)');
    DBMS_OUTPUT.PUT_LINE('UNRECOVERABLE');
    DBMS_OUTPUT.PUT_LINE('LOAD DATA');
    DBMS_OUTPUT.PUT_LINE('APPEND');
    DBMS_OUTPUT.PUT_LINE('INTO TABLE ' || UPPER(tablename_in));
    DBMS_OUTPUT.PUT_LINE('EVALUATE CHECK_CONSTRAINTS');
    if upper(v_delim) != 'FIXED' then
      DBMS_OUTPUT.PUT_LINE('FIELDS TERMINATED BY ' || '''' || v_delim || '''');
      DBMS_OUTPUT.PUT_LINE('OPTIONALLY ENCLOSED BY ''"'' ');
      DBMS_OUTPUT.PUT_LINE('TRAILING NULLCOLS');
    end if;
    DBMS_OUTPUT.PUT_LINE('(');

    -- The cursor for loop construct implicitly opens and closes the cursor.
    FOR COL IN COL_CUR
    LOOP
      IF COL.COLUMN_NAME != 'LOAD_DATE' THEN
        IF COL.COLUMN_NAME = 'LOAD_SEQ_ID' THEN
          dbms_output.put_line(','||RPAD('LOAD_SEQ_ID', 32)||'CONSTANT 0');
        ELSE
          DBMS_OUTPUT.PUT_LINE(COL.COL_DATA);
        END IF;
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(')' || CHR(10));

  EXCEPTION
    WHEN OTHERS THEN
      RASIE;
  END; -- GEN_CTL_FILE

--
-- NOTE - When using DIRECT=TRUE to perform block inserts to a table,
--        the table's triggers will not be used! Plan accordingly to
--        manually perform the trigger actions after loading, if needed.
--
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
APPEND
INTO TABLE TEST
EVALUATE CHECK_CONSTRAINTS
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 COLA                            CHAR(200) NULLIF(COLA=BLANKS)
,COLB                            CHAR(100) NULLIF(COLB=BLANKS)
,COLC                            CHAR(500) NULLIF(COLC=BLANKS)
,COLD                            DECIMAL EXTERNAL NULLIF (COLD=BLANKS)
,COLE                            CLOB
)

如果您对其进行了调整,请分享您的更改。