Overview  Deprecated  Index  Generator  
File and operating system interaction with SQL and PLSQL
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD


Package os_command

The OS_COMMAND package contains various functions for executing shell commands from the SQL engine. The different functions handle STDIN and STDOUT differently. As for the FILE_TYPE object type the database user needs appropriate privileges in order to execute the shell commands and to access STDIN and STDOUT. See the function documentation for details.

The following script grants read and write permissions on STDIN and STDOUT and execute privileges for a specific shell command: 


begin
-- this grants read privilege on STDIN
dbms_java.grant_permission(
grantee =>           'SCOTT',
permission_type =>   'SYS:java.lang.RuntimePermission',
permission_name =>   'readFileDescriptor',
permission_action => null
);
-- this grants write permission on STDOUT
dbms_java.grant_permission(
grantee =>           'SCOTT',
permission_type =>   'SYS:java.lang.RuntimePermission',
permission_name =>   'writeFileDescriptor',
permission_action => null
);
-- this grants execute privilege for the 'ls -la' command
dbms_java.grant_permission(
grantee =>           'SCOTT',
permission_type =>   'SYS:java.io.FilePermission',
permission_name =>   '/bin/ls',
permission_action => 'execute'
);
-- this grants execute privilege for ALL shell commands: VERY DANGEROUS!
dbms_java.grant_permission(
grantee =>           'SCOTT',
permission_type =>   'SYS:java.io.FilePermission',
permission_name =>   '<<ALL FILES>>',
permission_action => 'execute'
);
end;
Usage examples:

Method Summary
 clear_environment()
           removes all environment variables from the "custom environment".
 clear_working_dir()
           clears the working directory for the "exec" calls.
 numberexec(p_command varchar2, p_stdin blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin clob)
           executes a shell command
 numberexec(p_command varchar2)
           executes a shell command
 numberexec(p_command varchar2, p_stdin clob, p_stdout clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin clob, p_stdout blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin blob, p_stdout blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin blob, p_stdout clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdout clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdout blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin clob, p_stdout clob, p_stderr clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin clob, p_stdout blob, p_stderr blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin blob, p_stdout blob, p_stderr blob)
           executes a shell command
 numberexec(p_command varchar2, p_stdin blob, p_stdout clob, p_stderr clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdout clob, p_stderr clob)
           executes a shell command
 numberexec(p_command varchar2, p_stdout blob, p_stderr blob)
           executes a shell command
 blobexec_BLOB(p_command varchar2, p_stdin blob)
           executes a shell command
 blobexec_BLOB(p_command varchar2, p_stdin clob)
           executes a shell command
 blobexec_BLOB(p_command varchar2)
           executes a shell command
 clobexec_CLOB(p_command varchar2, p_stdin blob)
           executes a shell command
 clobexec_CLOB(p_command varchar2, p_stdin clob)
           executes a shell command
 clobexec_CLOB(p_command varchar2)
           executes a shell command
 varchar2get_env_var(p_env_name varchar2)
           gets the value of the specified environment variable from the custom environment.
 varchar2get_shell()
           returns the operating system call for the current shell.
 FILE_TYPEget_working_dir()
           gets the current working directory setting; NULL if no working directory was set.
 load_env()
           copies the default environment (in which the "oracle" process runs) into the custom environment.
 load_env(p_env_name varchar2)
           copies the specified environment variable from the default environment (in which the "oracle" process runs) into the custom environment.
 remove_env_var(p_env_name varchar2)
           removes an environment variable from the custom environment.
 set_env_var(p_env_name varchar2, p_env_value varchar2)
           sets an environment variable in the custom environment.
 set_exec_direct()
           Execute operating system commands (exec procedures and functions) directly.
 set_exec_in_shell()
           Execute operating system commands (exec procedures and functions) in a shell.
 set_Shell(p_shell_path varchar2, p_shell_switch varchar2)
           sets the path and "command switch" for the shell to use if set_exec_in_shell was called.
 set_working_dir(p_workdir file_type)
           sets the working directory for the "exec" calls.
 use_custom_env()
           Use "custom" environment variables defined by this package for the "exec" calls.
 use_default_env()
           Use "default" environment variables defined by this package for the "exec" calls.

Method Detail

set_working_dir

public  set_working_dir(p_workdir file_type)
sets the working directory for the "exec" calls. If no working directory is set, the working directory of the "oracle" process is being used.
Parameters:
p_workdir - the working directory to use as FILE_TYPE.

Version:
0.6

clear_working_dir

public  clear_working_dir()
clears the working directory for the "exec" calls. Now the working directory of the "oracle" process is being used.

Version:
0.6

get_working_dir

public FILE_TYPE get_working_dir()
gets the current working directory setting; NULL if no working directory was set.
Returns:
the working directory which was set with the last call to set_working_dir; NULL of no directory was set.

Version:
0.6

clear_environment

public  clear_environment()
removes all environment variables from the "custom environment". See use_custom_env for more information about the custom environment.

Version:
0.6

set_env_var

public  set_env_var(p_env_name varchar2, 
                    p_env_value varchar2)
sets an environment variable in the custom environment. An existing entry with the given variable name is being overwritten. See use_custom_env for more information about the custom environment.
Parameters:
p_env_name - the name of the environment variable.
p_env_value - the value for this environment variable.

Version:
0.6

remove_env_var

public  remove_env_var(p_env_name varchar2)
removes an environment variable from the custom environment. See use_custom_env for more information about the custom environment.
Parameters:
p_env_name - the name of the environment variable.

Version:
0.6

get_env_var

public varchar2 get_env_var(p_env_name varchar2)
gets the value of the specified environment variable from the custom environment. See use_custom_env for more information about the custom environment.
Parameters:
p_env_name - the name of the environment variable.
Returns:
the value for this environment variable.

Version:
0.6

load_env

public  load_env()
copies the default environment (in which the "oracle" process runs) into the custom environment. A java.lang.RuntimePermission (getenv.*) is needed in order to execute this.

This is available in 11g oder higher

Version:
0.6

load_env

public  load_env(p_env_name varchar2)
copies the specified environment variable from the default environment (in which the "oracle" process runs) into the custom environment.

This is available in 11g oder higher
Parameters:
p_env_name - the name of the environment variable to copy.

Version:
0.6

use_custom_env

public  use_custom_env()
Use "custom" environment variables defined by this package for the "exec" calls. Operating system commands can be executed with the "custom" or "default" environment. The "default" environment is the environment of the Oracle processes - the "custom" environment can be set using the set_env_var, get_env_var or remove_env_var calls.
The use_custom_env and use_default_env calls allow to switch between the default and the custom environment.

Version:
0.6

use_default_env

public  use_default_env()
Use "default" environment variables defined by this package for the "exec" calls. Operating system commands can be executed with the "custom" or "default" environment. The "default" environment is the environment of the Oracle processes - the "custom" environment can be set using the set_env_var, get_env_var or remove_env_var calls.
The use_custom_env and use_default_env calls allow to switch between the default and the custom environment.

Version:
0.6

set_Shell

public  set_Shell(p_shell_path varchar2, 
                  p_shell_switch varchar2)
sets the path and "command switch" for the shell to use if set_exec_in_shell was called. A typical call on Unix is: 
OS_COMMAND.SET_SHELL('/bin/sh', '-c');
On Windows: 
OS_COMMAND.SET_SHELL('C:\WINDOWS\SYSTEM32\CMD.EXE', '/C');
These are also the default settings.
Parameters:
p_shell_path - path to the shell executable e.g. "/bin/sh"
p_shell_switch - shell executable switch to execute a command e.g. "-c"

Version:
0.9

get_shell

public varchar2 get_shell()
returns the operating system call for the current shell. This shell is being used for the exec calls. 
select OS_COMMAND.GET_SHELL shell from dual;

SHELL
------
/bin/sh -c
Returns:
the current shell and "execute command switch"

Version:
0.9

set_exec_in_shell

public  set_exec_in_shell()
Execute operating system commands (exec procedures and functions) in a shell. The shell defaults /bin/sh -c on Unix and to C:\WINDOWS\SYSTEM32\CMD.EXE /C on Linux. The set_shell procedure allows to change the shell.
Use this to execute e.g. complex Unix shell commands: 
begin
OS_COMMAND.SET_EXEC_IN_SHELL;
end;
/

select os_command.exec_clob('/bin/ls -la /usr/lib | /usr/bin/grep .so | /usr/bin/wc -l') complex_command from dual;

COMPLEX_COMMAND
---------------
345

Version:
0.9

set_exec_direct

public  set_exec_direct()
Execute operating system commands (exec procedures and functions) directly. Note that complex shell commands will result in an error. Try set_exec_in_shell in those cases. 
begin
OS_COMMAND.SET_EXEC_DIRECT;
end;
/
select os_command.exec_clob('/bin/ls -la /usr/lib | /usr/bin/grep .so | /usr/bin/wc -l') complex_command from dual;
COMPLEX_COMMAND
---------------------------------------------------------------
Error during creation of the process

begin
OS_COMMAND.SET_EXEC_IN_SHELL;
end;
/
select os_command.exec_clob('/bin/ls -la /usr/lib | /usr/bin/grep .so | /usr/bin/wc -l') complex_command from dual;
COMPLEX_COMMAND
---------------
345

Version:
0.9

exec_CLOB

public clob exec_CLOB(p_command varchar2, 
                      p_stdin blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - binary content (BLOB) to be passed to STDIN
Returns:
content written by the shell command to STDOUT as CLOB

Version:
0.1

exec_CLOB

public clob exec_CLOB(p_command varchar2, 
                      p_stdin clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - character content (CLOB) to be passed to STDIN
Returns:
content written by the shell command to STDOUT as CLOB

Version:
0.1

exec_BLOB

public blob exec_BLOB(p_command varchar2, 
                      p_stdin blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - binary content (BLOB) to be passed to STDIN
Returns:
content written by the shell command to STDOUT as BLOB

Version:
0.1

exec_BLOB

public blob exec_BLOB(p_command varchar2, 
                      p_stdin clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - character content (CLOB) to be passed to STDIN
Returns:
content written by the shell command to STDOUT as BLOB

Version:
0.1

exec_CLOB

public clob exec_CLOB(p_command varchar2)
executes a shell command
Parameters:
p_command - the command string to execute
Returns:
content written by the shell command to STDOUT as CLOB

Version:
0.1

exec_BLOB

public blob exec_BLOB(p_command varchar2)
executes a shell command
Parameters:
p_command - the command string to execute
Returns:
content written by the shell command to STDOUT as BLOB

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - binary content (BLOB) to be passed to STDIN
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - character content (CLOB) to be passed to STDIN
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2)
executes a shell command
Parameters:
p_command - the command string to execute
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin clob, 
                   p_stdout clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - character content (CLOB) to be passed to STDIN
p_stdout - OUT Parameter: content written by the shell command to STDOUT as CLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin clob, 
                   p_stdout blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - character content (CLOB) to be passed to STDIN
p_stdout - OUT Parameter: content written by the shell command to STDOUT as BLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin blob, 
                   p_stdout blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - binary content (BLOB) to be passed to STDIN
p_stdout - OUT Parameter: content written by the shell command to STDOUT as BLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin blob, 
                   p_stdout clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - binary content (BLOB) to be passed to STDIN
p_stdout - OUT Parameter: content written by the shell command to STDOUT as CLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdout clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdout - OUT Parameter: content written by the shell command to STDOUT as CLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdout blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdout - OUT Parameter: content written by the shell command to STDOUT as BLOB. This must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
0.1

exec

public number exec(p_command varchar2, 
                   p_stdin clob, 
                   p_stdout clob, 
                   p_stderr clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - content to be passed to STDIN
p_stdout - CLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - CLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0

exec

public number exec(p_command varchar2, 
                   p_stdin clob, 
                   p_stdout blob, 
                   p_stderr blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - content to be passed to STDIN
p_stdout - BLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - BLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0

exec

public number exec(p_command varchar2, 
                   p_stdin blob, 
                   p_stdout blob, 
                   p_stderr blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - content to be passed to STDIN
p_stdout - BLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - BLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0

exec

public number exec(p_command varchar2, 
                   p_stdin blob, 
                   p_stdout clob, 
                   p_stderr clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdin - content to be passed to STDIN
p_stdout - CLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - CLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0

exec

public number exec(p_command varchar2, 
                   p_stdout clob, 
                   p_stderr clob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdout - CLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - CLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0

exec

public number exec(p_command varchar2, 
                   p_stdout blob, 
                   p_stderr blob)
executes a shell command
Parameters:
p_command - the command string to execute
p_stdout - BLOB to store STDOUT output in - must be a valid LOB locator (persistent or temporary LOB)
p_stderr - BLOB to store STDERR output in - must be a valid LOB locator (persistent or temporary LOB)
Returns:
the operating system return code (0 for success)

Version:
1.0
Overview  Deprecated  Index  Generator  
File and operating system interaction with SQL and PLSQL
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD