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

Posted on 2005-04-20
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
    LVL 8

    Author Comment

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

    LVL 25

    Expert Comment

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

    Expert Comment

    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.
    LVL 25

    Expert Comment

    For maximum points, I'm POSITIVE you can't do it :)
    LVL 4

    Accepted Solution

    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.
    LVL 6

    Assisted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now