sath350163
asked on
Changing permission on a file in NAS location from within PL/SQL stored procedure or from within DBMS_Scheduler job
I have this scheduler job, which inturn calls a stored procedure to create a .txt file in NAS location. Although the scheduler job successfully creates the file, nobody other than oracle user is able to open that .txt file.
Oracle user needs to have READ and WRITE privilege on the NAS location, while all others have just READ-ONLY access on that location.
Currently Oracle user has 2775 permission on the NAS directory.
Question:
After creating the text file from PL/SQL stored procedure, is there a way to change file permission to 664 from within the PL/SQL stored procedure or dbms_scheduler job itself?
If so, please provide a sample?
Thanks!
Oracle user needs to have READ and WRITE privilege on the NAS location, while all others have just READ-ONLY access on that location.
Currently Oracle user has 2775 permission on the NAS directory.
Question:
After creating the text file from PL/SQL stored procedure, is there a way to change file permission to 664 from within the PL/SQL stored procedure or dbms_scheduler job itself?
If so, please provide a sample?
Thanks!
ASKER
Oracle version that is being used is Oracle 10.2
Yes, the same still applies
From the 10.2 PL/SQL Types and Packages document Oracle PL/SQL Types 10.2
From the 10.2 PL/SQL Types and Packages document Oracle PL/SQL Types 10.2
On UNIX systems, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance. Normally, this owner is ORACLE. Files created using FOPEN are always writable and readable using the UTL_FILE subprograms, but non privileged users who need to read these files outside of PL/SQL may need access from a system administrator
ASKER
Found this link which says that there is a way to execute external programs from within oracle 10g.
http://www.dbasupport.com/oracle/ora10g/dbms_scheduler.shtml
I'm not sure what should go on program_action in order to change the file permission.
I tried with CHMOD and it did not work.
Thanks!
http://www.dbasupport.com/oracle/ora10g/dbms_scheduler.shtml
I'm not sure what should go on program_action in order to change the file permission.
I tried with CHMOD and it did not work.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
make sure you have global rwx rights on your script
ASKER
Thanks!
- Where should this file be placed? Should it be in the same NAS location where the .txt file will be dropped?
- What should be the contents of this file in order to change the permission to 664?
Thanks!
- Where should this file be placed? Should it be in the same NAS location where the .txt file will be dropped?
- What should be the contents of this file in order to change the permission to 664?
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
I want to try this approach in my local Express edition before I give it off to the DBAs.
So in that case, server is my local pc.
Will it work if I replace the path with windows path (c:\Oracle\\product\10.2.0 \server\Pe rmission.s h)?
I want to try this approach in my local Express edition before I give it off to the DBAs.
So in that case, server is my local pc.
Will it work if I replace the path with windows path (c:\Oracle\\product\10.2.0
ASKER
Also how to grant a schema user (other than SYS/SYSTEM) access to the shell script file?
Thanks
Thanks
Two problems - a bash script won't work on Windows, only Linux.
Set the script to mode 755 when you install it on your linux server.
Create a different script for your windows machine and execute it. It is irrelevant what the script does, as long as you can tell it executes.
Set the script to mode 755 when you install it on your linux server.
Create a different script for your windows machine and execute it. It is irrelevant what the script does, as long as you can tell it executes.
ASKER
I got the following code from: https://forums.oracle.com/forums/thread.jspa?messageID=2737501&tstart=0
This job fails with the following error:
ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.
STANDARD_ERROR="The system cannot find the file specified."
I'm running the above code in Oracle 10g Express edition.
The OracleJobSchedulerXR is running.
The test.bat file does exist in c:\temp\test.bat.
Is it due to some sort of permission issue?
Thanks!
This job fails with the following error:
ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.
STANDARD_ERROR="The system cannot find the file specified."
I'm running the above code in Oracle 10g Express edition.
The OracleJobSchedulerXR is running.
The test.bat file does exist in c:\temp\test.bat.
Is it due to some sort of permission issue?
Thanks!
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'testjob',
job_type => 'EXECUTABLE',
job_action => 'C:\WINDOWS\system32\cmd.exe',
repeat_interval => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=21;BYMINUTE=45;BYSECOND=0',
--start_date => systimestamp at time zone 'US/Eastern',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'test job',
auto_drop => FALSE,
number_of_arguments => 3,
enabled => FALSE);
sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 1, argument_value => '/q');
sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 2, argument_value => '/c');
sys.dbms_scheduler.set_job_argument_value( job_name => 'testjob', argument_position => 3, argument_value => '"c:\temp\test.bat"');
sys.dbms_scheduler.enable( 'testjob' );
END;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I finally created a korn shell script containing instructions to change file permission and got it copied to the oracle server location.
Thanks!
Thanks!
You could use a stored procedure to achieve this by calling the underlying operating system functions. See Run Shell command from PL/SQL