13
Applications Development

This chapter describes Windows NT-specific issues for applications developers.

Specific topics discussed are:

Building External Procedures

This section describes how to create and use external procedures on Windows NT.

What are External Procedures?

External procedures are functions written in a third-generation language (3GL), such as C, and callable from within PL/SQL or SQL as if they were a PL/SQL procedure or function. External procedures enable you to take advantage of the strengths and capabilities of a 3GL programming language in a PL/SQL environment. This is done by following the simple five-step process listed below.

The main advantages of external procedures are:

Performance, as some tasks are more efficiently performed in a 3GL language than in PL/SQL, which is better suited for SQL transaction processing
Code re-usability, as shared libraries can be called directly from PL/SQL programs on the server or in client tools such as SQL*Forms

You can use external procedures to perform very specific processes, such as:

Solving scientific and engineering problems
Analyzing data
Controlling real-time devices and processes

To create and use an external procedure, follow the instructions described in the following sections:

Step 1: Installing and Configuring
Step 2: Writing an External Procedure
Step 3: Building a Shared Library
Step 4: Registering an External Procedure
Step 5: Executing an External Procedure

Attention:

You can combine the instructions described in Steps 4 and 5 into one SQL script that automates the process of registering and executing your external procedure. For an example of a SQL script that combines these steps, see ORACLE_HOME\RDBMS80\ EXTERN.SQL.

Step 1: Installing and Configuring

This section describes:

Installing Oracle8

To install Oracle8:

Follow the procedures in the Oracle8 Installation for Windows NT CD-ROM Insert to install these products on your Windows NT server:

Oracle8

Contains PL/SQL, from which external procedures are called, and the PL/SQL external procedure program (EXTPROC), which executes external procedures

Net8 Client, Net8 Server, and Net8 Protocol Adapters

Note:

You must also have a 3GL compiler and linker installed on your system to build shared libraries.

Configuring Net8

The following Net8 server networking files must be configured to use external procedures:

ORACLE_HOME\NET80\ADMIN\LISTENER.ORA file
ORACLE_HOME\NET80\ADMIN\TNSNAMES.ORA file

When PL/SQL calls an external procedure, the Net8 listener launches a session-specific process called EXTPROC. Through the listener, 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.

If this is a new Oracle8 installation (that is, no TNSNAMES.ORA and SQLNET.ORA files previously existed on the hard drive), the following information is automatically added to your server's TNSNAMES.ORA and LISTENER.ORA files. If these files already exist, edit your client and server Net8 files as follows:

Add a system identifier (SID) name and 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=  
          (COMMUNITY= TCP.world) 
          (PROTOCOL= TCP) 
          (Host= inventory.us.company.com) 
          (Port= 1521) 
        ) 
) 
STARTUP_WAIT_TIME_LISTENER = 0 
CONNECT_TIMEOUT_LISTENER = 10 
TRACE_LEVEL_LISTENER = ADMIN 
SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (SID_NAME = ORCL) 
    ) 
    (SID_DESC = 
      (SID_NAME = extproc) 
      (PROGRAM=extproc) 
    ) 
  ) 
PASSWORDS_LISTENER = (oracle)

Add the following entries for EXTPROC in the server's TNSNAMES.ORA file:

extproc_connection_data.world = 
(DESCRIPTION = 
      (ADDRESS =  
                        (PROTOCOL=ipc) (KEY=ORCL) 
        ) 
        (CONNECT_DATA=(SID=extproc))
)

Note:

The SQLNET.ORA file requires no changes. By default, the value for the parameters NAMES.DEFAULT_DOMAIN and NAME.DEFAULT_ZONE is set to WORLD. This value matches with the .WORLD extension on the end of EXTPROC_CONNECTION_DATA in the TNSNAMES.ORA file.

Step 2: Writing an External Procedure

Using a 3GL programming language, such as C, you can write functions to be built into shared libraries and invoked by EXTPROC. The following is a simple Microsoft Visual C++ example of an external procedure:

Note:

Since external procedures are built into dynamic link libraries, they must be explicitly exported. In this example, the dllexport storage class modifier exports the function find_max from a dynamic link library.

#include <windows.h>
#define NullValue -1
/*
  This function simply returns the larger of x and y.
*/
long __declspec(dllexport) find_max(long        x, 
                                    short       x_indicator, long       y, 
                            short y_indicator, 
                                    short *ret_indicator)
{
   /* It can be tricky to debug DLL's that are being called by a process
      that is spawned only when needed, as in this case.  
      Therefore try using the DebugBreak(); command.  
      This will start your debugger.  Uncomment the following line and
      you can step right into your code.
   */
   /* DebugBreak();  */

   /* first check to see if you have any nulls */
   /* Just return a null if either x or y is null */

   if ( x_indicator==NullValue || y_indicator==NullValue) {
      *ret_indicator = NullValue;   
      return(0);
   } else { 
      *ret_indicator = 0;        /* Signify that return value is not null */
      if (x >= y) return x;
      else return y;
   }
}

