| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Looking for examples that call C functions from PL/SQL!
Pardon the formatting - This is straight off of technet.oracle.com.
I believe it may have a typo in that the KEY=ORCL should be
KEY=extrproc.
I currently can't get it to work, I have an open tar with Oracle. I've
been
looking into this for a week or so. You may have better luck.
CALLING EXTERNAL PROCEDURES IN ORACLE 8 PLSQL
This documentation explains how to make an external procedure call in
PL/SQL in
Oracle8.
1. What is an external procedure? 2. Configuring listener.ora and tnsname.ora 3. Creating DLLs(Shared Libraries) 4. Creating a library in PL/SQL 5. Registering an external procedure 6. Calling an external procedure
Enabling External Procedure Calls requires the Net8 Configuration files
on the
server be modified. If this is a NEW Oracle8 installation, the
following
information is automatically added to your server's TNSNAMES.ORA and
LISTENER.ORA files. For environments where these files already exist,
edit
your LISTENER.ORA and TNSNAMES.ORA files as follows:
A. Add a system identifier (SID) name and a program name for EXTPROC in
the
server's LISTENER.ORA file:
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = oracle.world)
)
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ORCL)
)
(ADDRESS = <-- add these lines
(PROTOCOL = IPC) <-- add these lines
(KEY = extproc) <-- add these lines
) <-- add these lines
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = <hostname>)
(PORT = 1521)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
)
(SID_LIST = <-- add these lines
(SID_DESC = <-- add these lines
(SID_NAME = extproc) <-- add these lines
(PROGRAM = extproc) <-- add these lines
) <-- add these lines
)
B. Add a service name entry for EXTPROC in the server's TNSNAMES.ORA file:
orcl.world =
(DESCRIPTION =
(ADDRESS =
(COMMUNITY = TCP.world)
(PROTOCOL = TCP)
(Host = <hostname>)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORCL))
)
extproc_connection_data.world = <-- add these lines
(DESCRIPTION = <-- add these lines
(ADDRESS = <-- add these lines
(PROTOCOL = IPC) <-- add these lines
(KEY = ORCL) <-- add these lines
) <-- add these lines
(CONNECT_DATA = (SID=extproc)) <-- add these lines
) <-- add these lines
The Net8 listener now listens for databases services like External
Procedure
Calls. When a PL/SQL or SQL application calls an external procedure,
the Net8
listener launches a session-specific processes called EXTPROC. Through
the
listener service, PL/SQL passes the following information to EXTPROC.
EXTPROC then loads the shared library and invokes the external procedure.
Note: If you do not have listener.ora and tnsname.ora configured
correctly, you
will get ORA-28575 when making an external procedure call.
ERROR at line 1:
ORA-28575: unable to open RPC connection to external procedure agent ORA-06512: at "SCOTT.SQUARE_OF", line 0 ORA-06512: at line 4
3. Creating DLLs(Shared Libraries)
The following is a simple C program(square.c) which will be compiled to
a share
libraries:
#include <stdio.h>
int square(int x);
int square(int x)
{
return(x*x);
}
To compile a .c file to a .o file without main in it, you can use:
%cc -c square.c
This will generate an .o file.
Since we have an object file, we can now create an share object.
Creating shared
libraries may differ slightly from one platform to another. On Sun
Solaris, the
command you need to use is:
%ld -G -o square.so square.o
4. Creating a library in PL/SQL4.
The next step is to create an libraries in PL/SQL. You must specify the
full
path to the DLL(.so) because the linker cannot resolve references to
just the
DLL(.so) name. In the following example, you will create alias libraries
square_lib, which represents DLL(.so) square.so.
SQL>CREATE LIBRARY square_lib AS '/home/csupport/spfan/802/square.so';
5. Registering an external procedure
Before you can call an external procedure, you must register it. The
following
example create a square_of function which takes a BINARY_INTEGER in
parameter
and returns a BINARY_INTEGER. In the following example, you will create
a PL/SQL
standlone function named square_of that registers C routine square as an
external function:
CREATE FUNCTION square_of(x BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY square_lib
NAME "square"
LANGUAGE C;
/
6. Calling an external procedure
You do not call an external procedure directly. Instead, you call the
PL/SQL
subprogram that registered the external procedure. Such calls, which you
code in
the usual way, can appear in
The following is an example of calling an external procedure in an
anonymous
block:
DECLARE
w BINARY_INTEGER;
BEGIN
w := square_of(20);
dbms_output.put_line(w);
END;
/
FOR MORE INFORMATION ON EXTERNAL PROCEDURES AND 3GL APPLICATIONS, PLEASE
REFER
TO ORACLE8 ENTERPRISE EDITION OR PL/SQL USER'S GUIDE AND REFERENCE
MANUALS.
Chow Hoi Ka, Eric wrote:
> Hello, > > Would you please to show me a simple example that from PL/SQL calls > external C functions ? > > Or from Pro C/C++ calls PL/SQL(store procedure) ??? > > Best regards, > Eric > > -- > _ _ > / ) |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ( \ > / / | | \ \ > _( /_ | _ Chow Hoi Ka, Eric _ | _) )_ > (((\ \> |/ ) ( \| </ /))) > (\\\\ \_/ / \ \_/ ////) > \ / E-Mail : eric138_at_yahoo.com \ / > \ _/ \_ / > / / |____________________________________________| \ \ > / / \ \Received on Tue Jul 27 1999 - 21:41:31 CDT
 |
 |