Skip to Main Content
  • Questions
  • Creating dll for executing external procedure('c' language)

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ramasubbu.

Asked: December 21, 2000 - 11:40 pm UTC

Last updated: June 13, 2018 - 2:29 am UTC

Version: Version 8i

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I am using Wint NT,Oracle 8i(server) and C language.
My goal is calling 'c' routine thru stored procedure.
For that I had made neccesary steps.I had modified Tnsnames and listener entry as follows.

Tnsnames entry
------------------------
extproc_connection_data =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL=IPC)
(KEY=extproc_key) )
(CONNECT_DATA =
(SID = extproc_agent)
)


Listener entry
-------------------
EXTERNAL_PROCEDURE_LISTENER =

(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=ipc)
(KEY=extproc_key)
)
)

SID_LIST_EXTERNAL_PROCEDURE_LISTENER =

(SID_LIST =
(SID_DESC = (SID_NAME=extproc_agent)
(ORACLE_HOME=d:\oracle8i)
(PROGRAM=extproc)
)
)

Then I want to create the dll.Here I have a doubt how to use the dll.Any my 'C' routine is
/*This program will gives square the number*/
/*square.c*/
#include <stdio.h>
main(int argc,char *argv[])
{
int no;
no = argv[1];
return no*no;
}
My next question is "Would I need to include any OCI statements inside of 'C' routines?".If say yes,Please tell me how to I do it?.


and Tom said...

You do not create a DDL with a main. You create an EXE with a main.

You do not *need* to have OCI in your extproc. Many (most) do however -- to handle errors and such.

Look in $ORACLE_HOME\plsql\demo for ext*.*. There is a full example of an extproc routine there including the C code for you to review.

It is recommended to test the installation of external procedures with the demonstration programs. The reason for this is twofold

ƒá Oracle support is aware of and can help setup/configure the demonstration program. If we work with an example they know about ¡V we¡¦ll be able to resolve any issues that much faster

ć The supplied demonstration program will illustrate the correct approach for compiling and linking on your platform

The demonstration program is found in your $ORACLE_HOME/plsql/demo directory on all releases of Oracle8i. The steps we should follow to make the demonstration are:

Compile the extproc.c code into a DLL or .so/.sl/.a file.

The process for doing that on NT is to simply cd to the ORACLE_HOME\plsql\demo directory and type make (they have supplied a Make.BAT file in that directory):

C:\oracle\plsql\demo>make
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 10.00.5270 for 80x86
Copyright (C) Microsoft Corp 1984-1995. All rights reserved.

extproc.c
Microsoft (R) 32-Bit Incremental Linker Version 3.00.5270
Copyright (C) Microsoft Corp 1992-1995. All rights reserved.

/out:extproc.dll
/dll
/implib:extproc.lib
/debug
..\..\oci\lib\msvc\oci.lib
msvcrt.lib
/nod:libcmt
/DLL
/EXPORT:UpdateSalary
/EXPORT:PercentComm
/EXPORT:PercentComm_ByRef
/EXPORT:EmpExp
/EXPORT:CheckEmpName
/EXPORT:LobDemo
extproc.obj
C:\oracle\plsql\demo>

In Unix, you will do much the same but the command to compile is different. There it will look like:


$ make -f demo_plsql.mk extproc.so
/usr/ccs/bin/make -f /export/home/ora816/rdbms/demo/demo_rdbms.mk extproc_callback SHARED_LIBNAME=extproc.so OBJS="extproc.o"
¡K..

After that command completes, you will have a .DLL file on NT or a .so/.sl/.a file on Unix (extension depends on platform. For example, Solaris is .so, HP/UX will be .sl).

Set up the scott/tiger account

For this demonstration program to work correct, we will need a scott/tiger demo account. If your database does not have a scott/tiger account, you can set one up by issuing:

SQL> grant connect, resource to scott identified by tiger;

That will create the scott user and give it the ability to connect to the database and create objects like tables and packages. You will most like want to assign this use a default tablespace other then SYSTEM and a temporary tablespace as well.

Given that we have the scott/tiger account, we must provide it with one additional grant before proceeding. The SCOTT user will need the ¡§CREATE LIBRARY¡¨ privilege. This privilege will allow SCOTT to issue the create library statement needed for external procedures (more on that in a moment). As this is a fairly powerful privilege ¡V you will want to consider revoking it from SCOTT after running the example. To accomplish this you will:

