How to execute a DOS shell command in a pl/sql procedure?

Have a need to run a DOS commads in a pl/sql procedure. For example,

in pl/sql procedure p1, want to run
copy /y c:\temp\a.txt d:\temp\b.txt
How to do that??
jl66Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cheers4beersCommented:
If you are talking from the PL/SQL (Database) point of view, it is not possible. You need to use DBMS_SCHEDULER package for running an OS command for it.

DBMS_SCHEDULER.CREATE_PROGRAM (
   'OS_Command',
   'EXECUTABLE',
   'copy /y c:\temp\a.txt d:\temp\b.txt',
   0,
   TRUE
   );


If you are talking from a forms point of view, it is possible to use host command to invoke the server side OS commands.

host ( 'cmd /C  copy /y c:\temp\a.txt d:\temp\b.txt' );
0
cheers4beersCommented:
OR...if you don't mind creating a simple java class:


This Note provides a sample Java Class that can be used to call
operating system commands from PL/SQL Stored Functions, Procedures
or Packages. Calling operating system commands can be implemented
using External Procedure call and C language. Please refer to
Note 130799.1 for more details on using this approach.

SCOPE & APPLICATION

This Note is intended for any user, analyst or developer who is
looking for a sample code to call operating system commands from
PL/SQL without the need to write C programs.
How to Call Operating System Commands from PL/SQL - The Java Way
Step by Step Instructions to Built the Sample Code.

1- Login to SQL*Plus with a user who has the privilege to create
Java Source and Functions.

2- Create the OSCommand Java Class using the following statement:

--- Cut Code Here ---
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static String Run(String Command){
try{
Runtime.getRuntime().exec(Command);
return("0");
}
catch (Exception e){
System.out.println("Error running command: " + Command +
"\n" + e.getMessage());
return(e.getMessage());
}
}
}
/
--- Cut Code Here ---

3- Create the following Wrapper Function using the following
statement:

--- Cut Code Here ---
CREATE or REPLACE FUNCTION OSCommand_Run(Command IN STRING)
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'OSCommand.Run(java.lang.String) return int';
/
--- Cut Code Here ---

4- Connect as SYS or SYSTEM and grant the following privilege
to your database user used in Step 1:

SQL> Execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor',
'*' );
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor',
'*' );

SQL> Commit;

Note:
o Replace 'YOUR_DATABASE_USER' with your database user name
used in Step 1 above. For example, SCOTT. The user name should
be written in UPPER CASE.
o Commit is mandatory.

5- Test your code, by connecting to your database user used
in Step 1 and run the following PL/SQL Block.
For example.
--- Cut Code Here ---
Set Serverout On
Declare
x Varchar2(2000);
Begin
x := OSCommand_Run('C:\home\test\myoscommand.cmd');
DBMS_OUTPUT.Put_Line(x);
End;
/

A good example is to change the command line to call a shell script or a cmd in windows that has your OS comand in it. This way, you can run the same PLSQL and change your OS script as many times as you can to do different things.

Now, create a file myoscommand.cmd to conatin the copy command below:

copy /y c:\temp\a.txt d:\temp\b.txt


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cheers4beersCommented:
...and run the PL/SQL as follows:

Set Serverout On
Declare
x Varchar2(2000);
Begin
x := OSCommand_Run('C:\home\test\myoscommand.cmd');
DBMS_OUTPUT.Put_Line(x);
End;
/
0
slightwv (䄆 Netminder) Commented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jl66Author Commented:
Thanks so much for everyone's tips.
slightwv:
One quetion is related to your approach: in PL/SQL procedure p1, how to create a DIRECTORY? Like

execute immediate create or replace directory USER_DIR1 as 'C:\temp';

It did not work.
0
jl66Author Commented:
The issue was resolved. Thanks a lot for everyone's tip.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.