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.

• Dc

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

1. What is an external procedure? An external procedure is a 3GL routine stored in a dynamic link libraries(DLL) or an shared object(.so), registered with PL/SQL, and called by you to do special-purpose processing. PL/SQL loads the library dynamically during run time. The routine runs in a separate address space. This feature is available on platforms that supports DLLs(or shared libraries such as .so libraries).
2. Configuring listener.ora and tnsname.ora If you have an Oracle8 database and plan on using External Procedures, you must have the following components installed.
   • Oracle8 Server or Enterprise Edition with PL/SQL program (extproc) installed.
   • Net8 Client, Net8 Server, and appropriate Net8 Protocol Adapters
   • A 3GL compiler and linker for building shared libraries.

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.

• shared library name
• external procedure name
• parameters (if necessary)

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

• anonymous blocks
• standalone and packaged subprogram
• methods of an object type
• database triggers
• SQL statements

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         \        /
>   \    _/                                            \_     /
>   /   / |____________________________________________| \    \
>  /   /                                                   \    \