1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

We Can Import any text data file into a oracle table

Discussion in 'Other Development Tools' started by sabyasachi, Oct 12, 2009.

  1. sabyasachi

    sabyasachi Active Member

    Messages:
    6
    Likes Received:
    2
    Trophy Points:
    65
    To Import Text formated data into oracle table we have to create a table and a control file according to the text file.
    textfile:-
    10001 20056 12122009
    10002 20036 12112009
    10002 20236 12122009
    10002 20776 12112009
    10002 20346 12112008

    Rename the Text file "textfilename.dat".

    1st 5 char are employee number,then 1 space
    2nd 5 char are salary,then 1 space
    3rd 8 char are date of joining.

    Creating oracle table:-
    Create table emp(empno varchar2(5), salary number, doj date);

    Create Control File:-
    LOAD DATA APPEND INTO TABLE EMP(
    EMPNO POSITION(1 : 6) CHAR,
    SALARY POSITION(1 : 6) CHAR,
    DOJ POSITION(1 : 6) CHAR
    )

    Save the file with extention ".ctl"

    How To Run The Control File :-

    From Linux/Unix-
    Open Terminal.
    $>$ORACLE_HOME/bin/sqlldr userid= userid/passwd@sid data=textfilename.dat
    control=controlfilename.ctl log=/home/oracle/Desktop/error.log bad=/home/oracle/Desktop/error.bad

    From Windows-
    Open Command Prompt.
    C:\>$ORACLE_HOME/bin/sqlldr userid= userid/passwd@sid data=textfilename.dat
    control=controlfilename.ctl log=/home/oracle/Desktop/error.log bad=/home/oracle/Desktop/error.bad

    You can change the log and bad file name and path.No need to commit.

    <Please keep your blog link in the signature>
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is not news; Oracle has provided SQL*Loader for years and the method for loading data from flat files in this manner was documented early on in the history of the utility. Why are you not providing examples of external tables, a newer feature of Oracle? This same task can be accomplished with external tables as illustrated below:

    Code (SQL):
    CONNECT / AS sysdba
    CREATE OR REPLACE DIRECTORY admin_dat_dir
        AS '/u01/data/smiggle/data';
    CREATE OR REPLACE DIRECTORY admin_log_dir
        AS '/u01/data/smiggle/log';
    CREATE OR REPLACE DIRECTORY admin_bad_dir
        AS '/u01/data/smiggle/bad';
    GRANT READ ON DIRECTORY admin_dat_dir TO bing;
    GRANT WRITE ON DIRECTORY admin_log_dir TO bing;
    GRANT WRITE ON DIRECTORY admin_bad_dir TO bing;
    -- hr connects
    CONNECT ying/yong
    -- create the external table
    CREATE TABLE admin_ext_employees
                       (empno             VARCHAR2(5),
                        salary            NUMBER(8,2),
                        doj                DATE
                       )
         ORGANIZATION EXTERNAL
         (
           TYPE ORACLE_LOADER
           DEFAULT DIRECTORY admin_dat_dir
           ACCESS PARAMETERS
           (
             records delimited BY newline
             badfile admin_bad_dir:'textfilename%a_%p.bad'
             logfile admin_log_dir:'textfilename%a_%p.log'
             FIELDS TERMINATED BY ','
             missing FIELD VALUES are NULL
             ( employee_id, first_name, last_name, job_id, manager_id,
               hire_date CHAR date_format DATE mask "dd-mon-yyyy",
               salary, commission_pct, department_id, email
             )
           )
           LOCATION ('textfilename.dat')
         )
         PARALLEL
         REJECT LIMIT UNLIMITED;
     
    You now have an external table using the data in textfilename.dat.

    I don't find any additional 'details' on this topic at allmyneed.blogspot.com; perhaps I've missed something?
     
  3. Abhinav Chandra

    Abhinav Chandra Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Hi,

    In my case I have to load a text file into database. But, situation is I have to create destination table and control file for sql loader dynamically since the column names are not same all the time. So, through the normal process I cant create the destination table in advance and also cant create control file mentioning the column names. Can you please advice on this?