Exception Handling Behavior With DBMS_LOB.READ() Between 9.2 AND 11G (Doc ID 970516.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Symptoms

In PL/SQL, if the type of an IN/OUT or OUT variable isn't _exactly_ the same as the parameter declaration, the output value will be lost if an exception is thrown where a NOCOPY hint is used.

This is causing a behavioral difference with the DBMS_LOB.READ() call across versions.
The following testcase scenario demonstrates the resulting value of "0" where the value is lost depending on the datatype of the variable and the RDBMS version.

set serveroutput on;

drop table foo;
create table foo (k number, c clob);
insert into foo values (1, 'AAAAAAAAAAAAAAAAAAAAAAAAA');
commit;
--a 
declare
 l clob;
 v varchar2(100);
 a number; -- use NUMBER
 begin
 select c into l from foo where k = 1;
 a := 100;
 dbms_lob.read(l, a, 50, v);
 dbms_output.put_line('length: ' || a);
 exception
 when no_data_found then
 dbms_output.put_line('error length: ' || a);
end;
/
--b
declare
 l clob;
 v varchar2(100);
 a integer; -- use INTEGER
 begin
 select c into l from foo where k = 1;
 a := 100;
 dbms_lob.read(l, a, 50, v);
 dbms_output.put_line('length: ' || a);
 exception
 when no_data_found then
 dbms_output.put_line('error length: ' || a);
end;
/
--c
declare
 l clob;
 v varchar2(100);
  a binary_integer; -- use BINARY_INTEGER
 begin
 select c into l from foo where k = 1;
 a := 100;
 dbms_lob.read(l, a, 50, v);
 dbms_output.put_line('length: ' || a);
 exception
 when no_data_found then
 dbms_output.put_line('error length: ' || a);
end;
/

Results in 9.2:
a.  error length: 100
b.  error length: 100
c.  error length: 0

Results in 11g:
a.  error length: 100
b.  error length: 0
c.  error length: 100

Summary of Results:
In 9i, where the amount parameter of dbms_lob.read was defined of type BINARY_INTEGER, the variable defined as type binary_integer zeroed out.

In 11g, where the amount parameter of dbms_lob.read is defined of type INTEGER, the variable defined as type integer zeros out.

Changes

The behavior was flagged because there was a change is in the definition of the amount parameter, the second formal parameter, of the DBMS_LOB.read().  In 9i, the definition was of type binary_integer.  Starting in 10g, the parameter was declared of type integer.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms