6
Developing Applications

This chapter describes topics of interest to application developers on Windows NT.

Specific topics discussed are:

• Finding Information on Application Development for Windows NT

• Building External Routines

• Accessing Web Data with Intercartridge Exchange

Finding Information on Application Development for Windows NT

The following table describes where to find the information on developing applications specifically for Windows NT.

To find information on...	Look in the guide...
XML Oracle's XML products include the XML Parser for Java - Version 1 and Version 2 (which includes an XSLT processor), the XML Class Generator, and the XML Parsers for C, C++, and PL/SQL as well as demos, utilities and sample code designed to illustrate the simplest and most powerful ways to work with XML-formatted data.	Oracle8i Application Developer's Guide - XML
Internet Tools Oracle WebDB enables you to publish your data to the Web	Oracle WebDB Installation Guide and Tutorial Note: WebDB is available on a separate CD-ROM and included with Oracle8i for Windows NT.
Application Wizards Oracle Application Wizards allow developers to create database applications easily and quickly. They improve ease-of-use and reduce development time by generating much of the code for database connectivity.	Oracle AppWizard for Microsoft Visual C++ User's Guide for Windows NT
OLE Automation	Oracle COM Automation Developer's Guide Oracle Objects for OLE (online Help) for Windows NT
Oracle Services for MTS Oracle8i Client provides Oracle Services for Microsoft Transaction Server (MTS). A Windows NT service called Oracle Service for MTS, permits enhanced deployment of COM components in MTS, using an Oracle database as the resource manager.	Using Microsoft Transaction Server With Oracle8
Pro*C/C++ and Pro*COBOL--based applications	Pro*C/C++ Precompiler Getting Started Pro*COBOL Precompiler Getting Started Oracle Call Interface Getting Started
Writing external routines and the call specification	This chapter. PL/SQL User's Guide and Reference Oracle8i Java Stored Procedures Developer's Guide Oracle8i Application Developer's Guide - Fundamentals The following files in ORACLE_BASE\ORACLE_HOME\RDBMS\EXTPROC: EXTERN.C (code example shown in "Step 2: Writing an External Routine") MAKE.BAT (batch file that builds the dynamic link library) EXTERN.SQL (automates the instructions described in "Step 4: Registering an External Routine" and "Step 5: Executing an External Routine") README.DOC (explains how to run the sample and provides debugging advice)
OLE DB	Oracle Provider for OLE DB

Additional Information: Oracle ODBC Driver Release 8.1.6 is included on your CD-ROM. This driver is updated on a regular basis. To download the latest release of this driver, visit the following Web site: http://technet.oracle.com/software/utilities/software_index.htm

Building External Routines

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

External Routines Overview

External routines, previously referred to as 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 routine or function. External routines let you take advantage of the strengths and capabilities of a 3GL programming language in a PL/SQL environment.

Note: Oracle also provides a special purpose interface, the call specification, that lets you call external routines from other languages, as long as it is callable by C.

The main advantages of external routines consist of the following:

• Performance, because some tasks are performed more efficiently in a 3GL language than in PL/SQL, which is better suited for SQL transaction processing

• Code re-usability, because dynamic link libraries (DLLs) can be called directly from PL/SQL programs on the server or in client tools such as SQL*Forms

You can use external routines to perform specific processes, such as the following:

• Solving scientific and engineering problems

• Analyzing data

• Controlling real-time devices and processes

Creating and using an external routine would involve the following sequential tasks:

• Step 1: Installing and Configuring

• Step 2: Writing an External Routine

• Step 3: Building a DLL

• Step 4: Registering an External Routine

• Step 5: Executing an External Routine

  Attention: You can combine the instructions described in the fourth and fifth tasks into one SQL script that automates the process of registering and executing your external routine. For an example of a SQL script that combines these steps, see ORACLE_BASE\ORACLE_HOME\RDBMS\EXTPROC\EXTERN.SQL.

Step 1: Installing and Configuring

This section describes the installation and configuration of the Oracle8i database and Net8.

Installing the Oracle8i Database

Install these products on your Windows NT server:

• Oracle8i Server. Contains PL/SQL, from which external routines are called, and the PL/SQL external routine program (EXTPROC), which executes external routines.

• Net8 Client, Net8 Server, and Oracle Protocol support

  Note: You must also have a C compiler and linker installed on your system to build DLLs.

Configuring Net8

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

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

• DLL name

• External routine name

• Parameters (if necessary)

EXTPROC then loads the DLL and invokes the external routine.