SQL> grant create library to SCOTT;

When connected as someone who has the CREATE LIBRARY privilege with the ADMIN option themselves (eg: SYSTEM or some DBA account).

Lastly, you will want to ensure the EMP/DEPT demo tables are in the scott schema and populated with data. You can verify this via:

SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> select count(*) from dept;

COUNT(*)
----------
4

If these tables do not exist or do not have any data ¡V you can rebuild them by executing demodrop.sql (to remove them) and demobld.sql (to create and populate them). These scripts are found in $ORACLE_HOME/sqlplus/demo and are meant to be executed via sqlplus when logged in as SCOTT.

Create the demolib LIBRARY

The next step in the demonstration is to create the library object in Oracle. This object is simply a mapping of a library name (some 30 character name you choose) to a physical operating system file. This OS file is your compiled binary we made in the first step. The user who issues the create library statement must have the CREATE LIBRARY privilege granted to them either via a ROLE or directly. This privilege is considered a fairly powerful one and should be given out only to those accounts you trust with the privilege. It will allow them to execute any arbitrary C code they wish on your server machine using the account the extproc service executes with. This is one of the reasons you would want to configure the extproc service to execute under some account other then the Oracle software owner (to avoid the inadvertent or malicious overwriting of your System tablespace for example).

In order to accomplish this step, you use SQLPlus and execute:

SQL> connect scott/tiger
Connected.
SQL> create or replace library demolib as 'c:\oracle\plsql\demo\extproc.dll';
2 /

Library created.

The name DEMOLIB is what the developers of the demo chose as the name of their library ¡V you must use DEMOLIB. The file name c:\oracle\plsql\demo\extproc.dll may be different for you ¡V I built the example directly in the demo directory of my Oracle_Home. You may have a different Oracle_Home then I do or you might have built the demo in some other directory entirely ¡V you should use the actual path name of the extproc.dll you built in the first step.

Installing and running the demo packages

The last step in the demo is to install the PLSQL code that maps to routines in the demolib library. We are not interested at this point in what they look like as much as we are what they output ¡V we are using this demo to test external procedures, we¡¦ll look at how we code them in a bit.

Now we will simply execute:

SQL> connect scott/tiger
Connected.
SQL> @extproc

When in the $ORACLE_HOME/plsql/demo directory. What we expect to see is:

SQL> @extproc

Package created.

No errors.

Package body created.

No errors.
ENAME : ALLEN
JOB : SALESMAN
SALARY : 1600
COMMISSION : 300
Percent Commission : 18.75
ENAME : MARTIN
JOB : SALESMAN
SALARY : 1250
COMMISSION : 1400
Percent Commission : 112
Return value from CheckEmpName : 0
old_ename value on return : ANIL
ENAME : 7369
HIREDATE : 17-DEC-80
Employee Experience Test Passed.
***************************************

PL/SQL procedure successfully completed.
¡K. (other feedback would be here as well)¡K.

That shows that external procedures are correctly configured and ready to be used on our system ¡V the first procedure executes many of the routines in the extproc.dll we created. This shows conclusively that all is configured correctly.

In the event of an incorrectly configured system, you would expect to see:

SQL> @extproc

Package created.

No errors.

Package body created.

No errors.
BEGIN demopack.demo_procedure; END;

*
ERROR at line 1:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SCOTT.DEMOPACK", line 61
ORA-06512: at "SCOTT.DEMOPACK", line 103
ORA-06512: at line 1

That implies that it is time to revisit your tnsnames and listener.ora configuration.




Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 21, 2001 - 7:38 pm UTC

hi tom

i don't have make.bat in
%oracle_home%\plsql\demo directoty
it's prcmake.bat

i read the text in side and it is telling
-- -----------------
Rem Use this script to create executables for the demo scripts with PL/SQL
Rem blocks embedded in C code. To create all executables, at the prompt type:

if (%1) == () goto usage

Rem MSVCDir is defined by running a batch file, vcvars32.bat,
Rem in Microsoft Visual Studio directory. You can find it using
Rem Windows Explorer.
Rem Environment Check:
if (%MSVCDir%) == () goto msvcdir_error
-- ---------
i don't have visual studio

I am running personal oracle 8.1.7 on windows 98

i want to call executable from pl/sql at my work.
so please respond as soon as possible




