Oracle8i
Release 2 (8.1.6.0.0) Fixed Bugs


Copyright (C) Oracle Corporation 1993, 1998, 1999, 2000

Editors: Richard Powell, Paul Lane, Bridget Burke, Venkat Subramanian

This software/documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

RESTRICTED RIGHTS LEGEND:

Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free.

Oracle is a registered trademark, and Net8, SQL*Plus, Oracle8i, Oracle Enterprise Manager, DBA Studio, Oracle Parallel Server, and PL/SQL are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners.

All trade names referenced are the service mark, trademark, or registered trademark of the respective manufacturer.



1.0 PL/SQL

Fix for BUG 1034486

Description: If you pass a CHAR actual parameter to a routine with an IN VARCHAR2 formal parameter, that parameter will behave as a CHAR-typed value in the routine. In particular, it will use CHAR (variable length, blank padded) comparison semantics when compared with a CHAR-typed value. The same sort of problem holds with VARCHAR2 actuals passed as IN CHAR formals.

Workaround:

1. Change the type of the formal parameter in the called function to match the type of the actual argument.

2. Assign the formal argument to a local variable of the same type inside the called routine, and use the local variable instead of the parameter.

3. Change the declaration of the formal parameter to be IN OUT rather than IN

Fix for BUG 709742

There is a memory leak when you append to collections in external procedures. The memory used for this will not be freed until the end of the user session.

Fix for BUG 778103

Duplicate of above information of Bug 709742


2.0 UTILITIES

Fix for BUG 944009

An ORA-1722 error message may be generated when loading zoned data.

Fix for BUG 952323

The Direct Path API may hang while loading a LONG with a column size less than 4000.

Fix for BUG 883867

The SQL*Loader utility (sqlldr) raises ORA-600[6971] when loading data of size >64K with "CONTINUEIF NEXT" option in direct mode.

Fix for BUG 890656

SQL*Loader does not read leading blanks in the record length portion of VAR records.

Fix for Fix for BUG 896493

The SQL*Loader utility (sqlldr) may crash when triggers or procedures being loaded use raise_application_error to report error messages with a length greater than 256.


3.0 Advanced Queuing

Fix for BUG 776564

When propagation is running under Oracle Parallel Server with multiple instances, if an instance fails while propagation is active (one or more SNP processes are propagating messages on behalf of some schedules), then it is possible that messages may not be propagated from such schedules until the instance is brought up.

Workaround: The schedules that have been affected by an instance failure can be determined by querying the administrative view DBA_QUEUE_SCHEDULES with the INSTANCE field set to the instance number of the failed instance. For each affected schedule, disable propagation for that schedule and then enable propagation. The schedule will then be handled by an available instance.


4.0 Application Failover

Fix for BUG 890075

V$SESSION information regarding Transparent Application Failover was sometimes not updated immediately after failover.


5.0 Data Dictionary

Fix for 904088

Selects against V$SQLTEXT or V$SQLTEXT_WITH_NEWLINES will no longer spin (or appear to hang) when HASH_VALUE is specified as a predicate.


6.0 Database Events

Fix for BUG 789865

When a "logon" database trigger becomes invalid, new sessions are disabled because the trigger has compilation errors and could not be successfully executed. The solution is to drop the trigger, but ORA-4098 does not provide the schema name of the offending trigger. Therefore, it is not easy to determine which trigger to clean up.

Workaround: Drop all "logon" database triggers with the same name:

  connect internal
  select u.name from user$ u, trigger$ t, obj$ o
    where o.name = '<upper_cased_trigger_name>' and o.namespace = 3
    and o.obj# = t.obj# and bitand(t.property, 8) > 0 and
    bitand(t.sys_evts, 8) > 0 and o.owner# = u.user#
     ;

Assume this query returns three rows: U1, U2, and U3. Do the following:

  drop trigger U1.<trigger_name>;
  drop trigger U2.<trigger_name>;
  drop trigger U3.<trigger_name>;

If there is more than one system trigger, and a user tries to do an 'alter database reset compatibility' the command fails with ORA-01422: exact fetch returns more than requested number of rows

Workaround:

Drop all your system triggers first and the "reset compatibility" should be able to proceed.

Fix for BUG 804988

Description: If an autonomous transaction is used in a logon trigger, then an internal error will be raised and subsequent connects will fail.

Workaround: None available; contact customer support.


7.0 Function-Based Indexes

Fix for BUG 933439

Function-based indexes on BITAND (), DATE, and datetime were not being used.

Workaround: None in 8.1.5. In 8.1.6 you must re-create those indexes in order to use them.

Fix for BUG 906239

Oracle did not use indexes on functions on (datecol + number). There is no workaround. These indexes need to be re-created in 8.1.6.

Fix for BUG 909381

Oracle returned incorrect results if both query and subquery have identical function-based index expressions.

Fix for BUG 831958

Queries using functions that match function-based bitmap indexes in the ORDER BY clause, GROUP BY clause, select list, or WHERE clause that contain OR often gave wrong results. The results would typically be the values of the last row fetched, apparently coming from every row scanned.

Workaround: In 8.1.5, do not use bitmap function-based indexes. In 8.1.6, no action is needed, but bitmap function-based indexes will not be used in the ORDER BY clause, GROUP BY clause, or select list. When OR-optimizations are used, bitmap function-based indexes will not be used at all.

Fix for BUG 840138

Queries with subqueries crash when using function-based indexes

Fix for BUG 869177

Selecting from a view or inline view with descending index or function-based index can fail with ORA-3113 and/or ORA-7445 with evaopn2() as top of the stack.

Fix for BUG 824669

Function-based indexes were being used in OR-optimizations, causing internal exceptions and core dumps.

