Release 8.1.5
A68003-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter presents brief introductions to these application development systems:
PL/SQL is Oracle's procedural extension to SQL, the standard database access language. An advanced 4GL (fourth-generation programming language), PL/SQL offers seamless SQL access, tight integration with the Oracle server and tools, portability, security, and modern software engineering features such as data encapsulation, overloading, exception handling, and information hiding.
With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of- control statements to process the data. Moreover, you can declare constants and variables, define procedures and functions, use collections and object types, and trap run-time errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.
Applications written using any of the Oracle programmatic interfaces (Oracle Call Interface, Java, Pro*C/C++, or COBOL) can call PL/SQL stored procedures and send anonymous blocks of PL/SQL code to the server for execution. 3GL applications have full access to PL/SQL scalar and composite datatypes via host variables and implicit datatype conversion.
PL/SQL's tight integration with Oracle Developer lets you use one language to develop the client and server components of your application, which can be partitioned to achieve optimum performance and scalability. Also, Oracle's Web Forms allows you to deploy your applications in a multi-tier Internet or intranet environment without modifying a single line of code.
A good way to get acquainted with PL/SQL is to look at a sample program. Consider the procedure below, which debits a bank account. When called, procedure debit_account accepts an account number and a debit amount. It uses the account number to select the account balance from the database table. Then, it uses the debit amount to compute a new balance. If the new balance is less than zero, an exception is raised; otherwise, the bank account is updated.
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS old_balance REAL; new_balance REAL; overdrawn EXCEPTION; BEGIN SELECT bal INTO old_balance FROM accts WHERE acct_no = acct_id; new_balance := old_balance - amount; IF new_balance < 0 THEN RAISE overdrawn; ELSE UPDATE accts SET bal = new_balance WHERE acct_no = acct_id; END IF; COMMIT; EXCEPTION WHEN overdrawn THEN -- handle the error END debit_account;
PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. So, you can manipulate Oracle data flexibly and safely. Moreover, PL/SQL fully supports SQL datatypes. That reduces the need to convert data passed between your applications and the database.
PL/SQL also supports dynamic SQL, an advanced programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements "on the fly" at run time.
Both PL/SQL and Oracle are based on SQL. Moreover, PL/SQL supports all the SQL datatypes. Combined with the direct access that SQL provides, these shared datatypes integrate PL/SQL with the Oracle data dictionary.
The %TYPE and %ROWTYPE attributes further integrate PL/SQL with the data dictionary. For example, you can use the %TYPE attribute to declare variables, basing the declarations on the definitions of database columns. If a definition changes, the variable declaration changes accordingly at run time. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
Without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic.
However, with PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce communication between your application and Oracle. If your application is database intensive, you can use PL/SQL blocks to group SQL statements before sending them to Oracle for execution.
PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Also, stored procedures, which execute in the server, can be invoked over slow network connections with a single call. This reduces network traffic and improves round-trip response times. Executable code is automatically cached and shared among users. That lowers memory requirements and invocation overhead.
PL/SQL adds functionality to non-procedural tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar procedural constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger. You need not use multiple trigger steps, macros, or user exits. Thus, PL/SQL increases productivity by putting better tools in your hands.
Moreover, PL/SQL is the same in all environments. As soon as you master PL/SQL with one Oracle tool, you can transfer your knowledge to other tools, and so multiply the productivity gains. For example, scripts written with one tool can be used by other tools.
PL/SQL stored procedures increase scalability by isolating application processing on the server. Also, automatic dependency tracking for stored procedures aids the development of scalable applications.
The shared memory facilities of the Multithreaded Server (MTS) enable Oracle to support 10,000+ concurrent users on a single node. For more scalability, you can use the Net8 Connection Manager to multiplex Net8 connections.
Once validated, a PL/SQL stored procedure can be used with confidence in any number of applications. If its definition changes, only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on various client machines.
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods, which you can implement in PL/SQL.
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
A collection is an ordered group of elements, all of the same type (for example, the grades for a class of students). Each element has a unique subscript that determines its position in the collection. PL/SQL offers two kinds of collections: nested tables and VARRAYs (short for variable-size arrays).
Collections, which work like the arrays found in most third-generation programming languages, can store instances of an object type and, conversely, can be attributes of an object type. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms. Furthermore, you can define collection types in a PL/SQL package, then use them programmatically in your applications.
Applications written in PL/SQL are portable to any operating system and platform on which Oracle runs. In other words, PL/SQL programs can run anywhere Oracle can run. You need not tailor them to each new environment. That means you can write portable program libraries, which can be reused in different environments.
PL/SQL stored procedures enable you to partition application logic between the client and server. That way, you can prevent client applications from manipulating sensitive Oracle data. Database triggers written in PL/SQL can disable application updates selectively and do content-based auditing of user queries.
Furthermore, you can restrict access to Oracle data by allowing users to manipulate it only through stored procedures that execute with their definer's privileges. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself.
The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to create applications that use a third-generation language's native procedures or function calls to access an Oracle database server and control all phases of SQL statement execution. OCI provides:
OCI allows you to manipulate data and schemas in an Oracle database using a host programming language, such as C. It provides a library of standard database access and retrieval functions in the form of a dynamic runtime library (OCILIB) that can be linked in an application at runtime. This eliminates the need to embed SQL or PL/SQL within 3GL programs.
OCI supports the datatypes, calling conventions, syntax, and semantics of a number of third-generation languages including C, C++, COBOL and FORTRAN. Oracle is also planning to provide support for Java.
OCI provides significant advantages over other methods of accessing an Oracle database:
INSERTs, UPDATEs, and DELETEs
The OCI encompasses four main sets of functionality:
The Oracle Call Interface (OCI) allows you to develop applications that combine the non-procedural data access power of Structured Query Language (SQL) with the procedural capabilities of most programming languages, such as C and C++.
The combination of both non-procedural and procedural language elements in an OCI program provides easy access to an Oracle database in a structured programming environment.
The OCI supports all SQL data definition, data manipulation, query, and transaction control facilities that are available through an Oracle database server. For example, an OCI program can run a query against an Oracle database. The queries can require the program to supply data to the database using input (bind) variables, as follows:
SELECT name FROM employees WHERE empno = :empnumber
In the above SQL statement,:empnumber is a placeholder for a value that will be supplied by the application.
You can also use PL/SQL, Oracle's procedural extension to SQL. The applications you develop can be more powerful and flexible than applications written in SQL alone. The OCI also provides facilities for accessing and manipulating objects in an Oracle database server.
As Figure 1-1 shows, you compile and link an OCI program in the same way that you compile and link a non-database application. There is no need for a separate preprocessing or precompilation step.
Note: On some platforms, it may be necessary to include other libraries, in addition to the OCI library, to properly link your OCI programs. Check your Oracle system-specific documentation for further information about extra libraries that may be required.
Oracle Objects for OLE (OO4O) is a product designed to allow easy access to data stored in Oracle databases with any programming or scripting language that supports the Microsoft COM Automation and ActiveX technology. This includes Visual Basic, Visual C++, Visual Basic For Applications (VBA), IIS Active Server Pages (VBScript and JavaScript), and others.
OO4O consists of the following software layers:
Figure 1-2, "Software Layers" illustrates the OO4O software components.
Note: See the OO4O online help for detailed information about using OO4O.
The OO4O Automation Server is a set of COM Automation objects for connecting to Oracle database servers, executing SQL statements and PL/SQL blocks, and accessing the results.
Unlike other COM-based database connectivity APIs, such as Microsoft ADO, the OO4O Automation Server has been developed and evolved specifically for use with Oracle database servers.
It provides an optimized API for accessing features that are unique to Oracle and are otherwise cumbersome or inefficient to use from ODBC or OLE database-specific components.
OO4O provides key features for accessing Oracle databases efficiently and easily in environments ranging from the typical two-tier client/server applications, such as those developed in Visual Basic or Excel, to application servers deployed in multi-tiered application server environments such as web server applications in Microsoft Internet Information Server (IIS) or Microsoft Transaction Server (MTS).
Features include:
The Oracle Objects for OLE object model is illustrated in Figure 1-3, "Objects and Their Relation".
An OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset objects used within an application.
Typically, a single OraSession object is created for each application, but you can create named OraSession objects for shared use within and between applications.
The OraSession object is the top-most level object for an application. It is the only object created by the CreateObject VB/VBA API and not by an Oracle Objects for OLE method. The following code fragment shows how to create an OraSession object:
Dim OraSession as Object Set OraSession = CreateObject("OracleInProcServer.XOraSession")
OraServer represents a physical network connection to an Oracle database server.
The OraServer interface is introduced to expose the connection multiplexing feature provided in the Oracle Call Interface. After an OraServer object is created, multiple user sessions (OraDatabase) can be attached to it by invoking the OpenDatabase method. This feature is particularly useful for application components, such as Internet Information Server (IIS), that use Oracle Objects for OLE in an n-tier distributed environments.
The use of connection multiplexing when accessing Oracle severs with a large number of user sessions active can help reduce server processing and resource requirements while improving the server scalability.
An OraDatabase interface in the Oracle8i release adds additional methods for controlling transactions and creating interfaces representing of Oracle object types. Attributes of schema objects can be retrieved using the Describe method of the OraDatabase interface.
In previous releases, an OraDatabase object is created by invoking the OpenDatabase method of an OraSession interface. The Net8 alias, user name, and password are passed as arguments to this method. In the Oracle8i release, invocation of this method results in implicit creation of an OraServer object.
As described in the OraServer interface description, an OraDatabase object can also be created using the OpenDatabase method of the OraServer interface.
Transaction control methods are available at the OraDatabase (user session) level. Transactions may be started as Read-Write (default), Serializable, or Read-only. These include:
For example:
UserSession.BeginTrans(OO4O_TXN_READ_WRITE) UserSession.ExecuteSQL("delete emp where empno = 1234") UserSession.CommitTrans
An OraDynaset object permits browsing and updating of data created from a SQL SELECT statement.
The OraDynaset object can be thought of as a cursor, although in actuality several real cursors may be used to implement the OraDynaset's semantics. An OraDynaset automatically maintains a local cache of data fetched from the server and transparently implements scrollable cursors within the browse data. Large queries may require significant local disk space; application implementers are encouraged to refine queries to limit disk usage.
An OraField object represents a single column or data item within a row of a dynaset.
If the current row is being updated, then the OraField object represents the currently updated value, although the value may not yet have been committed to the database.
Assignment to the Value property of a field is permitted only if a record is being edited (using Edit) or a new record is being added (using AddNew). Other attempts to assign data to a field's Value property results in an error.
An OraMetaData object is a collection of OraMDAttribute objects that represent the description information about a particular schema object in the database.
The OraMetaData object can be visualized as a table with three columns:
The OraMDAttribute objects contained in the OraMetaData object can be accessed by subscripting using ordinal integers or by using the name of the property. Referencing a subscript that is not in the collection (0 to Count-1) results in the return of a NULL OraMDAttribute object.
An OraParameter object represents a bind variable in a SQL statement or PL/SQL block.
OraParameter objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects' descriptions), by using the parameter's name as a placeholder in the SQL or PL/SQL statement. Such use of parameters can simplify dynamic queries and increase program performance.
An OraParamArray object represents an "array" type bind variable in a SQL statement or PL/SQL block as opposed to a "scalar" type bind variable represented by the OraParameter object.
OraParamArray objects are created, accessed, and removed indirectly through the OraParameters collection of an OraDatabase object. Each parameter has an identifying name and an associated value.
An OraSQLStmt Object represents a single SQL statement. Use the CreateSQL method to create the OraSQLStmt object from an OraDatabase.
During create and refresh, OraSQLStmt objects automatically bind all relevant, enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can improve the performance of SQL statement execution without re-parsing the SQL statement.
The SQLStmt object (updateStmt) can be later used to execute the same query using a different value for the :SALARY placeholder. This is done as follows:
OraDatabase.Parameters("SALARY").value = 200000 updateStmt.Parameters("ENAME").value = "KING" updateStmt.Refresh
An OraAQ object is instantiated by invoking the CreateAQ method of the OraDatabase interface. It represents a queue that is present in the database.
Oracle Objects for OLE provides interfaces for accessing Oracle's Advanced Queuing (AQ) Feature. It makes AQ accessible from popular COM-based development environments such as Visual Basic. For a detailed description of Oracle AQ, please refer to Oracle8i Application Developer's Guide - Advanced Queuing.
The OraAQMsg object encapsulates the message to be enqueued or dequeued. The message can be of any user-defined or raw type.
For a detailed description of Oracle AQ, please refer to Oracle8i Application Developer's Guide - Advanced Queuing.
The OraAQAgent object represents a message recipient and is only valid for queues which allow multiple consumers.
The OraAQAgent object represents a message recipient and is only valid for queues which allow multiple consumers.
An OraAQAgent object can be instantiated by invoking the AQAgent method. For example:
Set agent = qMsg.AQAgent(name)
An OraAQAgent object can also be instantiated by invoking the AddRecipient method. For example:
Set agent = qMsg.AddRecipient(name, address, protocol).
Oracle Objects for OLE provides full support for accessing and manipulating instances of object datatypes and LOBs in an Oracle database server. Figure 1-4, "Supported Oracle Datatypes" illustrates the datatypes supported by OO4O.
For a discussion of support for object datatypes, see "Oracle Objects For OLE".
Instances of these types can be fetched from the database or passed as input or output variables to SQL statements and PL/SQL blocks, including stored procedures and functions. All instances are mapped to COM Automation Interfaces that provide methods for dynamic attribute access and manipulation. These interfaces may be obtained from:
The OraBlob and OraClob interfaces in OO4O provide methods for performing operations on large objects in the database of data types BLOB, CLOB, and NCLOB. In this help file BLOB, CLOB, and NCLOB datatypes are also referred to as LOB datatypes.
LOB data is accessed using Read and the CopyToFile methods.
LOB data is modified using Write, Append, Erase, Trim, Copy, CopyFromFile, and CopyFromBFile methods. Before modifying the content of a LOB column in a row, a row lock must be obtained. If the LOB column is a field of an OraDynaset, then the lock is obtained by invoking the Edit method.
The OraBFile interface in OO4O provides methods for performing operations on large objects BFILE data type in the database.
The BFILEs are large binary data objects stored in operating system files (external) outside of the database tablespaces.
The Oracle Data Control (ODC) is an ActiveX Control that is designed to simplify the exchange of data between an Oracle database and visual controls such edit, text, list, and grid controls in Visual Basic and other development tools that support custom controls.
ODC acts an agent to handle the flow of information from an Oracle database and a visual data-aware control, such as a grid control, that is bound to it. The data control manages various user interface (UI) tasks such as displaying and editing data. It also executes and manages the results of database queries.
The Oracle Data Control is compatible with the Microsoft data control included with Visual Basic. If you are familiar with the Visual Basic data control, learning to use the Oracle Data Control is quick and easy. Communication between data-aware controls and a Data Control is governed by a protocol that has been specified by Microsoft.
The Oracle Objects for OLE C++ Class Library is a collection of C++ classes that provide programmatic access to the Oracle Object Server. Although the class library is implemented using OLE Automation, neither the OLE development kit nor any OLE development knowledge is necessary to use it. This library helps C++ developers avoid the chore of writing COM client code for accessing the OO4O interfaces.
For detailed information about Oracle Objects for OLE refer to the online help that is provided with the OO4O product:
To view examples of the use of Oracle Object for OLE, see the samples located in the ORACLE_HOME\OO4O directory of the Oracle installation. Additional OO4O examples can be found in the following Oracle publications, including:
The Pro*C/C++ precompiler is a software tool that allows the programmer to embed SQL statements in a C or C++ source file. Pro*C/C++ reads the source file as input and outputs a C or C++ source file that replaces the embedded SQL statements with Oracle runtime library calls, and is then compiled by the C or C++ compiler.
When there are errors found during the precompilation or the subsequent compilation, modify your precompiler input file and re-run the two steps.
Here is a simple code fragment from a C source file that queries the table EMP which is in the schema SCOTT:
... #define UNAME_LEN 10 ... int emp_number; /* Define a host structure for the output values of a SELECT statement. */ /* No declare section needed if precompiler option MODE=ORACLE */ struct { VARCHAR emp_name[UNAME_LEN]; float salary; float commission; } emprec; /* Define an indicator structure to correspond to the host output structure. */ struct { short emp_name_ind; short sal_ind; short comm_ind; } emprec_ind; ... /* Select columns ename, sal, and comm given the user's input for empno. */ EXEC SQL SELECT ename, sal, comm INTO :emprec INDICATOR :emprec_ind FROM emp WHERE empno = :emp_number; ...
The embedded SELECT statement is only slightly different from an interactive (SQL*Plus) version. Every embedded SQL statement begins with EXEC SQL. The colon, ":", precedes every host (C) variable. The returned values of data and indicators (set when the data value is NULL or character columns have been truncated) can be stored in structs (such as in the above code fragment), in arrays, or in arrays of structs. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, because of the unique employee number. You use the actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or you can enter values which give you control over the use of resources, how errors are reported, the formatting of output, and how cursors (which correspond to a particular connection, a SQL statement, etc.) are managed. Cursors are used when there are multiple result set values.
Enter the options either in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can then compile, link, and execute the output source file, like any other C program that you write.
Use the precompiler to create server database access from clients that can be on many different platforms. Pro*C/C++ allows you the freedom to design your own user interfaces and to add database access to existing applications.
Before writing your embedded SQL statements, you may want to test interactive versions of the SQL in SQL*Plus. You then make only minor changes to start testing your embedded SQL application.
The following is a short subset of the capabilities of Pro*C/C++. For complete details, see the Pro*C/C++ Precompiler Programmer's Guide.
You can write your application in either C or C++.
You can write multi-threaded programs if your platform supports a threads package. Concurrent connections are supported in either single-threaded or multi-threaded applications.
You can improve performance by embedding PL/SQL blocks. These blocks can call functions or procedures written by you or provided in Oracle packages, in either Java or PL/SQL.
Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, as well as at runtime.
You can call stored PL/SQL and Java subprograms. Modules written in COBOL or in C can be called from Pro*C/C++. External C procedures in shared libraries are callable by your program.
You can conditionally precompile sections of your code so that they can execute in different environments.
Use arrays, or structures, or arrays of structures as host and indicator variables in your code to improve performance.
You can deal with errors and warnings so that data integrity is guaranteed. As a programmer, you control how errors are handled.
Your program can convert between internal datatypes and C language datatypes.
The Oracle Call Interface (OCI), a lower-level C interface, is available for use in your precompiler source.
Pro*C/C++ supports dynamic SQL, a technique that allows users to input variable values and statement syntax.
Pro*C/C++ can use special SQL statements to manipulate tables containing user-defined object types. An Object Type Translator (OTT) will map the object types and named collection types in your database to structures and headers that you will then include in your source.
Two kinds of collection types, nested tables and VARRAYs, are supported with a set of SQL statements that allow a high degree of control over data.
Large Objects (LOBs, CLOBs, NCLOBs, and external files known as BFILEs) are accessed by another set of SQL statements.
A new ANSI SQL standard for dynamic SQL is supported for new applications, so that you can execute SQL statements with a varying number of host variables. An older technique for dynamic SQL is still usable by pre-existing applications.
National Language Support for multi-byte characters and UCS2 Unicode support are provided.
The Pro*COBOL precompiler is a software tool that allows the programmer to embed SQL statements in a COBOL source code file. Pro*COBOL reads the source file as input and outputs a COBOL source file that replaces the embedded SQL statements with Oracle runtime library calls, and is then compiled by the COBOL compiler.
When there are errors found during the precompilation or the subsequent compilation, modify your precompiler input file and re-run the two steps.
Here is a simple code fragment from a source file that queries the table EMP which is in the schema SCOTT:
... WORKING-STORAGE SECTION. * * DEFINE HOST INPUT AND OUTPUT HOST AND INDICATOR VARIABLES. * NO DECLARE SECTION NEEDED IF MODE=ORACLE. * 01 EMP-REC-VARS. 05 EMP-NAME PIC X(10) VARYING. 05 EMP-NUMBER PIC S9(4) COMP VALUE ZERO. 05 SALARY PIC S9(5)V99 COMP-3 VALUE ZERO. 05 COMMISSION PIC S9(5)V99 COMP-3 VALUE ZERO. 05 COMM-IND PIC S9(4) COMP VALUE ZERO. ... PROCEDURE DIVISION. ... EXEC SQL SELECT ENAME, SAL, COMM INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND FROM EMP WHERE EMPNO = :EMP_NUMBE END-EXEC. ...
The embedded SELECT statement is only slightly different from an interactive (SQL*Plus) version. Every embedded SQL statement begins with EXEC SQL. The colon, ":", precedes every host (COBOL) variable. The SQL statement is terminated by END-EXEC. The returned values of data and indicators (set when the data value is NULL or character columns have been truncated) can be stored in group items (such as in the above code fragment), in tables, or in tables of group items. Multiple result set values are handled very simply in a manner that resembles the case shown, where there is only one result, given the unique employee number. You use the actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or you can enter values which give you control over the use of resources, how errors are reported, the formatting of output, and how cursors (which correspond to a particular connection, a SQL statement, etc.) are managed.
Enter the options either in a configuration file, on the command line, or inline inside your source code with a special statement that begins with EXEC ORACLE. If there are no errors found, you can then compile, link, and execute the output source file, like any other COBOL program that you write.
Use the precompiler to create server database access from clients that can be on many different platforms. Pro*COBOL allows you the freedom to design your own user interfaces and to add database access to existing COBOL applications.
The embedded SQL statements available conform to an ANSI standard, so that you can access data from many databases in a program, including remote servers, networked through Net8.
Before writing your embedded SQL statements, you may want to test interactive versions of the SQL in SQL*Plus. You then make only minor changes to start testing your embedded SQL application.
The following is a short subset of the capabilities of Pro*COBOL. For complete details, see the Pro*COBOL Precompiler Programmer's Guide.
You can call stored PL/SQL or Java subprograms. You can improve performance by embedding PL/SQL blocks. These blocks can call PL/SQL functions or procedures written by you or provided in Oracle packages.
Precompiler options allow you to define how cursors, errors, syntax-checking, file formats, etc., are handled.
Using precompiler options, you can check the syntax and semantics of your SQL or PL/SQL statements during precompilation, as well as at runtime.
You can conditionally precompile sections of your code so that they can execute in different environments.
Use tables, or group items, or tables of group items as host and indicator variables in your code to improve performance.
You can program how errors and warnings are handled, so that data integrity is guaranteed.
Pro*COBOL supports dynamic SQL, a technique that allows users to input variable values and statement syntax.
Large Objects (LOBs, CLOBs, NCLOBs, and external files known as BFILEs) are accessed by another set of SQL statements.
A new ANSI SQL standard for dynamic SQL is supported for new applications, so that you can execute SQL statements with a varying number of host variables. An older technique for dynamic SQL is still usable by pre-existing applications.
Pro*COBOL has many features that are compatible with DB2, for easier migration.
JDBC (Java Database Connectivity) is an API (Applications Programming Interface) which allows Java to send SQL statements to an object-relational database such as Oracle8i.
The JDBC standard defines four types of JDBC drivers:
JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92 Entry Level standard.
Use JDBC to do dynamic SQL. Dynamic SQL means that the embedded SQL statement to be executed is not known before the application is run, and requires input to build the statement.
The drivers that are implemented by Oracle have extensions to the capabilities in the JDBC standard that was defined by Sun Microsystems. Oracle's implementations of JDBC drivers are described next:
The JDBC Thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to connect directly to a database server. It has its own implementation of a TTC, a lightweight implementation of a TCP/IP version of Oracle's Net8. It is written entirely in Java and is therefore platform-independent.
The Thin driver does not require Oracle software on the client side. It does need a TCP/IP listener on the server side. Use this driver in Java applets that are downloaded into a Web browser. The Thin driver is self-contained, but it opens a Java socket, and thus can only run in a browser that supports sockets.
The OCI driver is a Type 2 JDBC driver. It makes calls to the OCI (Oracle Call Interface) which is written in C, to interact with an Oracle database server, thus using native and Java methods.
Because it uses native methods (a combination of Java and C) the OCI driver is platform-specific. It requires a client Oracle8i installation including Net8, OCI libraries, CORE libraries, and all other dependent files. The OCI driver usually executes faster than the thin driver.
The OCI driver is not appropriate for Java applets, because it uses a C library that is platform-specific and cannot be downloaded into a Web browser. It is usable in the Oracle Web Application Server which is a collection of middleware services and tools that supports access from and to applications from browsers and CORBA (Common Object Request Broker Architecture) clients.
The JDBC server driver is a Type 2 driver that runs inside the database server and therefore reduces the number of round-trips needed to access large amounts of data. The driver, the Java server VM, the database, the NCOMP native compiler which speeds execution by as much as 10 times, and the SQL engine all run within the same address space.
This driver provides server-side support for any Java program used in the database: SQLJ stored procedures, functions, and triggers, Java stored procedures, CORBA objects, and EJB (Enterprise Java Beans). You can also call PL/SQL stored procedures, functions, and triggers.
The server driver fully supports the same features and extensions as the client-side drivers.
Among the Oracle extensions to the JDBC 1.22 standard are:
The following source code registers an Oracle JDBC Thin driver, connects to the database, creates a Statement object, executes a query, and processes the result set.
The SELECT statement retrieves and lists the contents of the ENAME column of the EMP table.
import java.sql.*
import java.math.*
import java.io.*
import java.awt.*
class JdbcTest {
public static void main (String args []) throws SQLException {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.dnlddriver.OracleDriver());
// Connect to the local database
Connection conn =
DriverManager.getConnection ("jdbc:oracle:dnldthin:@myhost:1521:orcl",
"scott", "tiger");
// Query the employee names
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("SELECT ENAME FROM EMP");
// Print the name out
while (rset.next ())
System.out.println (rset.getString (1));
// Close the result set, statement, and the connection
rset.close();
stmt.close();
conn.close();
}
}
An Oracle extension to the JDBC drivers is a form of the getConnection() method that uses a Properties object. The Properties object lets you specify user, password, and database information as well as row prefetching and execution batching.
To use the OCI driver in this code, replace the Connection statement with:
Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString", "scott", "tiger");
where MyHostString is an entry in the TNSNAMES.ORA file.
If you are creating an applet, the getConnection() and registerDriver() strings will be different.
The Oracle Database Server stores Java classes as well as PL/SQL subprograms. Except for GUI methods, any Java method can run in the RDBMS as a stored procedure. The following database constructs are supported:
You write these named blocks and then define them using the loadjava, SQL CREATE FUNCTION, CREATE PROCEDURE, and/or CREATE PACKAGE statements. These Java methods can accept arguments and are callable from:
CALL statements.
CALL statements.
INSERT, UPDATE, DELETE, and SELECT).
A database trigger is a stored procedure that Oracle invokes ("fires") automatically whenever a given DML operation modifies the trigger's table or schema. Triggers allow you to enforce business rules, prevent invalid values from being stored, and take many other actions without the need for you to explicitly call them.
JDBC code and SQLJ code (see "Oracle SQLJ") interoperates, allowing dynamic SQL statements in JDBC to be used with static SQL statements in SQLJ. A SQLJ iterator class corresponds to the JDBC result set. For more information on JDBC, see Oracle8i JDBC Developer's Guide and Reference.
SQLJ is:
The Oracle software tool SQLJ has two parts: a translator and a runtime. You execute on any Java VM with a JDBC driver and a SQLJ runtime library.
A SQLJ source file is a Java source file containing embedded static SQL statements. The SQLJ translator is 100% Pure Java and is portable to any standard JDK 1.1 or higher VM.
The Oracle8i SQLJ implementation runs in three steps:
Oracle8i supports SQLJ stored procedures, functions, and triggers which execute in a Java VM integrated with the data server. SQLJ is integrated with Oracle's JDeveloper. Source-level debugging support is available in JDeveloper.
Here is an example of the simplest SQLJ executable statement, which returns one value because empno is unique in the emp table:
String name; #sql { SELECT ename INTO :name FROM emp WHERE empno=67890 }; System.out.println("Name is " + name + ", employee number = " + empno);
Each host variable (or qualified name or complex Java host expression) is preceded by a colon (:). Other SQLJ statements are declarative (declares Java types) and allow you to declare an iterator (a construct related to a database cursor) for queries that retrieve many values:
#sql iterator EmpIter (String EmpNam, int EmpNumb);
The primary goal is to provide simple extensions to Java to allow rapid development and easy maintenance of Java applications that use embedded SQL to interact with databases.
Specific goals in support of the primary goal are:
JDBC provides a complete dynamic SQL interface from Java to databases. SQLJ fills a complementary role.
JDBC provides fine-grained control of the execution of dynamic SQL from Java, while SQLJ provides a higher level static binding to SQL operations in a specific database schema. Here are some differences:
prepStmt.prepareCall("{call fun(?,?)}"); //stored procedure SQL92 prepStmt.prepareCall("{? = call fun(?,?)}"); //stored function SQL92 prepStmt.prepareCall("begin fun(:1,:2);end;"); //stored procedure OracleprepStmt.prepareCall("begin :1 := fun(:2,:3);end;");//stored func Oracle
SQLJ provides simplified notations:
#sql {call fun(param_list) }; //Stored procedure // Declare x ... #sql x = {VALUES(fun(param_list)) }; // Stored function // where VALUES is the SQL construct
Here are similarities:
Here is a simple use of user-defined objects and object refs taken from Oracle8i SQLJ Developer's Guide and Reference , where more information on SQLJ is available:
The following items are created using the SQL script below:
PERSON and ADDRESS
PERSON objects
EMPLOYEE table that includes an ADDRESS column and two columns of PERSON references
SET ECHO ON; / /*** Clean up in preparation ***/ DROP TABLE EMPLOYEES / DROP TABLE PERSONS / DROP TYPE PERSON FORCE / DROP TYPE ADDRESS FORCE / /*** Create address UDT ***/ CREATE TYPE address AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / /*** Create person UDT containing an embedded address UDT ***/ CREATE TYPE person AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr address ) / /*** Create a typed table for person objects ***/ CREATE TABLE persons OF person / /*** Create a relational table with two columns that are REFs to person objects, as well as a column which is an Address ADT. ***/ CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF person, manager REF person, office_addr address, salary NUMBER ) /*** Insert some data--2 objects into the persons typed table ***/ INSERT INTO persons VALUES ( person('Wolfgang Amadeus Mozart', 123456, address('Am Berg 100', 'Salzburg', 'AT','10424'))) / INSERT INTO persons VALUES ( person('Ludwig van Beethoven', 234567, address('Rheinallee', 'Bonn', 'DE', '69234'))) / /** Put a row in the employees table **/ INSERT INTO employees (empnumber, office_addr, salary) VALUES ( 1001, address('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'), 50000) / /** Set the manager and person REFs for the employee **/ UPDATE employees SET manager = (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart') / UPDATE employees SET person_data = (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven') / COMMIT / QUIT
Next, JPublisher is used to generate the Address class for mapping to Oracle ADDRESS objects. We omit the details.
The following SQLJ sample declares and sets an input host variable of Java type Address to update an ADDRESS object in a column of the employees table. Both before and after the update, the office address is selected into an output host variable of type Address and printed for verification.
... // Updating an object static void updateObject() { Address addr; Address new_addr; int empno = 1001; try { #sql { SELECT office_addr INTO :addr FROM employees WHERE empnumber = :empno }; System.out.println("Current office address of employee 1001:"); printAddressDetails(addr); /* Now update the street of address */ String street ="100 Oracle Parkway"; addr.setStreet(street); /* Put updated object back into the database */ try { #sql { UPDATE employees SET office_addr = :addr WHERE empnumber = :empno }; System.out.println ("Updated employee 1001 to new address at Oracle Parkway."); /* Select new address to verify update */ try { #sql { SELECT office_addr INTO :new_addr FROM employees WHERE empnumber = :empno }; System.out.println("New office address of employee 1001:"); printAddressDetails(new_addr); } catch (SQLException exn) { System.out.println("Verification SELECT failed with "+exn); } } catch (SQLException exn) { System.out.println("UPDATE failed with "+exn); } } catch (SQLException exn) { System.out.println("SELECT failed with "+exn); } } ...
Note the use of the setStreet() accessor method of the Address instance. Remember that JPublisher provides such accessor methods for all attributes in any custom Java class that it produces.
SQLJ applications can be stored and run in the server. You have the option of translating, compiling, and customizing SQLJ source on a client and loading the generated classes and resources into the server with the loadjava utility, typically using a Java archive (.jar) file.
Or, you have a second option of loading SQLJ source code into the server, also using loadjava, and having it translated and compiled by the server's embedded translator.
