Skip to Main Content
  • Questions
  • Executing OS commands from PLSQL code

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ik.

Asked: February 28, 2017 - 8:11 am UTC

Last updated: July 06, 2022 - 10:33 am UTC

Version: 12.1.0.2

Viewed 50K+ times! This question is

You Asked

Hi,

Is there a native Oracle solution to execute OS commands via PLSQL or SQL? I searched on the web and do not find an easy or straight forward technique. Can you please help?

Assuming OS is Linux and the command to execute is any executable or a tool, say srvctl.

thanks
Ik

and Chris said...

There are a few ways you can achieve this. You could write a Java stored procedure to accept and run commands:

https://oracle-base.com/articles/8i/shell-commands-from-plsql

Or use DBMS_pipe to send commands to a daemon running on the server:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16212348050

Or use DBMS_scheduler to run OS commands (10g and up):

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16212348050#39092679158568

Whichever approach you take, be careful. Ensure you can't run commands like "rm -rf *" as a privileged OS user from PL/SQL!

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OS command from PlSQL stopped working

Amit Roy Chowdhury, March 23, 2022 - 7:56 am UTC

Hi!,
We have been calling OS command from PLSQL packages for quite a few years. We are currently upgrading our Application to 12.2.11 from 12.1.3. Our DB is same however 19C. While testing we find out OS command calls not executing the command. The package execution says successful but the command in the OS call is not executed.
For simplicity sake I am trying to copy a file.

exec apps.XX_Call(p_command => '/usr/bin/cp /home/oracle/dp123.txt /home/oracle/dp1234.txt');

Java Class contains
===================
CREATE OR REPLACE JAVA SOURCE NAMED APPS."xx_Call" as
import java.io.*;
public class xx_Call {
/*AmitRC This Source can be used to call any OS command the full path has to be given */
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_in = new BufferedReader(new
InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println(buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
}
}).start();
new Thread(new Runnable() {
public void run() {
try {
BufferedReader br_err = new BufferedReader(new
InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println(buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
===================
Chris Saxon
March 23, 2022 - 2:23 pm UTC

While testing we find out OS command calls not executing the command.

How have you verified this? Have file/directory permissions changed in the OS? What else changed when you upgraded the application?

OS command from PlSQL stopped working

Amit Roy Chowdhury, March 24, 2022 - 12:56 pm UTC

I am actually unaware of finding out the access permission status. How do I find out?
Chris Saxon
March 24, 2022 - 5:47 pm UTC

I'm referring to the file/folder permissions on the database server. You can get this information by running ls -lah, e.g.:

[oracle@dbaas21c home]$ ls -lah
total 24K
drwxr-xr-x  6 root   root     4.0K Dec  8  2020 .
dr-xr-xr-x 19 root   root     4.0K Mar 24 17:45 ..
drwx------  3 grid   oinstall 4.0K Apr 27  2021 grid
drwx------  3 mysql  mysql    4.0K Dec  8  2020 mysql
drwx------  3 opc    opc      4.0K Dec 16  2020 opc
drwx------  3 oracle oinstall 4.0K Mar 21 09:59 oracle


But best speak with your DBA/server admin - they should be able to help you figure out what's going on.

another way to run a os command

linson, July 05, 2022 - 5:50 am UTC

or you could create a library based on OS libraries and create a procedure as given below to execute OS command

create or replace PROCEDURE execute_shell(
cmdstring IN CHAR)
IS
external NAME "system" library exec_shell LANGUAGE C;
Chris Saxon
July 06, 2022 - 10:33 am UTC

True - thanks for sharing

A reader, September 15, 2022 - 1:13 pm UTC



CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SYS."xx_Call"
as
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.concurrent.atomic.AtomicReference;

public class xx_Call {
/*
* AmitRC This Source can be used to call any OS command the full path has to be
* given
*/

// private volatile static String result = "";

public static AtomicReference<String> result = new AtomicReference<>();

public static String executeCommand(String command) {
// final String[] result = {"Default"};
String init = "Inital Value";
result.set(init);

try {
String[] finalCommand;
finalCommand = new String[3];
finalCommand[0] = "/usr/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
final Process pr = Runtime.getRuntime().exec(finalCommand);

try {
BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println(buff);

try {
Thread.sleep(100);
} catch (Exception e) {
result.set(e.getMessage());
}
}
br_in.close();

BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff2 = null;
while ((buff2 = br_err.readLine()) != null) {
System.out.println(buff2);
result.set(buff2);
try {
Thread.sleep(100);
} catch (Exception e) {
result.set(e.getMessage());
}
}
br_err.close();

if(buff2 ==null){
result.set("File Transfer Successful");
}

} catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();

result.set(ioe.getMessage());

}

} catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());

result.set(ex.getMessage());

}
return result.get();
}

}
;
/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library