Fix for BUG 788894

Although it is possible to create an index on a CAST function, you get an internal error if you try to use it.


8.0 Index-Organized Tables

Fix for BUG 864987

Attempts to access an index-organized table row using a rowid, obtained originally as new.urowid in an UPDATE AFTER row trigger where the UPDATE statement modified one or more primary-key columns, resulting in a change in the total length of the primary key, sometimes failed to find the row even when the row was present in the table.

Fix for BUG 794614

Error using CREATE ... AS SELECT to create an index-organized table from collection types.

Workaround: Create a conventional table as select from collection types. Then create an index-organized table as select from the conventional table above.

Fix for BUG 802712

Dropping a column from an index-organized table that has key compression enabled causes internal error.

Workaround: Use the SET UNUSED parameter of the drop column clause instead of DROP.

Fix for BUG 818468

An internal error may be generated when direct loading an index-organized table with non-key columns and an overflow segment.


9.0 Initialization Parameters

Fix for BUG 889748

When setting PARALLEL_AUTOMTIC_TUNING to TRUE, the error ORA-04031: unable to allocate 1327116 bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

may appear during startup. This is because the internal algorithms are unable to deal with a value of SHMMAX which has been set too low.

Workaround: Increase the size of SHMMAX to be as large as SHMMALL.


10.0 LOBs

Fix for BUG 835501

Loading zero-length LOB columns via the direct path API would result in invalid LOB locators being put into the row.

Fix for BUG 814031

When both the client and the server are varying width, then OCILobRead()used to sometimes return ORA-24812 (buffer not big enough for data) even in streaming mode.

Fix for BUG 801486

Description: If you called DBMS_LOB.TRIM(), DBMS_LOB.COPY(), or DBMS_LOB.APPEND()without first explicitly opening the LOB, the domain index on the LOB would not be updated. The same was true for OCILobTrim(), OCILobCopy(), and OCILobAppend.

Fix for BUG 787575

Altering a table to add a varray column stored as LOB will generate an error.

Workaround: Currently, it is allowed to add a varray column without the storage specification. If the varray type is >4K, then that column will be stored as a LOB by default. This problem will be fixed in 8.1.6 to allow specifying the LOB storage clause for the VARRY column during ALTER TABLE operations.

Fix for BUG 701929

Description: If you alter the chunk size for an existing LOB in an ALTER TABLE statement, no error is returned and the chunk size is not modified. Note that modifying the chunk size is not allowed.

Workaround: Do not modify the chunk size.

Fix for BUG 772172

The DBA_SEGMENTS data dictionary view does not show space used by temporary LOBs.

Fix for BUG 773638

Description: Nested tables will be improperly exported from an 8i database that is running in 8.0.x compatibility mode. When importing such a table in a database running in 8.0.x compatibility mode, the ORA-1403 error will be displayed.

Workaround: When trying to import nested tables into a 8.0.x compatible database, create the dump file by using the 8.0.x version of export against the database.


11.0 Materialized Views

Fix for BUG 908000

Some materialized views were created with the order of the primary key columns not the same as in the base table. This usually happened when one of the primary key columns was part of an equijoin with an inner subquery column.

Fix for BUG 905846

If a view was defined on an materialized view that was not yet created, the subsequent creation of the materialized view failed with ORA-955.

Fix for BUG 901953

When a non-atomic incremental materialized view refresh was interrupted (for example, by a system power outage or if the process was killed) and was subsequently followed by another non-atomic incremental refresh, the materialized view data was corrupted. This bug is now fixed. Any subsequent non-atomic incremental refresh after an interrupted refresh is forbidden.

Fix for BUG 893308

Once a full non-atomic refresh starts, a materialized view is truncated and becomes unusable, because TRUNCATE is an unrecoverable operation. In some situations, such as a privilege violation, a materialized view would be left

unusable prior to refresh actually starting. Oracle now checks these conditions prior to the marking of the materialized view and returns an error when appropriate.

Fix for BUG 825872

Refresh of a materialized view with joins or aggregates on a single table was being incorrectly optimized away, resulting in a materialized view with invalid contents, if the sequence of operation was as follows:

   CREATE TABLE,
   CREATE MATERIALIZED VIEW LOG
   [DML to table]
   CREATE MATERIALIZED VIEW
   [DML TO TABLE]
Fix for BUG 851227

Creating a materialized view with column aliases sometimes failed with the error "ORA-00904: invalid column name".

Fix for BUG 879949

The CREATE DIMENSION statement would overwrite and replace previous dimensions created with the same name. If a dimension exists with the same name, an error is now produced.

Fix for BUG 883099

Wrong results were obtained when attempting to perform incremental refresh with atomic_refresh = FALSE, if some rows in master tables contained NULLs.

Fix for BUG 811824

An error was encountered during refresh of a materialized view if the database has a very large number of objects or has evolved significantly (objects have been dropped and created).

Fix for BUG 768478

The message "ORA-00928: missing SELECT keyword" can occur when Oracle cost-based optimization attempts to rewrite a query that contains a set operator (e.g. UNION, MINUS, or INTERSECT) with a materialized view.

There are three workarounds:

Fix for BUG 779166

The message "ORA-00600: internal error code, arguments: [kkqsrre-1],[16]" can occur if Oracle cost-based optimization performs a query rewrite using a materialized view and the query references the same aggregate function in the SELECT list and the ORDER BY. Also the rewrite must require a "rollup" such that the rewrite will keep the original query group by in the rewritten query. For example:

explain plan for select round(sum(l_extendedprice),2) as revenue, l_orderkey
    from  lineitem l
    group by l.l_orderkey
    order by revenue;

produces error: ORA-00600: internal error code, arguments: [kkqsrre-1],

Note that the aggregate function must also be nested with in another function. The example above shows the SUM aggregate function nested in the ROUND function.

There are three workarounds:

  1. If you can modify the query string then, duplicate the expression from the select list in the ORDER BY. In the example, the ORDER BY would change from "order by revenue" to ???

  2. Turn off query rewrite for the query block by adding a NOREWRITE hint.

  3. Turn off query rewrite completely by issuing an "ALTER {SESSION|SYSTEM} DISABLE QUERY REWRITE" command.

Fix for BUG 803513

Materialized views with joins and single table aggregates should be owned by the same schema that owns all the dependent base tables.

Fix for BUG 805613

If you use the CREATE MATERIALIZED VIEW statement using a lower or mixed case ANSI quoted materialized view name (for example, CREATE MATERIALIZED VIEW "MyMaterializedView") and you specify ENABLE QUERY REWRITE, Oracle erroneously

returns an 'ORA-12003: snapshot "MYSCHEMA"."MYMATERIALIZEDVIEW" does not exist.' error message. (The terms "materialize view" and "snapshot" are synonyms.) In spite of the error message, the materialized view is created. However, it is not enabled for query rewrite and a row is not created for it in the ALL_MVIEW_ANALYSIS, DBA_MVIEW_ANALYSIS or USER_MVIEW_ANALYSIS data dictionary views. Subsequent ALTER operations on such a materialized view will result in similar behavior. The workaround is to use an uppercase name for the materialized view.


12.0 Oracle Call Interface (OCI)

Fix for BUG 1025022

OCIDESCRIPTORALLOC hangs after a call to OCILOBFREETEMPORARY in another thread.

Fix for BUG 981973

Freeing the environment handle caused core dumps in multi-threaded environments if handles were not freed individually.


13.0 Fix for BUG 853782

OCIStmtFetch() with nrows=0 gives error ORA-24347.

Fix for BUG 870085

OCISTMTEXECUTE with OCI_BATCH_ERRORS and ITERS=1 fails for INSERT operations (ORA-3113).

Fix for BUG 869170

OCIStmtExecute with OCI_BATCH_ERRORS mode for INSERT operations gave incorrect results. That is, not all insertable rows got inserted. This bug is also fixed in 8.1.5.1.

Fix for BUG 881883

An incorrect number of errors is returned for OCI_ATTR_NUM_DML_ERRORS when the value being entered is too large for all rows.

Fig for BUG 884729

Fetching into an array gives different errors or core dumps based on the array size.


14.0 Parallel Query

Fix for BUG 641846

Description: Parallel query that selects MIN or MAX of an object column along with GROUP BY gives ORA-12801: error signaled in parallel query server P000, and ORA-00600: internal error code.

Workaround: Do not use the parallel query option if computing Min/Max of an ADT column with GROUP BY.


15.0 Partitioning

Fix for BUG 849759

Queries crashed when performing parallel CREATE TABLE ... AS SELECT using partial partition-wise join.

Fix for BUG 770742

When creating a table that is partitioned on a nested table column, the system generates an internal error.

Workaround: The system supposed to return an error, because partitioning on a nested table column is not allowed instead of generating an internal error.

Fix for BUG 792290

Creating a partitioned table in parallel with an ORDER BY clause is very slow and only few (often one) parallel execution servers are active.

Workaround: There is no real work-around except to run the statement serially or remove the ORDER BY clause.


16.0 PL/SQL

Fix for BUG 825191

When objects with temporary LOB attributes were passed to external procedures from PL/SQL, the extproc process crashed.

Fix for BUG 857748

Running certain queries against nested table locators using native dynamic SQL from PL/SQL was causing the server to segment fault.

Fix for BUG 651352

The PL/SQL compiler ignored the NOCOPY modifier if the actual parameter was a bind variable. This bugfix permits bind variables to be passed by reference to NOCOPY formal parameters.

Fix for BUG 665647

Description: In a dynamic SQL statement that executes a PL/SQL block, if you assign a value to an IN OUT bind variable and then attempt to read that bind variable within the same dynamic SQL statement, the read value is inconsistent with the assigned value.

Example:

   DECLARE
     v1 number;
   BEGIN
     EXECUTE IMMEDIATE
       'begin
          :1 := 2;
          dbms_output.put(''during execution, :1 should be 2; found '');
          if :1 is null then dbms_output.put_line(''NULL'');
          else dbms_output.put_line(:1);
          end if;
        end;'
       USING IN OUT v1;
     dbms_output.put_line('after execution, v1 should be 2; found '||v1);
   END;

The printed result is:

   > during execution, :1 should be 2; found NULL
   > after execution, v1 should be 2; found 2

Workaround: Assign this value to a local variable within the PL/SQL block and then read from this local variable. Here is the modified example:

   DECLARE
     v1 number;
   BEGIN
     EXECUTE IMMEDIATE
       'declare
          a_local number := 2;
        begin
          :1 := a_local;
          dbms_output.put(''during execution, a_local should be 2; found '');
          if a_local is null then dbms_output.put_line(''NULL'');
          else dbms_output.put_line(a_local);
          end if;
        end;'

     USING IN OUT v1;
     dbms_output.put_line('after execution, v1 should be 2; found '||v1);
Fix for BUG 703285

Description: An attempt to fetch/select a logical rowid column from an index-organized table into a PL/SQL variable of type ROWID should result in error ORA-1410 (invalid rowid). PL/SQL fails to issue such an error.

Workaround: Fix your code to fetch the column into a PL/SQL variable of type UROWID.

