Specifying variable in SQL loader control file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Specifying variable in SQL loader control file

  1. #1
    Join Date
    Nov 2003
    Posts
    37

    Red face Specifying variable in SQL loader control file [RESOLVED]

    How can I specify the load file name in sql loader control file during runtime (as command line parameter) instead of hard coding the name in variable?
    Last edited by sbasak1; 09-02-2004 at 08:23 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    If you want it in the control file infile='1.dat'

    or on the command line data=1.dat

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002

    Re: Specifying variable in SQL loader control file

    Originally posted by sbasak1
    How can I specify the load file name in sql loader control file during runtime (as command line parameter) instead of hard coding the name in variable?
    You can dynamically create the control file at run time from the procedure that runs sql loader.

  4. #4
    Join Date
    Nov 2003
    Posts
    37
    Tried running

    sqlldr outln/outln@odsdev01 control='/data/src/xs54/database/Tesco_TicketVoucher.ctl' INFILE=/data/src/xi11/test/data/Tesco_TicketVoucher.bcp

    but says

    SQL*Loader-100: Syntax error on command-line

    Tried with both quote & without quote

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no, infile is when you want to put it in the control file

    you want data=...

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    As you were told in the first reply, you must use DATA=..., if you want to specify the input datafile from the command line! So,

    sqlldr outln/outln@odsdev01 control='/data/src/xs54/database/Tesco_TicketVoucher.ctl' DATA=/data/src/xi11/test/data/Tesco_TicketVoucher.bcp
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2003
    Posts
    37
    Thanks, it worked

    /appl/oracle/product/9.2.0/bin/sqlldr outln/outln@odsdev01 control='/data/src/xs54/database/Tesco_TicketVoucher.ctl' DATA=/data/src/xi11/test/data/Tesco_TicketVoucher.bcp BAD=/data/src/xs54/database/Tesco_TicketVoucher.bad DISCARD=/data/src/xs54/database/Tesco_TicketVoucher.dsc


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