Tom Kyte
November 21, 2001 - 7:47 pm UTC

If you don't have MAKE.BAT you don't have a version of Oracle with extprocs. prcmake.bat doesn't make the extprocs. you need make.bat.

if you

dir ext*

in that directory -- you should see:

Directory of C:\oracle\plsql\demo

02/29/2000 02:03p 17,853 extproc.c
02/29/2000 02:03p 15,779 extproc.sql



if not, you don't have the extproc stuff.

I've never installed Personal Oracle -- I suppose there is a chance its "not there".

try downloading

</code> http://asktom.oracle.com/~tkyte/extproc.tar <code>

I've tarred up (winzip will extract it) the source and make.bat. Lets start there and see what happens.


A reader, November 21, 2001 - 8:07 pm UTC

Tom
So FAST REPLY!!!!!!!!!!!!!!!!!!!!!!!!!!!

IT REALLY HELPS ME A LOT
CAN'T BELIEVE IT

I REALLY APPRICIATE IT

YOU ARE REALLY BOON TO DEVLOPER COMMUNITY

HAVE NICE THANKSGIVING








A reader, November 21, 2001 - 8:11 pm UTC

Another question.

External procedure in C is better
OR java stored procedure is better
(Consider Resources and perfomance of database)

Tom Kyte
November 22, 2001 - 8:42 am UTC

It is a double edged sword.

C will be faster, its closer to the machine.

Java will be a little slower but it is approaching the speed of C with regards to performance when NCOMP'ed (natively compiled)

C will run outside the address space of the server -- it will take another process. Java runs inside the database, in the same address space. No extra process.

Calling a java stored procedure for the first time in a session takes a bit of time (jvm startup). Its getting better with each release but its still rather large the first time. A C extproc is not. This mostly counts in a stateless environment where "for the first time in a session" happens very often.

My mantra in general is:

