
Summary of Changes in Oracle7, Release 7.1
This appendix describes changes in Oracle7, Release 7.1. The following topics are:
Terminology
New terms have been introduced with Oracle7, Release 7.1. The new terms and their definitions follow:
Parallelism Parallelism allows multiple processes to work together to process a single task simultaneously, such as a SQL query, CREATE INDEX, or DIRECT LOAD using SQL*LOADER.
Query Coordinator
Query coordinators separate the execution functions of an information processing task into parallel pieces, distribute the pieces to several query servers, coordinate the results from all of the servers, and send the results back to the user.
Query Server
Query servers operate in parallel to execute processing commands provided by a query coordinator.
Refresh Group
Refresh groups allow you to update a collection of snapshots consistently to a single point in time by creating a snapshot refresh group.
Symmetric Replication
Symmetric replication allows multiple copies of data to be maintained at different sites in a distributed environment. Immediate, local access to data is provided and systems are allowed to function autonomously, even when other systems in the distributed environment or network fail.
This section contains the following topics:
Although primarily a maintenance release, Oracle7, Release 7.1 contains several important functional enhancements. These enhancements include the following:
Server Manager
Oracle has a new database administration tool with a graphical user interface. This tool is documented in the Oracle Server Manager User's Guide. Release notes for Server Manager installation and startup are included in the file SVRMGR.TXT.
Procedural Option
Procedural option features previously available only to users of the procedural option, such as stored procedures and triggers, are now available to all users. Although this is no longer a separate option, it is still installed separately. Additionally, you may now have multiple triggers of the same type on a single table.
Symmetric Replication
Oracle's symmetric replication facility, which lets you maintain multiple updatable copies of data at different sites in a distributed environment, is available starting with Oracle7, Release 7.1.6 and is described in Oracle7 Server Distributed Systems, Volume II.
Consistent Snapshot Refresh
Snapshot refresh groups now allow you to refresh a collection of related snapshots to a transaction-consistent point in time. For example, this allows you to preserve master detail relationships between snapshots.
Enhancements to SQL and PL/SQL
Oracle7, Release 7.1 conforms to the entry level ANSI SQL92 (ANSI X3.135-1992 and ISO 9075:1992) standard requirements. This enhancement includes the use of the optional AS keyword to define column or expression aliases as well as allowing column or expression aliases to be used in the ORDER BY clause. This enhancement also allows administrators to enforce SELECT privileges on tables for deletes and updates that require a scan of the table.
SQL has been enhanced to allow PL/SQL user-defined functions to be called from expressions in SQL statements.
Read-Only Tablespaces
You can now create read-only tablespaces to avoid backup and recovery of static data. For multimedia or archival applications, read-only tablespaces can be stored on low cost per bit media, such as CD-ROM and WORM drives.
Parallel Recovery
You can now invoke multiple processes to recover your database concurrently, thus decreasing recovery time.
Improved Security When Connecting to Remote Databases
When you log on to a remote database, the password is now encrypted when it is sent over the network. Additionally, remote O/S-authorized DBA and OPER sessions over non-secure connections are now supported, even when a database is not available.
Parallel Query Option
Oracle7 with the new parallel query option improves the performance of bulk operations, such as data loading, queries, and index creation, by using parallel processing on multiple CPUs.
Dynamic SQL Supplied Package
The new DBMS_SQL package allows you to use dynamic SQL in stored procedures and PL/SQL anonymous blocks. This allows you to develop more flexible and general-purpose procedures.
SQL*Net Oracle Names
The SQL*Net Oracle Names feature allows you to use a database link without defining a connect string in the database. This is done by providing a central location for database link definitions.
Link to Oracle Office
The DBMS_OFFICE supplied package allows you to send a message to Oracle Office, in much the same manner that the DBMS_MAIL package, available with Release 7.0 of the Oracle Server, allows you to send a message to Oracle*Mail. The DBMS_OFFICE package is available with Oracle*Office and is described in the Oracle Office Server Release Notes, Release 2.0.12.
Mapping of Trusted Oracle7 Labels during Import
Exported Trusted Oracle MLSLABELs can now be imported to Trusted Oracle databases with different labels.
Referencing PL/SQL Functions in SQL Expressions
In Release 7.1 of the Oracle Server, you can include user-written PL/SQL functions in SQL expressions. See the Oracle7 Server Application Developer's Guide for more information on this feature.
By using PL/SQL functions in SQL statements, you can do
the following:
- Increase user productivity by extending SQL. Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.
- Increase query efficiency. Functions in the WHERE clause of a query can filter data using criteria that would otherwise have to be evaluated by the application.
- Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).
- Provide parallel query execution. If the query is parallelized, SQL statements in your PL/SQL function may be executed in parallel also (using the parallel query option).
Referencing Sequences in Distributed SQL Statements
In Oracle7, Release 7.1, the following object types referenced in distributed SQL statement may be located on different databases:
- tables locked by the FOR UPDATE clause of a SELECT statement
- tables from which LONG columns are selected
Using PL/SQL Functions
PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. PL/SQL functions are created as top-level functions by using the CREATE FUNCTION statement. Packaged functions are specified within a package with the CREATE PACKAGE statement and created in the CREATE PACKAGE BODY statement.
See the Oracle7 Server Application Developer's Guide for more information about creating functions and packages.
Restrictions
PL/SQL functions called from SQL statements cannot violate the following restrictions:
- The functions cannot contain OUT or IN OUT parameters; all parameters must be IN parameters.
- The functions cannot update the database.
- Only local functions that are referenced in a SELECT list, VALUES clause of an INSERT statement, or SET clause of an UPDATE statement can update a variable defined in a package.
- Remote functions cannot read or write package state.
- Functions that read or write package state cannot take advantage of the parallel query option.
- Functions cannot be called from a CHECK constraint or DEFAULT clause in a CREATE or ALTER TABLE command.
See the Oracle7 Server Application Developer's Guide for more information on PL/SQL functions and packages.
Privileges Required
To call a PL/SQL function from SQL, you must either own or have EXECUTE privileges on the function. To select from a view defined
with a PL/SQL function, you are required to have SELECT privileges
on the view. No separate EXECUTE privileges are needed to select from the view.
SQL Syntax Changes
This section outlines changes to SQL syntax introduced with Oracle7, Release 7.1 of the Oracle Server.
This section contains the following topics:
SELECT List
Column aliases in the SELECT list can optionally be separated from their expressions by the new AS keyword. Although the AS keyword adds no functionality, it may make SQL statements easier to read. These column aliases effectively rename the SELECT list items. For example:
SELECT empno, ename AS name
FROM emp
For example, the statement
SELECT empno, ename, sal+comm AS comp
FROM emp
ORDER BY sal+comm
which uses an ORDER BY column expression, is equivalent to the following statement which uses an ORDER BY column expression alias:
SELECT empno, ename, sal+comm AS comp
FROM emp
ORDER BY comp
Both statements result in column names of EMPNO, ENAME, and COMP, and both order the results by SAL+COMM. The difference is that the second statement uses the column expression's alias in the ORDER BY clause rather than the column expression itself. This
saves typing and can prevent errors, because the expression need not be retyped.
FIPS SQL Flagging
The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions. Oracle provides a FIPS flagger through interactive SQL to help you write portable applications.
When SQL flagging is set to on, your SQL statements are checked to see whether they include extensions that go beyond the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Server flags them as errors and displays the violating syntax.
SELECT Privileges When Updating or Deleting
In conformance with Entry level SQL92, Oracle7, Release 7.1 allows the security administrator to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. This feature can prevent unauthorized users from learning about the table's data through a covert channel.
Compatibility with Previous Releases
To ensure compatibility with Oracle7, Release 7.0 or Version 6, when migrating to release 7.1, keep the SQL92_SECURITY parameter at FALSE, thus inhibiting this feature.
Outer Joins
Oracle7 is more stringent than Oracle Version 6 in rejecting two types of semantically undefined outer join queries:
- The OR logical operator cannot combine two conditions if
either contains the outer join operator (+). Also, a condition cannot compare a column marked with the (+) operator to another expression using the IN comparison operator. Error 1719, outer join operator (+) not allowed in operand of OR or IN, is returned for such invalid conditions. If you have applications
that issue queries with such conditions, replace them with equivalent queries that use the UNION or UNION ALL set operators instead.
- If a condition compares a column marked with the (+) operator to a subquery, Oracle7 returns the following message.
ORA-01799: a column may not be outer-joined to a subquery
Oracle, Version 6 ignored the (+) operator in such conditions. If you have applications that issue queries with such conditions, remove the (+) from them and they will behave in Oracle7 as they did in Oracle, Version 6.
For more information, see Oracle7 Server SQL Reference.
This command has a PARALLEL clause and a CACHE clause to support the parallel query option. See Oracle7 Server SQL Reference for more information on this command.
This command has a RESET COMPATIBILITY option for
compatibility control.
You must have ALTER DATABASE system privilege and your instance must have the database open for you to issue this command.
The RECOVER option of this command has changed to include a PARALLEL clause for use with the parallel recovery feature. See Oracle7 Server SQL Reference for more information on this command.
This command has a new SET FLAGGER option to support flagging of SQL extensions that go beyond the SQL92 standard for SQL. The SET FLAGGER option has four additional options: entry, intermediate, full, and off.
This command also has a new option for closing cached cursors used by PL/SQL. Using the ALTER SESSION command with this option overrides the initialization parameter CLOSE_CACHED_OPEN_CURSORS for your current session.
For more information about the initialization parameter, see Oracle7 Server Reference.
This command also has a new option for specifying the size of the session cursor cache. The syntax is:
ALTER SESSION SET SESSION_CACHED_CURSORS = integer
The integer specified can be any positive integer, but the maximum value is operating-system dependent.
See Oracle7 Server SQL Reference for more information on using the ALTER SESSION command.
This command has a PARALLEL clause and a CACHE clause to support the parallel query option. See Oracle7 Server SQL Reference for more information on this command.
This command has READ ONLY and READ WRITE options to support read-only tablespaces. See Oracle7 Server SQL Reference for more information on this command.
This command has a PARALLEL clause to support the parallel query option. See Oracle7 Server SQL Reference for more information on
this command.
This command has a PARALLEL clause and a CACHE clause to support the parallel query option. See Oracle7 Server SQL Reference for more information on this command.
ALTER and CREATE SNAPSHOT and Index Storage
The creation of a simple snapshot automatically causes an index to be created. The CREATE and ALTER SNAPSHOT commands now support a USING INDEX clause to allow users to specify the tablespace and storage parameters to use for this index. If the USING INDEX clause is not specified, the index is created with the same tablespace and storage parameters as the snapshot.
Changes to Supplied Packages
The distribution mechanism for supplied packages, described in the Oracle7 Server Application Developer's Guide, was changed in release 7.1. The public package specifications continue to reside in the DBMS*.SQL files; for example, DBMSPIPE.SQL. The package bodies now reside in separate files, which follow a similar naming convention to the package specification file. The package body files are generally named PRVT*.SQL; for example PRVTPIPE.SQL.
However, the following packages have public package specifications, but the bodies are stored in binary format, which has the
extension .PLB:
To create a supplied package, you must run both of these files against your database. These scripts should be run automatically for you when you create a new database or upgrade an existing database.
Additional Information: Refer to your operating system-specific Oracle documentation for more information about the location and installation of these packages.
SQL*Net and the Multi-Threaded Server
Oracle7 fully supports SQL*Net, Version 1, either as a client or a server. Therefore, clients running SQL*Net, Version 1 can connect to Oracle7 Servers running SQL*Net, Version 1 for client/server access. Furthermore, Oracle7 Servers can communicate with one another for distributed queries, and (with the distributed option) for distributed update, two-phase commit and snapshots using SQL*Net, Version 1. The multi-threaded server architecture in Oracle7 requires use of SQL*Net, Version 2.
New Features of the Oracle Precompilers, Release 1.6
This section contains the following topics:
Release 1.6 of the Oracle Precompilers was introduced with Oracle7, Release 7.1 and incorporated many new features designed to enhance precompiler functionality and application performance, as well as allow your applications to access new Oracle7, Release 7.1 capabilities.
Most users will upgrade to Release 1.6 at the same time they upgrade to Oracle7, Release 7.1. Use of Releases 1.3, 1.4 and 1.5 of the Oracle Precompilers is supported against Oracle7, Release 7.1. However, if you wish to use the enhanced functionality of Oracle7, Release 7.1, you must use the Release 1.6 of the Precompilers. For more information on the Oracle Precompilers, Release 1.6, see the Programmer's Guide to the Oracle Precompilers.
The following new features are introduced with the Oracle Precompilers, Release 1.6.
SQL Standards Compliance
The Oracle Precompilers, Release 1.6 supports the latest ANSI/ISO embedded SQL standard (SQL92) by letting you
- use the status variable SQLSTATE
- check the SQLCA warning flag SQLWARN(3) for data exceptions
- declare multiple host variables with the same name, provided their scopes do not overlap
Configuration Files
The Oracle Precompilers, Release 1.6 can use a configuration file containing preset command-line options. By default, a text file
called the system configuration file is used. However, you can specify
any of several alternative files, called user configuration files, on the command line.
EXEC TOOLS Statements
EXEC TOOLS statements support the basic Oracle Toolset (Oracle Forms, Oracle Reports, and Oracle Graphics) by providing a generic way to handle get, set, and exception callbacks from user exits.
AUTO_CONNECT Option
To increase compatibility with other precompilers, the Oracle Precompilers, Release 1.6 allow your program to log on to the default database without using the CONNECT statement. Simply specify the new precompiler option AUTO_CONNECT on the command line.
Optional INAME and ONAME Keywords
When specifying the names of your input and output files on the command line, the keywords INAME and ONAME are optional.
Two New SQLLIB Routines
The Oracle Precompiler runtime library, SQLLIB, contains two new routines: SQLGLS and SQLVCP.
- SQLGLS returns the text and length of the most recently parsed SQL statement. It also returns a two-digit code indicating the SQL operation (such as INSERT or DELETE) performed by
the statement.
- SQLVCP returns the actual length (in bytes) of a VARCHAR data field. This is useful when your host-language compiler blank-pads the data field, making its actual length longer than its declared length. To get the actual (possibly padded) length of a VARCHAR data field, just pass the declared length to SQLVCP.
CHARF Datatype Specifier
The Oracle Precompilers, Release 1.6 introduce a new datatype specifier named CHARF. You can use this new datatype in EXEC SQL TYPE and VAR statements to equivalence host-language datatypes
to the fixed-length ANSI datatype CHARF, regardless of the
DBMS setting.
New Pro*FORTRAN Option
With Pro*FORTRAN, Release 1.6, the new option MULTISUBPROG controls the generation of COMMON statements and BLOCK DATA subprograms.
When MULTISUBPROG = YES, the precompiler generates
COMMON statements and BLOCK DATA subprograms like its
Release 1.5 predecessor.
When MULTISUBPROG = NO, the precompiler generates no COMMON statements or BLOCK DATA subprograms.
Pro*C/C++, Release 2.0
Pro*C/C++, Release 2.0 offers the following new features:
- the ability to declare a host structure that contains host variables
- the ability to declare host variables in a Declare Section
- the ability to use the EXEC SQL WHENEVER DO statement to call functions that take arguments and return a value
Note: If you are using Pro*C source code that is not ANSI C or not Kernigan and Ritchie, you cannot upgrade directly to Release 2.0.
Advanced Replication Option
Symmetric replication allows multiple copies of data to be maintained
at different nodes in a distributed environment. Changes to local copies of data are stored locally, and then periodically forwarded within separate transactions. If a remote node is unavailable, the changes remain in the local node and are propagated when the remote node becomes available. At any given point in time, some replicated copies may contain older data than other copies, but over time, the copies converge to the same value.
The Advanced Replication option, available with Release 7.1.6, supports the following features:
- Immediate, local access to data is provided.
- Nodes in the system can function autonomously, even when other nodes in the distributed environment or network fail.
- A symmetric, update-anywhere replication model ensures that all copies of data can be updated with updates automatically propagated to all other copies.
- Integrated, update conflict detection facilities are provided
that automatically invoke application-level conflict
resolution routines.
- Full transactional consistency is provided. Updates made by transactions to multiple tables are applied to remote copies of the tables, transactionally, to ensure data consistency and referential integrity between the tables.
For more information, see Oracle7 Server Distributed Systems, Volume II.
Initialization Parameter Changes
This section lists initialization parameters that are new or changed with Oracle7, Release 7.1. For a complete list of initialization parameters and their definitions, see Oracle7 Server Reference.
CACHE_SIZE_THRESHOLD
| PARALLEL_MAX_SERVERS
|
CLOSE_CACHED_OPEN_
CURSORS
| PARALLEL_MIN_SERVERS
|
COMPATIBLE
| PARALLEL_SERVER_IDLE_
TIME
|
DB_DOMAIN
| PRE_PAGE_SGA
|
DBLINK_ENCRYPT_LOGIN
| RECOVERY_PARALLELISM
|
LOG_CHECKPOINTS_TO_
ALERT
| REMOTE_LOGIN_
PASSWORDFILE
|
MAX_COMMIT_
PROPAGATION_DELAY
| SESSION_CACHED_CURSORS
|
NLS_DATE_FORMAT
| SNAPSHOT_REFRESH_
INTERVAL
|
OPTIMIZER_MODE
| SNAPSHOT_REFRESH_KEEP_
CONNECTIONS
|
PARALLEL_DEFAULT_MAX_
INSTANCES
| SNAPSHOT_REFRESH_
PROCESSES
|
PARALLEL_DEFAULT_MAX_
SCANS
| SQL92_SECURITY
|
PARALLEL_DEFAULT_
SCANSIZE
|
|
Data Dictionary Views and Dynamic Performance Tables for Release 7.1
This section contains the following topics:
Data Dictionary Views
This section lists data dictionary views that were new in
Release 7.1.
Data Dictionary Views
|
DBA_SEGMENTS
|
DBA_TABLESPACES
|
PLAN_TABLE
|
PRODUCT_COMPONENT_VERSION
|
USER_SEGMENTS
|
USER_TABLESPACES
|
Dynamic Performance Tables
This section lists dynamic performance tables that were new in
Release 7.1.
Dynamic Performance Tables
|
|
V$COMPATIBILITY
| V$OPTION
|
V$COMPATSEG
| V$PQ_SESSTAT
|
V$CONTROLFILE
| V$PQ_SLAVE
|
V$DATAFILE
| V$PQ_SYSSTAT
|
V$LOCK
| V$PWFILW_USERS
|
V$NLS_VALID_VALUES
|
|
Release 7.1 Backward Compatibility
Release 7.1 is completely backward compatible with Version 6 and Release 7.0 of the Oracle7 Server. As a maintenance release, Release 7.1 is the normal maintenance path for users running Version 6 and Release 7.0 applications.
Warning: If the SQL*DBA COMPATIBILITY parameter is set to Version 6, the functionality of certain Release 7.1 features will not be available.