Re: list contents of directory
- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Sep 2006 11:21:43 +0200
>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:
