Variables in SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Variables in SQL Loader

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Variables in SQL Loader

    Hi All,
    Can anyone kindly give me an approach to use a variable in a sql loader ctl file. I am trying to add the value before each insert of row and this value is the file name. So the question is how can I dynamically identify the input data file name, if not, is there a way I can make my SQL Loader to insert a value (file name) before each row into the table.
    Regards,
    Aj

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can always specify a "constant" value in your sql loader control file.

    If you want to make that constant a variable depending on the session you may want to explore creating the control file on the fly; your process build your control file dynamically then executes sql loader pointing to your newly created control file.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Dear PAVB, I appreciate you response. I never though we could declare variables/constants (or I must say I never tried declaring variables/constants) in a SQL Loader Control File. I will do some research on it. I would really appreciate if you could forward me any links/document/sample code that might help my research. Thank you, Aj

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That's the beauty of Oracle documentation; allows you to master each piece of the technology, it's easily available and free!

    You may want to start here... http://download.oracle.com/docs/cd/B...dr.htm#i436326
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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