Step 3: Building a Shared Library

After you have written your external procedure(s) in a 3GL programming language, use the appropriate compiler and linker to build a dynamic link library, making sure to export the external procedures, as noted above. See your compiler and linker documentation for instructions on building a dynamic link library and exporting its functions.

After building the dynamic link library, you can move it to any directory on your system. For the example above, you can build the external procedure find_max into a DLL called externProcedures.

Step 4: Registering an External Procedure

Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with the Oracle8 database. This involves two distinct steps:

Create a PL/SQL library to map to the DLL.

Start Server Manager:

C:\> SVRMGR30

Create the PL/SQL library using the CREATE LIBRARY command:

SVRMGR> CREATE LIBRARY externProcedures AS 'C:\ORANT\RDBMS80\ 
EXTPROC\EXTERN.DLL';

Where...	Represents the...
externProcedures	Alias library (essentially a schema object in the database)
C:\ORANT\RDBMS80\EXTPROC\EXTERN.DLL	Path to the Windows NT operating system shared library externProcedures

Note:

The DBA must grant EXECUTE privileges on the PL/SQL library to users that want to call the library's external procedures from PL/SQL or SQL.

Create a PL/SQL program unit specification.

Do this by writing a PL/SQL subprogram that uses the EXTERNAL clause instead of declarations and a BEGIN...END block. The EXTERNAL clause is the interface between PL/SQL and the external procedure. The EXTERNAL clause identifies the following information about the external procedure:

name
shared library alias
programming language in which it was written
calling standard (defaults to C if omitted)

For example:

Step 5: Executing an External Procedure

To execute an external procedure, you must call the PL/SQL program that registered the external procedure. These calls can appear in:

anonymous blocks
standalone and packaged subprograms
methods of an object type
database triggers
SQL statements (calls to packaged functions only)

In the section "Step 4: Registering an External Procedure", the PL/SQL function PLS_MAX registered the external procedure find_max. Follow the procedures below to execute find_max:

Call the PL/SQL function PLS_MAX from a PL/SQL procedure named Useit.

CREATE OR REPLACE PROCEDURE UseIt AS
                a integer;
                b integer;
                c integer;
BEGIN
                a := 1;
                b := 2;
                c := PLS_MAX(a,b);
                dbms_output.put_line('The maximum of '||a||' and '||b||' is  '||c);
END;

Run the procedure:

SVRMGR> EXECUTE UseIt;

Additional Documentation

See the following documents for additional information on external procedures:

Oracle8 Server Administrator's Guide
PL/SQL User's Guide and Reference
The following files in ORACLE_HOME\RDBMS80:

EXTERN.C (code example shown in "Step 2: Writing an External Procedure")
MAKE.BAT (batch file that builds the dynamic link library)
EXTERN.SQL (automates the instructions described in "Step 4: Registering an External Procedure" and "Step 5: Executing an External Procedure")

Compiling and Linking the Oracle XA Library

The XA Application Program Interface (API) is typically used to enable an Oracle8 database to interact with a transaction processing (TP) monitor, such as:

BEA Tuxedo
IBM Transarc Encina
IBM CICS

The Oracle XA Library is automatically installed as part of the Oracle8 installation option. The following components are created in your Oracle home directory:

Component	Location
XA80.DLL	ORACLE_HOME\BIN
XA80.LIB	ORACLE_HOME\RDBMS80\XA
XA.H	ORACLE_HOME\RDBMS80\XA

You can also use TP monitor statements in your client programs. The use of the XA API is also supported from both Pro*C and Oracle Call Interface (OCI). In either case, the XA80.DLL must be contained in the execution path of the calling program.

Compiling and Linking a Pro*C Program

To compile and link a Pro*C program:

Precompile PROGRAM.PC using Pro*C to generate PROGRAM.C.
Compile PROGRAM.C, making sure to include ORACLE_HOME\RDBMS80\XA in your path.
Link PROGRAM.OBJ with the following libraries:

Library	Located in...
XA80.LIB	ORACLE_HOME\RDBMS80\XA
ORA803.LIB	ORACLE_HOME\OCI80\LIB
SQLLIB80.LIB	ORACLE_HOME\PRO80\LIB\MSVC
	or
	ORACLE_HOME\PRO80\LIB\BORLAND

Run PROGRAM.EXE. (XA80.LIB makes the function calls to XA80.DLL)

Compiling and Linking an OCI Program

To compile and link an OCI program:

Compile PROGRAM.C by using MSVC++ or Borland C, making sure to include ORACLE_HOME\RDBMS80\XA in your path.
Link PROGRAM.OBJ with the following libraries:

Library	Located in...
XA80.LIB	ORACLE_HOME\RDBMS80\XA
ORA803.LIB	ORACLE_HOME\OCI80\LIB\MSVC
	or
	ORACLE_HOME\OCI80\LIB\BORLAND

Run PROGRAM.EXE.

See the following general information about XA and TP monitors:

Distributed Transaction Processing: The XA Specification published by X/Open, (now part of OpenGroup). Visit: http://www.rdg.opengroup.org/public/pubs/catalog/full_lst.htm
Transaction Processing XPG4 X/Open CAE Specification XO/CAE/91/300 or C193 2/92
Your specific TP monitor documentation

For more information about the Oracle XA Library, see the chapter "Oracle XA" of the Oracle8 Server Application Developer's Guide.

Accessing Web Data with Intercartridge Exchange

Configuring Intercartridge Exchange

You must add a parameter to the registry before using Intercartridge Exchange (ICX).

To configure ICX:

Start the Registry Editor from the MS-DOS command prompt by entering:

C:\> REGEDT32

Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.
Choose Edit>Add Value.

The Add Value dialog box appears.

Enter HTTP_PROXY in the Value Name field and REG_SZ in the Data Type field:

Click OK when done.
Enter www-proxy.your-site in the String field:

where your-site is your web site.

Using Intercartridge Exchange

Intercartridge Exchange (ICX) enables you to use a stored package called UTL_HTTP to make hypertext transfer protocol (HTTP) calls from PL/SQL, SQL, and Server Manager statements. UTL_HTTP can:

access data on the internet
call Oracle Web Application Server Cartridges

UTL_HTTP contains two similar entry points, known as packaged functions, that make HTTP callouts from PL/SQL and SQL statements:

UTL_HTTP.REQUEST
UTL_HTTP.REQUEST_PIECES

Both packaged functions:

take a string universal resource locator (URL) of a site
contact that site
return the data (typically HTML) obtained from that site

The declarations to use with both packaged functions are described below:

Packaged Function UTL_HTTP.REQUEST

Description

UTL_HTTP.REQUEST takes a URL as its argument and returns up to the first 2000 bytes of data retrieved from the given URL.

Declaration

UTL_HTTP.REQUEST is specified as:

        FUNCTION REQUEST (URL IN VARCHAR2) RETURN VARCHAR2;

To use UTL_HTTP.REQUEST from Server Manager, enter:

        SVRMGR> SELECT UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/') FROM DUAL;

which outputs:

        UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')

------------------------------------------------------

        <html>
        <head><title>Oracle Corporation Home Page</title>
        <!--changed Jan. 16, 19
        1 row selected.

Packaged Function UTL_HTTP.REQUEST_PIECES

Description

UTL_HTTP.REQUEST_PIECES takes a URL as its argument and returns a PL/SQL table of 2000 bytes of data retrieved from the given URL. The final element may be shorter than 2000 characters. The UTL_HTTP.REQUEST_PIECES return type is a PL/SQL table of type UTL_HTTP.HTML_PIECES.

Declaration

UTL_HTTP.REQUEST_PIECES, which uses type UTL_HTTP.HTML_PIECES, is specified as:

type html_pieces is table of varchar2(2000) index by binary_integer;
function request_pieces (url in varchar2, 
           max_pieces natural default 32767)
        return html_pieces;

A call to REQUEST_PIECES can look like the example below. Note the use of the PL/SQL table method COUNT to discover the number of pieces returned, which may be zero or more:

declare pieces utl_http.html_pieces;
begin 
  pieces := utl_http.request_pieces('http://www.oracle.com/'); 
   for i in 1 .. pieces.count loop
     .... -- process each piece
   end loop;
end;

The second argument to UTL_HTTP.REQUEST_PIECES, (MAX_PIECES) is optional. MAX_PIECES is the maximum number of pieces (each 2000 characters in length, except for the last, which may be shorter), that UTL_HTTP.REQUEST_PIECES returns. If provided, that argument is likely a positive integer.

For example, the following block retrieves up to 100 pieces of data (each 2000 bytes, except perhaps the last) from the URL. The block prints the number of pieces retrieved and the total length, in bytes, of the data retrieved.

        set serveroutput on
        /
        declare 
          x utl_http.html_pieces;
        begin
          x := utl_http.request_pieces('http://www.oracle.com/', 100);
          dbms_output.put_line(x.count || ' pieces were retrieved.');
          dbms_output.put_line('with total length ');
          if x.count < 1 
          then dbms_output.put_line('0');
          else dbms_output.put_line
                ((2000 * (x.count - 1)) + length(x(x.count)));
          end if;
        end;
        /

