Skip Headers

Oracle® Databas e Concepts
10g Release 1 (10.1)
Part Number B10743-01
Go to Documentation Home
Home		 Go to Book List Book List Go to Table of Contents
Contents		 Go to Index
Index 		Go to Master Ind ex
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
< a href="../b10743.pdf">View PDF
< !-- End Header -->

24 SQL, PL/SQL, and Java

This chapter provides an overview of SQL, PL/SQL, and Java.

This chapter contains the following topics:

Overview of SQL

SQL is a database access, nonprocedural language. Users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.

IBM Research developed and defined SQL, and ANSI/ISO has refined SQL as the standard language for relatio nal database management systems.The minimal conformance level for SQL-99 is known as Core. Core SQL-99 is a superset of SQL-92 Entry Level specification. Oracle Database is broadly compatible with the SQL-99 Core specification.

Oracle SQL includes many extensions to th e ANSI/ISO standard SQL language, and Oracle tools and applications provide additional statements. The Oracle tools SQL*Plus and Orac le Enterprise Manager let you run any ANSI/ISO standard SQL statement against an Oracle database, as well as additional statements or functions that are available for those tools.

Although some Oracle tools and applications simplify or mask SQL use, all datab ase operations are performed using SQL. Any other data access method circumvents the security built into Oracle and potentially compr omise data security and integrity.



SQL Statements

All operations performed on the information in an Oracle database are run using SQL statements. A statement consists partially of SQL reser ved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

A SQL statement is a computer program or instruction . The statement must be the equivalent of a complete SQL sentence, as in:

SELECT last_name, department_
id FROM employees;

Only a complete SQL statement can be run. A fragment such as the following generates an error indicati ng that more text is required before a SQL statement can run:

SELECT last_name

Oracle SQL statements are divided into the following categorie s:

See Also:

See Also:

Chapter 22, " Triggers " for more inf ormation about using SQL statements in PL/SQL program units

Data Manipulation Language Statements

Data manipulation language (DML) statements query or man ipulate data in existing schema objects. They enable you to:

  • Re trieve data from one or more tables or views (SELECT); fetches can be scrollable (see "Scrollable Curs ors")

  • Add new rows of data into a table or view (INSE RT)

  • Change column values in existing rows of a table or view (UPDATE)

  • Update or insert rows conditionally into a table or view (MERGE)

  • Remove rows from tabl es or views (DELETE)

  • See the execution plan for a SQL statement (EXPLAIN PLAN)

  • Lock a table or view, temporarily limiting other users' acc ess (LOCK TABLE)

DML statements are the most frequently used SQL statements. Some examples of DML statements are:

SELECT last_name, manager_id, commission_pct + salary FROM employees; 

INSERT
INTO employees VALUES 
    (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); 

DELETE FROM employees WHERE last_name I
N ('WARD','JONES');

Data Definition Language Statements

< p>Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:

  • Create, alter, and drop schema obj ects and other database structures, including the database itself and database users (CREATE, ALTER, DROP)

  • Change the names of schema objects (RENAME)

  • Delete all the data in schema objects without removing t he objects' structure (TRUNCATE)

  • Grant and revoke privileges and roles (GRANT, REVOKE)

  • Turn auditing options on and off (AUDIT, NOAUDIT)

  • Add a comment to the data dictionary ( COMMENT)

DDL statements implicitly commit the preceding and start a new transaction. Some examples of D DL statements are:

CREATE TABLE plants  
    (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); 

D
ROP TABLE plants; 

GRANT SELECT ON employees TO scott; 

REVOKE DELETE ON employees FROM scott;

Transaction Control Statements

Transaction control statements manage the changes made by DML statemen ts and group DML statements into transactions. They enable you to:

Session Control Statements

Session control statements manage the properties of a particular u ser's session. For example, they enable you to:

  • Alter the curre nt session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESS ION)

  • Enable and disable roles (groups of privileges) for the current session (SET ROLE)

System Control Statements

System control s tatements change the properties of the Oracle database server instance. The only system cont rol statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared serv ers), kill a session, and perform other tasks.

Embedded SQL Statements

Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statem ents enable you to:

  • Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE)

  • Specify a database and connect to Oracle (DECLARE DATABASE, CONNECT)

    • Assign variable names (DECLARE STATEMENT)

  • Initialize descriptors (DESCRIBE)

  • Specify how error and warning conditions are handled (WHENEVER)

  • Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE)

  • Retrieve data from the database (FETCH)

Cursors

A cursor is a handle or name for a private SQL area—an area in memory in which a parsed statement and other informatio n for processing the statement are kept.

Although most Oracle users rely on the automatic cursor handling of the Oracle utilit ies, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a nam ed resource available to a program and can be used specifically to parse SQL statements embedded within the application.

Each user session can open multiple cursors up to the limit set by the initialization para meter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initializati on parameter.

Some statements (primarily DDL statements) require Oracle to implicitly issue rec ursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE s tatement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can run several recursive calls. These recursive cursors also use shared SQL areas.

Scrollable Cursors

Execution o f a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or nonsequential ly. Scrollable cursors are cursors in which fetches and DML operations do not need to be forward sequential only. In terfaces exist to fetch previously fetched rows, to fetch the nth row in the result set, and to fetch the nth row from the c urrent position in the result set.


See Also:

Oracle Call Interface Programmer's Gui de for more information about using scrollable cursors in OCI

Shared SQL

Oracle automatically notices when applications send similar SQL statements to the database. The SQL area used t o process the first occurrence of the statement is shared—that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memor y areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory use on the database server, thereby in creasing system throughput.

In evaluating whether statements are similar or identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.


< td align="left">

See Also:

Oracle Database Application Developer's Guide - Fundamentals and Oracle Database Performance Tuning Guide for more information about shared SQL

Pa rsing

Parsing is one stage in the processing of a SQL statem ent. When an application issues a SQL statement, the application makes a parse call to Oracl e. During the parse call, Oracle:

  • Checks the statement for syntactic and semantic validity

  • Determines whether the process issuing the statement has privileges to run it

  • Allocates a private SQL area for the statement

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and runs the statement immediately. If not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.

Note the difference between an application making a parse call for a SQL statement an d Oracle actually parsing the statement. A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be run repeatedly without your applic ation making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a share d SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.

Both parse calls and parsing c an be expensive relative to execution, so perform them as seldom as possible.


SQL Processing

This sect ion introduces the basics of SQL processing. Topics include:

SQ L Statement Execution

