Home > Miscellaneous > ExtProc in Oracle

ExtProc in Oracle

What is it?

Extproc is an agent to call External programs from inside the database.

Pl/sql supports creation of two types of stored objects, having a body or not having a body. The bodiless procedures can call java or C programs inside them. These are called External Programs.

When running external code, you do not want to run that inside the Oracle server process. That code may be unstable. May be buggy. It can can corrupt memory.

Thus if it runs inside the Oracle server process, it is risky.. it can damage/corrupt Oracle process memory.. and that it turn can cause the process to damage/corrupt data in the database that then is written to disk.

So external code (like that in DLLs or Shared Objects) cannot be safely loaded and executed inside the Oracle server process – and needs to be done via a separate “proxy” process. If this process gets damaged.. so be it. It does not impact the Oracle server process that is communicating with that external process.

So how does the Oracle server start this external process it? Courtesy of the Listener. The Listener is the one that starts all client-server for users/applications (dedicated server processes). So it is already part of the job description so to speak.

The Listener is therefore configured for starting external processes – and it can do a couple of different ones. It can start an external process that interacts with (load/exec) a DLL/Shared Object.

It can load an Oracle Agent process – like a Heterogeneous Agent that, via ODBC, can communicate with anything from an Excel Spreadsheet to a SQL-Server/Postgres/etc database. In this case, you also need a corresponding TNS name entry for it (or manually code a connection string) when defining the database link for that external database.

Below setting should be made before using ExtProc

in tnsnames.ora

 (connect_data=(sid=<<any name of our choice>>)

in listener.ora

(sid_name=<<any name of our choice>>)
(ORACLE_SID=<<database_sid>>)  //Optional
(oracle_home= <<oracle_home path>>)


How it works?

Example 1:

Create a small program in Java to calculate factorial

public class fact {
public static int calcf (int n) {
if (n == 1) return 1;
else return n * calcf (n - 1) ;

Next load this procedure inside my db using a built-in Loadjava.

loadjava -user <<username>>/<<password>> /home/oracle/fact.java

Now create a function using the same program.

 NAME 'fact.calcf
 (int) return int'; 
 Function created.

Add following entries in,




  (oracle_home= /ora10/app/oracle/product/11.2.0)

Start the listener

lsnrctl start listener_extproc

Now using this function , we can call the java program. To load that program, we need that Extproc agent.

SQL> select proc1(5) from dual;

SQL> select proc1(6) from dual; 

Example 2:

10 Steps to Create and Run a Sample 
                External Procedure Program on Unix [ID 312564.1]

Example 3:

How to Call a UNIX Shell Script From PL/SQL 
                Using External Procedures [ID 168065.1]
Categories: Miscellaneous Tags:
  1. No comments yet.
  1. No trackbacks yet.

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

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.


Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!

%d bloggers like this: