Oracle8 Enterprise Edition Getting Started 
Release 8.0.5 for Windows NT 
A64416-01
 
Library		  
Product		  
Contents		  
Index
 

Prev Next

15
Developing Applications

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

Specific topics discussed are:

Building External Procedures

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

External Procedures Overview

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.

The main advantages of external procedures are:

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

Follow these instructions to create and use an external procedure:

Step 1: Installing and Configuring

This section describes:

Installing Oracle8 Enterprise Edition

Follow the procedures in Oracle8 Enterprise Edition Installation for Windows NT to install these products on your Windows NT server:

Configuring Net8

If you install Net8 Server from your Oracle8 Enterprise Edition CD-ROM, your server network files are automatically configured to use external procedures.

When PL/SQL calls an external procedure, the Net8 listener launches a session-specific process called EXTPROC. Through Net8, PL/SQL passes the following information to EXTPROC:

EXTPROC then loads the DLL and invokes the external procedure.

If you copy your Oracle7 server network files into your Oracle8 network files directory, the following files must be manually configured for the external procedure behavior described above to occur:

See "Configuring External Procedure Calls" in Chapter 8 of Net8 Getting Started for Windows NT and Windows 95/98.
.

Note:  

The SQLNET.ORA file requires no changes. By default, the values for the parameters NAMES.DEFAULT_DOMAIN and NAME.DEFAULT_ZONE are set to WORLD. These values match 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 DLLs and invoked by EXTPROC. The following is a simple Microsoft Visual C++ example of an external procedure:

Note:  

Since external procedures are built into DLLs, 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 DLL

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

After building the DLL, 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 EXTERN.DLL. To build the above example, go to ORACLE_HOME\ RDBMS80\EXTPROC and type MAKE.

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:

  1. Create a PL/SQL library to map to the DLL.
    1. Start Server Manager:
      2. C:\> SVRMGR30
    2. Create the PL/SQL library using the CREATE LIBRARY command:
      3. 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 DLL EXTERN.DLL. This example uses C:\ORANT as your Oracle home. 
       
      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. 

       
       
  2. Create a PL/SQL program unit specification.

    3. 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:

For example:
 

Step 5: Executing an External Procedure

To execute an external procedure, you must call the PL/SQL program unit (that is, the alias for the external function) that registered the external procedure. These calls can appear in:

In "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:

  1. Call the PL/SQL function PLS_MAX from a PL/SQL procedure named Useit:
    2. 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;
  2. Run the procedure:
    3. SVRMGR> EXECUTE UseIt;

Step 6: Enabling Multi-threaded Callout Support

You can increase the speed of external procedure invocations when making calls to external programs from PL/SQL stored procedures or PL/SQL methods of object types in the database by taking advantage of multiple, light-weight threads instead of a single dedicated process per user session.

WARNING:  

Do not enable multi-threaded callout support if your C code is not thread-enabled. For example, do not use global variables in DLLs. Ensure also that there is synchronized access to variables in DLLs. See your Microsoft programming documentation for information on making your code thread-enabled.  

 
 

To enable multi-threaded callouts:

  1. Open the Windows NT Control Panel.
  2. Double-click Services.

    3. The Services window appears.

  3. Select OracleExtprocAgent (or OracleHOME_NAMEExtprocAgent, where HOME_NAME is the home name of the Oracle home directory you want to use).
  4. Click Start.
  5. Exit from the Windows NT Control Panel.
  6. Set ORA_EXTPROC_THREADED to TRUE in the registry subkey of the Oracle home directory that you are using. The location of this parameter is determined by how many Oracle home directories are on your machine:


    7. If You Have...  ORA_EXTPROC_THREADED is Located in... 

    One home directory 

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 

    Additional directories 

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ HOMEID  

    where ID is incremented for each additional Oracle home directory on your computer. 
     

    See Appendix C, "Configuration Parameters and the Registry" for instructions on accessing and modifying the registry.

    This enables you to use multiple, light-weight threads when making calls to EXTPROC from PL/SQL programs.

Additional Documentation

See the following documents for additional information on external procedures:

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:

The Oracle XA Library is automatically installed as part of Oracle8 Enterprise Edition. 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/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/C++ Program