Figure 24-1 outlines the stages commonly used to process and run a SQL statement. In some cases, Oracle can run these stages i n a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depend ing on how you wrote your code.

For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, this information could be useful when writing Oracle applications.

< div class="figure">

Figure 24-1 The Stages in Processing a SQL Statement

Descrip tion of cncpt105.gif follows
Description of the illustrat ion cncpt105.gif

DML Statement P rocessing

This section provides an example of what happens during the execution of a SQL statement in each stage of DML statemen t processing.

Assume that you are using a Pro*C program to increase the salary for al l employees in a department. The program you are using has connected to Oracle and you are connected to the proper schema to update t he employees table. You can embed the following SQL statement in your program:

EXEC SQL UP
DATE employees SET salary = 1.10 * salary 
    WHERE department_id = :department_id;

Department_id is a pro gram variable containing a value for department number. When the SQL statement is run, the value of department_id is use d, as provided by the application program.

The following stages are necessary for each type of statement processing:

Option ally, you can include another stage:

See Also:

"Overview of PL/SQL"
< /table>
Stage 1: Create a Cursor

A program interface call creates a cursor. The cursor is created independent of any SQL statement: it is cre ated in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, curso r creation can either occur implicitly or be explicitly declared.

< div class="sect4">
Stage 2: P arse the Statement

During parsing, the SQL statement is passed from the user process to Oracle, and a parsed representation of t he SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.

Parsin g is the process of:

  • Translating a SQL statement, verifying it to be a valid statement

  • Performing data dictionary lookups to check table and column definitions

  • Acquiring parse locks on requ ired objects so that their definitions do not change during the statement's parsing (however, parse locks can be broken to allow conf licting DDL operations)

  • Checking privileges to access reference d schema objects

  • Deter mining the optimal execution plan for the statement

  • Loading it into a shared SQL area

  • Routing all or part of distributed statements to remote nodes that contain referenced data

Oracle parses a SQL stat ement only if a shared SQL area for an similar SQL statement does not exist in the shared pool. In this case, a new shared SQL area i s allocated, and the statement is parsed.

The parse stage includes processing requirements that need to be done only once no m atter how many times the statement is run. Oracle translates each SQL statement only once, reexecuting that parsed statement during s ubsequent references to the statement.

Although parsing a SQL statement validates that statement, parsing only identifies erro rs that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situatio ns that can be encountered and reported only during the execution stage.


See Also:

"Query Processing"

See Also:

"Shared SQL "

Query Processing

Queries are different from other types of SQL statements because, if successful, they return data as result s. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.

Several issues relate only to query processing. Queries include not only explicit SELECT stat ements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following state ments requires a query as a part of its execution:

INSERT INTO table SELECT... 

UPDATE table SET x = y
 WHERE... 

DELETE FROM table WHERE... 

CREATE table AS SELECT...

In particular, queries:

  • Require read consistency

  • Can use temporary segments for intermediate processing

  • Can require the describe, def ine, and fetch stages of SQL statement processing.

Stage 3: Descri be Results of a Query

The describe s tage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively b y a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query

In the define stage for queries, you specify the location, size, and datatype of var iables defined to receive each fetched value. Oracle performs datatype conversion if necessary.

Stage 5: Bind Any Variables

At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to run the statement. Oracle needs values for any variables listed in the statement; in the example , Oracle needs a value for department_id. The process of obtaining these values is called binding variables.

A program must specify the location (memory address) where the value can be found. End users of applications ma y be unaware that they are specifying bind variables, because the Oracle utility can simply prompt them for a new value.

Becau se you specify the location (binding by reference), you need not rebind the variable before reexecution. You can change its value and Oracle looks up the value on each execution, using the memory address.

You must also specify a datatype and length for each value (unle ss they are implied or defaulted) if Oracle needs to perform datatype conversion.


See Also:

for more information about specifying a datatype and length for a value


Stage 6: Parallelize the S tatement

Oracle can parallelize queries (SELECTs, INSERTs, UPDATEs, MERGE s, DELETEs), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.

Stage 7: Run the Statement

At this point, Oracle has all necessary information and resources, so the statement is run. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE o r DELETE statement, however, all rows that the statement affects are locked from use by other users of the database unti l the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.

For some statements you can specify a number o f executions to be performed. This is called array processing. Given n number of executions, the bind and d efine locations are assumed to be the beginning of an array of size n.

Stage 8: Fetch Rows of a Query

In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetche d.

Stage 9: Close the Cursor

The final stage of processing a SQL statement is closing the cursor.

DDL State ment Processing

The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.

< p>Transaction management, session management, and system management SQL statements are processed using the parse and run stages. To r erun them, simply perform another execute.

Control of Transactions

In g eneral, only application designers using the programming interfaces to Oracle are concerned with the types of actions that should be grouped together as one transaction. Transactions must be defined so that work is accomplished in logical units and data is kept cons istent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less.

  • Data in all referenced tables should be in a consistent state before the trans action begins and after it ends.

  • Transactions should consist of only the SQL statements that make one co nsistent change to the data.

For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both acti ons should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated a ctions, such as a new deposit to one account, should not be included in the transfer of funds transaction.

< h3>Overview of the Optimizer

All SQL statements use the optimizer, a part of Oracle that determine s the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer pe rform its job better.

There are often many different ways to process a SQL DML (SELECT, INSERT, UPDATE, MERGE, or DELETE) statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle uses to run a statement can greatly affect how quickly the statement runs. The optimizer considers many factors among alternative access paths.


Note:

The optimizer might not make the same decisions from one version of Oracle to the next. In recent versions , the optimizer might make decisions based on better information available to it.

You can influence the optimizer's choices by setting the optimizer approach and goal. Objects with stale or no statistics are automatically analyzed. You can also gather statistics for the optimizer using the PL/SQL package DBMS_STATS< /code>.

Sometimes the application designer, who has more information about a particular application's data than is available t o the optimizer, can choose a more effective way to run a SQL statement. The application designer can use hints in SQL statements to specify how the statement should be run.


See Also:


< /a>Execution Plans

To run a DML statement, Oracle might need to perform many steps. Each of these steps either retriev es rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to run a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.

Stored Outlines

Stored outlines are abstractions of an e xecution plan generated by the optimizer at the time the outline was created and are represented primarily as a set of hints. When th e outline is subsequently used, these hints are applied at various stages of compilation. Outline data is stored in the OUTLN schema. You can tune execution plans by editing stored outlines.

< /a>Editing Stored Outlines

