15 Developing Applications for Windows

Java Enhancements

Oracle Database includes an integrated Java Virtual Machine and JServer Accelerator. Oracle Database also provides Oracle Java Database Connectivity (JDBC) Drivers. For more information, see Oracle Database Java Developer's Guide.

XML Support

Oracle XML products include XML Developer's Kit (XDK) and Oracle XML SQL Utility. For more information:

• Oracle XML Developer's Kit Programmer's Guide

• Oracle XML API Reference

Support for Internet Applications

Oracle Database support for internet applications includes Oracle Portal, which enables you to publish your data to the Web, Oracle HTTP Server, and PL/SQL Embedded Gateway, which offers PL/SQL procedures stored in Oracle Database that can be started through browsers. For more information:

• Oracle Portal Installation Guide and Tutorial

• Oracle Enterprise Manager Grid Control Installation and Basic Configuration

  
  

  Note: Oracle Portal is available on a separate CD-ROM and included with Oracle Database for Windows.

  
  

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. For information and downloads:

• http://otn.oracle.com/software/

Oracle COM/COM+ Integration Feature

Oracle COM/COM+ integration feature enables Java stored procedure developers and COM/COM+ developers to load COM+ objects from Oracle Database. For more information:

• Oracle COM Automation Feature Developer's Guide

• Oracle Objects for OLE Developer's Guide

Oracle Services For Microsoft Transaction Server

Oracle Database for Windows permits enhanced deployment of COM/COM+ components in Microsoft Transaction Server, using Oracle Database as the resource manager. For more information:

• Oracle Services for Microsoft Transaction Server Developer's Guide

Pro*C/C++ and Pro*COBOL Applications

• Pro*C/C++ Getting Started for Windows

• Pro*COBOL Getting Started for Windows

• Oracle Call Interface Programmer's Guide

OLE DB

• Oracle Provider for OLE DB Developer's Guide

  
  

  Note: Oracle ODBC Driver is updated on a regular basis. The newest release available is included on your CD-ROM. To download the latest release, visit: http://otn.oracle.com/software/ Select Oracle ODBC Drivers from the Select a Utility or Driver list.

  
  

Developing Internet Applications

Oracle Database provides Windows extensions for internet development, enabling access from any client, such as:

• Any browser

• Windows or Macintosh client

• FTP client

• Database client

• COM/COM+ client

• E-mail client

Oracle Database data access interfaces and development tools, along with Microsoft Transaction Server integration, can be used to build internet applications, as illustrated in Figure 15-1, "Microsoft Transaction Server and Oracle Database Integration".

Figure 15-1 Microsoft Transaction Server and Oracle Database Integration

Description of the illustration ntqrf004.gif

External Procedures Overview

External procedures are functions written in a third-generation language (C, for example) and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function. External procedures let you take advantage of strengths and capabilities of a third-generation programming language in a PL/SQL environment.

The main advantages of external procedures are:

• Performance, because some tasks are performed more efficiently in a third-generation 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 Oracle Forms

You can use external procedures to perform specific processes:

• Solving scientific and engineering problems

• Analyzing data

• Controlling real-time devices and processes

To create and use an external procedure, perform the following sequential steps:

• Installing and Configuring

• Writing an External Procedure

• Building a DLL

• Registering an External Procedure

• Executing an External Procedure

  
  

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

  
  
  
  

  Note: You can combine the instructions described in the fourth and fifth tasks into one SQL script that automates the task of registering and executing your external procedure. See ORACLE_BASE\ORACLE_HOME\rdbms\extproc\extern.sql for an example of a SQL script that combines these steps.

  
  

Installing and Configuring

This section describes installation and configuration of Oracle Database and Oracle Net.

Writing an External Procedure

Using a third-generation programming language, you can write functions to be built into DLLs and started by EXTPROC. The following is a simple Microsoft Visual C++ example of an external procedure:

