8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
List Files in a Directory From PL/SQL and SQL : Comparison of Methods
This article compares the methods available to list files in a directory on the database server.
They are presented in my preferred order, but all are viable alternatives. This article includes the pros and cons of each method, but these are also included in the articles themselves.
External Table
This method is described here.
Pros of this method are.
- Doesn't require too much initial setup.
- Doesn't give additional access to the files. It just lists them.
- Has a control mechanism for what directories can be accessed.
- Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
- Gives addition details about the files, not just the file names. We can tailor this further by altering the pre-processor script if we want.
Cons of this method are.
- Does require some initial setup on the file system.
Java
This method is described here.
Pros of this method are.
- Doesn't require too much initial setup.
- Doesn't give additional access to the files. It just lists them. This assumes we've set the Java permissions correctly.
- Has a control mechanism for what directories can be accessed. This assumes we grant the Java permissions on a per-directory basis.
- Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
- Doesn't require initial setup on the file system.
Cons of this method are.
- We don't get any additional details about the files. Just the file name. The Java API allows us to gather more information, but that would be extra code.
- We are using Java in the database. This is not available from old XE releases. There is nothing wrong with using Java in the database, but some people prefer to avoid it.
DBMS_BACKUP_RESTORE
This method is described here.
Pros of this method are.
- Doesn't give additional access to the files. It just lists them.
- Can list files from any directory on the DB server that is accessible to the "oracle" OS user.
Cons of this method are.
- We have to create objects in the SYS schema. This is not a great idea.
- The
DBMS_BACKUP_RESTOREpackage is not documented, so it is not officially supported for our use. It's an internal package used by the database. - We don't get any additional details about the files. Just the file name.
- We have no control over the directories that are searched unless we code in some additional checks.
DBMS_SCHEDULER
This method is described here.
Pros of this method are.
- Can list files from any directory on the DB server that the user credential has access to.
- Assuming you have created the pipelined table function, it's available from SQL and PL/SQL with no extra work.
- Gives addition details about the files, not just the file names. We can tailor this further by altering the pre-processor script if we want.
Cons of this method are.
- There is some setup required on the file system of the database server.
- There is a lot of additional code compared to some of the alternative methods, so there is more scope for something to fail.
- If the scheduler is disabled, this method stops working.
For more information see:
- List Files in a Directory From PL/SQL and SQL : External Table
- List Files in a Directory From PL/SQL and SQL : Java
- List Files in a Directory From PL/SQL and SQL : DBMS_BACKUP_RESTORE
- List Files in a Directory From PL/SQL and SQL : DBMS_SCHEDULER
Hope this helps. Regards Tim...