If you copy your Oracle7 server network files into your Oracle8i network files directory, you must manually configure the following files for the external routine behavior described previously to occur:

• ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\LISTENER.ORA

• ORACLE_BASE\ORACLE_HOME\NETWORK\ADMIN\TNSNAMES.ORA

See Chapter 8 of the Net8 Administrator's Guide for instructions.

.

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 Routine

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

Note: Since external routines 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 line with DebugBreak(); 
  in it 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 writing your external routine(s) in a 3GL programming language, use the appropriate compiler and linker to build a DLL, making sure to export the external routines, 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 routine find_max into a DLL called EXTERN.DLL. To build the above example, go to ORACLE_BASE\ORACLE_HOME\RDBMS\EXTPROC and type MAKE.

Step 4: Registering an External Routine

Once you have built a DLL containing your external routine(s), you must register your external routine(s) with the Oracle8i Database:

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

2. Start SQL*Plus:

   C:\> SQLPLUS
   

3. Connect to the database with the appropriate user name and password.

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

   SQL> CREATE LIBRARY externProcedures AS 'C:\ORACLE\ORA81\RDBMS\ 
   EXTPROC\EXTERN.DLL';
   

   Where...	Represents the...
   externProcedures	Alias library (essentially a schema object in the database)
   C:\ORACLE\ORA81\RDBMS\EXTPROC\EXTERN.DLL	Path to the Windows NT operating system DLL EXTERN.DLL. This example uses C:\ORACLE as your Oracle base and \ORA81 as your Oracle home.

   Note: The DBA must grant EXECUTE privileges on the PL/SQL library to users who want to call the library's external routine from PL/SQL or SQL.

5. 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 routine. The EXTERNAL clause identifies the following information about the external routine:

   • Name

   • DLL alias

   • Programming language in which it was written

   • Calling standard (defaults to C if omitted)

     
     

Step 5: Executing an External Routine

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

• Anonymous blocks

• Standalone and packaged subprograms

• Methods of an object type

• Database triggers

• SQL statements (calls to packaged functions only)

In "Step 4: Registering an External Routine", the PL/SQL function PLS_MAX registered the external routine find_max. Follow the steps below to execute find_max:

1. Call the PL/SQL function PLS_MAX from a PL/SQL routine 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;
   

2. Run the routine:

   SQL> EXECUTE UseIt;
   

Accessing Web Data with Intercartridge Exchange

This section discusses the following topics:

• Configuring Intercartridge Exchange

• Using Intercartridge Exchange

• UTL_HTTP Exception Conditions

• Exception Conditions and Error Messages

• Troubleshooting

Configuring Intercartridge Exchange

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

To configure Intercartridge Exchange:

1. Start the registry editor from the MS-DOS command prompt:

   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 of this parameter is determined by how many Oracle home directories are on your computer:

   If you have...	Add HTTP_PROXY to...
   One home directory	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
   Additional directories	HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ HOMEID where ID is incremented for each additional Oracle home directory on your computer.

3. Choose Add Value from the Edit menu.

   The Add Value dialog box appears.

4. Type HTTP_PROXY in the Value Name text box and REG_SZ in the Data Type text box.

   
   

5. Click OK.

6. Type www-proxy.your-site in the String text box,

   
   

   where marketing.com is an example of a Web site. (Type the domain name of your real Web site.)

Using Intercartridge Exchange

Intercartridge Exchange enables you to use a stored package called UTL_HTTP to make Hypertext Transfer Protocol (HTTP) calls from PL/SQL, SQL, and SQL*Plus statements.

UTL_HTTP can do both of the following:

• Access data on the Internet

• Call Oracle Web Application Server cartridges

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

• UTL_HTTP.REQUEST

• UTL_HTTP.REQUEST_PIECES

Both packaged functions peform the following tasks:

• 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 in the following subsections.

Packaged Function UTL_HTTP.REQUEST

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

Specify UTL_HTTP.REQUEST as follows:

FUNCTION REQUEST (URL IN VARCHAR2) RETURN VARCHAR2;

To use UTL_HTTP.REQUEST from SQL*Plus, enter:

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

which returns:

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 uses 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; this 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 usually 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 subsection describes the exceptions (errors) that can be raised by packaged functions UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES.

UTL_HTTP.REQUEST

PRAGMA RESTRICT_REFERENCES enables the display of exceptions:

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

PRAGMA RESTRICT_REFERENCES enables exceptions 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);

Exception Conditions and Error Messages

The following table describes error messages that may appear.

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 due to 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 the function made no contact with a site corresponding to that URL	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>

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, and so on).

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.