Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered:

    i couldnt see the answer

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by aruncse30
    i couldnt see the answer
    And your question was?

  3. #3
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by shammat
    And your question was?
    oops.. "SQL*Loader: insert data file name?" this was the question raised by someone. I have also wanted to know this..

    How to load a filename into a table in sql loader?

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    *scratches head*

    SQL*Loader FAQ - Oracle FAQ

  5. #5
    Join Date
    Aug 2009
    Posts
    5
    I guess I didnt explain my doubt clearly.

    Im calling a ctl file from a bat file.

    sqlldr /@test control=C:\test.ctl log=C:\test.log bad=C:\test.bad silent=feedback data = 'c:\test.txt'

    test.ctl

    LOAD DATA
    APPEND
    INTO TABLE test
    ( filename <<here I need the file name to be stored. i.e. test.txt>>)

    I dont want to use INFILE in ctl file since I would be calling this ctl file from many places for different files

    How to do this? Can somebody help me out?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I still don't understand this. Do you want to load the contents of the file into a column in the target table?

    If the answer is yes, then have a look at the FAQ posted by artacus72, (Question 15)

  7. #7
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by shammat
    I still don't understand this. Do you want to load the contents of the file into a column in the target table?

    If the answer is yes, then have a look at the FAQ posted by artacus72, (Question 15)
    No Mate. I want the file name to be stored in the target table.

    i.e. the data file here is test.txt and this name should be stored in the target table.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by aruncse30
    No Mate. I want the file name to be stored in the target table.

    i.e. the data file here is test.txt and this name should be stored in the target table.
    Hmm, that would mean the filename will be stored for every record of the input file. But I guess that's what you want.

    You could "generate" the control file dynamically using a batch file:
    Code:
    echo LOAD DATA>test.ctl
    echo APPEND>>test.ctl
    echo INTO TABLE test>>test.ctl
    echo (>>test.ctl
    echo filename CONSTANT '%1',>>test.ctl
    echo .... rest of control file ...
    
    sqlldr /@test control=test.ctl log=test.log bad=test.bad data='%1'
    Then you can call that batch file using run_loader test.txt

  9. #9
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by shammat
    Hmm, that would mean the filename will be stored for every record of the input file. But I guess that's what you want.

    You could "generate" the control file dynamically using a batch file:
    Code:
    echo LOAD DATA>test.ctl
    echo APPEND>>test.ctl
    echo INTO TABLE test>>test.ctl
    echo (>>test.ctl
    echo filename CONSTANT '%1',>>test.ctl
    echo .... rest of control file ...
    
    sqlldr /@test control=test.ctl log=test.log bad=test.bad data='%1'
    Then you can call that batch file using run_loader test.txt
    Thanks for this, i though about this one, but it would not work in my case.
    I would be loading the header record of 100 different files into this table.

    i.e FileName = test1.txt, FileHdr = 'ihopklk'
    i.e FileName = test2.txt, FileHdr = 'ihsdsdsd'
    i.e FileName = test3.txt, FileHdr = 'ihopas'

    I already have 100 bat files which would call the same ctl file for 100 different files. I could not create dynamic ctl in this case. (not recommended in my proj). So is there any way I could load the filename into the targer table by calling some default variables in ctl file?

  10. #10
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    If you actually have 100 control files it should be dead easy. You can't do variable replacement in the control file, so you will have to generate it dynamically. In your loop, write the contents of the ctl file then call sqlldr.

    I don't have a clue how to do it in a batch file, but unix shell scripting or python could handle it quite well.
    Last edited by artacus72; 08-04-09 at 15:07.

Posting Permissions

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