Solved

Calling of Unix Script from Oracle Procedure

Posted on 2011-09-07
20
236 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

831 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