Release 8.1.5 for Windows NT
A68694-01
Library |
Product |
Contents |
Index |
| Oracle8i
Enterprise Edition for Windows NT Getting Started
Release 8.1.5 for Windows NT A68694-01 |
|
This chapter describes topics of interest to application developers.
Specific topics discussed:
This section describes the Java-related products that are included in Oracle8i Enterprise Edition release 8.1.5.
Oracle Java option provides a Java Development Kit (JDK) 1.1.6-compliant Java Virtual Machine, a CORBA 2.0 Object Request Broker, an embedded JDBC driver, a SQLJ translator, and an Enterprise JavaBeans transaction server.
Oracle Java Utilities provide Java tools to build and deploy Java stored procedures, CORBA objects, and Enterprise JavaBeans with Oracle's Java Virtual Machine.
JDBC is a standard Java interface for connecting to relational databases from Java. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers.
JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92 Entry Level standard.
In addition to the standard JDBC API, Oracle drivers have extensions to properties, types, and performance.
SQLJ enables application programmers to embed SQL operations in Java code. A SQLJ program is a Java program that contains embedded SQL statements that comply with the SQLJ Language Reference syntax.
SQLJ consists of a translator and a runtime component.
You run the translator before compiling and it replaces embedded SQL with calls to the runtime component. The runtime component then implements the SQL operations. In standard SQLJ this is usually done through calls to a JDBC driver. In the case of an Oracle database, an Oracle JDBC driver is used.
This section describes how to create and use external procedures on Windows NT.
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 let you take advantage of the strengths and capabilities of a 3GL programming language in a PL/SQL environment.
The main advantages of external procedures consist of the following:
Creating and using an external procedure would involve the following sequential tasks:
This section describes the installation and configuration of Oracle8i Enterprise Edition and Net8.
Follow the procedures in Oracle8i Enterprise Edition Installation for Windows NT to install these products on your Windows NT server:
|
Note: You must also have a C compiler and linker installed on your system to build DLLs. |
If you install Net8 Server from your Oracle8i 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:
If you copy your Oracle7 server network files into your Oracle8i network files directory, you must manually configure the following files for the external procedure behavior described previously to occur:
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 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; } }
After writing 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_BASE\ORACLE_HOME\RDBMS\EXTPROC and type MAKE.
Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with the Oracle database:
SVRMGR> CREATE LIBRARY externProcedures AS 'C:\ORACLE\ORA81\RDBMS\EXTPROC\EXTERN.DLL';
|
Note: The DBA must grant EXECUTE privileges on the PL/SQL library to users who want to call the library's external procedures from PL/SQL or SQL. |
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 any of the following:
In "Registering an External Procedure", the PL/SQL function PLS_MAX registered the external procedure find_max. Follow the procedures below to execute find_max:
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;
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, lightweight threads instead of a single, dedicated process-per-user session.
To enable multi-threaded callouts:
This enables you to use multiple, lightweight threads when making calls to EXTPROC from PL/SQL programs.
See the following documents for more information on external procedures:
This section discusses the following topics:
You must add a parameter to the registry before using Intercartridge Exchange.
To configure Intercartridge Exchange:
C:\> REGEDT32
The Registry Editor window appears.
The Add Value dialog box appears.
where marketing.com is an example of a Web site. (Type the domain
name of your real Web site.)
Intercartridge Exchange 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 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: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 Server Manager, enter:
SVRMGR> 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.
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.
This sub-section describes the exceptions (errors) that can be raised by packaged functions UTL_HTTP.REQUEST and UTL_HTTP.REQUEST_PIECES.
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);
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);
The following table describes error messages that may appear.
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.
