Skip Headers

Oracle9i Database Getting Started
Release 2 (9.2) for Windows
Part Number A95490-01
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to next page

10
Developing Applications for Windows

This chapter points to sources of information on developing applications for Windows and outlines a procedure for building and debugging external procedures.

This chapter contains these topics:

Finding Information on Application Development for Windows

This section describes where to find information on developing applications specifically for Windows. These products are included on your Oracle Server CD-ROM.

Java Enhancements

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

XML Support

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

Support for Internet Applications

Oracle's 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 an Oracle9i database that can be started through browsers. For more information:

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:

Oracle COM/COM+ Integration Feature

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

ORACLEMTSRecoveryService

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

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

OLE DB

Developing Windows Applications

Oracle provides a comprehensive set of APIs for Windows application developers and is well suited for both Java and COM/COM+ development. Oracle is integrated with Microsoft's development and deployment components, known as Windows Distributed interNet Applications Architecture (DNA). Performance and data access on Windows is enhanced in the following areas:

Developers are able to deploy their database applications more quickly by using the data access method with which they are familiar, rather than having to learn a new one. An Oracle database server can communicate with Windows clients in a variety of methods, as described in Table 10-1

Table 10-1  Oracle Data Access Methods
Development Environment Data Access Method

Java

JDBC

SQLJ

COM/COM+

Oracle Objects for OLE (OO4O)

Oracle Provider for OLE DB

COM/COM+ Automation Feature

By using Oracle data access interfaces, developers can take advantage of specific Oracle9i features. These interfaces also offer flexibility and adherence to open standards.

Oracle COM/COM+ interfaces include the following features:

Developing Internet Applications

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

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

Figure 10-1 Microsoft Transaction Server and Oracle Database Integration

Text description of ntqrf004.gif follows
Text description of the illustration ntqrf004.gif



Building External Procedures

This section describes how to create and use external procedures on Windows. The following files are located in ORACLE_BASE\ORACLE_HOME\rdbms\extproc:

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.

Note:

Oracle also provides a special purpose interface, the call specification, that lets you call external procedures from other languages, as long as they are callable by C.

The main advantages of external procedures are:

You can use external procedures to perform specific processes:

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

Task 1: Installing and Configuring

This section describes installation and configuration of Oracle9i database and Oracle Net.

Installing Oracle9i Database

Follow the steps in Oracle9i Database Installation Guide for Windows to install these products on your Windows server:

Configuring Oracle Net Services

During database server installation, Oracle Net Configuration Assistant configures listener.ora and tnsnames.ora files for external procedure calls.

When an application calls an external procedure, Oracle Net Listener starts an external procedure agent called EXTPROC. Using a network connection established by the listener, the application passes the following information to EXTPROC:

EXTPROC then loads the DLL, runs the external procedure, and passes back any values returned by the external procedure.

If you overwrite default listener.ora and tnsnames.ora files, then you must manually configure the following files for the external procedure behavior described previously to occur:

Task 2: 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:

Note:

Because 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 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;
   }
}

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

After building the DLL, you can move it to any directory on your system. 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.

Task 4: Registering an External Procedure

Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with Oracle9i 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.

Task 5: 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:

In "Task 4: 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;

Debugging External Procedures

Usually, when an external procedure fails, its C prototype is faulty. That is, the prototype does not match the one generated internally by PL/SQL. This can happen if you specify an incompatible C datatype. For example, to pass an OUT parameter of type REAL, you must specify float *. Specifying float, double *, or any other C datatype will result in a mismatch.

In such cases, you might get a lost RPC connection to external procedure agent error, which means that agent extproc terminated abnormally because the external procedure caused a core dump. To avoid errors when declaring C prototype parameters, refer to Oracle9i Data Cartridge Developer's Guide.

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 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. Select OK in the message box.

    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:

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

    Text description of http.gif follows.

    Text description of the illustration http.gif

  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,

    Text description of http2.gif follows.

    Text description of the illustration http2.gif

    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:

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

Both packaged functions perform the following tasks:

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 can 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 this example. Note the use of PL/SQL table method COUNT to discover the number of pieces returned; it can 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 can 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 displays:
Statement processed.
4 pieces were retrieved.
with total length 
7687

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 exceptions (errors) that can be raised by packaged functions UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES.

UTL_HTTP.REQUEST

PRAGMA RESTRICT_REFERENCES enables 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 display of exceptions:

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.

Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback