Re: list contents of directory

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Tue, 19 Sep 2006 11:21:43 +0200
Message-ID: <016d01c6dbcd$08b9c9d0$1a03310a@ETNOTEAM6XUQ9V>

>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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 19 2006 - 04:21:43 CDT