Solved

Can I run a UNIX script from within an Oracle PL/SQL procedure?

Posted on 2009-05-08
7
1,272 Views
Last Modified: 2013-12-19
Can I run a UNIX script from within an Oracle PL/SQL procedure?

If yes, then please show me an example..
0
Comment
Question by:joekeri
  • 5
  • 2
7 Comments
 

Expert Comment

by:newfiepicks
ID: 24342299
Yes you would do it using external procedure to run host commands. A step by step example is attached
extproc.doc.docx
0
 

Author Comment

by:joekeri
ID: 24363636
Hello newfiepicks...

I am unable to read the attachment you sent. I tried al my avaible word processign programs and nothing opens it up. Can you send it as a TXT file instead?
0
 

Expert Comment

by:newfiepicks
ID: 24365829
attached a RTF
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Accepted Solution

by:
newfiepicks earned 50 total points
ID: 24365841
To do this, I use a simple oracle external procedure. The example Im presenting below has been tested and used on Oracle 10gR2 and RedHat Linux AS3.
Heres how it works:
1) First create a directory on your unix box to hold the C program we will write, its makefile, and the compiled .so file. On my server I created the directory using the command:
mkdir p /u01/app/usfapp/oracle_external_procs/oracle_host


2) Change to the directory you just created, all files created below will be created in this directory. Make sure you are the oracle user and that the oracle user has write privileges to the directory you just created.


3) Create a file named oracle_host.c. The contents of this file will be:
#include

int RunCmd(char *cmd)
{
        return(system(cmd));
}


4) Create the file named makefile. The contents of this file will be:
oracle_host: oracle_host.o
        gcc -shared -o oracle_host.so oracle_host.o
Note that the character before the gcc start to the second line MUST be a tab. It must not be anything else.


5) Now run the command make
The output on my server looks like:
[/u01/app/usfapp/oracle_external_procs/oracle_host]
banner@usfbannerte [TRNG] > make
gcc -shared -o oracle_host.so oracle_host.o


6) When you reach this step, you should have a file in the current directory named oracle_host.so.

Congratulations, the hard part is done!


7) Now we need to tell oracle about the external procedure. Login to oracle through SQL Plus, or your favorite Oracle tool such as TOAD or SQL Navigator. Login as the system user, or as a user who as the appropriate privileges to create libraries.


8) Create the library in oracle that will tell the system the location of our newly compiled oracle_host.so file. Run the following command in your SQL Plus session.
CREATE LIBRARY usf_host_lib as '/u01/app/usfapp/oracle_external_procs/oracle_host/oracle_host.so';
The user must have the CREATE LIBRARY privilege who will be creating the library. In my server, as the system user I run the following as the system user:
GRANT CREATE LIBRARY TO tima;


9) Create the PL/SQL function that we will use to call the library we created and execute our host commands.
CREATE OR REPLACE FUNCTION USF_RUN_HOST_CMD (p_Cmd IN VARCHAR2) RETURN PLS_INTEGER AS
EXTERNAL
LIBRARY USF_HOST_LIB
NAME "RunCmd"
PARAMETERS (p_Cmd STRING);


10) Now, we need to do a little server configuration and ensure that the listener.ora and tnsnames.ora files on our database server are setup so that our external procedures can be called. First off the listener.ora file needs to have appropriate entries. In my listener.ora file I added the sections:
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
And the section:
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
My complete listener.ora file looks like:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = TRNG.db.timarcher.com)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = TRNG)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db.timarcher.com)(PORT = 16969))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
  )


11) Now we change our tnsnames.ora file. I added the following to the bottom of my tnsnames.ora file:
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
My complete tnsnames.ora file now looks like:
TRNG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db.timarcher.com)(PORT = 16969))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TRNG. db.timarcher.com)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


12) Now that the server configuration files have been setup, we need to restart our listener. From the unix shell prompt, as the oracle user we run the following commands:
lsnrctl stop
lsnrctl start


13) Finally, you can call our new function to execute a host command. An example of a PL/SQL script to echo the word test to the file /tmp/tim.txt is:
declare
  nRetVal NUMBER;
begin
  nRetVal := USF_RUN_HOST_CMD('echo test >> /tmp/tim.txt');
  dbms_output.put_line('RetValue:'||nRetVal);
end;
The function USF_RUN_HOST_CMD will pass back the unix return code from the process you run. For most processes a 0 means success, and a non zero value means it failed.

0
 

Author Closing Comment

by:joekeri
ID: 31579594
excellent! thanks for your help..
0
 

Expert Comment

by:newfiepicks
ID: 24365867
Trying to keep formatting. dashes are being removed hopefully this code snippet will keep formatting.
To do this, I use a simple oracle external procedure. The example Im presenting below has been tested and used on Oracle 10gR2 and RedHat Linux AS3.

Heres how it works:

1) First create a directory on your unix box to hold the C program we will write, its makefile, and the compiled .so file. On my server I created the directory using the command:

mkdir p /u01/app/usfapp/oracle_external_procs/oracle_host
 
 

2) Change to the directory you just created, all files created below will be created in this directory. Make sure you are the oracle user and that the oracle user has write privileges to the directory you just created.
 
 

3) Create a file named oracle_host.c. The contents of this file will be:

#include 
 

int RunCmd(char *cmd)

{

        return(system(cmd));

}
 
 

4) Create the file named makefile. The contents of this file will be:

oracle_host: oracle_host.o

        gcc -shared -o oracle_host.so oracle_host.o

Note that the character before the gcc start to the second line MUST be a tab. It must not be anything else.
 
 

5) Now run the command make

The output on my server looks like:

[/u01/app/usfapp/oracle_external_procs/oracle_host]

banner@usfbannerte [TRNG] > make

gcc -shared -o oracle_host.so oracle_host.o
 
 

6) When you reach this step, you should have a file in the current directory named oracle_host.so. 
 

Congratulations, the hard part is done!
 
 

7) Now we need to tell oracle about the external procedure. Login to oracle through SQL Plus, or your favorite Oracle tool such as TOAD or SQL Navigator. Login as the system user, or as a user who as the appropriate privileges to create libraries.
 
 

8) Create the library in oracle that will tell the system the location of our newly compiled oracle_host.so file. Run the following command in your SQL Plus session.

CREATE LIBRARY usf_host_lib as '/u01/app/usfapp/oracle_external_procs/oracle_host/oracle_host.so';

The user must have the CREATE LIBRARY privilege who will be creating the library. In my server, as the system user I run the following as the system user:

GRANT CREATE LIBRARY TO tima;
 
 

9) Create the PL/SQL function that we will use to call the library we created and execute our host commands.

CREATE OR REPLACE FUNCTION USF_RUN_HOST_CMD (p_Cmd IN VARCHAR2) RETURN PLS_INTEGER AS

EXTERNAL

LIBRARY USF_HOST_LIB

NAME "RunCmd"

PARAMETERS (p_Cmd STRING);
 
 

10) Now, we need to do a little server configuration and ensure that the listener.ora and tnsnames.ora files on our database server are setup so that our external procedures can be called. First off the listener.ora file needs to have appropriate entries. In my listener.ora file I added the sections:

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ANY")

    )

And the section:

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

My complete listener.ora file looks like:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ANY")

    )

    (SID_DESC =

      (GLOBAL_DBNAME = TRNG.db.timarcher.com)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

      (SID_NAME = TRNG)

      (ENVS = "EXTPROC_DLLS=ANY")

    )

  )
 

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = db.timarcher.com)(PORT = 16969))

    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

  )
 
 

11) Now we change our tnsnames.ora file. I added the following to the bottom of my tnsnames.ora file:

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

My complete tnsnames.ora file now looks like:

TRNG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = db.timarcher.com)(PORT = 16969))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TRNG. db.timarcher.com)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )
 
 

12) Now that the server configuration files have been setup, we need to restart our listener. From the unix shell prompt, as the oracle user we run the following commands:

lsnrctl stop

lsnrctl start
 
 

13) Finally, you can call our new function to execute a host command. An example of a PL/SQL script to echo the word test to the file /tmp/tim.txt is:

declare

  nRetVal NUMBER;

begin

  nRetVal := USF_RUN_HOST_CMD('echo test >> /tmp/tim.txt');

  dbms_output.put_line('RetValue:'||nRetVal);

end;

The function USF_RUN_HOST_CMD will pass back the unix return code from the process you run. For most processes a 0 means success, and a non zero value means it failed.

Open in new window

0
 

Expert Comment

by:newfiepicks
ID: 24365951
Found the link where I got the original information. Should be all nice and formatted here:

http://timarcher.com/node/9
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now