To compile and link a Pro*C /C++ program:

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

    4. Library  Located in... 

    XA80.LIB 

    ORACLE_HOME\RDBMS80\XA 

    OCI.LIB 

    ORACLE_HOME\OCI80\LIB\MSVC 

     

    or 

     

    ORACLE_HOME\OCI80\LIB\BORLAND 

    SQLLIB80.LIB 

    ORACLE_HOME\PRO80\LIB\MSVC 

     

    or 

     

    ORACLE_HOME\PRO80\LIB\BORLAND 
     
  4. 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:

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


    3. Library  Located in... 

    XA80.LIB 

    ORACLE_HOME\RDBMS80\XA 

    OCI.LIB 

    ORACLE_HOME\OCI80\LIB\MSVC 

     

    or 

     

    ORACLE_HOME\OCI80\LIB\BORLAND 
     

  3. Run PROGRAM.EXE.

XA Dynamic Registration

The Oracle8 database supports the use of XA dynamic registration. XA dynamic registration improves the performance of applications interfacing with XA-compliant TP monitors. For TP Monitors to use XA dynamic registration with an Oracle8 database on Windows NT, you must add either an environmental variable or a registry variable to the Windows NT computer on which your TP monitor is running. See either of the following sections for instructions:

After adding this variable, see the Oracle8 Application Developer's Guide for information on using XA dynamic registration.

Adding an Environmental Variable for the Current Session

Adding an environmental variable at the command prompt affects only the current MS-DOS session.

To add an environmental variable:

  1. Go to the computer where your TP monitor is installed.
  2. Enter the following at the MS-DOS command prompt:
    3. C:\> SET ORA_XA_REG_DLL = VENDOR.DLL

    where VENDOR.DLL is the TP monitor DLL provided by your vendor.

Adding a Registry Variable for All Sessions

Adding a registry variable affects all sessions on your Windows NT computer. This is useful for computers where only one TP monitor is running.

To add a registry variable:

  1. Go to the computer where your TP monitor is installed.
  2. Enter the following at the MS-DOS command prompt:
    3. C:\> REGEDT32

    The Registry Editor window appears.

  3. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.

  4. Choose the Add Value option in the Edit menu.

    5. The Add Value dialog box appears:

  5. Enter ORA_XA_REG_DLL in the Value Name field.
  6. Select REG_EXPAND_SZ from the Data Type drop-down list box.
  7. Click OK.

    8. The String Editor dialog box appears:

  8. Enter VENDOR.DLL in the String field.

    9. where VENDOR.DLL is the TP monitor DLL provided by your vendor.

  9. Click OK.

    10. The Registry Editor adds the parameter.

  10. Choose Exit from the Registry menu.

    11. The registry exits.

XA and TP Monitor Information

See the following general information about XA and TP monitors:

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

Accessing Web Data with Intercartridge Exchange

This section discusses the following topics:

Configuring Intercartridge Exchange

You must add a parameter to the registry before using ICX.

To configure ICX:

  1. Start the registry editor from the MS-DOS command prompt:
    2. C:\> REGEDT32

    The Registry Editor window appears.

  2. Add HTTP_PROXY to the registry subkey of the Oracle home directory that you are using. The location in which to add this parameter is determined by how many Oracle home directories are on your machine:


    3. If You Have...  Add HTTP_PROXY to... 

    One home directory 

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 

    Additional directories 

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ HOMEID  

    where ID is incremented for each additional Oracle home directory on your computer. 
     
     

    1. Choose Add Value from the Edit menu.

      2. The Add Value dialog box appears.

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

      3.  

    3. Click OK.
    4. Enter www-proxy.your-site in the String field:

      5.  

       

      where marketing.com is only an example of a Web site. Enter your real Web site.

Using 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:

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

Both packaged functions:

The declarations to use with both packaged functions are described in the following sections.

Packaged Function UTL_HTTP.REQUEST

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

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

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.

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

This table describes possible error messages that can display:

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> 
 

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 computer (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 computer.

 
Prev		  
Next
 		 Oracle 
Copyright © 1998 Oracle Corporation. 
All Rights Reserved. 
 
Library		  
Product		  
Contents		  
Index