?
Solved

Calling of Unix Script from Oracle Procedure

Posted on 2011-09-07
20
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 77

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 77

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 77

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 77

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 77

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 77

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 77

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 77

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 77

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

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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

762 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