Important Note: Because of implementation restriction, we will disallow any assignment from UROWID to ROWID in 8.1.6 regardless whether the assignment is legal or not. This restriction will be relaxed in later releases. Using UROWID instead of ROWID in your application is highly recommended.

Fix for BUG 706587

The first call to UTL_FILE.PUT_LINE in an application sometimes returns an ORA-1403 when buffer length is specified.

Fix for BUG 716862

The use of weak REF cursors with bulk binds should result in a compilation time error, because this is currently not supported. The bug here is that internal error ORA-600 [7005] is instead observed at run time.

Fix for BUG 780410

Description: PL/SQL crashes when inserting a collection entry into a nested table.

Example:

   create type ttab as table of number;
   create table mytab(c1 number, c2 ttab) nested table c2 store as atab;
   insert into mytab values(1, ttab(1));
   -- the following will crash

   declare
     v ttab := ttab(3,5,7,11);
   begin
     insert into table(select c2 from mytab where c1=1) values(v(1));
   end;

Workaround: The "INSERT INTO TABLE" statement crashes only if VALUES clause contains an indexing expression of a collection. To work around this, do the following:

   declare
     n number;
     v ttab := ttab(3,5,7,11);
   begin
     n := v(1);
     insert into table(select c2 from mytab where c1=1) values(n);
   end;
BUG 781291

Description: If you create a schema-level user-defined type, create a table with a column of that type in the same schema as the user-defined type and with the same name as the schema, and then attempt to use this table inside PL/SQL code, the PL/SQL compiler incorrectly issues PLS-302 saying "component '<type-name>' must be declare".

Example:

   connect test/test
   create type t1 as object (a number);
   create table test (p number, q t1);
   declare v test%rowtype; begin null; end;

Workaround: Rename the table with a name different from the schema name.

Fix for BUG 801541

The PL/SQL compiler should cleanly compile TRIM(expr), where expr is of type NCHAR, NVARCHAR2, or any other national character type argument. Instead, it issues error PLS-561. PL/SQL does not accept national language character sets for the simple SQL92 TRIM function TRIM(<value>).

Workaround: Use the alternative syntax TRIM(BOTH FROM expr), which returns the same value as TRIM(expr).

Fix for BUG 802281

Prior to 8.1.6, use of the DML RETURNING statement could cause the session memory to increase over time. The index table being fetched into were not being trimmed between bulk fetches.

Fix for BUG 807880

A VARCHAR2 variable fetched from SQL does not have a null terminator. If the variable is passed out to an external C routine as an IN parameter, the 'strlen' function does not work properly. This holds for both trusted and safe callouts from SQL and PL/SQL.

Fix for BUG 808925

PL/SQL cannot compile a program if it has a NCHAR literal of byte length greater than 4000. Here the byte length is computed as the number of characters multiplied by the worst case byte size of a character in the NCHAR char set.

Fix for BUG 813429

Where a type name is expected, the use of a qualified (i.e., dot-separated or including a dblink) name beginning with the keyword TIME, TIMESTAMP, or INTERVAL may result in syntax error PLS-103. This change in behavior may break existing programs.

Workaround: Rewrite your program to use identifiers other than these.

Example:

   create or replace package time is
     type t is record (hr number, min number, sec number);
   end;

   declare
     x time.t; -- this causes PLS-103 in 8.1.5 (worked in 8.0 & 7.3)
   begin
     x.hr := 10;
   end;


17.0 Fix for BUG 813631

Probe now provides the ability to set a breakpoint on a particular OER and to obtain information about the current OER (if any).

Fix for BUG 816030

Description: In a PL/SQL program, if an instantiation of a target library unit fails with a ORA-04030 or ORA-04031, a subsequent attempt (in the same session) to call an entry point in the target library unit might fail with a segmentation fault.

Fix for BUG 819229

The NOCOPY compiler hint is sometimes ignored for temporary lobs passed as parameters, causing a performance degradation. Similarly, temporary lobs passed as IN parameters are sometimes passed by value, causing a performance degradation.

Fix for BUG 819885

Potentially, any bulk bind statement with a RETURNING clause could fail due to this error.

Fix for BUG 821428

Objects with constraints, when specified as NOCOPY parameters, were incorrectly being passed by reference. Variables with constraints need to be passed by value in order to ensure that the constraints are satisfied when the call returns back to the caller.

Fix for BUG 822440

Certain numeric arguments are indistinguishable in their description in the ALL_ARGUMENTS data dictionary view.

Example: For PROCEDURE foo (n NUMBER, f FLOAT), the ALL_ARGUMENTS entries will look like:

     ARGUMENT_NAME                    DATA_TYPE         
     N                                NUMBER
     F                                NUMBER

In addition, the length, radix, scale, and precision information for these arguments will also be identical.

Fix for BUG 838413

PL/SQL does not support VALUE in the LHS side of the SET clause in UPDATE, although this is supported in SQL as of the 8.1 release.

Fix for BUG 844469

PL/SQL fails to compile Boolean expressions of UROWID type.

Example:

 declare
   u1 urowid;
   u2 urowid;
   b boolean;
 begin
   b := u1 > u2;
 end;
Fix for BUG 844471

The PL/SQL compiler crashes at run-time when a UROWID value is assigned to a CHAR variable.

Example:

 declare
   u urowid;
   c char(64);
 begin
   c := u;
 end;
Fix for BUG 860170

When a CALL statement is executed via Native Dynamic SQL in PL/SQL (8i), if the IN/OUT of the bind variable is incorrectly specified, then an exception should be raised. Currently, no exception is getting raised and the program behaves incorrectly.

Fix for BUG 864932

Wrap was not able to compile large package bodies.

Fix for BUG 866197

