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