The outline is cloned into the user's schema at th e onset of the outline editing session. All subsequent editing operations are performed on that clone until the user is satisfied wit h the edits and chooses to publicize them. In this way, any editing done by the user does not impact the rest of the user community, which would continue to use the public version of the outline until the edits are explicitly saved.


See Also:

Oracle Database Performance Tuning Guide for details about execution plans and using stored outli nes

Overview of Procedural Languages< /h2>

In Oracle, SQL, PL/SQL, XML, and Java all interoperate seamlessly in a way that allows developers to mix-and-match the most r elevant features of each language. SQL and PL/SQL form the core of Oracle's application development stack. Not only do most enterpris e back-ends run SQL, but Web applications accessing databases do so using SQL (wrappered by Java classes as JDBC), Enterprise Applica tion Integration applications generate XML from SQL queries, and content-repositories are built on top of SQL tables. It is a simple, widely understood, unified data model. It is used standalone in many applications, but it is also invoked indirectly from Java (JDBC ), Oracle Call Interface (dynamic SQL), and XML (XML SQL Utility).

This section includes the following:

Overview of PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural la nguage that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Orac le Developer, providing developers with a consistent and leveraged development model on both the client and the server side. In addit ion, PL/SQL stored procedures can be called from a number of Oracle clients, such as Pro*C or Oracle Call Interface, and from Oracle Reports and Oracle Forms.

PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can define and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous bl ocks and stored procedures.

An anonymous block is a PL/SQL block that appears in your application and is not named or s tored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.

A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. O racle also lets you create and store functions (which are similar to procedures) and packages (which are groups of procedures and fun ctions).



How PL/SQL Runs

PL/SQL can run with either interpreted execution or na tive execution.

Interpreted Execution

In versions earlier than Oracle9i, PL/SQL source code was always compiled in to a so-called bytecode representation, which is run by a portable virtual machine implemented as part of the Oracle database server, and also in products such as Oracle Forms. Starting with Oracle9i, you can choose between native execution and interpreted execution

Native Execution

For best performance on computationally intensive program units, compile the source code of PL/SQL program units stored in t he database directly to object code for the given platform. (This object code is linked into the Oracle database server.)


See Also:

"Overview of Java"

Chapter 22, " Triggers "
< tbody>

See Also:

PL/SQL User's Guide and Reference

The PL/SQL engine is the tool you use to define, compile, and run PL/SQL program units. This engine is a special component of ma ny Oracle products, including the Oracle database server.

While many Oracle products have PL/SQL components, this section specifically covers the program units that can be stored in an Oracle database and processed usi ng the Oracle database server PL/SQL engine. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool's docu mentation.

Figure 24-2 illustrates the PL/SQL engine contained in Oracle database server.

Figure 24-2 The PL/SQL Engine and the Oracle Database Server

Descripti on of cncpt070.gif follows
Description of the illustratio n cncpt070.gif

The program unit is stored in a datab ase. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.

< p>The following Oracle products contain a PL/SQL engine:

  • Oracle database server

  • Oracle Forms (version 3 and later)

    • SQL*Menu (version 5 and later)

  • Oracle Reports (version 2 and later)

  • Oracle Graphics (version 2 and later)

You can call a stored proc edure from another PL/SQL block, which can be either an anonymous block or another stored procedure. For example, you can call a stor ed procedure from Oracle Forms (version 3 or later).

Also, you can pass anonymous blo cks to Oracle from applications developed with these tools:

  • Oracle precompilers (including user exits)

  • Oracle Call Interfaces (OCIs)

  • SQL*Plus

  • Oracle Enterprise Manager

Language Constructs for PL/SQL

PL/SQL blocks can include the following PL/SQL language constructs:

  • Variables and constants

  • Cursors< /p>

  • Exceptions

This section gives a general description of each construct.

Variables and Constants

Variables and constants can be declared within a procedure, function, or package. A variable or constant can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed.

Some interac tive tools, such as SQL*Plus, let you define variables in your current session. You can use such variables just as you would variables declared within procedures or packages.

Cursors

Cursors can be declared explicit ly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared imp licitly (to support other data manipulation actions) by the PL/SQL engine.

Exceptions

PL/SQL lets you explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as division by zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negativ e balance).

When an exception is raised, the execution of the PL/SQL code stops, and a routine called an exception handler is invoked. Specific exception handlers can be written for any internal or user-defined excepti on.

< h5>Dynamic SQL in PL/SQL

PL/S QL can run dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general purpose procedure s. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL in two ways:< /p>

  • By embedding dynamic SQL statements in the PL/SQL block

  • By using the DBMS_SQL package

Additionally, you can issue DML or DDL statements using dynamic SQL. This helps solve the problem of not being able to sta tically embed DDL statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from wi thin a stored procedure by using the EXECUTE IMMEDIATE statement or the PARSE procedure suppli ed with the DBMS_SQL package.


See Also:


PL/SQL Program Units

Oracle lets you access and manipulate database information using procedural schema objects called PL/SQL pro gram units. Procedures, functions, and packages are all examples of PL/SQL program units.

< a id="i18617" name="i18617">

Stored Procedures and Functions

A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and out put values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structure d programming language.

Procedures and funct ions are identical except that functions always return a single value to the caller, while procedures do not. For simplicity, procedure as used in the remainder of this chapter means procedure or function.< /p>

You can run a procedure or function interact ively by:

  • Using an Oracle tool, such as SQL*Plus

  • Calling it explicitly in t he code of a database application, such as an Oracle Forms or precompiler application

  • Calling it explici tly in the code of another procedure or trigger


    < td align="left">

    See Also:

    • Pro*C/C++ Programmer's Guide for information about how to call stored C or C++ procedures

    • Pro*COBO L Programmer's Guide for information about how to call stored COBOL procedures

    • Other programmer 's guides for information about how to call stored procedures of specific kinds of application


Figure 24-3 illustrates a simple procedure that is stored in the database and called by several different database applications.

Figure 24-3 Stored Procedure

Description of cncpt073.gif follows
Description of the illustration cncpt073.gif

The s tored procedure in Figure 24-3, which inserts an employee record into the employees table, is show n in Figure 24-4.

Figure 24-4 Stored Procedure Example

Description of cncpt074.gif follows
Description of the illustration cncpt074.gif

All of the database applications in Figure 24-3 call the h ire_employees procedure. Alternatively, a privileged user can use Oracle Enterprise Manager or SQL*Plus to run the hire_ employees procedure using the following statement:

EXECUTE hire_employees ('TSMITH', 'CLERK', 10
37, SYSDATE, 500, NULL, 20);

This statement places a new employee record for TSMITH in the employees< /code> table.

