quote:

"When I cd to the /url/oracle_data it is there - I see the directory, but when I 
cd to this directory it is not there! "

HUH?

but in anycase, your query shows "no leading / on utl/oracle"  so, I think the command you cut and pasted here is differen then the one you actually executed OR the query cut and paste was done wrong -- something does not add up there.


You use DIRECTORY objects in utl_file in 9i - not "real paths", why would we use "directory objects" in one place and "real paths" in another place?  You use directory objects -- or a path specified in UTL_FILE_DIR (the init.ora parameter)

I sort of disagree with your 80/20 -- for you see, the vast majority of users never file a tar in the first place!


But here is a simple, teeny example showing you the basics here.  But, I don't know why you would do this (the copy) since you need to either

a) have utl_file_dir set (meaning plsql could already read it)
b) have a directory object created (meaning plsql could already read it)




ops$tkyte@ORA920> create or replace directory my_home as '/home/tkyte'
  2  /
 
Directory created.
 
ops$tkyte@ORA920> create or replace directory tmp_dir as '/tmp'
  2  /
 
Directory created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> !ls -l /tmp/test.dat /home/tkyte/test.dat
ls: /tmp/test.dat: No such file or directory
ls: /home/tkyte/test.dat: No such file or directory
 
ops$tkyte@ORA920> !echo hello world > /home/tkyte/test.dat
 
ops$tkyte@ORA920> !ls -l /tmp/test.dat /home/tkyte/test.dat
ls: /tmp/test.dat: No such file or directory
-rw-rw-r--    1 tkyte    tkyte          12 Dec 14 10:50 /home/tkyte/test.dat
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2     UTL_FILE.fcopy ( src_location => 'MY_HOME',
  3                      src_filename => 'test.dat',
  4                      dest_location => 'TMP_DIR',
  5                      dest_filename => 'test.dat' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> !ls -l /tmp/test.dat /home/tkyte/test.dat
-rw-rw-r--    1 tkyte    tkyte          12 Dec 14 10:50 /home/tkyte/test.dat
-rw-rw-r--    1 ora920   ora920         12 Dec 14 10:50 /tmp/test.dat
 
ops$tkyte@ORA920> !cat /tmp/test.dat
hello world
 
ops$tkyte@ORA920>
 

permissions.

what are the permissions on the file itself in the OS (can Oracle erase that file)

probably not.  consider


ops$tkyte@ORA9IR2> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DelFile" AS
  2  import java.io.*;
  3    public class     DelFile
  4    {
  5     public static void remove(String filename)     throws Exception
  6     {
  7     File myfile = new File(filename);
  8
  9         System.out.println("Deleting ..... "+filename );
 10         if ( myfile.delete() )
 11            System.out.println( "success" );
 12         else
 13            System.out.println( "failure" );
 14    }
 15   }
 16  /
                                                                                             
Java created.
                                                                                             
ops$tkyte@ORA9IR2> create or replace procedure delfile( p_string in varchar2 )
  2  as language java
  3  name 'DelFile.remove( java.lang.String )';
  4  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> EXEC Dbms_Java.Grant_Permission(user, 'java.io.FilePermission', '/tmp/*', ' delete');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo Hello > /tmp/t1.dat
 
ops$tkyte@ORA9IR2> !ls -l /tmp/t?.dat
-rw-rw-r--    1 tkyte    tkyte           6 Dec 16 11:43 /tmp/t1.dat
-rw-rw-r--    1 ora9ir2  ora9ir2         6 Dec 16 11:43 /tmp/t3.dat
 
ops$tkyte@ORA9IR2> exec dbms_java.set_output(100000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec delfile( '/tmp/t1.dat' );
Deleting ..... /tmp/t1.dat
failure
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec delfile( '/tmp/t3.dat' );
Deleting ..... /tmp/t3.dat
success
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> !ls -l /tmp/t?.dat
-rw-rw-r--    1 tkyte    tkyte           6 Dec 16 11:43 /tmp/t1.dat
 


t3.dat -- gone, t1.dat -- still there.  if I su'ed to ora9ir2 -- it cannot erase /tmp/t1.dat, not allowed.


[tkyte@localhost tkyte]$ msu - ora9ir2
[ora9ir2@localhost ora9ir2]$ rm /tmp/t1.dat
rm: remove write-protected regular file `/tmp/t1.dat'? y
rm: cannot remove `/tmp/t1.dat': Operation not permitted
[ora9ir2@localhost ora9ir2]$


so, must be a perm problem at the OS level, remember the java proc is running as ORACLE.

 

"process l-cursor" was pure psuedo code saying "fetch and do whatever here"

sys_refcursor is a datatype in 9i and above.  in 8i you would:


ops$tkyte@ORA9IR2> declare
  2      type rc is ref cursor;
  3      l_cursor rc;
  4      l_data   varchar2(20);
  5  begin
  6      open l_cursor for 'select * from dual';
  7      loop
  8          fetch l_cursor into l_data;
  9          exit when l_cursor%notfound;
 10
 11          dbms_output.put_line( 'This represents processing this row ' || l_data );
 12      end loop;
 13      close l_cursor;
 14  end;
 15  /
This represents processing this row X
 
PL/SQL procedure successfully completed.