CODEWITHSUNDEEP
oracle-databasefileplsqldirectoryplsqldeveloper
Albert Rafalski
Albert Rafalski

Reputation: 19

Looking for files in a folder in PLSQL

I need to create a function in PL/SQL that will return a list of files in a folder. In the folder called "MY_SCRIPTS" there are two files: file1.sql and file2.sql Ultimately, I want to search for a list of scripts in this folder and run each one. I don't want to do it by using functions from the UTL_FILE library and providing file names explicitly. The idea is to search for them knowing only the path to the folder. Is this possible in PL/SQL?

I've been trying to do it like below, but the program returns zero records.

create type file_array as table of varchar2(100);
/
CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null)
RETURN file_array pipelined AS

lv_pattern VARCHAR2(1024);
lv_ns VARCHAR2(1024);

BEGIN
 
SELECT directory_path
INTO lv_pattern
FROM dba_directories
WHERE directory_name = '/home/oracle/Desktop/MY_SCRIPTS';

SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns);

FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
FROM X$KRBMSFT
WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
PIPE ROW(file_list.file_name);
END LOOP;

END;
/
grant execute on LIST_FILES to public;
create public synonym list_files for sys.LIST_FILES;
/
select * from table(list_files);

and like this:

DECLARE

pattern VARCHAR2(1024) := '/home/oracle/Desktop/MY_SCRIPTS';
ns VARCHAR2(1024);

BEGIN
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);

FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT WHERE FNAME_KRBMSFT LIKE '%.trc') LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;

I changed name to "directory path" and It works. Could you show me, how can I change this to make it work without hard coding?

Upvotes: 0

Views: 369

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11591

Rather than heading deep down into unsupported territory, because one day those (internal) APIs might go away, or change, or do something totally different, I'd recommend you use an external table.

The preprocessor option lets you run (any) OS command you like from a SELECT statement, so you can easily have an OS command that lists files in a directory.

select col
from   external (
      ( col varchar2(4000) )
      type oracle_loader
      default directory tmpdir
      access parameters
      ( records delimited by newline
        preprocessor bin: 'ls'  -- or shell script to do what you want
        nobadfile
        nologfile
        nodiscardfile
       )
       location ( 'xyz.txt' )
reject limit unlimited ) ext

Use UTL_FILE to write the directory you want to list into xyz.txt above and then run that query.

A more detailed walk through here

https://youtu.be/kMknfeGa6Ug

Upvotes: 2

Jon Heller
Jon Heller

Reputation: 36912

The query on DBA_DIRECTORIES is incorrectly look at the directory name, which would be the Oracle object name:

SELECT directory_path
INTO lv_pattern
FROM dba_directories
WHERE directory_name = '/home/oracle/Desktop/MY_SCRIPTS';

Instead, it should look at the directory path:

SELECT directory_path
INTO lv_pattern
FROM dba_directories
WHERE directory_path = '/home/oracle/Desktop/MY_SCRIPTS';

On the other hand, the code doesn't even need to access DBA_DIRECTORIES at all. Your code looks like it is loosely based on this code from Tim Hall at oracle-base.com. I recommend going back to the original version, which works fine for me and doesn't require any hard-coding.

Upvotes: 0

Related Questions