An open statement for a regular cursor causes the process to hang.

Fix for BUG 868717

Bulk bind features are not supported in client-side programs. A compiler error will be raised if any bulk bind feature is used in a client-side program.

Fix for BUG 874076

When a PL/SQL LOB variable that is currently pointing to a TEMPORARY lob is assigned either a NULL literal or another uninitialized LOB variable, then the target temporary LOB is not freed, causing a memory leak.

Fix for BUG 876150

PL/SQL does not support RPC of LOB locators. Prior to this bugfix, an attempt to pass LOB arguments (or arguments containing LOBs) from client side PL/SQL (Forms, for example) resulted in a run-time error. After this bugfix, such an attempt is reported as a compile time error of the form:

     err 564 (...) lob arguments are not permitted in remote calls
Fix for BUG 881365

PL/SQL did not accept the syntax for CUBE/ROLLUP and GROUPING extensions to the SELECT statement.

Workaround: Use dynamic SQL to embed such SQL statements in PL/SQL.

Fix for BUG 881430

Prior to this bugfix subpartition-extended tables were not supported in PL/SQL. One workaround is to use dynamic SQL or create a view on the subpartition and select from the view instead.

Fix for BUG 881494

BULK fetch from a weak REF cursor should be allowed, but an exception is raised by PL/SQL.

Example:

   declare
     type trc is ref cursor;  -- weak ref cursor type
     rc trc;
     arr_n a_collection_of_num;
   begin
     open rc for select a_number from a_table;
     -- The following BULK fetch should be allowed
     fetch rc BULK COLLECT INTO arr_n;
     close rc;
   end;
Fix for BUG 881499

BULK COLLECT INTO a scalar instead of a collection is an error, but PL/SQL does not return any error message.

   create table tabn(a number);
   declare
     b number;
   begin
     -- the following is an error
     select a bulk collect into b from tabn;
   end;
Fix for BUG 881500

The bug fix allows constraint violations from the following list to be handled correctly:

  1. NULL constraints

  2. Precision/Scale

  3. Range constraints

  4. Length (for VARCHAR and fixed CHAR)

Code with bulk binds will need to be recompiled to take advantage of this fix.

Fix for BUG 883694

If a DML (delete/insert/update) statement without bulk in-binds was used inside a FORALL statement, no error would be raised.

Fix for BUG 888668

Prior to this fix, invalid index accesses into the bulk rowcount collection (only used with bulk binds) would return a NULL value. An exception would better characterize the invalid access.

Fix for BUG 890206

Open a_ref_cursor for 'create table tabn(a number)'. A run-time exception is raised for the above statement, but the table 'TABN' is created in the database. The correct behavior is to not create 'TABN'.

Fix for BUG 893261

SQL trim functions (LTRIM, RTRIM and TRIM) return NULL if the trim set is NULL. PL/SQL trim functions treat NULL trim set as no trim set and use the default behavior of trimming white space for non-NCHAR types and return the trim source unchanged in case of NCHAR types. This fix will make PL/SQL LTRIM and RTRIM exactly as SQL LTRIM and RTRIM. The TRIM also will be made seamless with SQL TRIM through fix to bug 929035.

The fix can be disabled using backout event 10943 level 16.

Example: Current behavior is shown below. The CHAR set used for NCHAR is US16TSTFIXED. It just repeats the non-space characters. Therefore, N'a' becomes 'aa'. Space in this char set is ' \' (0x205c).

    Call                    SQL      PL/SQL (before this fix)

    TRIM(NULL FROM ' ha ')  NULL    'ha'
    LTRIM(' ha ', NULL)     NULL    'ha '
    RTRIM(' ha ', NULL)     NULL    ' ha'
    TRIM(NULL FROM N' ha ') NULL    ' \hhaa \'
    LTRIM(N' ha ', NULL)    NULL    ' \hhaa \'
    RTRIM(N' ha ', NULL)    NULL    ' \hhaa \'

Workaround: Avoid using implicit (that is, uninitialized variable) or explicit NULL trim sets in trim functions.

Fix for BUG 898298

The bulk_rowcount attribute is redundant for bulk inserts. For example, the code fragment:

   FORALL i IN 1..100
      INSERT INTO EMP(EMPNO) VALUES (COLL(i));

inserts one row per iteration. So, the bulk_rowcount attribute, where bulk_rowcount(i), indicates the number of rows processed in the ith iteration, is redundant because bulk_rowcount(i) = 1 for i in 1..100. The bugfix involves disabling counter maintenance code for bulk inserts.

Fix for BUG 898511

Prior to this bugfix, the PL/SQL compiler erroneously permitted char actual parameters to be passed by reference to VARCHAR, LONG, and ROWID formals and vice versa. This behavior was incorrect, because CHAR and VARCHAR types have different blank pad semantics. Clients who rely on the old (incorrect) behavior can get it by recompiling their code with the following ALTER SESSION setting:

  alter session set events = '10943 trace name context level 2';
Fix for BUG 900124

SELECT or FETCH of a NULL literal or column value into a collection element produces no compile or run-time messages when the collection element type is constrained as NOT NULL.

Fix for BUG 900136

Bulk fetches not returning any rows were not generating NO_DATA_FOUND exceptions.

Fix for BUG 902001

FORALL bulk DML does yet not support table(bulk_index).attribute syntax. PL/SQL is not enforcing this implementation restriction.

Fix for BUG 919390

Prior to this bugfix, the PL/SQL parser did not accept partition or subpartition extended names in the lock table statement. The workaround was to use dynamic SQL.

Fix for BUG 939865

DBMS_DEBUG used to return error_exception on timeout instead of the more appropriate error_timeout.

