Re: call extern C function from PL/SQL
Date: Thu, 27 Jun 2002 00:03:20 +0200
Message-ID: <afddn4$71eq$1_at_as201.hinet.hr>
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 > .Received on Thu Jun 27 2002 - 00:03:20 CEST