Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Question Unanswered: SQLloader - create controlfile - variable import directory - HELP

    Hello,

    sorry, but I'm a Oracle Newbie.

    I have a big problem.
    We have to write a "UNIX shell script" (for an external company) which loads about 5 - 7 000 000 data a day, and this importjob will happen every 1 or 2 hours.

    Every hour they (external company) generate a txt file - with a cronjob the data will loaded in a table - and check if data from this file already exists and...

    1. but MY big problem is - I don't know how I can "store" the value from the config table (select cfg_value from config where blabla...) -
    and "use" this variable in the sqlloader - control file for the data directory.

    2. Is it possible - and the next problem is, that I don't know exactly the name of the file...
    - in the import directory is the file which is renamed after the import -

    3. how must i create the sqlloader control file?


    please help me....
    thanks a lot.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Can you be a little more clear on your problem?

    Are you saying you need to create the control-file on the FLY
    each time there is a new load?

    Or are you saying something else entirely?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    OR, are you asking how you will determine the filename that you
    want to laod?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2003
    Posts
    14
    Hi Duck,

    I think i have to create the control-file on the fly.
    Sorry i don't know - I am a completely Oracle newbie. Just a friend of mine told me about the sqlloader.

    And i spent a few days reading documentations and googling.

    Now i tried to write a "unix script" - at least an attempt - which should select the "importdirectory" value from the config table (some paramters are stored in this table; these values are editable by the admin) where the import data file (the filename is not defined and will never be defined) can be found.
    that's

    and then i don't know...exactly what to do....
    ...
    Last edited by ppetra74; 09-15-04 at 12:12.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by ppetra74
    Hi Duck,
    Now i tried to write a "unix script" - at least an attempt - which should select the "importdirectory" value from the config table (some paramters are stored in this table; these values are editable by the admin) where the import data file (the filename is not defined and will never be defined) can be found.
    that's

    and then i don't know...exactly what to do....
    ...
    you can create the file on the fly. That is the easy part.
    I am confused by the above paragraph.
    Can you clarify and post an example of what you would be getting from
    that directory?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Posts
    14
    ok...in the database (this is an oracle based appliaction) is a config table - in this config table is a parameter that contains the directory where the import data file is.
    e.g. /applicationtest/oracle/import (or whatever) - in this directory is the data file to import - NameOfTheFile.csv

    I tried to write a unix script -
    and started the sqlplus - and made a select statement to find out where the datafile is (select config_value from config where id=10)
    but then???
    how can i load the data in the table?
    i must tell the control file where my data import file(s) is/are)


    1. find out in which directory the data import file is (is defined in config table)
    2. load data in table (control file???)


    (The admin can edit config values any time for any reason.)



    My problem is, that i don't know how i to tell the sqlloader where the import directory is...

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    A variable file name is easy enough in a shell script, e.g:
    Code:
    data_dir=/some/derived/value
    data_file=name_of_file.csv
    
    sqlldr user/pass control=fixedcontrolfile.ctl data=${data_dir}/${data_file}
    If it was me, I would first see whether this could be achieved using External Tables (requires 9i). Perhaps a trigger on the config table could issue an ALTER TABLE to change the external table's file location. This would keep everything within the database.
    Last edited by WilliamR; 09-16-04 at 03:47.

  8. #8
    Join Date
    Nov 2003
    Posts
    14
    thanks 4 your advice.

    But can you give me a detailed example - sorry, but I'm completely new to Oracle.
    I've read a lot of docs and forums, but.....

    ...

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    check tyhis shit out.
    I got this awesome script from our very own Gregg! (thanks Gregg!)
    I modified it for your purposes. You can create a controlfile ON THE FLY
    which you can SPOOL and then have sqlloader call the spool file.

    Example is below, please ask questions if you are confused.
    PHP Code:
    SQLdesc test_a
     Name                          Null
    ?    Type
     
    ----------------------------- -------- -------------
     
    LOCATION                               VARCHAR2(50)
     
    FILENAME                               VARCHAR2(50)

    SQLselect from test_a;
    LOCATION                  FILENAME
    ------------------------- ------------------
    /
    u01/oracle/home/import   data_load.csv


    SQL
    SELECT 
      2  
    'LOAD DATA'||chr(10
      
    3  ||'INFILE '''|| location ||'/'|| filename ||''''||chr(10
      
    4  ||'BADFILE '''|| location ||'/'|| filename ||'.bad'' '||chr(10
      
    5  ||'DISCARDFILE '''|| location ||'/'|| filename ||'.dis''  '||chr(10
      
    6  ||'APPEND'||chr(10
      
    7  ||'INTO TABLE TABLE_I_AM_LOADING'||chr(10
      
    8  ||'FIELDS TERMINATED BY '','' optionally enclosed by ''"'' '||chr(10
      
    9  ||'TRAILING NULLCOLS'||chr(10
     
    10  ||'('||chr(10
     
    11  ||'COLUMN1            CHAR,'||chr(10
     
    12  ||'COLUMN2     constant ''5M'','||chr(10
     
    13  ||'COLUMN3              CHAR,'||chr(10
     
    14  ||'COLUMN4              CHAR,'||chr(10
     
    15  ||'COLUMN5     constant ''NEISO'''||chr(10
     
    16  ||')' 
     
    17  FROM test_a

    LOAD DATA
    INFILE 
    '/u01/oracle/home/import/data_load.csv'
    BADFILE '/u01/oracle/home/import/data_load.csv.bad'
    DISCARDFILE '/u01/oracle/home/import/data_load.csv.dis'
    APPEND
    INTO TABLE TABLE_I_AM_LOADING
    FIELDS TERMINATED BY 
    ',' optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    COLUMN1            CHAR,
    COLUMN2     constant '5M',
    COLUMN3              CHAR,
    COLUMN4              CHAR,
    COLUMN5     constant 'NEISO'

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Nov 2003
    Posts
    14

    Thumbs up

    THANKS DUCK & Gregg!!!! :-)))

    that was really very helpful - but I do have some other questions/problems.

    1. I don't know the exact filename, because an external application generates the input datafile every hour. I looks like YYYYMMDD_SOMETHING_ELSE.cdrb.
    -->This datafile is loaded every hour into the database (cronjob).

    2. and how can i create the table? (create after midnght via cronjob??)
    get the actual date and create the table- does it make sense??? what do you think??

    tablename: CDR_YYYYMMMDD
    e.g. for 25 August: CDR_20040825
    and for 26 August: CDR_20040826

    3. I am not sure about the

    - INSERT INTO TABLE <tablename> - statement in the controlfile ???
    do i have to create a controlfile every day with the cuurent tablename??


    Thanks for your help!!!!!!!!


    (in my input datafile looks like this:
    20040826;........;......;..)

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Whoa whoa whoa!
    You never said you need to CREATE the table each day!
    Is this the case?

    Below are the rules as I have read them.

    Rule #1:
    You stated the TABLE name could change ANY day. Thus, we MUST
    select from the config table to get the tablename we are loading the data into.

    Rule #2:
    You stated the path to the file could be different ANY day. Thus, we
    must select the PATH from the config table before each load.

    Rule #3:
    You stated you don't know the filename. The file name
    is based on the DATE/DAY, but if you have NO WAY of knowing the
    FULL filename you are SOL.

    UNLESS:
    Since the filename is based on YYYYMMDD_SOMETHING_ELSE.cdrb
    AND you have the path to the file you can do a "find" in a shells script
    to get the filename as a parameter and pass it into the sql*loader
    commandline


    TO be honest, WHY THE HELL are they making it THIS complicated
    for you?? (don't answer that, just shoot them)

    ALSO:
    If you have a cronjob that is getting the file every hour, then I am
    assuming this cronjob calls a shell-script which gets the file VIA ftp.
    If it gets the file via FTP, then it knows the filename. HOW does it know
    what file to get???
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Nov 2003
    Posts
    14
    1. sorry, i forgot to tell you that a new table is created eyery day.
    i'm not sure how to create this table...
    ...my idea was exectuing a procedure...but how can i set the correct table name??
    is it possible passing the "right" tablename the sqlloader controfile? and how?

    sorry...for my silly questions...

    2. no FTP
    because of the security guidelines no FTP is allowed.
    The file is copied to the directory (named in the conf table) - (that's the admin's business) - and imported in the "current" table.

    After the import, the imported file is removed to another directory. if everything is fine, there's just 1 File before the IMPORT process (every hour).


    Thanks a lot for your patience and help!!!!
    :-))

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    how do you know what the columns and precision of the new table are?

    you might want to consider creating a DBMS_JOB that creates an
    external table for you every hour which would point the specific
    directory and filename that the file is located at on the host server.

    Read up on EXTERNAL tables. It might be easier that way.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Jan 2009
    Posts
    5

    need to read a data file name in sql loader

    Hi,

    I also have same problem. I know file name.

    I have two table

    1) ihtload :- we need to load all data in this table via sql loader.
    2) filedetails :- in this table we need to load file name and file processing time that are using in sql loader as data file.

    But i don't how i will read data file name in sql loader and load in file details table with time.

    I am using sql loader in a UNIX shell script. and we load many file every day with different filename.

    Ex:-

    filename:- abc_20092303234506.dat

    I am using this filename in command line:-

    sqlldr username/password control=iht.ctl data=abc_20092303234506.dat

    1) Can we load this file name and processing time in filedetails table with using the same sqlloader?
    2) If no then please suggest me any other way for reading this file name and loading in table.

    Thanks&Regards

Posting Permissions

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