Archive

Posts Tagged ‘extproc’

ExtProc in Oracle

April 1, 2013 Leave a comment

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

extproc_connection_data=
(description=
 (address=(protocol=ipc)(key=<<database_sid>>))
 (connect_data=(sid=<<any name of our choice>>)
 )
)

in listener.ora

listener_extproc=
(address=(protocol=ipc)(key=<<database_sid>>>))
sid_list_listener_extproc=
(sid_list=
(sid_desc=
(sid_name=<<any name of our choice>>)
(ENVS = EXTPROC_DLLS=ANY)
(ORACLE_SID=<<database_sid>>)  //Optional
(oracle_home= <<oracle_home path>>)
(program=extproc)
)
)

 

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.

SQL> CREATE OR REPLACE FUNCTION proc1
 (N NUMBER)
 RETURN NUMBER
 AS
 LANGUAGE JAVA
 NAME 'fact.calcf
 (int) return int'; 
/
 Function created.

Add following entries in,

tnsnames.ora

extproc_connection_data=
(description=
 (address=(protocol=ipc)(key=bharatdb))
 (connect_data=(sid=abc)
 )
)

listener.ora

listener_extproc=
  (address=(protocol=ipc)(key=bharatdb))
sid_list_listener_extproc=
  (sid_list=
  (sid_desc=
  (sid_name=abc)
  (ENVS = EXTPROC_DLLS=ANY)
  (ORACLE_SID=bharatdb)
  (oracle_home= /ora10/app/oracle/product/11.2.0)
  (program=extproc)
  )
)

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;
 PROC1(5)
 ----------
 120

SQL> select proc1(6) from dual; 
 PROC1(6)
----------       
720

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:
Oracle database internals by Riyaj

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

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!