Monday, March 17, 2014

utl_file operations on remote directory,network shares

I have a project contains database migration from windows to linux enviroment with database upgrade . For years developers have worked on windows database server and done their utl_file operations on windows file system.After migration they have asked to work on windows file server  as so. Due to new database is on linux system i have asked that remote directory on another server is supported during utl_file operations ?

As a result, utl_file script has to create file on remote windows server from linux. I have used cifs protocol for this issue.

1. On linux new directory is created for required mount point
 
     mkdir -p /remotewin

2. With windows user,password ,domain information new mount point is mounted to related windows directory

     Assume you have directory named "remotelin" on windows server has necessary sharing.

     mount -t cifs //<WINDOWS_SERVER_IP>/remotelin /remotewin -o username= <WIN_USER_NAME>,password=<WIN_PASSWORD>,domain=<WIN_DOMAIN_NAME>,uid=<LINUX_ORACLE_USER_ID>,gid=<LINUX_ORACLE_USER_GROUP_ID>

With this command /remotewin directory on linux enviroment is mounted to windows enviroment with oracle user privilige .

After this you can use DBA_DIRECTORY or utl_file parameter  to create files on windows enviroment.

connect / as sysdba

create or replace Directory OUTBOUND_DIR as '/remotewin';

DECLARE 
  filex UTL_FILE.FILE_TYPE;
BEGIN 
  filex := UTL_FILE.fopen('OUTBOUND_DIR','Testfile.txt','W',32767);    

  UTL_FILE.put_line(filex, 'Hello World');    
  UTL_FILE.fclose(filex);                         
EXCEPTION
  WHEN utl_file.invalid_path THEN           
    dbms_output.put_line('Path not found on DB-server.');       
  WHEN utl_file.invalid_mode THEN           
    dbms_output.put_line('Invalid mode.');       
  WHEN utl_file.invalid_operation THEN           
    dbms_output.put_line('Error when opening file');       
  WHEN utl_file.invalid_filehandle THEN           
    dbms_output.put_line('Invalid filehandle');       
  WHEN OTHERS THEN         
    dbms_output.put_line('Others: '|| SQLERRM); 
END;


Sources:
http://microitblog.com/micro-it-blog/2011/04/21/mounting-cifs-shares-through-linux-with-write-permissions
http://www.cyberciti.biz/tips/how-to-mount-remote-windows-partition-windows-share-under-linux.html
http://www.samba.org/samba/docs/man/manpages-3/mount.cifs.8.html


No comments:

Post a Comment