#include <windows.h>
#define NullValue -1
/*
  This function tests if x is at least as big as 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;
   }
}

Building a DLL

After writing your external procedure(s) in a third-generation programming language, use the appropriate compiler and linker to build a DLL, making sure to export the external procedures as noted previously. See your compiler and linker documentation for instructions on building a DLL and exporting its functions.

For the example in Task 2, you can build the external procedure find_max into a DLL called extern.dll by going to ORACLE_BASE\ORACLE_HOME\rdbms\extproc and typing make. After building the DLL, you can move it to any directory on your system.

Starting with Oracle9i release 2, however, the default behavior of EXTPROC is to load DLLs only from ORACLE_HOME\bin or ORACLE_HOME\lib. To load DLLs from other directories, you must set environment variable EXTPROC_DLLS to a colon (:) separated list of DLL names qualified with their complete paths. The preferred way to set this environment variable is through the ENVS parameter in listener.ora.

Registering an External Procedure

Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with Oracle Database:

To create a PL/SQL library to map to the DLL:

1. Start SQL*Plus:

   C:\> sqlplus
   
   

2. Connect to the database with appropriate username and password.

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

   SQL> CREATE LIBRARY externProcedures AS 'C:\oracle\ora92\rdbms\ extproc\extern.dll';
   
   

   where externProcedures is an alias library (essentially a schema object in the database), and C:\oracle\ora92\rdbms\extproc\extern.dll is the path to the Windows operating system dllextern.dll. This example uses C:\oracle as your Oracle base and \ora92 as your Oracle home.

   
   

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

   
   

4. 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

   • DLL alias

   • Programming language in which it was written

   • Calling standard (defaults to C if omitted)

   In the following example, externProcedures is a DLL alias. You need the EXECUTE privilege for this library. The external procedure to call is find_max. If enclosed in double quotation marks, it becomes case-sensitive. The LANGUAGE term specifies the language in which the external procedure was written.

   CREATE OR REPLACE FUNCTION PLS_MAX(
     x BINARY_INTEGER,
     y BINARY_INTEGER)
   RETURN BINARY_INTEGER AS
     EXTERNAL LIBRARY externProcedures
     NAME "find_max"
     LANGUAGE C
     PARAMETERS (
       x long,                  -- stores value of x
       x_INDICATOR short,       -- used to determine if x is a NULL value
       y long,                  -- stores value of y
       y_INDICATOR short        -- used to determine if y is a NULL value
     RETURN INDICATOR short );  -- need to pass pointer to return value's
                                -- indicator variable to determine if NULL
   -- This means that my function will be defined as:
       -- long max(long x, short x_indicator,
       -- long y, short y_indicator, short * ret_indicator)
   

Executing an External Procedure

To run 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 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 "Registering an External Procedure", PL/SQL function PLS_MAX registered external procedure find_max. Follow these steps to run find_max:

1. Call PL/SQL function PLS_MAX from a PL/SQL routine named UseIt:

   SET SERVER OUTPUT ON
   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;
   

Using Package DEBUG_EXTPROC

To help you debug external procedures, PL/SQL provides utility package DEBUG_EXTPROC. To install the package, run script dbgextp.sql, which you can find in the PL/SQL demo directory.

To use the package, follow instructions in dbgextp.sql. Your Oracle Database account must have EXECUTE privileges on the package and CREATE LIBRARY privileges.

To debug external procedures:

1. From Windows Task Manager, in the Processes dialog, select ExtProc.exe.

2. Right click, and select Debug.

3. Click OK in the message window.

   If you have built your DLL in a debug fashion with Microsoft Visual C++, then Visual C++ is activated.

4. In the Visual C++ window, select Edit > Breakpoints.

   Use the breakpoint identified in dbgextp.sql in the PL/SQL demo directory.

   
   

   See Also: ORACLE_BASE\ORACLE_HOME\rdbms\extproc\readme.doc (explains how to run the sample and provides debugging advice) PL/SQL User's Guide and Reference Oracle Database Java Developer's Guide "Calling External Procedures" in Oracle Database Application Developer's Guide - Fundamentals Oracle Data Cartridge Developer's Guide

   
   

Configuring Intercartridge Exchange

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

1. Start Registry Editor from the command prompt:

   C:\> regedt32
   
   

   The Registry Editor window appears.

   
   

   Note: For another way to configure your registry, see "Modifying a Registry Value with regedt32"

   
   

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 only one home directory, add HTTP_PROXY to

   HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0. 
   
   

   If you have more than one home directory, add it to

   HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME 
   
   

3. Choose Add Value from the Edit menu.

   The Add Value dialog appears.

   
   Description of the illustration http.gif
   
   

4. Type HTTP_PROXY in the Value Name field and REG_SZ in the Data Type field.

5. Click OK.

6. Type www-proxy.your-site in the String field.

In this example, the Web site is marketing.com. You will enter the domain name of your actual 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 perform the following tasks:

• Take a string universal resource locator (URL) of a site

• Contact that site

• Return data (typically HTML) obtained from that site

Declarations to use with both packaged functions are described in the following subsections.

FUNCTION REQUEST (URL IN VARCHAR2) RETURN VARCHAR2;

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

UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')                         
        ------------------------------------------------------
<html>
<head><title>Oracle Home Page</title>
<!--changed Jan. 16, 19
1 row selected.

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;

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;

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 displays:
Statement processed.
4 pieces were retrieved.
with total length 
7687

UTL_HTTP Exception Conditions

This subsection describes exceptions (errors) that can be raised by packaged functions UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES.

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

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

If initialization of the HTTP callout subsystem fails for environmental reasons (such as lack of available memory), then exception UTL_HTTP.INIT_FAILED is raised:

init_failed exception;

If 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), then exception UTL_HTTP.REQUEST_FAILED is raised:

request_failed exception;

Unless explicitly caught by an exception handler, these first two exceptions are reported by a generic message that shows them as "user-defined" exceptions, even though they are defined in this system package:

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

If any other exception is raised during 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.

If no response is received from a request to the given URL, because the function made no contact with a site corresponding to that URL, then 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 UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES 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.