Benefits of Procedures

St ored procedures provide advantages in the following areas:

  • Security w ith definer's rights procedures

    Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions th at run with the definer's privileges.

    For example, you can grant users access to a procedure that updates a table but not gran t them access to the table itself. When a user invokes the procedure, the procedure runs with the privileges of the procedure's owner . Users who have only the privilege to run the procedure (but not the privileges to query, update, or delete from the underlying tabl es) can invoke the procedure, but they cannot manipulate table data in any other way.


    < /tbody>

    See Also:

    "Dependency Tracking for Stored Procedures "

  • Inherited pri vileges and schema context with invoker's rights procedures

    An invoker's rights procedure inherits privileges and schema conte xt from the procedure that calls it. In other words, an invoker's rights procedure is not tied to a particular user or schema, and ea ch invocation of an invoker's rights procedure operates in the current user's schema with the current user's privileges. Invoker's ri ghts procedures make it easy for application developers to centralize application logic, even when the underlying data is divided amo ng user schemas.

    For example, a user who runs an update procedure on the employees table as a manager can update salary, whereas a user who runs the same procedure as a clerk can be restricted to updating address data.

  • Improved performance

    • The amount of information that must be sent o ver a network is small compared with issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, becau se the information is sent only once and thereafter invoked when it is used.

    • A procedure's compiled form is readily available in the database, so no compilation is required at execution time.

    • If the procedure is already present in the shared pool of the system global area (SGA), then retrieval f rom disk is not required, and execution can begin immediately.

  • Memory allocation

    Because stored procedures take advantage the shared memory capab ilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the sa me code among many users results in a substantial reduction in Oracle memory requirements for applications.

  • Improved productivity

    Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

    For example, pr ocedures can be written to insert, update, or delete employee records from the employees table. These procedures can the n be called by any application without rewriting the SQL statements necessary to accomplish these tasks. If the methods of data manag ement change, only the procedures need to be modified, not all of the applications that use the procedures.

  • Integrity

    Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors .

    For example, you can test a procedure or function to guarantee that it returns an accurate result and, once it is verified, reuse it in any number of applications without testing it again. If the data structures referenced by the procedure are altered in an y way, then only the procedure needs to be recompiled. Applications that call the procedure do not necessarily require any modificati ons.

Procedure Guidelines

Use the following guidelines when designing stored procedu res:

  • Define procedures to complete a single, focused task. Do not define long procedures with several di stinct subtasks, because subtasks common to many procedures can be duplicated unnecessarily in the code of several procedures.

  • Do not define procedures that duplicate the functionality already provided by other features of Oracle. For ex ample, do not define procedures to enforce simple data integrity rules that you could easily enforce using declarative integrity cons traints.

Anonymous PL/SQL Blocks Compared with Stored Procedures

A stored procedure is created and stored in the database as a schema object. Once created and compiled, it is a named object that c an be run without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of eac h stored procedure.

As an alternative to a stored procedure, you can create an anonym ous PL/SQL block by sending an unnamed PL/SQL block to the Oracle database server from an Oracle tool or an application. Oracle compi les the PL/SQL block and places the compiled version in the shared pool of the SGA, but it does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be re used and shared until they are flushed out of the shared pool.

In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnec essary procedure recompilations by Oracle at runtime, improving the overall performance of the application and Oracle.

Standalone Procedures

Stored procedures not defined within the context of a package are called stand alone procedures. Procedures defined within a package are considered a part of the package.


See Also:

"PL/SQL Packages " for information about the advantages of packages

< !-- class="sect4" -->
Dependency Tracking for Stored Procedures

A stored procedure depends o n the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the defini tion of a table referenced by a procedure, then the procedure must be recompiled to validate that it will still work as designed. Usu ally, Oracle automatically administers such dependency management.


See Also:

Chapter 6, " Dependencies Among Schema Objects " for more information about dependency tracking

External Procedures

A PL/SQL procedure executing on an Oracle database server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine runs in a separate address space from that of the Oracle database server.


See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about ext ernal procedures

Table Functions

Table functions are function s that can produce a set of rows as output. In other words, table functions return a collection type instance (nested table and VARRAY datatypes). You can use a table function in place of a regular table in the FROM clause of a SQL statemen t.

Oracle allows table functions to pipeline results (act like an Oracle rowsource) out of the functions. This can be achieve d by either providing an implementation of the ODCITable interface, or using native PL/SQL instructions.

Pipelini ng helps to improve the performance of a number of applications, such as Oracle Warehouse Builder (OWB) and cartridges groups.

The ETL (Extraction-Transformation-Load) process in data warehouse building extracts data from an OLTP system. The extracted data pa sses through a sequence of transformations (written in procedural languages, such as PL/SQL) before it is loaded into a data warehous e.

Oracle also allows parallel execution of table and non-table functions. Parallel execution provides the following extensions:

  • Functions can directly accept a set of rows corresponding to a subquery operand.

  • A set o f input rows can be partitioned among multiple instances of a parallel function. The function developer specifies how the input rows should be partitioned between parallel instances of the function.

Thus, table functions are similar to views. Howeve r, instead of defining the transform declaratively in SQL, you define it procedurally in PL/SQL. This is especially valuable for the arbitrarily complex transformations typically required in ETL.

< div class="sect3">

PL/SQL Pac kages

A package is a group of related procedures and functions, along with the cursors and variables they use, stored together in the database for conti nued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by app lications or users.

Oracle supplies many PL/SQL packages with the Oracle database server to extend database functionality and provide PL/SQL access to SQL features. For example, the ULT_HTTP supplied package enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges. You can use the supplied packages when creating your appl ications or for ideas in creating your own stored procedures.

You create a package in two parts: the specification and the bod y. The package specification declares all public constructs of the package and the body defines all constructs (public and private) of the package. This separation of the two parts provides the following advantages:

  • You have more flexibility in the development cycle. You can create specifications and reference public procedures withou t actually creating the package body.

  • You can alter procedure bodies contained within the package body s eparately from their publicly declared specifications in the package specification. As long as the procedure specification does not c hange, objects that reference the altered procedures of the package are never marked invalid. That is, they are never marked as needi ng recompilation.

Figure 24-5 illustrates a package that encapsulates a number of procedures u sed to manage an employee database.

Figure 24-5 A Stored Package

Description of cncpt075.gif follows
Descripti on of the illustration cncpt075.gif

Database applications explicitly call packaged procedures as necessary. After being granted the pr ivileges for the employees_management package, a user can explicitly run any of the procedures contained in it. For exam ple, Oracle Enterprise Manager or SQL*Plus can issue the following statement to run the hire_employees package procedure :

EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20); 





 

Benefits of Packages


Packages provide advantages in the following areas:


    

  • 

    Encapsulation of rela
ted procedures and variables
    

    Stored packages allow you to encapsulate or group stored procedures, variables,
datatypes, and so on in a single named, stored unit in the database. This provides better organization during the development process
. Encapsulation of procedural constructs also makes privilege management easier. Granting the privilege to use a package makes all co
nstructs of the package accessible to the grantee.
    • 

  • 

    Declaration of public and private procedures, variabl
es, constants, and cursors
    

    The methods of pa
ckage definition allow you to specify which variables, cursors, and procedures are public and private. Public means that it is direct
ly accessible to the user of a package. Private means that it is hidden from the user of a package.
    

    For example, a package can
 contain 10 procedures. You can define the package so that only three procedures are public and therefore available for execution by
a user of the package. The remainder of the procedures are private and can only be accessed by the procedures within the package. Do
not confuse public and private package variables with grants to PUBLIC.
    


    

    


    


    See Also:
    
Chapter 20, " Database Security " for more i
nformation about grants to PUBLIC
    

    


    • 

  • 

    Better performance
    

    An entire package is loaded into memory when a procedure
 within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required
for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to run the compiled c
ode already in memory.
    
 

    A package body can be replaced and recompiled without affecting the
specification. As a result, schema objects that reference a package's constructs (always through the specification) need not be recom
piled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in
 less impact on overall database performance.

PL/SQL Collections and Records

Many programming techniques use collection types such as arrays, bags, lists, nested tables, sets, and trees. To support these techniques in database applications, PL/SQL provides the datatypes TABLE and VARRAY, which allow you to declare index-by tables, nested tables, and variable-size arrays.

Collections

A collection is an ordered grou p of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.

Colle ctions work like the arrays found in most third-generation programming languages. 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.< /p>

Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, with a user-defined record, you can declare fields of your own.

Records co ntain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary , and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat t he data as a logical unit.


See Also:

PL/SQL User's Guide and Reference for detailed information on using collections and records

PL/SQL Server Pages

PL/SQL S erver Pages (PSP) are server-side Web pages (in HTML or XML) with embedded PL/SQL scripts marked with special tags. To produce dynami c Web pages, developers have usually written CGI programs in C or Perl that fetch data and produce the entire Web page within the sam e program. The development and maintenance of such dynamic pages is costly and time-consuming.

Scripting fulfills the demand for rapid development of dynamic Web pages. Small scripts can be embedded in HTML pages witho ut changing their basic HTML identity. The scripts contain the logic to produce the dynamic portions of HTML pages and are run when t he pages are requested by the users.

The separation of HTML content from application logic makes script pages easier to develo p, debug, and maintain. The simpler development model, along the fact that scripting languages usually demand less programming skill, enables Web page writers to develop dynamic Web pages.

There are two kinds of embedded scripts in HTML pages: client-side scr ipts and server-side scripts. Client-side scripts are returned as part of the HTML page and are run in the browser. They are mainly used for client-side navigation of HTML pages or data validation. < strong>Server-side scripts, while also embedded in the HTML pages, are run on the server side. They fetch and manipulate dat a and produce HTML content that is returned as part of the page. PSP scripts are server-side scripts.

A PL/SQL gateway receives HTTP requests from an HTTP client, invokes a PL/SQL stored procedure as specified in the U RL, and returns the HTTP output to the client. A PL/SQL Server Page is processed by a PSP compiler, which compiles the page into a PL /SQL stored procedure. When the procedure is run by the gateway, it generates the Web page with dynamic content. PSP is built on one of two existing PL/SQL gateways:

Overview of Java

Java is an object-oriented programming language efficient for application-level programs. It includes the following features:

  • A Java virtual machine (JVM), which provides the fundamental basis for platform independence

  • Automatic storage management techniques, such as gathering scattered memory into contiguous memory space

  • Language syntax that borrows from C and enforces strong typing

This section contains the following topics:

Java and Object-Oriented Progr amming Terminology

This section covers some basic terminology of Java application development in the Oracle environmen t.

Cl asses

All object-oriented programming languages support the concept of a clas s. As with a table definition, a class provides a template for objects that share common characteristics. Each class can contain the following:

  • Attributes—static or instance variables that each object of a pa rticular class possesses

  • Methods—you can invoke methods defined by the clas s or inherited by any classes extended from the class

When you create an object from a class, you are creating an in stance of that class. The instance contains the fields of an object, which are known as its data, or state.

Figure 24-6 shows an example of an Employee class defined with two attributes: last name (lastName) and employee identifier (ID).

Figure 24-6 Classes and Instances

Description of jjdev001.gif follows
Description of the illustration jjdev001.gif

When you create an instance, the attri butes store individual and private information relevant only to the employee. That is, the information contained within an employee i nstance is known only for that single employee. The example in Figure 24-6 shows two instances of employee 14;Smith and Jones. Each instance contains information relevant to the individual employee.

Attributes

Attributes within an instance are known as field s. Instance fields are analogous to the fields of a relational table row. The class defines the fields, as well as the type of each f ield. You can declare fields in Java to be static, public, private, protected, or default access.

  • Public , private, protected, or default access fields are created within each instance.

  • Static fields are like global variables in that the information is available to all instances of the employee class.

The language specifica tion defines the rules of visibility of data for all fields. Rules of visibility define under what circumstances you can access the d ata in these fields.

Methods

The class also defines the methods you can invoke on an instance of that class. Methods are written in Java and define the behavior of an object. This bundling of state and behavior is the essence of encapsulation, which is a feature of all object-oriente d programming languages. If you define an Employee class, declaring that each employee's id is a private fi eld, other objects can access that private field only if a method returns the field. In this example, an object could retrieve the em ployee's identifier by invoking the Employee.getId() method.

In addition, with encapsulation, you ca n declare that the Employee.getId()method is private, or you can decide not to write an Employee.getId() method. Encapsulation helps you write programs that are reusable and not misused. Encapsulation makes public only those features of an object that are declared public; all other fields and methods are private. Private fields and methods can b e used for internal object processing.

Class Hierarchy

Java defines classes within a large hierarchy of classes. At the t op of the hierarchy is the Object class. All classes in Java inherit from the Object class at some level, as you walk up through the inheritance chain of superclasses. When we say Class B inherits from Class A, each instance of Class B contains all the fields defined in class B, as well as all the fields defined in Class A. For example, in Figure 24-7, the FullTimeEmployee class contains the id and lastName fields defined in the Employee cla ss, because it inherits from the Employee class. In addition, the FullTimeEmployee class adds another field, bonus , which is contained only within FullTimeEmployee.

