Release 1 (9.0.1.1.0) for Windows 98
Part Number A95907-01
 Contents |
 Index |
| Oracle9i Release 1 (9.0.1.1.0) for Windows 98 Part Number A95907-01 |
|
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:
This section describes where to find information on developing applications specifically for Windows. These products are included on your Oracle Server CD-ROM.
Oracle9i includes many Java enhancements. JVM includes the integrated Java Virtual Machine, JServer Accelerator, and Oracle Servlet Engine. Oracle also provides Oracle Java Database Connectivity (JDBC) Drivers. For more information:
Oracle's XML products include the XML Developer's Kit (XDK) and Oracle XML SQL Utility. For more information:
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's COM/COM+ integration feature enables Java stored procedure developers and COM/COM+ developers to load COM+ objects from the Oracle9i database. For more information:
Oracle ODBC Driver is updated on a regular basis. Release 9.0 is included on your CD-ROM. To download the latest release, visit:
Select Oracle ODBC Drivers from the "Select a Utility or Driver" drop-down list.
Note:
http://technet.oracle.com/software/
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 the 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 8-1.
| Development Environment | Data Access Method |
|---|---|
|
Java/CORBA |
SQLJ |
|
COM/COM+ |
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:
This section describes how to create and use external procedures on Windows. The following files are located in ORACLE_BASE\ORACLE_HOME\rdbms\extproc:
extern.c Code example shown in "Task 2: Writing an External Procedure"
make.bat Batch file that builds the dynamic link library
extern.sql Automates the instructions described in "Task 4: Registering an External Procedure" and "Task 5: Executing an External Procedure"
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 the strengths and capabilities of a third-generation programming language in a PL/SQL environment.
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:
This section describes the installation and configuration of the Oracle9i database and Oracle Net.
Follow the steps in your Oracle9i Personal Edition Installation Guide for Windows 98 to install these products on your Windows server:
If you install Oracle Net Server from your CD-ROM, then your server network files are automatically configured to use external procedures.
When PL/SQL calls an external procedure, the Oracle Net Listener starts a session-specific process called EXTPROC. Through Oracle Net, PL/SQL passes the following information to EXTPROC:
EXTPROC then loads the DLL and starts the external procedure.
If you copy your server network files into your Oracle9i network files directory, then you must manually configure the following files for the external procedure behavior described previously to occur:
ORACLE_BASE\ORACLE_HOME\network\admin\listener.ora
ORACLE_BASE\ORACLE_HOME\network\admin\tnsnames.ora
See the Oracle9i Net Services Administrator's Guide for instructions.
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; } }
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.
Once you have built a DLL containing your external procedure(s), you must register your external procedure(s) with the Oracle9i database:
To create a PL/SQL library to map to the DLL:
C:\> sqlplus
CREATE LIBRARY command:
SQL> CREATE LIBRARY externProcedures AS 'C:\oracle\ora81\rdbms\ extproc\extern.dll';
where externProcedures is an alias library (essentially a schema object in the database), and C:\oracle\ora81\rdbms\extproc\extern.dll is the path to the Windows operating system dllextern.dll. This example uses C:\oracle as your Oracle base and \ora90 as your Oracle home.
|
Note:
The DBA must grant the |
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:
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)
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", the PL/SQL function PLS_MAX registered the external procedure find_max. Follow these steps to run find_max:
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;
SQL> EXECUTE UseIt;
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 the Oracle9i Data Cartridge Developer's Guide.
To help you debug external procedures, PL/SQL provides the utility package DEBUG_EXTPROC. To install the package, run the script dbgextp.sql, which you can find in the PL/SQL demo directory.
To use the package, follow the instructions in dbgextp.sql. Your Oracle account must have EXECUTE privileges on the package and CREATE LIBRARY privileges.
To debug external procedures:
If you have built your DLL in a debug fashion with Microsoft Visual C++, then Visual C++ is activated.
Use the breakpoint identified in dbgextp.sql in the PL/SQL demo directory.
|
See Also:
|
This section discusses the following topics:
You must add a parameter to the registry before using Intercartridge Exchange.
To configure Intercartridge Exchange:
C:\> REGEDIT
The Registry Editor window appears.
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:
The declarations to use with both packaged functions are described in the following subsections.
UTL_HTTP.REQUEST uses an 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.
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 (of 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 subsection 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.
|
 Copyright © , 2001 Oracle Corporation. All Rights Reserved. |
|