Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calling of Unix Script from Oracle Procedure

Posted on 2011-09-07
20
Medium Priority
?
242 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

618 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