Fix for BUG 940064

PL/SQL TRIM used to accept trim sets more than one character long. Now an ORA-30001 exception will be raised in compliance with ANSI and SQL.

Example behaviors are shown below:

SQL does

    TRIM('ab' FROM 'aabcdba')
    ORA-30001: trim set should have only one character

PLSQL did

    TRIM('ab' FROM 'aabcdba')
    'cd'

PLSQL now does

    TRIM('ab' FROM 'aabcdba') 
    ERROR at line 1:
    ORA-30001: trim set should have only one character

Workaround (if needed): To use more than one character trim set and get the TRIM effect, please use RTRIM or LTRIM of the source. They accept trim sets longer than one character. For example, use:

     (RTRIM (LTRIM (x,y),y) 

to get

     TRIM(y from x) 

where y is more than one character long.

Fix for BUG 951161

When using bulk bind operations, such as FORALL/UPDATE, to update database tables that contain columns of DATE type, junk information was inserted into the DATE type columns.

Fix for BUG 961212

SQL trim functions (LTRIM, RTRIM and TRIM) return NULL if the trim set is empty. PL/SQL trim functions treat NULL trim set as no trim set and use the default behavior of trimming white space for non-NCHAR types and return the trim source unchanged in case of NCHAR types. This fix is very similar to one for bug 893261 and uses the same backout event. The fix can be disabled using backout event 10943 level 16.

Example: The current behavior is shown below. The character set used for NCHAR is US16TSTFIXED. It just repeats the non-space characters. So N'a' becomes 'aa'. Space in this char set is ' \' (0x205c).

  Call                    SQL      PLSQL (before this fix)
  TRIM('' FROM ' ha ')    NULL    'ha'
  LTRIM(' ha ', '')       NULL    'ha '
  RTRIM(' ha ', '')       NULL    ' ha'
  TRIM(N'' FROM N' ha ')  NULL    ' \hhaa \'
  LTRIM(N' ha ', '')      NULL    ' \hhaa \'
  RTRIM(N' ha ', '')      NULL    ' \hhaa \'

Workaround (if needed): Avoid using an empty trim set.

Fix for BUG 967837

TRIM and CAST were recognized only as built-in functions by the PL/SQL grammar. Now they can also be used as user identifiers.

Workaround: Do not use TRIM and CAST as user object names such as variable names. Treat them like reserved words. If code already used them as user names, change the user names.

Fix for BUG 791384

Append of a LOB to a null LOB returns nondescript error message.


18.0 Recovery Manager (RMAN)

Fix for BUG 850960

If you run RMAN under 8.1.5 in order to upgrade a catalog database that is running under 8.0.x, using the UPGRADE CATALOG command, it was possible to get RMAN-6444, and the upgrade would fail. In addition, the DBMS_RCVCAT and DBMS_RCVMAN packages were left in an invalid state and could not be recompiled.

Fix for BUG 898138

A logfile was not generated when RMAN was started with a logfile option. In addition, as the prompt was not printed to STDOUT, it appeared to the user

that RMAN was hung.

Fix for BUG 728666:

CROSSCHECK and DELETE EXPIRED commands now work without a catalog.

Fix for BUG 816646:

Device parms syntax is now validated. For example, PARMS 'ENV(BACKUP_DIR=xxx)' would not set BACKUP_DIR, because the equal sign was missing, but no error message was issued. In 8.1.6, an error message is issued when the PARMS option on the ALLOCATE CHANNEL command has invalid syntax.

Fix for BUG 817493:

Introduces the AUTOLOCATE option to allow RMAN to identify the correct node from which to restore each backup, in the OPS environment.

Fix for BUG 850961:

RMAN executable 8.0.6 can be used with target database 8.1.6. This bug fix requires the patch for the 8.0.6 RMAN executable)

Fix for BUG 862080:

Proxy backup or restore would not work after the target database was started from within RMAN.

Fix for BUG 893864:

The RESTORE command would fail when the recovery catalog was 8.1 and the target database was 8.0.

Fix for BUG 898625:

CHANGE .. UNAVAILABLE is now allowed when the target database is not mounted.

Fix for BUG 906556:

The RMAN RECOVER TABLESPACE command and the SQL ALTER DATABASE RECOVER TABLESPACE did not work with mixed-case tablespace names.

Fix for BUG 907258:

The RMAN ALTER DATABASE OPEN command now supports the RESETLOGS option. If issued while connected to the recovery catalog, it also automatically performs the RESET DATABASE command. Note that the RESET DATABASE command must still be issued by the user if the database is opened in RESETLOGS mode with a non-RMAN tool such as SQL*Plus.

OPEN RESETLOGS also has the side effect of invalidating any previous SET UNTIL command, if issued from inside of an RMAN RUN block.

Fix for BUG 915954:

After issuing the RESET DATABASE command to re-register the database after OPEN RESETLOGS, RMAN would not work properly. The workaround was to exit and reenter RMAN.

Fix for BUG 927353:

PRGRMANC.SQL would delete archive log records from the recovery catalog, which would prevent restoration of those logs.

Fix for BUG 935058:

The DUPLICATE command did not support the SET UNTIL setting, which made it impossible to use DUPLICATE to construct a version of the database from an earlier point in time.

Fix for BUG 979956:

The DUPLICATE command would sometimes return ORA-600 [3024].

Fix for BUG 1004398:

A core dump would sometimes occur when the RMAN DEBUG command line option was used.

Fix for BUG 1025752:

Proxy copy of the controlfile always used the default snapshot controlfile name, even if the snapshot controlfile had been set to a different name by using the SET SNAPSHOT CONTROLFILE command.

Fix for BUG 1043531:

