DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql loader control files

  1. #1
    Join Date
    Sep 2000
    Posts
    7

    Lightbulb

    Does anyone have a script that will spool out a ctl file by giving a table name ? I'm looking for something that is easy and straight forward. I would appreciate any help.
    My e-mail is misticawi@aol.com

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    I don't think a table name is enough. You also need the datafile and the strucure of the data in the datafile.

  3. #3
    Join Date
    Sep 2000
    Posts
    7
    ofcourse u need the structure of the table. I'm looking for a shell script that will just ask you to enter the table name then it will do the rest.

  4. #4
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    -- =========
    --
    -- Prepares a SQL*Loader Control FILE FOR a TABLE already existing IN the
    -- DATABASE. The script accepts the TABLE name AND automatically creates a FILE
    -- WITH the TABLE name AND extension 'ctl'.
    -- DEFAULT choices FOR the FILE are AS follows (ALTER TO your needs):
    -- Delimiter: comma (',')
    -- INFILE FILE extension: .dat
    -- DATE format: 'MM/DD/YY'
    --
    -- You may define the Loader Data Types OF the other Data Types BY revising the
    -- DECODE FUNCTION pertaining TO them.
    --
    --
    -- =============
    -- Requirements:
    -- =============
    --
    -- SELECT PRIVILEGES ON the TABLE.

    REM ---------------------------------------------------------------------------
    REM EXAMPLE:
    REM SQL> start control.sql emp
    REM
    REM LOAD DATA
    REM INFILE 'EMP.dat'
    REM INTO TABLE EMP
    REM FIELDS TERMINATED BY ','
    REM (
    REM
    REM EMPNO
    REM , ENAME
    REM , JOB
    REM , MGR
    REM , HIREDATE DATE "MM/DD/YY"
    REM , SAL
    REM , COMM
    REM , DEPTNO
    REM
    REM )
    REM
    REM ---------------------------------------------------------------------------
    REM DISCLAIMER:
    REM This script is provided for educational purposes only. It is NOT
    REM supported by Oracle World Wide Technical Support.
    REM The script has been tested and appears to work as intended.
    REM You should always run new scripts on a test instance initially.
    REM --------------------------------------------------------------------------
    REM Main text of script follows:

    SET heading OFF
    SET verify OFF
    SET feedback OFF
    SET show OFF
    SET trim OFF
    SET pages 0
    SET CONCAT ON
    spool &&table_name..ctl

    SELECT
    'LOAD DATA'||CHR(10)
    ||'INFILE '''||LOWER(table_name)||'.dat'' '||CHR(10)
    ||'INTO TABLE '||table_name||CHR(10)
    ||'FIELDS TERMINATED BY ''&field_termitated_by'' '||CHR(10)
    ||'TRAILING NULLCOLS'||CHR(10)
    ||'('
    FROM user_tables
    WHERE TABLE_NAME = UPPER('&table_name');

    SELECT DECODE(ROWNUM,1,' ',' , ')||RPAD(column_name,33,' ')
    || DECODE(data_type,
    'VARCHAR2','CHAR 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/YY" NULLIF ('||column_name
    ||'=BLANKS)',NULL)
    FROM user_tab_columns
    WHERE TABLE_NAME = UPPER('&table_name')
    ORDER BY COLUMN_ID;
    SELECT ')'
    FROM sys.dual;
    spool OFF



  5. #5
    Join Date
    Sep 2000
    Posts
    7
    Highlander thanks for ur help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width