o if you can do it in SQL, do it
o if not, do it in PLSQL, if you cannot do it there (eg: ftp'ing a file, writing a binary file, etc)
o do it in Java in a simple stored procedure
o if Java won't do it (you have a 3rd party api, existing C code) do it in C as an extproc.

make.bat and MS .net

Jeff Yuan, June 18, 2003 - 12:41 am UTC

First of all, thank you very much for your excellent coverings on extproc in your book and here.

I believe the existing make.bat (from Oracle 9.0.1 installation) works with MS Visual Studio. Now in my box, I have only MS Visual Studio .NET installed, including MS Visual C++ .NET. I can not have MS Visaul C++ in same box.(?)

When I try to run make.bat, I doesn't work. I don't know much about .NET. Could you please look into this? Is some modification on the make.bat necessary to find the C++ compiler?

Regards,
Jeff
~~~~~~~~~~~~~~
C:\oracle\plsql\demo>make.bat
.
Environment variable MSVCDIR must be set before running this batch file.
Please run vcvars32.bat from MS Visual Studio directory.
.

C:\>cd program files\microsoft visual studio .net

C:\Program Files\Microsoft Visual Studio .NET>cd vc7\bin

C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin>vcvars32.bat

C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin>"C:\Program Files\Microsof
t Visual Studio .NET\Common7\Tools\"vsvars32.bat
Setting environment for using Microsoft Visual Studio .NET tools.
(If you also have Visual C++ 6.0 installed and wish to use its tools
from the command line, run vcvars32.bat for Visual C++ 6.0.)

C:\>cd oracle\plsql\demo

C:\oracle\plsql\demo>make
Files\Microsoft was unexpected at this time.


Tom Kyte
June 18, 2003 - 6:04 pm UTC

looks like there might be an issue with spaces in the filenames.

maybe you can use the 8.3 filenames instead. I don't have access to any windows machines, sorry.

vc

Aladdin, September 17, 2003 - 10:44 am UTC

sir how could i use external dll files not made by c language but with visual c ++ or vb

Tom Kyte
September 17, 2003 - 4:29 pm UTC

you just need to know the "c prototype" they use. then you can map to it.

Need a clarification

Sanjay Jha, October 08, 2003 - 1:03 pm UTC

For using External Proc..do we need any additional piece of software? I am not able to run make.bat. It complains:

C:\oracle\ora81\plsql\demo>make
'cl' is not recognized as an internal or external command,
operable program or batch file.

I referred metalink doc id (70110.1)and it says, I need to run vcvars32.bat before running make.bat:
**********************************************************
3. Use the make.bat available in the %ORACLE_HOME%\rdbms\extproc directory. You need to run vcvars32.bat file before running this batch file. This will create a dll file.
**********************************************************

However, I did not see that step mentioned in your book, nor do I see a mention of the fact that I need any extra piece of software than Oracle RDBMS. Do I need Visual Studio to be installed, since I did not see vcvars32.bat, cl.exe etc. anywhere in my Oracle folder?

Tom Kyte
October 09, 2003 - 3:42 pm UTC

you need a C compiler, yes. In order to compile C, you need a C compiler.


You do not need a C compiler if you have a DLL already.

You do need a C compiler to turn C into a DLL on windows.

Demo C-Based External Procedure -- Error ORA-03114

Cheema, January 25, 2005 - 10:02 am UTC

Hi Tom,
I followed Chapter 18 of your "One-on_One" and was able to compile and link the demo program. However, when I call it from  demopack.demo_procedure I get the following errors:
SQL> @extproc
Package created.
No errors.
Package body created.
No errors.
ERROR:
ORA-03114: not connected to ORACLE
BEGIN demopack.demo_procedure; END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Is there anything which I may have missed out.
Thanks
 

Tom Kyte
January 25, 2005 - 10:22 am UTC

please contact support -- that is the sample that is shipped with the server, if you followed the setup exactly and that happens -- there is a problem.... (the reason I put the shipping example in the book like that is -- in the event of somehting going horribly wrong -- support would be able to work with you, you are using the standard sample....)

ORA-28595: Extproc agent : Invalid DLL Path

rose, October 04, 2006 - 10:29 am UTC

Hi Tom,
I am using a external procedure , but when I execute the function in pl/sql, it returns this error:
"ORA-28595: Extproc agent : Invalid DLL Path"

My listener.ora is:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = ORAR9I.us.oracle.com))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.85)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = OraR9i)
(ORACLE_HOME = C:\Ora9i)
(SID_NAME = OraR9i)
)
(SID_DESC =
(SID_NAME = extproc)
(ENVS=LD_LIBRARY_PATH=E:\Formt\source)
(ORACLE_HOME = C:\Ora9i)
(PROGRAM = extproc)
)
)
-----------------
My tnsnames.ora :
extproc_connection_data =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = orar9i.us.oracle.com))
(CONNECT_DATA = (SID = extproc))
)

-----------
My library is :
create or replace library lib1 as
'E:\Formt\source\testdll.dll';
/
--------------
My function in pl/sql is:
create or replace function vdig (
dig1 in varchar2, dig2 in varchar2) return number as
external library lib1
name "ComparDig"
language c
parameters (dig1 string,dig2 string);
/

----------------
I call the externa procedure this way:
variable ret number;
variable d1 varchar2(100);
variable d2 varchar2(100);
begin
:d1 := '20000000496E59046B58E2036A46B';
:d2 := '20003200498739046B58E2036A46B';
:ret := vdig (:d1, :d2);
end;
/


and this returns
"ORA-28595: Extproc agent : Invalid DLL Path"


In these steps, where are the errors?

Thank you!

Tom Kyte
October 04, 2006 - 5:15 pm UTC

when you searched for 28595 on this site....


what did you see?



(before you added the 28595 to this message, you would have seen ONE hit, now you'll see two, this one and.......)

28595

rose, October 04, 2006 - 7:11 pm UTC

Tom,
I read the document, altered my listener.ora,
but I didn't yet got success! The error ora-28595 continues!
listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = orar9i.us.oracle.com))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.85)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = OraR9i)
(ORACLE_HOME = C:\Ora9i)
(SID_NAME = OraR9i)
)
(SID_DESC =
(SID_NAME = extproc)
(ENVS="EXTPROC_DLLS=E:\Formt\source\testdll.dll,
LD_LIBRARY_PATH=E:\Formt\source")
(ORACLE_HOME = C:\Ora9i)
(PROGRAM = extproc)
)
)

could you help me please?



Tom Kyte
October 04, 2006 - 7:40 pm UTC

you did restart the listener to get the new configuration read right?

28595

rose, October 05, 2006 - 8:21 am UTC