The DUPLICATE command would sometimes encounter error ORA-01195 when some archive logs that were needed to recover the clone database were never backed up, even if those logs were available on disk.

Fix for BUG 1058691:

An RMAN-20043 error would occur when resyncing the recovery catalog after adding a datafile to a tablespace after resetting the system clock to a time before the tablespace was created.

Fix for BUG 1069106:

A core dump would sometimes occur when the RMAN DEBUG command line option was used.


19.0 Replication

Fix for BUG 774760

Description: Instead of raising an ORA-1435 error message, an ORA-600 error message is raised when a bad owner is specified when creating a snapshot. For example, if BOB is not a valid owner (schema), the CREATE SNAPSHOT bob.foo ... statement raises an ORA-600 error message.

Workaround: View the error message for ORA-1435. To avoid error, create a snapshot using a valid schema.

Fix for BUG 775487

If you attempt to register a user proxy snapadmin privileges for an object group at a non-masterdef master using DBUS_REPCAT_ADMDIN.REGISTER_USER_REPGROUP(),proxy snapadmin privileges will not be granted after you issue DBMS_REPCAT.CREATE_MASTER_REPOBJECT()as the masterdef site for an object in that object group.

Fix for BUG 893259

CREATE SNAPSHOT on a table with a VARCHAR2(4000) column truncates the column to VARCHAR2(2000).

Fix for BUG 894376

There used to be two separate but closely related sets of views, DBA/ALL-USER_MVIEW_ANALYSIS and DBA/ALL-USER_SNAPSHOTS. They are now superseded by the views DBA/ALL/USER_MVIEWS.

Fix for BUG 926397

When using deployment templates in replication, objects of type PACKAGE BODY do not appear in the DBA/ALL/USER_REPCAT_RGT_TEMPLATE_OBJECTS data dictionary views.

Fix for BUG 938676

Invalid character when attempting to instantiate templates containing PL/SQL type database objects.

Fix for BUG 678268

8.1 snapshots created with the fast refresh mode against a 7.x master site return an ORA-12018 error. The workaround for this situation is to create the snapshot with the refresh complete mode and then change the refresh mode to fast using the ALTER SNAPSHOT command.

Fix for BUG 839413

For an aggregate materialized view on a single table which is fast refreshable under any DML, if AVG() was present without SUM(), depending on the position of the AVG() in the query, an error was being generated during refresh.

Fix for BUG 839413

AVG() in the query an error was being generated during refresh.

Fix for BUG 878501

When attempting to create many materialized views that reference the same base data simultaneously, deadlocks occasionally happened. This has been resolved.


20.0 SQL Execution

Fix for BUG 932236

Regardless of the options on COMMIT PRESERVE ROWS or DELETE ROWS, the DURATION column USER/DBA/ALL_TABLES is always SYS$TRANSACTION for temporary tables.

Fix for BUG 909302

Queries that contained LIKE operators with accompanying escape characters were not returning rows when bitmap indexes were used.

Fix for BUG 924671

Index range scans gave incorrect results sometimes for IS NULL predicates.

Fix for BUG 925994

Anti-join and semi-join gave incorrect results due to wrong transitive predicate generation.

Fix for BUG 899070

Performing a SELECT with a LIKE operator on a unique index column may cause incorrect results if the LIKE pattern length is same as the column length (excluding wild card).

Fix for BUG 894085

Deletion from the parent table of a foreign key constraint could cause a segmentation violation in the server.

Fix for BUG 893021

Automatic recompilation of a view containing ORDER BY clause raised ORA-1785 error.

Fix for BUG 869982

Using a sequence in an UPDATE statement's WHERE clause caused the server to crash.

Fix for BUG 851704

If a sequence can be used in a WHERE clause, a query with an ORDER BY, GROUP BY, DISTINCT, set operation, aggregate function, in a DELETE, in a view, or anywhere other than an INSERT, UPDATE, or SELECT statement, then this bug has been rediscovered.

Workaround: Sequences used to be disallowed in these places before 8.1.5. They are disallowed again in 8.1.6. It was never documented that the restriction was being lifted, so the documented behavior is not changing.

Fix for BUG 874886

Oracle returns incorrect results when a query does not include columns from a single set function view.

Fix for BUG 875870

ORA-3113 DURING PARSE WITH BIND VARIABLE IN LEADING COLUMN OF GROUP BY

Fix for BUG 876445

The LCK process timed out too frequently.

Fix for BUG 877619

ORA-4030 was encountered when a query EXPLAIN PLAN included nested loops with an index range scan in the loop.

Fix for BUG 811835

If you define more than one INSTEAD OF trigger on a nested table column of a view for the same operation (such as INSERT or UPDATE) and if you reference :PARENT bind variables in both the triggers, then the bind variable could get bound to a wrong value, depending on the position of the bind. (The position of a bind is the count of the number of binds variables before and including this bind variable inside the trigger body.)

Workaround: Use a single trigger to perform the function for a particular DML for triggers on nested table view columns involving PARENT binds. Otherwise, change the position of the PARENT bind, by introducing some dummy binds before this one.

Fix for BUG 773901

Description: Parallel INSERT statements fail with ora-1280 if the destination table is schema qualified.

Fix for BUG 783314

ALTER TABLE ... ADD NESTED TABLE COLUMN WITH DEFAULT raises OER-00001.

Fix for BUG 783483

Specification of a table name that is 27 characters long in a DBMS_REPAIR.ADMIN_TABLES with CREATE_ACTION results in an ORA-972.

Workaround:

Specify table names that are less than 27 characters long.

Fix for BUG 784184

