Here is a quick and dirty implementation that builds on:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>
(read the whole trail there...)
Then, we can do this:
rt_test@ORA817DEV.US.ORACLE.COM> create or replace type myScalarType
2 as
3 object
4 ( filesystem varchar2(255),
5 free_space number
6 )
7 /
Type created.
rt_test@ORA817DEV.US.ORACLE.COM> create or replace type myTableType
2 as
3 table of myScalarType
4 /
Type created.
rt_test@ORA817DEV.US.ORACLE.COM>
rt_test@ORA817DEV.US.ORACLE.COM>
rt_test@ORA817DEV.US.ORACLE.COM> create or replace function df(p_cmd in varchar2) return myTableType
2 as
3 l_status number;
4 l_line varchar2(255);
5 l_data myTableType := myTableType();
6 begin
7 l_status := run_cmd(p_cmd);
8 dbms_output.get_line( l_line, l_status );
9 loop
10 dbms_output.get_line( l_line, l_status );
11 exit when l_status = 1;
12 l_data.extend;
13 l_data(l_data.count) := myScalarType( substr( l_line, 1, instr(l_line,' ')+1 ), 0 );
14 l_line := ltrim(substr(l_line,instr(l_line,' ')));
15 l_line := ltrim(substr(l_line,instr(l_line,' ')));
16 l_line := ltrim(substr(l_line,instr(l_line,' ')));
17 l_data(l_data.count).free_space := substr( l_line, 1, instr(l_line,' ')+1 );
18 end loop;
19 return l_data;
20 end;
21 /
Function created.
rt_test@ORA817DEV.US.ORACLE.COM>
rt_test@ORA817DEV.US.ORACLE.COM> set serveroutput on size 1000000
rt_test@ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output(1000000)
PL/SQL procedure successfully completed.
rt_test@ORA817DEV.US.ORACLE.COM> /
Function created.
rt_test@ORA817DEV.US.ORACLE.COM>
rt_test@ORA817DEV.US.ORACLE.COM> column filesystem format a35
rt_test@ORA817DEV.US.ORACLE.COM>
rt_test@ORA817DEV.US.ORACLE.COM> select *
2 from TABLE ( cast ( df('/usr/bin/df -k' ) as myTableType ) )
3 /
FILESYSTEM FREE_SPACE
----------------------------------- ----------
/proc 0
/dev/dsk/c0t0d0s0 200043
fd 0
/dev/dsk/c0t0d0s1 52641
/dev/dsk/c0t3d0s3 4505218
/dev/dsk/c0t1d0s0 2331161
/dev/dsk/c2t2d0s3 2956077
/dev/dsk/c3t1d0s1 3057383
/dev/dsk/c3t0d0s0 2491558
/dev/dsk/c2t1d0s0 2527326
/dev/dsk/c3t1d0s0 218210
/dev/dsk/c2t3d0s3 2084348
/dev/dsk/c0t2d0s3 2605532
aria:/export/home 4278107
/vol/dev/dsk/c1t6d0/orcl901_1 0
aria:/export/home 4278107
16 rows selected.