Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: call extern C function from PL/SQL
"REM" <ilica.brnadic_at_zg.hinet.hr> wrote in message news:<af885j$fkpa$1_at_as201.hinet.hr>...
> Hello, I'm having problem with calling extern C routin.
> As you see I'm loading extern C routin, publishing it, but I don't know how
> call Plzbroj_func function!!!
>
>
> SQL> CREATE OR REPLACE LIBRARY C_utils AS
> 'G:\users\ibrnadic\Extern_Rutins_to_Oracle\Debug';
> 2 /
>
> Library created.
>
> SQL> CREATE OR REPLACE FUNCTION Plzbroj_func (
> 2 x FLOAT,
> 3 y FLOAT)
> 4 RETURN FLOAT
> 5 AS LANGUAGE C
> 6 LIBRARY C_utils
> 7 NAME "zbroj";
> 8 /
>
> Function created.
>
> SQL> DECLARE
> 2 g FLOAT;
> 3 a FLOAT:=5;
> 4 b FLOAT:=10;
> 5 BEGIN
> 6 dbms_output.put_line('proba');
> 7 g:=Plzbroj_func(a, b);
> 8 end;
> 9 /
> DECLARE
> *
> ERROR at line 1:
> ORA-06520: PL/SQL: Error loading external library
> ORA-06522: Unable to load DLL
> ORA-06512: at "SCOTT.PLZBROJ_FUNC", line 0
> ORA-06512: at line 7
>
> With regards,
>
> desparait ilica
>
> "Dale Edgar" <Dale_at_DataBee.com> wrote in message
> news:3d16fb51.14679407_at_news.btclick.com...
> >
> > On Sat, 22 Jun 2002 23:45:16 +0200, "REM" <ilica.brnadic_at_zg.hinet.hr>
> > wrote:
> >
> > >I'm know that is posible to load java class to the database but, is't
> > >posible call extern C function direct from PL/SQL???
> >
> > Yes it is - a google search on "extproc" should turn up a lot
> > information. Here's an excellent tutorial to get you started:
> >
> > http://home.clara.net/dwotton/dba/oracle_extn_rtn.htm
> >
> > Regards
> > Dale
> > ----------------------
> > Need instant schema documentation? Check out the DDL to HTML generator
> > in the free DataBee DBATool. http://www.DataBee.com/dt_home.htm
Seems like you are shying away from going to the links others have pointed out. I downloaded this article (verbatim) from Metalink a while back. Maybe this can get you started. I used it and it worked like a charm.
Procedure to Create an External Procedure on Windows NT
Overview
An external procedure is a third generation language routine stored in
a
dynamic link library (DLL) called by PL/SQL block. Before calling the
DLL,
a library must be created and registered with PL/SQL. Once
registered, it
can be called from any PL/SQL program (i.e. Stored Procedure,
Function,
Triggers, etc.). At run time, PL/SQL loads the library dynamically
then
calls the routine as if it were a PL/SQL subprogram.
To execute the external procedure, ensure that you are an authorized
user,
which means you must have EXECUTE privileges on the library, or CREATE
ANY
LIBRARY privileges if you wish to create your own library. Creating
your
own library gives EXECUTE privileges by default. Keep in mind that
the
external procedure executes with the privileges granted to your
userid.
When you execute an external procedure, PL/SQL alerts a Listener
process,
which in turn launches a session-specific agent named extproc. Once
the
external procedure completes, extproc remains active throughout your
Oracle session and is terminated once you log off.
Procedure
Before running an external procedure, you must make some initial
entries
in the TNSNAMES.ORA and LISTENER.ORA files on the server. Once these
entries are successfully made, the only other steps required are to
create
a library for each DLL and register the external procedures.
This section covers the steps to configure the TNSNAMES.ORA and
LISTENER.ORA
files, start the listener process, create the required library, and
register
the external procedure.
Step 1 - Make entry in the TNSNAMES.ORA.
Make the following entry in the TNSNAMES.ORA:
extproc_connection_data.world =
(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = extproc)) (CONNECT_DATA = (SID = extproc) ) )
It is important to note that the KEY value and SID value given in the
TNSNAMES.ORA file must match their respected values in the
LISTENER.ORA file.
Also, the alias defined must be 'extproc_connection_data.world'.
Step 2 - Make the necessary entries in the LISTENER.ORA.
You can either add an entry to the existing listener or add another
listener
process.
Option 1: Add an Entry to the Existing Listener
The information contained in each listener differs. Defining the listener process is done in two parts. The first part is as follows:
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC) (KEY= V803) ) (ADDRESS= <-Add this line. (PROTOCOL= IPC) <-Add this line. (KEY= extproc) <-Add this line. ) (ADDRESS= (PROTOCOL= TCP) (Host= ###.#.#.#) (Port= ####) )
Note: The KEY value entered should match the KEY value entered in the
TNSNAMES.ORA file.
The second part is as follows:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = msltstsv) (SID_NAME = V803) ) (SID_DESC = <-Add this line (SID_NAME = extproc) <-Add this line (PROGRAM = extproc) <-Add this line ) )
Notes: The SID_NAME value entered should match the SID value entered in the
TNSNAMES.ORA file.
The PROGRAM value is an executable (extproc.exe) found in the ORACLE_HOME\bin directory. This value should not change.
Option 2: Create a New Listener Process
Once again, the entries below should be made within the LISTENER.ORA file. You could append the following code:
MY_EXTPROC_LISTENER =
(ADDRESS_LIST = (ADDRESS= (PROTOCOL=ipc) (KEY = extproc) ) ) SID_LIST_MY_EXTPROC_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = extproc) (ORACLE_HOME = d:\orant) (PROGRAM=d:\orant\bin\extproc) ) )
Step 3 - Start the listener process.
Any modifications to the existing listener require that you stop and
restart the listener to reload the new listener configuration. Adding
a
second listener process, instead of modifying the current, only
requires
the startup of the new listener process.
The following three methods are available to start an existing
listener
process:
1 - Click the Services icon found on the Control Panel to invoke
the Services Dialog Box to start and stop existing listener processes.
Highlight the servicename and click either the Start or Stop button.
2 - Use the NET START <ServiceName> or the NET STOP <ServiceName>.
NET is a Windows NT command and requires the full ServiceName.
3 - Use LSNRCTL80 START <ListenerName/ServiceName> or
LSNRCTL80 STOP <ListenerName/ServiceName>. The LSNRCTL80 is an Oracle
executable used to start and stop the listener and can be used with
either the ListenerName (defined in LISTENER.ORA) or the ServiceName
(defined usually as OracleTNSListener80).
Step 4 - Create a sample DLL using the following code.
The following is a basic example of an external procedure. It is a
good
way to test to ensure that the environment (mainly the listener) is
configured
correctly.
Note: For the remaining steps, the DLL name should be MYEXTPROC.DLL.
#include <stdio.h>
int __declspec(dllexport) square(int x)
{
return(x*x);
}
The process for building the DLL is dependent upon which compiler is used.
Step 5 - Move the DLL to same server where the database is located.
The DLL can be placed in any directory. One approach would be to
create an
EXTPROC directory under the ORACLE_HOME and use this to house all
external
procedures.
Note: If you do not put the DLL on the server, you may receive the following
error:
ORA-28577 argument <datatype name> of external procedure <function
name> has unsupported datatype <datatype that is being used>
Step 6 - Create the required library.
Log into SQL*Plus and create a library to be associated with the
external
procedure.
Note: The user must have CREATE LIBRARY privileges.
CREATE LIBRARY <mylib> AS <path\filename>;
SQL> CREATE LIBRARY SQUARE_LIB AS 'D:\ORANT\EXTPROC\MYEXTPROC.DLL';
If a mistake is made, either do:
DROP LIBRARY <mylib>;
Or
CREATE OR REPLACE <mylib> AS <path\filename>;
Step 7 - Register the external procedure.
An external procedure is not called directly. Instead, you call the
PL/SQL
subprogram that registers the external procedure. This can either be
a
Function or a Stored Procedure.
CREATE OR REPLACE FUNCTION <FunctionName> (<parm1>...) RETURN <value>
AS
EXTERNAL LIBRARY <mylib>
NAME "<Name Of Function In DLL>"
LANGUAGE C;
CREATE OR REPLACE PROCEDURE <ProcedureName> (<parm1>...) AS
EXTERNAL LIBRARY <mylib>
NAME "<Name Of Function In DLL>"
LANGUAGE C;
CREATE OR REPLACE FUNCTION square_of (X BINARY_INTEGER)
RETURN BINARY_INTEGER AS
EXTERNAL LIBRARY SQUARE_LIB
NAME "square"
LANGUAGE C;
/
Step 8 - Run the external procedure.
Use the following code to execute the external procedure:
CREATE OR REPLACE PROCEDURE EXTPROCTEST (X BINARY_INTEGER) AS
RetValue BINARY_INTEGER;
BEGIN
RetValue := square_of(X);
DBMS_OUTPUT.PUT_LINE(RetValue);
End;
/
EXECUTE EXTPROCTEST(15); Related Articles
[NOTE:74159.1] External Procedures Using Pro*C
[NOTE:47484.1] PL/SQL 8.0 External Procedures: Setting up the Callback
Demonstration