CODEWITHSUNDEEP
oracle-databaseoracle11g
supertonsky
supertonsky

Reputation: 2733

How do I query the filenames in an Oracle logical directory created using CREATE DIRECTORY?

How do I query using SQL or PLSQL so that I can get the filenames in an Oracle logical directory created using CREATE DIRECTORY?

Suppose the following script is executed to create the directory:

create or replace directory my_directory as '/var/tmp/mydirectory';

Using SQL or PLSQL, how do I get the filenames of the files in directory my_directory? If the directory contains the files my_file_1.txt and my_file_2.txt, I should get 2 rows.

I don't need the files' content but just the filenames.

Upvotes: 0

Views: 872

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Unfortunately, there isn't a really easy PL/SQL API for this. Some day, this will be in the utl_file package.

One option is to write a small Java stored procedure that populates a temporary table. Here is an AskTom example of a Java stored procedure that lists the files in a directory.

There is also a trick using the dbms_backup_restore package but that requires you to query an x$ table which is technically undocumented. Here is an example of using dbms_backup_restore to list files in a directory.

Personally, the Java stored procedure strikes me as easier to read, easier to secure, and more likely to work consistently moving forward. But if you want a pure PL/SQL solution, you can look at dbms_backup_restore.

Upvotes: 3

Related Questions