Parallel direct load is not currently supported for index-organized tables (IOTs). It is possible that if "skip_index_maintenance" is set to TRUE that a corruption of the IOT will occur. The next release will issue a message indicating that the attempted load is not supported and the load will be aborted.

An error message has been added to indicate parallel load is not supported for partitioned IOTs.

Fix for BUG 788539

If you have a trigger with object outbinds and you issue an INSERT statement that does not supply the value for trigger, a SIGSEGV results. (This happens only in the case of a BEFORE INSERT row trigger that is modifying object types.)

Workaround:

Specify a NULL for that object type in the INSERT statement rather than leaving it unspecified.


21.0 SQL DML

Fix for BUG 819045

A CREATE FORCE VIEW statement with compilation errors would raise an OERI(16221).

21.1 Objects

Fix for BUG 892662

A simple view that is based on a scoped REF column from a table becomes non-insertable if the insert list consists of user-defined objects or user-defined REF columns. For example:

 create type emp_t as object
 (id number,
 name varchar2(20),
 salary number);
 create table emps of emp_t (primary key (id));
 create table temptab ( dno number, emp emp_t , emp_ref REF emp_t
                            scope is emps);
 create view tempview as select * from temptab;

Now the following insert into the view succeeds:

    insert into tempview (dno, emp_ref) select 1,null from dual

whereas the following insert fails with a virtual column not allowed error:

    insert into tempview (dno, emp, emp_ref) select 1,null,null from dual

Workaround: Do the insert without any object types or user-defined REFs first and then try an update. This is the only possible workaround, if you need the underlying REF to be a scoped REF.

Fix for BUG 844222

INSTEAD OF triggers were not part of Standard or Personal editions of Oracle in 8.1.5.

Fix for BUG 812718

If you add a SCOPE constraint on a REF column through an ALTER TABLE statement and the target table is the same table in which the REF column is located, a core dump occurs.

Workaround: Add the SCOPE constraint on the REF column during create time.

Fix for BUG 813304

Passing object types to triggers implemented in Java is not supported.

Workaround: Create the trigger in PL/SQL as a BEGIN..END block and invoke the Java method from the BEGIN..END block. For example, instead of creating the trigger as a call to a Java procedure with object type parameters as:

 create trigger t ... is CALL javaprocedure(:new.col1, :new.col2)
  implement the trigger as -

you should instead specify:

   create trigger t ... begin javaprocedure(:new.col1, :new.col2) end
Fix for BUG 813414

If a view has a nested table column that is a scalar nested table (for example, TABLE OF NUMBER) other than REFs, then if an INSTEAD OF trigger for UPDATE is defined over this nested table column, any UPDATE to the nested table elements using the TABLE() clause raises internal errors.

Workaround: Create the nested table type as TABLE OF some object type, where the

object type has the single scalar value. For example, instead of using foo_nested_t which is of type TABLE OF NUMBER, use

 CREATE TYPE foo_num_t AS OBJECT ( numval NUMBER);
 CREATE TYPE foo_nested_t AS TABLE OF foo_num_t;

and then create the views with the new foo_nested_t. Now the triggers will work fine. Note that only updates have this problem. Deletes and inserts work fine.


22.0 Optimization

Fix for BUG 962238

When using DBMS_STATS to store histograms in the dictionary, you could sometimes get a 19005 internal error when querying from those tables.

Fix for BUG 989614

When using DBMS_STATS, some columns could not have statistics if one column name was the prefix of another.

Fix for BUG 929840

When using DBMS_STATS on a partitioned table after using ANALYZE without explicitly deleting statistics in between, sometimes resulted in a 15163 internal error.

Fix for BUG 837943

DBMS_STATS would sometimes return Oracle error 1467 when gathering statistics on tables with long character columns.

Fix for BUG 844215

DBMS_STATS package operations would run very slowly if the data dictionary was not analyzed.

Fix for BUG 851736

When running DBMS_SATS,SET_INDEX_STATS, the command sometimes failed with ORA 54, resource busy, if the index's table was being used.

Fix for BUG 866222

When using statistics gathering routines in DBMS_STATS with the CASCADE option,

Oracle returned an error if one of the indexes was in a different schema then its corresponding table.

Fix for BUG 883030

Temporary table transformation can cause core dump or ora-600[qknAddSib1]

Fix for BUG 792393

When creating a local index on an object attribute, the system generates a global index instead.

Workaround: In 8.1.5, there is no work around for this. This problem will be fixed in 8.1.6.


23.0 Parallel Query

Fix for BUG 808987

A parallel query containing a join between many tables and inline views with a NO_MERGE hint could hang during execution. This situation is exacerbated if some of the tables have a parallel hint and others do not.


24.0 SQL*Loader

Fix for BUG 1032419

Using the direct path API to load a temp table would fail with an ORA-03113.


25.0 Tablespace Management

Fix for BUG 891996

Meaningless values for MAX_EXTENTS were shown in DBA_TABLESPACES for temporary tablespaces.

Fix for BUG 880852

A failed DROP TABLESPACE of a locally managed temporary tablespace did not properly roll back. A subsequent attempt to drop the temporary tablespace failed with ORA-600 [kftlft-01].

Fix for BUG 880885

A failed CREATE TEMPORARY TABLESPACE of a locally managed temporary tablespace did not properly roll back. Some time later, ORA-1157 and ORA-1110 messages appeared in the alert log.


26.0 Utilities: IMPORT-EXPORT

Fix for BUG 904970

If a transport of a tablespace fails because file permissions do not allow it, or the datafile is also mounted on another database, the internal error 25012 is raised. A more informative error message is given in 8.1.6.

Fix for BUG 871066

Error ORA-1455 may be raised during export of large analyzed objects.