Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to run a Unix command using a query in Oracle?

I want to run simple commands (like df -h) using a query.

Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.

share|improve this question
3  
Have you seen DBMS_SCHEDULER yet? –  dezso Oct 30 '12 at 9:50
2  
stackoverflow.com/questions/1058509/… –  Phil Oct 30 '12 at 10:38
add comment

2 Answers 2

You can use the DBMS_Scheduler

DBMS_SCHEDULER.create_program
(
program_name => 'testScript',
program_type => 'EXECUTABLE',
program_action => '/home/someone/scripts/script.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Just a Test'
);
end;
/

and a little hosekeeping:

exec DBMS_SCHEDULER.drop_program(PROGRAM_NAME => 'testScript');
share|improve this answer
add comment

I just want to share an alternative approach for calling operating system commands. In this approach a simple web-service interface is set up for calling OS commands from SQL or PL/SQL. Details about this approach can be found here.

Using the same approach, a step-by-step description of how to invoke SQL*Loader from SQL is described here.

Please note that in the above approach the web-service is defined in Python. Python comes pre-installed with most common Linux distributions (including Oracle Linux), however if you are on a Windows platform you may need to install Python 2.7.3

share|improve this answer
    
That's pretty crazy. You "command checks" that's supposed to restrict the security hole you're opening isn't effective at all given your use of shell=True. And the sql*loader thing is really perplexing - an external table is both much simpler and much more secure. (I'll not even comment about the required clear-text password in there.) –  Mat Jan 20 '13 at 9:59
    
Thanks for highlighting the security flaw. I have fixed the issue by setting shell=False. Regarding External Table option i would say that it may be the best option in sc –  Moiz Jan 20 '13 at 11:47
    
Regarding External Table option i think it is not the preferred option in every scenario. There are cases where sql*loader is used directly from command shell. So what i have discussed is just an another way of invoking the sql*loader. Clear-text password issue may be mitigated by saving the username/password in the parameter file that can be specified in parFile option. –  Moiz Jan 20 '13 at 12:19
1  
There are cases where sql*loader is used directly from command shell. Sure. What does that have to do with your blog post or this question though? If you're inside Oracle PL/SQL, use PL/SQL or external tables to do it. –  Mat Jan 20 '13 at 12:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.