Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: utl_file/create directory

    Hi,
    I am not sure if this is possible or if someone would have a workaround but here's my problem ..

    I need to load files from a directory located on the db server. The problem with is that all the file are located in multiple subdirectories under the same main directory. Now I know that if I create an "oracle directory" ( new utl_file dir in ora9i) as the main directory , I would not be able to see or load any files in the sub directories...
    so i've tried ...

    CREATE DIRECTORY log_dir AS '/dir/subdir1,/dir/subdir2';

    the command is successful , but when running the pl/sql procedure it doesn't seem to see the files in the second sub directory.

    Do I have to create multiple oracle directories? I really don't want to do this because of the number of sub directories , and putting all files under the same directory wont work because the file names are not unique.

    Does anyone have any ideas?

    Thanks...

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by accelmtl
    Do I have to create multiple oracle directories? I really don't want to do this because of the number of sub directories , and putting all files under the same directory wont work because the file names are not unique.

    Thanks...
    Do you have directory names in a table or do they follow some kind of pattern like subdir1 through subdir100?
    If so you maybe able to create and drop directories on the fly.
    Code:
    loop
     exit when  
    
    EXECUTE IMMEDIATE
     'CREATE DIRECTORY log_dir AS ''/dir/subdir1,/dir/subdir2'';
    
      read from it
    
    EXECUTE IMMEDIATE
     'DROP DIRECTORY log_dir AS ''/dir/subdir1,/dir/subdir2'';
    
    EXECUTE IMMEDIATE
     'CREATE DIRECTORY log_dir AS ''/dir/subdir1,/dir/subdir3'';
    
      and so on
    
    end loop;
    My way or the highway. Yeah

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    The sub directories do follow a certain pattern , they are created by date . I guess this is my only option!!!
    I'll give it a try


    Thanks .

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by accelmtl
    The sub directories do follow a certain pattern , they are created by date . I guess this is my only option!!!
    I'll give it a try


    Thanks .
    Why
    This is not all that scary.
    My way or the highway. Yeah

Posting Permissions

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