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 is available on a separate media and included with Oracle Database for Windows.

Note:

Oracle Portal is not supported on Oracle Database for Microsoft Windows (x64).

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, go to:

ODP.NET

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for Oracle Database. ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library. For more information, refer Oracle Data Provider for .NET Developer's Guide.

Developing Windows Applications

Oracle Database provides a comprehensive set of APIs for Windows application developers and is well suited for both Java and COM/COM+ development. Oracle Database 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 16-1.

Table 16-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 Database data access interfaces, developers can take advantage of specific Oracle Database features. These interfaces also offer flexibility and adherence to open standards.

Oracle COM/COM+ interfaces include the following features:

OO4O can be used from any programming environment that supports Microsoft COM/COM+ technology, such as Visual Basic, Visual C++, VBA in Excel, Active Server Pages, PowerBuilder, Delphi, Internet Information Server (IIS), and Microsoft Transaction Server. OO4O is developed specifically for use with Oracle Database servers.

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

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

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.

Configuring Oracle Net Services

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:

DLL name

External procedure name

Parameters (if necessary)

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:

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 called FIND_MAX:

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

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.

You can build the external procedure FIND_MAX, created in "Writing an External Procedure", 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.

where externProcedures is an alias library (essentially a schema object in the database), and

C:\oracle\product\11.1.0\db_1\rdbms\extproc\extern.dll

is the path to the Windows operating system dllextern.dll. This example uses C:\oracle\product\11.1.0 as your Oracle base and db_1 as your Oracle home.

Note:

The DBA must grant the EXECUTEprivilege on the PL/SQL library to users who want to call the library's external procedure 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

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.

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:

Multithreaded Agent Architecture

An agent process is started for each session to access a system at the same time leading to several thousand agent processes running concurrently. The agent processes operate regardless of whether each individual agent process is actually active at the moment. Agent processes and open connections can consume a disproportionate amount of system resources. This problem is addressed by using multithreaded agent architecture.

The multithreaded agent architecture uses a pool of shared agent threads. The tasks requested by the user sessions are put in a queue and are picked up by the first available multithreaded agent thread. Because only a small percentage of user connections are active at a given moment, using a multithreaded architecture allows for more efficient use of system resources.

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 lostRPCconnectiontoexternalprocedureagent 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 Oracle Database 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 Database account must have EXECUTE privileges on the package and CREATELIBRARY privileges.

To debug external procedures:

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

Right click, and select Debug.

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.

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)

Accessing Text Files with UTL_FILE

Package UTL_FILE allows your PL/SQL programs to read and write operating system text files. It provides a restricted version of standard operating system stream file I/O, including open, put, get, and close operations. When you want to read or write a text file, you call the function fopen, which returns a file handle for use in subsequent procedure calls. For example, the procedure put_line writes a text string and line terminator to an open file, and the procedure get_line reads a line of text from an open file into an output buffer.

FSEEK, a UTL_FILE subprogram, adjusts the file pointer forward or backward within the file by the number of bytes specified. In order for UTL_FILE.FSEEK to work correctly, the lines in the file must have platform-specific line terminator characters. On Windows platforms the correct line terminator characters are <CR><LF>.

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

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.

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.

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:

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.

Scripting on this page enhances content navigation, but does not change the content in any way.