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