[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL LOADER - File name needs to be inserted into oracle table

Posted on 2005-04-20
Medium Priority
Last Modified: 2008-01-09
I have a control file for sql loader but I have one last problem. One of the columns I am inserting into I want as the file name. I can do thise fine using the CONSTANT keyword but I would like a way of automatically doing this so I don't have to edit the file each time I run the script.

Same as this question, but I need a POSITIVE answer ( not involving scripts etc)
Maximum points -))

Question by:rajaloysious

Author Comment

ID: 13826114
Further details:
I am running Oracle 9i version and the environment is Sun Solaris

LVL 25

Expert Comment

ID: 13827043
If the file name changes, don't you already have to edit the file to specify a new INFILE?
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13828891
As far as I know, that ability to specify a variable file name for SQL*Loader has been a requested enhancement for some years and Oracle versions, but its not here yet.  That may not be the answer you were hoping for, but I don't think there is a way to do this yet.  One of my reasons for posting this comment is so that I will be notifed by e-mail of other comments posted here, so if someone has found a way to do this, I can learn too.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 25

Expert Comment

ID: 13829002
For maximum points, I'm POSITIVE you can't do it :)

Accepted Solution

schubach earned 1000 total points
ID: 13832340
I don't think this is what you want either, but you coud use the UTL_FILE package in PL/SQL instead of SQL*Loader.  Then you could specify the file you want to load as a parameter to the PL/SQL and insert it into a table using the &1 variable.

Assisted Solution

morphman earned 1000 total points
ID: 13836180
Ok, how about this:-

Create a plsql function that will read current/open/latest etc... filename from your directory.

Then, in your sql*loader file, just call the function as a standard SQL operator as a normal SQL mask.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question