11 Replies Latest reply: Jul 15, 2010 3:35 PM by 571956 RSS

    Dynamic External Table

    571956
      We have multiple clients who each have their own file directory. A stored procedure will be called to loop through all the directories. Each directory has multiple files in it. The procedure needs to process all the files in those directories.

      So do I mount each of those files as an external table? If so, how do you create an external table who's DIRECTORY and LOCATION need to be dynamic?

      Help!!
        • 1. Re: Dynamic External Table
          Hoek
          Regarding LOCATION, you could alter that dynamically:

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37593123416931
          • 2. Re: Dynamic External Table
            Herald ten Dam
            Hi,

            and if you want to change the directory, you can change the point to which the directory points with an 'create or replace directory'

            Herald ten Dam
            http://htendam.wordpress.com
            • 3. Re: Dynamic External Table
              571956
              Excellent! Thank you very much.

              But now that I can alter the directory and location, how can I pull the names of all the files in the directory to use in the location?
              • 4. Re: Dynamic External Table
                MScallion
                ivalum21 wrote:
                Excellent! Thank you very much.

                But now that I can alter the directory and location, how can I pull the names of all the files in the directory to use in the location?
                Do you need to know which file the data came from or do you simply want all of the data in a single query?

                If it is the latter then you can list all of the files from all of the directories, or all of the files for 1 directory, then another, or whatever combination works for your business case;

                Multiple sample directories;
                create or replace directory my_dir1 as 'c:\temp\dir1';
                 
                Directory created
                create or replace directory my_dir2 as 'c:\temp\dir2';
                 
                Directory created
                The table;
                create table ext_t (
                  col1 varchar2(40)
                )
                organization external
                   (
                   type oracle_loader
                   default directory my_dir1
                   access parameters
                      (records delimited by newline
                       fields
                          terminated by ','
                          optionally enclosed by '"'
                          missing field values are null
                      )
                   location (my_dir1:'file1.txt',my_dir1:'file2.txt',my_dir2:'file3.txt')
                );
                 
                Table created
                select * from ext_t;
                 
                COL1
                ----------------------------------------
                line 1 of dir1.file1
                line 2 of dir1.file1
                line 1 of dir1.file2
                line 2 of dir1.file2
                line 1 of dir2.file3
                line 2 of dir2.file3
                {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                • 5. Re: Dynamic External Table
                  Herald ten Dam
                  Hi,

                  see asktom for getting a list from the file system, it is using Java: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584

                  Herald ten Dam
                  http://htendam.wordpress.com
                  • 6. Re: Dynamic External Table
                    571956
                    I would like all the data in a single query. The problem is, I won't know the names of the files. So in your example you have them hardcoded, is there a way I can include them all like this, but do it dynamically?
                    • 7. Re: Dynamic External Table
                      MScallion
                      ivalum21 wrote:
                      I would like all the data in a single query. The problem is, I won't know the names of the files. So in your example you have them hardcoded, is there a way I can include them all like this, but do it dynamically?
                      There are a few different ways to approach this. If you search this forum for 'directory list files' or similar you will get some suggestions.

                      For a PL/SQL solution you could try generating a directory list using DBMS_SCHEDULER then parsing through it for file names, similar to this post; Re: How to load all files in a directory into Oracle table
                      • 8. Re: Dynamic External Table
                        571956
                        Am I even able to dynamically run a create or replace directory statement in a PL/SQL block?

                        Like this...

                        DECLARE
                        directory_name VARCHAR2(20) ;
                        BEGIN
                        directory_name := 'D:\' ;
                        EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY test_directory AS :1'
                        USING directory_name ;
                        END ;

                        And I get this error....

                        Error starting at line 1 in command:
                        DECLARE
                        directory_name VARCHAR2(20) ;
                        BEGIN
                        directory_name := 'D:\' ;
                        EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY test_directory AS :1'
                        USING directory_name ;
                        END ;
                        Error report:
                        ORA-01780: string literal required
                        ORA-06512: at line 5
                        01780. 00000 - "string literal required"
                        *Cause:   
                        *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                        • 9. Re: Dynamic External Table
                          MScallion
                          ivalum21 wrote:
                          Am I even able to dynamically run a create or replace directory statement in a PL/SQL block?
                          Yes you can by using dynamic SQL, however, it is considered bad practice to create and drop tables and other objects dynamically. I wouldn't recommend modifying the directory object dynamically unless you can guarantee that there are no other dependencies on that object. Certain DDL statements are deemed to be acceptable in PL/SQL, such as TRUNCATE TABLE, ALTER TABLE LOCATION;

                          You would be better off to create the table and the relevant directories once, then dynamically update the location in your stored procedure, ie;
                          begin
                             execute immediate
                                'alter table ext_t location (my_dir1:''file1.txt'',my_dir1:''file2.txt'')';
                          end;
                          /
                           
                          PL/SQL procedure successfully completed
                          select * from ext_t;
                           
                          COL1
                          ----------------------------------------
                          line 1 of dir1.file1
                          line 2 of dir1.file1
                          line 1 of dir1.file2
                          line 2 of dir1.file2
                          • 10. Re: Dynamic External Table
                            Hoek
                            What do you get returned when you execute the following query:
                            SQL> select * from v$version;
                            • 11. Re: Dynamic External Table
                              571956
                              Oracle Database 10g Release 10.2.0.4.0 - Production
                              PL/SQL Release 10.2.0.4.0 - Production
                              CORE     10.2.0.4.0     Production
                              TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
                              NLSRTL Version 10.2.0.4.0 - Production