Re: call extern C function from PL/SQL

I made mistake because i didn't createSTATIC DLL on NT. all I need is create right dll on NT platform:

cl -D_DLL -D_MT /LD -Zi extern_routin.c /link msvcrt.lib /nod:libcmt /DLL /EXPORT:zbroj

after this, all is right!!

auf Sarwar" <rsarwar_at_ifsna.com> wrote in message news:c2d690f2.0206251749.622aff77_at_posting.google.com... > "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
> .