Re: list contents of directory

>I have question about directory which is created by command: "create
> directory ....."
> 
> Is it possible to list contents of directory from sql. If yes could you give
> me answer.

In 10G you can try something like this:

SQL> begin
  2  dbms_scheduler.create_job(
  3  job_name=>'os_ls',
  4  job_type=>'executable',
  5  job_action=>'/app/oracle/script.sh',
  6  comments=>'OS ls');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> !cat /app/oracle/script.sh
#!/bin/bash -xv
/bin/ls -1 /app/oracle > /app/oracle/os_list.txt
exit 0

SQL> create or replace directory data_dir as '/app/oracle';

Directory created.

SQL> exec dbms_scheduler.run_job('os_ls');

PL/SQL procedure successfully completed.

SQL> declare
  2   l_file utl_file.file_type;
  3   l_text     VARCHAR2(2000);
  4   l_line     NUMBER(10) := 1;
  5  BEGIN
  6    l_file := UTL_FILE.fopen('DATA_DIR', 'os_list.txt', 'r');
  7    BEGIN
  8      LOOP
  9        utl_file.get_line(l_file, l_text);
 10        dbms_output.put_line(l_text);
 11        l_line := l_line + 1;
 12      END LOOP;
 13    EXCEPTION
 14      WHEN NO_DATA_FOUND THEN
 15        NULL;
 16    END;
 17    UTL_FILE.fclose(l_file);
 18   end;
 19  /
admin
oraInventory
os_list.txt
product
script.sh
stagearea

PL/SQL procedure successfully completed.



Regards
Dimitre

Other related posts: