[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calling of Unix Script from Oracle Procedure

Posted on 2011-09-07
20
Medium Priority
?
243 Views
Last Modified: 2014-03-12
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..
0
Comment
Question by:bonku_roy
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498766
Since this one is 500 points and the other 250, I assume you will want to delete the other one so I'll answer here.

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.
0
 

Author Comment

by:bonku_roy
ID: 36498998
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36499397
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bonku_roy
ID: 36499431
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 ....
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36499445
Are you trying to draw a correlation between VB and PL/SQL?
0
 
LVL 2

Expert Comment

by:Ringthane
ID: 36501122
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502238
Ringthane,

Can you elaborate on how you see using DBMS_PIPE to execute a shell script from within PL/SQL?
0
 
LVL 2

Expert Comment

by:Ringthane
ID: 36502843
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502880
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.
0
 

Author Comment

by:bonku_roy
ID: 36506492
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36506542
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?
0
 

Author Comment

by:bonku_roy
ID: 36506697
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.



0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36506735
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.
0
 

Author Comment

by:bonku_roy
ID: 36531723

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.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36531832
>>Can a unix script be called from Oracle procedure through DBMS_SCHGEDULER?


Per the docs:  Yes.

There are several examples out there.

Here's one:
http://www.dba-oracle.com/t_dbms_scheduler_cron_shell_scripts.htm

>>but failed with errors.

What were the errors?
 
0
 

Author Comment

by:bonku_roy
ID: 36532053
Well I would bring the code before you first.

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 completed

Open in new window

.  




Step 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.     

Open in new window



Step 3



exec dbms_scheduler.enable(name=>'My_job');                                                                                                    
                                                                                                                                                      
PL/SQL procedure successfully completed.     

Open in new window

           


Step 4

exec dbms_scheduler.run_job('My_job', TRUE);                                                                                                    

Open in new window






Finally at the end of Step 4 , I received errors :
BEGIN dbms_scheduler.run_job('My_job', TRUE); END;                                                                                                  
                                                                                                                                                     
*                                                                                                                                                    
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36532173
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/lric_data/preproc_dataload.ksh'
0
 

Author Comment

by:bonku_roy
ID: 36575684
Hurray I made it.. thanks to all who provided their assistance and guidance.
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39922862
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question