Get the names of all files in a folder in PL / SQL (Oracle XE)

Question

There is a folder (locally) C: \ LB into which someone uploads files. I need (periodically, after a certain time) to get the names of all these files, and if these names contain the name that I need, I attach this file (.PDF) to a specific record in the table.

While I am working on getting a list of file names:

create global temporary table DIR_LIST (filename varchar2(255)) on commit delete rows; / create or replace and compile java source named "DirList" as import java.io.*; import java.sql.*; public class DirList { public static void getList(String directory) throws SQLException { File path = new File( directory ); String[] list = path.list(); String element; for(int i = 0; i < list.length; i++) { element = list[i]; #sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) }; } } } / create or replace procedure get_dir_list( p_directory in varchar2 ) as language java name 'DirList.getList( java.lang.String )';

DB 11g XE and I can not deploy JAVA inside. Writing an external JAR-nickname is not entirely relevant. I understand you need a sheduler and JOB, but all the forums say that you cannot write this on pure PL / SQL (get the file names and turn them into a table). OK. Is it possible to deploy .NET classes inside a database by wrapping it in a procedure? As with the classes java. I'm sure someone definitely came across this. I would be grateful for the tips!

Java support No Java server compilation No SQL Server No docs.oracle.com/cd/E17781_01/license.112/e18068/…

Community spirit ♦ one · Accepted Answer · 2016-11-19T00:12:27

You can use external tables to implement:

 mkdir /tmp/stage cat >/tmp/stage/readdir.sh<<eof #/bin/bash cd /tmp/stage/ /bin/ls -1 eof for i in {1..5}; do touch /tmp/stage/invoice_no_$RANDOM.pdf; done

In sqlplus:

 create or replace directory stagedir as '/tmp/stage'; --Directory STAGEDIR created. create table files (filename varchar2(255)) organization external ( type oracle_loader default directory stagedir access parameters ( records delimited by newline preprocessor stagedir:'readdir.sh' fields terminated by "|" ldrtrim ) location ('readdir.sh') ); / --Table FILES created. select * from files; FILENAME -------------------------------------------------------------------------------- FILES_27463.log invoice_no_20891.pdf invoice_no_2255.pdf invoice_no_24086.pdf invoice_no_30372.pdf invoice_no_8340.pdf readdir.sh 7 rows selected

It is also possible with a one-time job , but this decision will obviously be heavier, since Standard output cannot be directly returned to the calling procedure. But, as the client wished, everything will be in pl / sql.

"standard output can not be directly returned to the calling procedure" - I do not quite understand what you mean, is there an explanation or a link?
If you use 'ls, dir' and the like, as in the example above, you need to somehow return or read their 'stdout', for example: redirect to a file and read its contents with the same 'utl_file'.
Comment for Windows: If you have (like me) in Windows Server 2008, then in the file directory, put .bat with the code: @echo off dir /b "путь к папке без кавычек" for return settings from. bat look dir /?

Get the names of all files in a folder in PL / SQL (Oracle XE)

1 answer 1

More articles: