Run Operating System (OS) Command in Oracle PL/SQL: A Web Service Approach

Three years back when i was working on a project in which there was a requirement for execution of OS commands from Oracle 11g, i found the following options:

Option 1: Create Java Stored Procedure and access it through PL/SQL wrapper
Option 2: Use DBMS_SCHEDULER package to schedule an OS command
Option 3: Create a native shared library in C

Technical implementation details of the above options can be found here [1], [2], [3]

After weighing the pros and cons of each approach, i went with the first option. However recently when i was exploring python network library, i found that this simple command can share my files/folder over HTTP interface

python -m SimpleHTTPServer 80

This made me to think that can we use the HTTP interface for invocation of Operating system commands like:

  • running a shell/batch script from Oracle PL/SQL
  • getting list of running processes
  • getting list of files in a folder
  • getting contents of log file

A simple service architectural diagram is shown below:

os_service_architecture

Now for implementing a proof of concept, i am using python’s subprocess.Popen class (as discussed in my previous blog) for interacting with operating system and cherrypy — a python’s minimalist web framework. A step by step instruction for creating such service is given below:

Step 1: Setup Cherrypy

a) Dowonload cherrypy and unzip to a folder say “c:/cherrypy”

b) Change your working directory to “c:/cherrypy” and run the following command

python setup.py install

Step 2: Create a python script named ‘osservice.py’ and copy the following code into it

import cherrypy, subprocess

#overide the default cherrypy port from 8080 to say 9090
cherrypy.config.update({'server.socket_port': 9090,})

#define set of commands that you want to allow to be executed over this interface
command_set = ['ls','ps','dir','head','tnsping','sqlldr']

class ServiceManager:

   def run(self, command, working_directory=None, environment=None):

      #check whether the given command is allowed over this interface or not
      if command.split()[0] in command_set:

         #run the command
         p = subprocess.Popen(command, shell=False, universal_newlines=True, cwd=working_directory, env=environment, stdout=subprocess.PIPE, stderr=subprocess.PIPE)

         #read the stdout and stderror messages
         output = p.stdout.read()
         error = p.stderr.read()

         #if no error reported then format the stdout message
         if len(error) == 0:
            str = self.format(output)
         else:
            str = self.format(error)

      else:
         str = 'Service Error: This command is not allowed over this interface!'

      #return the message
      return str

   run.exposed = True

   def format(self,str):
      lst = str.split('\n')
      result = "
"      for row in lst:         result += "
"      result += "</pre>
<table>
<tbody>
<tr>
<td>"
 result += row
 result += "</td>
</tr>
</tbody>
</table>
<pre>"
      print result
      return result

cherrypy.quickstart(ServiceManager())

Step 3: Start the service by issuing the following command

python c:/myprojects/osservice.py

Step 4: Test the service in web browser. Note the following url should display the contents of the folder from where the python command was issued in step3

http://localhost:9090/run?command=ls

Step 5: Test the service from sqlplus (connect as sysdba).

select utl_http.request(utl_url.escape(‘http://localhost:9090/run?command=ls -l’)) from dual;

select utl_http.request(utl_url.escape(‘http://localhost:9090/run?command=head -5 c:/myprojects/osservice.py’)) from dual;

select utl_http.request(utl_url.escape(‘http://localhost:9090/run?command=tnsping my_remotedb’))

Please note that the current implementation of osservice.py formats all the output as html, however this can be modified as per your the requirement by refining the code of format method in ServiceManager class.

When comparing this option with other 3 options(as described in the starting) in terms of security, this interface can pose a higher security risk. However such risks can be mitigated with the following strategies:

  • only allow this interface to be accessible from local machine
  • limit the type of commands that may be executed over this interface

I think with proper risk mitigation strategies, this interface can open up numerous opportunities for performing operating system level operations from within sql or plsql in minimum amount of time.

About these ads

About Moiz

I am working as a data warehouse architect. My areas of expertise includes data modeling. I use Python for data integration and system administration related tasks.
This entry was posted in File system, Oracle and tagged , , , , , , , , , , , , , , , , , . Bookmark the permalink.

3 Responses to Run Operating System (OS) Command in Oracle PL/SQL: A Web Service Approach

  1. Pingback: Run Oracle SQL*Loader from SQL or PL/SQL | Python Recipes for Data Administrators

  2. Mano says:

    the code posted here has syntax error. could you please fix this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s