I have create pl/sql function that return CLOB like that :
SQL> create or replace function exec_CLOB(p_command in varchar2) return Clob
2 is language java name 'ExternalCall.execClob(java.lang.String) return oracle.sql.CLOB';
3 /
Function created.
SQL> set long 5000000
SQL> execute DBMS_JAVA.SET_OUTPUT(10000);
PL/SQL procedure successfully completed.
I execute this function in sqlplus enviroment without problem.
SQL> select exec_CLOB('df -h') from dual;
EXEC_CLOB('DF-H')
--------------------------------------------------------------------------------
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
25G 20G 3.7G 85% /
/dev/sda1 99M 27M 67M 29% /boot
tmpfs 1.5G 695M 826M 46% /dev/shm
I develop PL/SQL procedure that use this function.
SQL> CREATE OR REPLACE PROCEDURE HR.DFPROCEDURE AS
2 DFFILE UTL_FILE.FILE_TYPE;
3 BEGIN
4 DFFILE := UTL_FILE.FOPEN('VERIFYFS','dffile.txt','w', 32767);
5 DBMS_JAVA.SET_OUTPUT(10000);
6 --DFFILE := BFILENAME('VERIFYFS','dffile');
7 --DBMS_LOB.FILEOPEN(DFFILE);
8 DFFILE := OS_COMMAND.exec_CLOB('df -h');
9 exception
10 when OTHERS then
11 dbms_output.put_line('Error : ' || SQLERRM );
12 END;
13 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE HR.DFPROCEDURE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PL/SQL: Statement ignored
8/11 PLS-00382: expression is of wrong type
Can some one have an idea.