Executing Linux command from Oracle PLSQL

In this blog we are going to get an idea on how we can execute OS commands from PLSQL code. In many cases we get some requirements like to run or use some basic OS commands which can later be used in our business logic.

To start with we have to created a “C” program . Create a file called “extprocsh.c” with below code under “/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib” path.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
void sh(char *);
int sh2(char *);
void sh( char  *cmd )
{
        int num;
        num = system(cmd);
}
int sh2( char  *cmd )
{
        int num;
        num = system(cmd);
        return num;
}

Now compiled the “extprocsh.c”  program with below steps:

[oracle@**********]$ gcc -fPIC -c extprocsh.c
[oracle@**********]$ gcc -shared -static-libgcc -o extprocsh.so extprocsh.o
[oracle@**********]$ chmod 775 extprocsh.so

After this three files are created .

Now go to “/home/oracle/app/oracle/product/11.2.0/dbhome_1/hs/admin” path and opened “extproc.ora” file.

And add the setting as below:

# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
SET EXTPROC_DLLS=ANY

 

Now we have to make some changes to “tnsnames.ora” and  “listener.ora” files which will be under  “/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin” path .

listner.ora file changes :

From :

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app/oracle

To:

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1 )
      (PROGRAM = extproc)
      (ENV = "EXTPROC_DLLS=ANY:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/extprocsh.so,LD_LIBRARY_PATH=/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /home/oracle/app/oracle

tnsname.ora changes :

From :

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11204 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11204)
    )
  )

To:

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11204 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db11204)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = 
        (PROTOCOL = IPC)
        (KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )  

Now we have to restart or reload listener :

lsnrctl reload

Check the service and status:

 lsnrctl service   
 lsnrctl status

Now created the library from Oracle:

create or replace library EXTPROCextprocsh_LIB
     as '/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/extprocsh.so'; 

Create a oracle function to utilize the library:

CREATE OR REPLACE FUNCTION msi_extprocsh (cmd IN CHAR) RETURN number
AS EXTERNAL
   NAME "sh2"
   LIBRARY EXTPROCextprocsh_LIB
   LANGUAGE C
PARAMETERS (
    cmd STRING);

Finally let’s test the function :

declare
  retval number;
begin
  retval := msi_extprocsh('/bin/ls /home/oracle/app/ > /home/oracle/app/scripts/extproc/out.txt');
 if (retval <>0) then
   dbms_output.put_line('Error');
else
  dbms_output.put_line('Passed!');
end if;
end;

Now you can go to “/home/oracle/app/scripts/extproc/” path and check if “out.txt” file is created or not and what data you are able to see under this file.

Thank you for reading my blog , the reference is taken from OTN Doc ID 99136.1 . Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

One thought on “Executing Linux command from Oracle PLSQL

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