which outputs:

        Statement processed.
        4 pieces were retrieved.
        with total length 
        7687
The elements of the PL/SQL table returned by UTL_HTTP.REQUEST_PIECES are 
successive pieces of data obtained from the HTTP request to that URL.

UTL_HTTP Exception Conditions

This section describes the exceptions that can be raised by packaged functions UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES:

UTL_HTTP.REQUEST

The PRAGMA RESTRICT_REFERENCES enables exceptions (errors) to be displayed:

create or replace package utl_http is
function request (url in varchar2) return varchar2;
pragma restrict_references (request, wnds, rnds, wnps, rnps);

UTL_HTTP.REQUEST_PIECES

The PRAGMA RESTRICT_REFERENCES enables exceptions (errors) to be displayed:

create or replace package utl_http is
type html_pieces is table of varchar2(2000) index by binary_integer;
function request_pieces (url in varchar2, 
                        max_pieces natural default 32767)
        return html_pieces;
pragma restrict_references (request_pieces, wnds, rnds, wnps, rnps);

Exceptional Conditions and Error Messages

If...	Then...
Initialization of the HTTP callout subsystem fails for environmental reasons such as lack of available memory	Exception UTL_HTTP.INIT_FAILED is raised: init_failed exception;
The HTTP call fails because of failure of the HTTP daemon or because the argument to REQUEST or REQUEST_PIECES cannot be interpreted as a URL because it is NULL or has non-HTTP syntax	Exception UTL_HTTP.REQUEST_FAILED is raised: request_failed exception;
No response is received from a request to the given URL because no site corresponding to that URL was contacted	A formatted HTML error message may be returned: <HTML> <HEAD> <TITLE>Error Message</TITLE> </HEAD> <BODY> <H1>Fatal Error 500</H1> Can't Access Document: http://home.nothing.comm. <P> <B>Reason:</B> Can't locate remote host: home.nothing.comm. <P> <P><HR> <ADDRESS><A HREF="http://www.w3.org"> CERN-HTTPD3.0A</A></ADDRESS> </BODY> </HTML>

If...

Then...

Initialization of the HTTP callout subsystem fails for environmental reasons such as lack of available memory

Exception UTL_HTTP.INIT_FAILED is raised:

init_failed exception;

The HTTP call fails because of failure of the HTTP daemon or because the argument to REQUEST or REQUEST_PIECES cannot be interpreted as a URL because it is NULL or has non-HTTP syntax

Exception UTL_HTTP.REQUEST_FAILED is raised:

request_failed exception;

No response is received from a request to the given URL because no site corresponding to that URL was contacted

A formatted HTML error message may be returned:

<HTML>

<HEAD>

<TITLE>Error Message</TITLE>

</HEAD>

<BODY>

<H1>Fatal Error 500</H1>

Can't Access Document: http://home.nothing.comm.

<P>

<B>Reason:</B> Can't locate remote host: home.nothing.comm.

<P>

<P><HR>

CERN-HTTPD3.0A</A></ADDRESS>

</BODY>

</HTML>

Note:

The first two exceptions in the table above, unless explicitly caught by an exception handler, are reported by this generic message:

ORA-06510: PL/SQL: unhandled user-defined exception

that shows them as "user-defined" exceptions, although they are defined in this system package.

If any other exception is raised during the processing of the HTTP request (for example, an out-of-memory error), then function UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES reraises that exception.

Troubleshooting

Do not expect UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES to succeed in contacting a URL unless you can contact that URL by using a browser on the same machine (and with the same privileges, environment variables, etc.). If UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES fails (that is, if it raises an exception or returns an HTML-formatted error message, yet you believe that the URL argument is correct), try contacting that same URL with a browser to verify network availability from your machine.

13 Applications Development

Building External Procedures

What are External Procedures?

Step 1: Installing and Configuring

Installing Oracle8

Configuring Net8

Step 2: Writing an External Procedure

Step 3: Building a Shared Library

Step 4: Registering an External Procedure

Step 5: Executing an External Procedure

Additional Documentation

Compiling and Linking the Oracle XA Library

Compiling and Linking a Pro*C Program

Compiling and Linking an OCI Program

Accessing Web Data with Intercartridge Exchange

Configuring Intercartridge Exchange

Using Intercartridge Exchange

Packaged Function UTL_HTTP.REQUEST

Description

Declaration

Packaged Function UTL_HTTP.REQUEST_PIECES

Description

Declaration

UTL_HTTP Exception Conditions

UTL_HTTP.REQUEST

UTL_HTTP.REQUEST_PIECES

Exceptional Conditions and Error Messages

Troubleshooting

13
Applications Development