-After I modified listener.ora, I restarted listener, I recreate library, recreate the function that calls the .dll, and then I execute the test.
-The .dll isn't mine.
-OracleOra92Agent is running.

-extproc.exe must be running? Listener starts up it?



Tom Kyte
October 05, 2006 - 8:31 am UTC

listener runs the exproc

ld library path is a unix thing, what's up with that?

AND: most important: e: is a LOCAL (direct attach, not network) disk right?

calling .dll

rose, October 05, 2006 - 10:20 am UTC

- e: is other partition of local disk. Oracle is in c:.
- I deleted ld_library_path from listener.ora

One question:
-in listener.ora, when
a) (ENVS="EXTPROC_DLLS=E:\formt\source"), my test results ora-28595-Invalid DLL Path.
but when
b) (ENVS="EXTPROC_DLLS=E:\formt\source\<filename.dll>"), my test results ORA-28575: unable to open RPC connection to external procedure agent
the a) option is OK, right?





Tom Kyte
October 05, 2006 - 1:16 pm UTC

you specify the dll name.


if you are getting the 28575, you have solved the dll problem.... type 28575 into search..

Your External Procdure in your first book

CG, December 01, 2006 - 2:35 pm UTC

Tom,
I am currently trying to create a Pro C file on 9i AIX 64 bit. Cant get help from my DBAs on looking for proc.mk so I have used demo_proc.mk to build my file. Well it worked but I get a coredump.

Well I wanted to get more info about what was going on in code so I got out your book and started looking at the code you use to start out your external procs.

I noticed the extproc.ini file referenced in your code. I cant find this file on my AIX server to use your code.
Will your code work for 9i EE AIX 64bit?

BTW I downloaded the 10g companion cd cpio hoping to find proc.mk in it and it was not there. Im waiting for my metalink account to be approved so I can get support to send me the file.

Thanks for your time.



Tom Kyte
December 01, 2006 - 9:47 pm UTC

just type it in - extproc.ini is just an ini file, small file with "variable=value" pairs. i created it in the book.



Executing external procedure('c' language)" in version 10g

HVN, January 10, 2008 - 1:38 pm UTC

Is there is a different way of executing external procedure in 'C' language from PL/SQL in Oracle10gR2? Or is it still the same way as in version 8i?
Tom Kyte
January 10, 2008 - 2:47 pm UTC

in 10g you can

a) call an external procedure
b) use java
c) use dbms_scheduler to create and schedule a job that runs an external program

External procedure on a different host?

Rich, February 26, 2009 - 3:10 pm UTC

Hi Tom,

We are running 9.2.0.8 on Windows 2003. Is it possible to call an external procedure located on a different server than the database server?


Tom Kyte
March 03, 2009 - 8:17 am UTC

technically, yes. The extproc is a service setup under a listener.

supported wise, no.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#sthref1668

... #

The database server, the agent process, and the listener process that spawns the agent process must all reside on the same host.
....



more precisions...

Rich, February 26, 2009 - 6:31 pm UTC

Hi Tom,

a little more description might help..

--Situation now.
Database 9.2.0.8 running on windows 2000. In our database, there is a procedure which call a Microsoft api running locally on the same server using a database link (in fact, a loopback db link).

--Situation to go.
Database must be migrated on Linux, impossible to find linux api which would do the same trick (as the microsoft api). The client needs a quick fix (he is planning on removing the dependancy, but needs a temporary fix)

My questions:
Can we move the api on the app server and have it remotely called by the database?


Rich


Tom Kyte
March 03, 2009 - 8:25 am UTC

ah, this is doable.


leave database install on windows - just the database INSTALL, you can take all of the data away (you'd need that anyway to get the extproc code, so this is OK)

put the new database on linux. Create a dblink back to windows instance and call the stored procedure that runs the external process.

Need DEMO to understand extproc

Vitaliy, June 12, 2018 - 8:52 am UTC

Hi Tom! You wrote

-- try downloading
-- http://asktom.oracle.com/~tkyte/extproc.tar
-- I've tarred up (winzip will extract it) the source and 
-- make.bat. Lets start there and see what happens.


This link don't work, can You tarred up the sorce for me?

Connor McDonald
June 13, 2018 - 2:29 am UTC

It's been lost over time, but I'd suggest this one instead. Go to our presentations and search for "Expert One-on-One source code".

That's the code from Tom's book, which contained a whole chapter on external calls.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library