You can invoke any method on an instance of Class B tha t was defined in either Class A or B. In our employee example, the FullTimeEmployee instance can invoke methods defined only within its own class, or methods defined within the Employee class.

Figure 24-7 Using Inher itance to Localize Behavior and State

Description of jjdev002.gif follows
Description of the illustration jjdev002.gif

Instances of Class B are substitutable for instances of Class A, which makes inheritance another p owerful construct of object-oriented languages for improving code reuse. You can create new classes that define behavior and state wh ere it makes sense in the hierarchy, yet make use of pre-existing functionality in class libraries.

Interfaces

Java supports only single inheritance; t hat is, each class has one and only one class from which it inherits. If you must inherit from more than one source, Java provides th e equivalent of multiple inheritance, without the complications and confusion that usually accompany it, through interfaces. Interfac es are similar to classes; however, interfaces define method signatures, not implementations. The methods are implemented in classes declared to implement an interface. Multiple inheritance occurs when a single class simultaneously supports many interfaces.

Polymorphism

Assume in our Employee example that the different types of employees must be able to respond with their compensation to date. Compens ation is computed differently for different kinds of employees.

  • FullTimeEmployees are eligi ble for a bonus

  • NonExemptEmployees get overtime pay

In traditional procedu ral languages, you would write a long switch statement, with the different possible cases defined.

swit
ch (employee.type) {
case: Employee
return employee.salaryToDate;
case: FullTimeEmployee
return employee.salaryToDate + employee.bonu
sToDate;
...

If you add a new kind of employee, then you must update your switch statement. If you modify your data struc ture, then you must modify all switch statements that use it.

In an object-oriented language such as Java, you implement a met hod, compensationToDate(), for each subclass of Employee class that requires any special treatment beyond w hat is already defined in Employee class. For example, you could implement the compensationToDate() method of NonExemptEmployee, as follows:

private float compensationToDate() {
return super.compen
sationToDate() + this.overtimeToDate();
}

Implement FullTimeEmployee's method as follows:

private float compensationToDate() {
return super.compensationToDate() + this.bonusToDate();
}

The common usa ge of the method name compensationToDate() lets you invoke the identical method on different classes and receive differe nt results, without knowing the type of employee you are using. You do not have to write a special method to handle FullTimeEmp loyees and PartTimeEmployees. Thisability for the different objects to respond to the identical message in differ ent ways is known as polymorphism.

In addition, you could create an entirely new class that does not inherit from Employ ee at all—Contractor—and implement a compensationToDate() method in it. A program that calculates total payroll to date would iterate over all people on payroll, regardless of whether they were full-time, part-time, or contractors, and add up the values returned from invoking the compensationToDate() method on each. You can safely make c hanges to the individual compensationToDate() methods with the knowledge that callers of the methods will work correctly . For example, you can safely add new fields to existing classes.

Overview of the Java Virtual Machine (JVM)

As with other high-level computer language s, Java source compiles to low-level machine instructions. In Java, these instructions are known as bytecodes (because their size is uniformly one byte of storage). Most other languages—such as C—compile to machine-specific instructions, such as instru ctions specific to an Intel or HP processor. Java source compiles to a standard, platform-independent set of bytecodes, which interac ts with a Java virtual machine (JVM). The JVM is a separate program that is optimized for the specific platform on which you run your Java code.

Figure 24-8 illustrates how Java can maintain platform independence. Java source is compiled into bytecodes, which are platform independent. Each platform has installed a JVM that is specific to its operating system. The Java bytecodes from your source get interpreted through the JVM into appropriate platform dependent actions.

Figu re 24-8 Java Component Structure

Description of jjdev003.gif follows
Description of the illustration jjdev003.gif

When you develop a Java program, you use predefined core class libraries written in the Java language. The Java core class libraries are logically divided into packages that provide commonly-used functionality, such as basic language su pport (java.lang), I/O (java.io), and network access (java.net ). Together, the JVM and core class libraries provide a platform on which Java programmers can develop with the confidence tha t any hardware and operating system that supports Java will execute their program. This concept is what drives the "write once, run a nywhere" idea of Java.

Figure 24-9 illustrates how Oracle's Java applications sit on top of the Java cor e class libraries, which in turn sit on top of the JVM. Because Oracle's Java support system is located within the database, the JVM interacts with the Oracle database libraries, instead of directly with the operating system.

Figure 24-9 Java Component Structure

Description of jjdev004.gif follows
Description of the illustration jjdev004.gif

Sun Microsystems furnishes publicly available specifications for both the Java language and the JVM. The Java Langu age Specification (JLS) defines things such as syntax and semantics; the JVM specification defines the necessary low-level behavior f or the "machine" that runs the bytecodes. In addition, Sun Microsystems provides a compatibility test suite for JVM implementors to d etermine if they have complied with the specifications. This test suite is known as the Java Compatibility Kit (JCK). Oracle's JVM im plementation complies fully with JCK. Part of the overall Java strategy is that an openly specified standard, together with a simple way to verify compliance with that standard, allows vendors to offer uniform support for Java across all platforms.

Why Use Java in Oracle?

You can write and load Java applications within the database, because it is a safe language. Java prevents anyone from tampering with the operating system that the Java code resides in. Some languages, such as C, can introduce security problems within the database; Java, because of its design, is a safe language to a llow within the database.

Although Java presents many advantages to developers, providing an implementation of a JVM that supp orts Java server applications in a scalable manner is a challenge. This section discusses some of these challenges.

Multithreading

Mul tithreading support is often cited as one of the key scalability features of Java. Certainly, the Java language and class libraries m ake it simpler to write shared server applications in Java than many other languages, but it is still a daunting task in any language to write reliable, scalable shared server code.

As a database server, Oracle efficiently schedules work for thousands of user s. The Oracle JVM uses the facilities of the RDBMS server to concurrently schedule Java execution for thousands of users. Although Or acle supports Java language level threads required by the JLS and JCK, using threads within the scope of the database does not increa se scalability. Using the embedded scalability of the database eliminates the need for writing shared server Java servers. You should use the database's facilities for scheduling users by writing single-threaded Java applications. The database takes care of the sche duling between each application; thus, you achieve scalability without having to manage threads. You can still write shared server Ja va applications, but multiple Java threads does not increase your server's performance.

One difficulty multithreading imposes on Java is the interaction of threads and automated storage management, or garbage collection. The garbage collector executing in a g eneric JVM has no knowledge of which Java language threads are executing or how the underlying operating system schedules them.

< ul>

  • Non-Oracle model—A single user maps to a single Java language level thread; the same single garbage co llector manages all garbage from all users. Different techniques typically deal with allocation and collection of objects of varying lifetimes and sizes. The result in a heavily shared server application is, at best, dependent upon operating system support for nativ e threads, which can be unreliable and limited in scalability. High levels of scalability for such implementations have not been conv incingly demonstrated.

  • Oracle JVM model—Even when thousands of users connect to the server and ru n the same Java code, each user experiences it as if he is executing his own Java code on his own Java virtual machine. The responsib ility of the Oracle JVM is to make use of operating system processes and threads, using the scalable approach of the Oracle RDBMS. As a result of this approach, the JVM's garbage collector is more reliable and efficient because it never collects garbage from more th an one user at any time.

    • Automated Storage Management

    Garbage collection is a major feature of Java's automated storage management, eliminating the need for Java developers to alloca te and free memory explicitly. Consequently, this eliminates a large source of memory leaks that commonly plague C and C++ programs. There is a price for such a benefit: garbage collection contributes to the overhead of program execution speed and footprint. Althoug h many papers have been written qualifying and quantifying the trade-off, the overall cost is reasonable, considering the alternative s.

    Garbage collection imposes a challenge to the JVM developer seeking to supply a highly scalable and fast Java platform. The Oracle JVM meets these challenges in the following ways:

    • The Oracle JVM uses the Oracle scheduling faci lities, which can manage multiple users efficiently.

    • Garbage collection is performs consistently for mul tiple users because garbage collection is focused on a single user within a single session. The Oracle JVM enjoys a huge advantage be cause the burden and complexity of the memory manager's job does not increase as the number of users increases. The memory manager pe rforms the allocation and collection of objects within a single session—which typically translates to the activity of a single user.

    • The Oracle JVM uses different garbage collection techniques depending on the type of memory used. These techniques provide high efficiency and low overhead.

    < /a>
    Foot print

    The footprint of an executing Java program is affected by many factors:

    • Size of the program itself—how many classes and methods and how much code they contain.

    • Complexity of the pr ogram—the amount of core class libraries that the Oracle JVM uses as the program runs, as opposed to the program itself.

    • Amount of state the JVM uses—how many objects the JVM allocates, how large they are, and how many must be retained across calls.

    • Ability of the garbage collector and memory manager to deal with the demands of the executing program, which is often non-deterministic. The speed with which objects are allocated and the way they are held on t o by other objects influences the importance of this factor.

    From a scalability perspective, the key to supporting m any concurrent clients is a minimum user session footprint. The Oracle JVM keeps the user session footprint to a minimum by placing a ll read-only data for users, such as Java bytecodes, in shared memory. Appropriate garbage collection algorithms are applied against call and session memories to maintain a small footprint for the user's session. The Oracle JVM uses three types of garbage collection algorithms to maintain the user's session memory:

    • Generational scavenging for short-lived objects

    • Mark and lazy sweep collection for objects that exist for the life of a single call

    • Copying collector for long-lived objects—objects that live across calls within a session

    Performance

    Oracle JVM performance is enhanced by implementing a native compiler. Java runs platform-independent bytecodes on top of a JVM, which in turn interacts with the specific hardware platform. Any time you a dd levels within software, your performance is degraded. Because Java requires going through an intermediary to interpret platform-in dependent bytecodes, a degree of inefficiency exists for Java applications that does not exists within a platform-dependent language, such as C. To address this issue, several JVM suppliers create native compilers. Native compilers translate Java bytecodes into plat form-dependent native code, which eliminates the interpreter step and improves performance.

    The following table describes two methods for native compilation.

    Native Compilation Me thod Descriptio n
    Just-In- Time (JIT) Compilation JIT compilers quickly compile Java bytecodes to native (plat form-specific) machine code during runtime. This does not produce an executable to be run on the platform; instead, it provides platf orm-dependent code from Java bytecodes that is run directly after it is translated. This should be used for Java code that is run fre quently, which will be run at speeds closer to languages such as C.
    Static Compilation Static compilation translates Java byt ecodes to platform-independent C code before runtime. Then a standard C compiler compiles the C code into an executable for the targe t platform. This approach is more suitable for Java applications that are modified infrequently. This approach takes advantage of the mature and efficient platform-specific compilation technology found in modern C compilers.

    Oracle uses static compilation to deliver its core Java class libraries: the ORB and JDBC code in nat ively compiled form. It is applicable across all the platforms Oracle supports, whereas a JIT approach requires low-level, processor- dependent code to be written and maintained for each platform. You can use this native compilation technology with your own Java code .

    < h5>Dynamic Class Loading

    Another strong feature of Java is d ynamic class loading. The class loader loads classes from the disk (and places them in the JVM-specific memory structures necessary f or interpretation) only as they are used during program execution. The class loader locates the classes in the CLASSPATH and loads them during program execution. This approach, which works well for applets, poses the following problems in a server envir onment:

    Problem Description Solution
    Predictability The class loading operation places a severe penalty on first-time execution. A simple program can cause the Oracle JVM to lo ad many core classes to support its needs. A programmer cannot easily predict or determine the number of classes loaded. The Oracle JVM loads classes dynamically, just as with any other Java virtual machine. The same one-time class loading speed hit is encountered. However, because the classes are loaded into shared memory, no other users of those classes will cause the classes to load again—they will simply use the same pre-loaded classes.
    Reliability A benefit of dynamic class loading is that it supports program updating. For example, you would update classes on a server, and clients who dow nload the program and load it dynamically see the update whenever they next use the program. Server programs tend to emphasize reliab ility. As a developer, you must know that every client runs a specific program configuration. You do not want clients to inadvertentl y load some classes that you did not intend them to load. Oracle separates the uplo ad and resolve operation from the class loading operation at runtime. You upload Java code you developed to the server using the load java utility. Instead of using CLASSPATH, you specify a resolver at installation time. The resolver is analogous to CLASSPATH, but lets you specify the schemas in which the classes reside. This separation of resolution from class loading me ans you always know what program users run.

    < /a>

    Oracle's Java Application Strategy

    One appeal of Ja va is its ubiquity and the growing number of programmers capable of developing applications using it. Oracle furnishes enterprise app lication developers with an end-to-end Java solution for creating, deploying, and managing Java applications. The total solution cons ists of client-side and server-side programmatic interfaces, tools to support Java development, and a Java virtual machine integrated with the Oracle database server. All these products are compatible with Java standards.

    In addition to the Oracle JVM, the Ja va programming environment consists of the following:

    • Java stored procedures as the Java equivalent and companion for PL/SQL. Java stored procedures are tightly integrated with PL/SQL. You can call a Java stored procedure from a PL/SQL p ackage; you can call PL/SQL procedures from a Java stored procedure.

    • SQL data can be accessed through th e JDBC programming interface.

    • Tools and scripts used in assisting in development, class loading, and cla ss management.

    This section contains the following topics:
    Java Stored Procedures

    A Java stored procedure is a program you write in Java to run in the server, exactly as a PL/SQL stored proce dure. You invoke it directly with products like SQL*Plus, or indirectly with a trigger. You can access it from any Oracle Net client& #x2014;OCI, precompiler, or JDBC.

    In addition, you can use Java to develop powerful programs independently of PL/SQL. Oracle p rovides a fully-compliant implementation of the Java programming language and JVM.


    See Also:

    Oracle Database Java Developer's Guide explains how to write stored procedures in Java, how to access them from PL/S QL, and how to access PL/SQL functionality from Java.

    PL/SQL Integration and Oracle Functionality

    You can invoke existi ng PL/SQL programs from Java and invoke Java programs from PL/SQL. This solution protects and leverages your existing investment whil e opening up the advantages and opportunities of Java-based Internet computing.

    JDBC

    Java database connectivity (JDBC) is an application programming in terface (API) for Java developers to access SQL data. It is available on client and server, so you can deploy the same code in either place.

    Oracle's JDBC allows access to objects and collection types defined in the database from Java programs through dynamic SQL. Dynamic SQL means that the embedded SQL statement to be run is not known before the application is run, and requires input to b uild the statement. It provides for translation of types defined in the database into Java classes through default or customizable ma ppings, and it also enables you to monitor, trace, and correlate resource consumption of Java and J2EE applications down to the datab ase operation level.

    Core Java class libraries provide only one JDBC API. JDBC is designed, however, to allow vendors to suppl y drivers that offer the necessary specialization for a particular database. Oracle delivers the following three distinct JDBC driver s.

    < /tr>
    Driver Description
    JDBC Thin Driver You can use the JDBC Thin driver to write 100% pure Java applications and applets that access Oracle SQL data. The JDBC Th in driver is especially well-suited to Web browser-based applications and applets, because you can dynamically download it from a Web page just like any other Java applet.
    JDB C Oracle Call Interface Driver The JDBC Oracle Call Interface (OCI) driver accesses Oracle-specific native code (that is, non-Java) libraries on the client or middle tier, providing a richer set of functionality and some performance boost compared to the JDBC Thin driver, at the cost of significantly larger size and client-side installation.
    JDBC Server-side Internal Driver Oracle uses the server-side internal driver when Java code runs on the server. It allows Java app lications executing in the server's JVM to access locally defined data (that is, on the same machine and in the same process) with JD BC. It provides a further performance boost because of its ability to use underlying Oracle RDBMS libraries directly, without the ove rhead of an intervening network connection between your Java code and SQL data. By supporting the same Java-SQL interface on the serv er, Oracle does not require you to rework code when deploying it.

    SQLJ

    SQLJ allows developers to use object datatypes in Java programs. Developers can use JPublisher to map Oracle obj ect and collection types into Java classes to be used in the application.

    SQLJ provides access to server objects using SQL sta tements embedded in the Java code. SQLJ provides compile-time type checking of object types and collections in the SQL statements. Th e syntax is based on an ANSI standard (SQLJ Consortium).

    You can specify Java classes as SQL user-defined object types. You can define columns or rows of this SQLJ type. You can also query and manipulate the objects of this type as if they were SQL primitive types. Additionally, you can do the following:

    • Make the static fields of a class visible in SQL

    • Allow the user to call a Java constructor

    • Maintain the dependency between the Java class and its corresponding type

    JPublisher

    Java Publisher (JPublisher) is a utility, written entirely in Java, that generates Java classes t o represent the following user-defined database entities in your Java program:

    • SQL object types

    • Object reference types ("REF types")

    • SQL collection types (VARRAY types or nested t able types)

    • PL/SQL packages

    JPublisher lets you to specify and customize the mapping of these entities to Java classes in a strongly typed paradigm.


    See Also:

    Oracle Database JPublisher User's Guide

    Java Messaging Service

    Java Messaging Service (JMS) is a messaging st andard developed by Sun Microsystems along with Oracle, IBM, and other vendors. It defines a set of interfaces for JMS applications a nd specifies the behavior implemented by JMS providers. JMS provides a standard-based API to enable asynchronous exchange of business events within the enterprise, as well as with customers and partners. JMS facilitates reliable communication between loosely coupled components in a distributed environment, significantly simplifying the effort required for enterprise integration. The combination o f Java technology with enterprise messaging enables development of portable applications.

    Oracle Java Messaging Service is a J ava API for Oracle Streams, based on the JMS standard. Multiple client applications can send and receive messages of any type through a central JMS provider (Oracle Streams). The JMS client consists of the Java application as well as a messaging client runtime libra ry that implements the JMS interface and communicates with Oracle Streams.

    Java Messaging Oracle JMS supports the standard JMS interfaces and has extensions to support other Streams features that are not a part of the standard. It can be used to enqueue and d equeue messages in the queue available with Oracle Streams. Oracle JMS includes the standard JMS features:

    • Point-to-point communication using queues

    • Publish-subscribe communication using topics

    • Synchronous and asynchronous message exchange

    • Subject-based routing

    Orac le Streams also provides extensions to the standard JMS features:

    • Point-to-multipoint communication usin g a recipient list for specifying the applications to receive the messages

    • Administrative API to create the queue tables, queues and subjects

    • Automatic propagation of messages between queues on different data bases, enabling the application to define remote subscribers

    • Transacted session support, allowing both J MS and SQL operations in one transaction

    • Message retention after message is consumed

    • Exception handling

    • Delay specification before a message is visible

    Go to previous page
    Previous    		 Go to next page
    < font size="-2">Next
    		 Oracle
    Copyright © 1993, 2003 Oracle C orporation
    All Rights Reserved.
    Go to Documentation Home
    Home    		 Go to Book List
    Book List    		 Go to Table of Contents
    Contents    		 Go to Index
    Index    		 Go to Master Index
    Master Index    		 Go to Feedback page
    Feedback