bonku_roy
asked on
Calling of Unix Script from Oracle Procedure
I have a situation , I ned to call an unix script [unix flavour - HP TRU64 ], from Oracle 10g.
Any assistance highly appreciated.
Requesting to treat this issue on priority basis.
Thanks to all those who not only make life easy but a wonderful world to live..
Any assistance highly appreciated.
Requesting to treat this issue on priority basis.
Thanks to all those who not only make life easy but a wonderful world to live..
ASKER
Well in case I need to call an unix shell script with parametrs from Oracle procedure.
Is there any other way around apart from DBMS_SCHEDULER?
Call a batch file from the procedure and call "rsh" from the batch file.
In return the batch file calls the the unix scripot with parameters.
It would really great if you could provide some code snippets.
Is there any other way around apart from DBMS_SCHEDULER?
Call a batch file from the procedure and call "rsh" from the batch file.
In return the batch file calls the the unix scripot with parameters.
It would really great if you could provide some code snippets.
What is wrong with dbms_scheduler? I believe you can add parameters to it.
The only other way I know is the External Procedure JAVA wrapper.
As far as sample code, I'm on mobile so cannot provide links bit they are easily found with some quick Googling.
The only other way I know is the External Procedure JAVA wrapper.
As far as sample code, I'm on mobile so cannot provide links bit they are easily found with some quick Googling.
ASKER
The reason I am asking is because there are situations within the project specially when calling remote shell within a VB program.
Well theres nothing wrong with DBMS_SCHEDULER ....
Well theres nothing wrong with DBMS_SCHEDULER ....
Are you trying to draw a correlation between VB and PL/SQL?
An easy way to do it is to use dbms pipes (DBMS PIPES).
You can create a seperate process (shell, perl, ...) which listens to the pipe and gets it's input from the oracle process. It will start any subprocesses (scripts, program, ...) on request.
You can create a seperate process (shell, perl, ...) which listens to the pipe and gets it's input from the oracle process. It will start any subprocesses (scripts, program, ...) on request.
Ringthane,
Can you elaborate on how you see using DBMS_PIPE to execute a shell script from within PL/SQL?
Can you elaborate on how you see using DBMS_PIPE to execute a shell script from within PL/SQL?
I found this when looking for an example: http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:16212348050
Interesting.
That does require an external shell script running in the background at all times to intercept and execute the commands but it is an interesting approach.
That does require an external shell script running in the background at all times to intercept and execute the commands but it is an interesting approach.
ASKER
Just coming back to the old line.
Is there anyway I can call an unix script through "rsh" command from an oracl eprocedure.
I am stuck.
Is there anyway I can call an unix script through "rsh" command from an oracl eprocedure.
I am stuck.
The rsh command is just another unix command. Any one of the methods mentioned above that can execute a simple command like: ls > /tmp/junk.txt
Can be execute the rsh command.
Where are you getting lost?
Can be execute the rsh command.
Where are you getting lost?
ASKER
Thanks for coming to my rescue :-)
The thing is that I am just not able to login through rsh ..
Say I have an oracle procedure ..
Option1]
My unix system is another host machine different from where Oracle is been hosted .
My Oracle login credentials : Username : test1 Paswword: cat01
My unix login credentials: Username: app_owner Password: test123
Then how do I login? what would be the parameters?
Option2]
My unix system is the same host machine where Oracle is hosted .
My Oracle login credentials : Username :app_owner Paswword: cat01
My unix login credentials: Username: app_owner Password: test123
Then how do I login? what would be the parameters?
Do let me know if there is some additional information required for proceeding ahead.
The thing is that I am just not able to login through rsh ..
Say I have an oracle procedure ..
Option1]
My unix system is another host machine different from where Oracle is been hosted .
My Oracle login credentials : Username : test1 Paswword: cat01
My unix login credentials: Username: app_owner Password: test123
Then how do I login? what would be the parameters?
Option2]
My unix system is the same host machine where Oracle is hosted .
My Oracle login credentials : Username :app_owner Paswword: cat01
My unix login credentials: Username: app_owner Password: test123
Then how do I login? what would be the parameters?
Do let me know if there is some additional information required for proceeding ahead.
How to use rsh is a different question. I suggest you work with your System Administrators on how to get rsh working.
Once you do that, how to execute the commands from Oracle can be done with the ways above.
Once you do that, how to execute the commands from Oracle can be done with the ways above.
ASKER
Just to get back to the scratch.
I am using Oracle 10g and Unix OS.
Can a unix script be called from Oracle procedure through DBMS_SCHGEDULER?
I had tried this option but failed with errors.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I would bring the code before you first.
Step1
Step 2
Step 3
Step 4
Finally at the end of Step 4 , I received errors :
BEGIN dbms_scheduler.run_job('My
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 274664
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
Please assist.
Step1
SQL> begin dbms_scheduler.create_job
(
job_name => 'My_job',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 , /* this to postponed the startdate 5 minutes until enabling the job at last step .*/ number_of_arguments => 1,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',
enabled => false,
comments => 'test as per ANUJ95 '
); end;
/
PL/SQL procedure successfully completedStep 2
SQL> begin
dbms_scheduler.set_job_argument_value
(job_name=>'My_job',argument_position=>1,argument_value=>'/h701/oradb/admin/LRCDEV/lric_data/preproc_dataload.ksh') ;
end;
/
PL/SQL procedure successfully completed. Step 3
exec dbms_scheduler.enable(name=>'My_job');
PL/SQL procedure successfully completed. Step 4
exec dbms_scheduler.run_job('My_job', TRUE); Finally at the end of Step 4 , I received errors :
BEGIN dbms_scheduler.run_job('My
*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: 274664
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
Please assist.
You seem to be making it more complicated than it needs to be.
Did you check the syntax form the link I posted above?
program_action => '/h701/oradb/admin/LRCDEV/
Did you check the syntax form the link I posted above?
program_action => '/h701/oradb/admin/LRCDEV/
ASKER
Hurray I made it.. thanks to all who provided their assistance and guidance.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
As you mentioned in the other question: DBMS_SECHDULER. Just create a dynamic job that will execute immediately.
The other way is to create a JAVA wrapper as an External Procedure.