Solved

Calling of Unix Script from Oracle Procedure

Posted on 2011-09-07
20
232 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
20 Comments
 
LVL 76

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 76

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
 

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 76

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 76

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 76

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 76

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 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 76

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 22

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now