Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23

    Unanswered: dynamically enter text filename to load

    Is there a way to dynamically enter a text filename to load to database?i mean with a prompt screen to enter the filename or from command line as a parameter.
    Last edited by subwrc; 01-12-04 at 18:57.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    yes

  3. #3
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    could you tell me the way?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What have you tried already & what were the results?

    For which school, class & instructor are you doing this homework assignment?

  5. #5
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Originally posted by anacedent
    What have you tried already & what were the results?

    For which school, class & instructor are you doing this homework assignment?
    Does it makes difference if i tell you?
    I checked sql*loader but can't change the filename inside the control file dynamically.
    I have also in mind UTL_FILE and PRO*C but haven't started something yet.
    I would like to know if there is a better and easier way that i don't know and you could tell me to start working with it.I don't wannt to begin something and see afterdays that it can't be done this way.

  6. #6
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: dynamically enter text filename to load

    UTL_FILE
    With the UTL_FILE package, your PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

    UTL_FILE I/O capabilities are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitations. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any output and free resources associated with the file.


    --------------------------------------------------------------------------------
    Note:
    The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned using PL/SQL exceptions.

    http://download-west.oracle.com/docs...e.htm#ARPLS069
    Joel Pérez

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    We often learn more by the mistakes we make then we do with our successes.

    I applaud you for taking the time to check the doc. Are you trying to do all of this from within Oracle or is OS interaction expected. What is your OS? My advice which I gave in a different thread on the same topic would be to dynamically create a SQL*Loader control file. Your would use a base control file and prepend your INFILE line. In Shell language something akin to:

    controlfile="filename"
    echo "file name: \c"
    read filename
    echo "IFILE $filename" > $controlfile
    cat basecontrolfile >> $controlfile

    Some of my syntax might be wrong but it would be enough to get you going.

    Have you thought of defining your data files as external tables and sucking in the data that way?

Posting Permissions

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