Rem 
Rem $Header: lobdemo.sql 17-jul-96.20:45:55 mchien Exp $
Rem
Rem lobdemo.sql
Rem 
Rem  Copyright (c) Oracle Corporation 1995, 1996, 1997. All Rights Reserved.
Rem
Rem    NAME
Rem      lobdemo.sql - PL/SQL procedures demonstrating LOB access
Rem
Rem    DESCRIPTION
Rem      Demonstration/Test program for use of Oracle8 Large Objects
Rem
Rem    NOTES
Rem      . This test/demo uses dbms_output and utl_raw packages
Rem      . The INSERT statements currently outside the tst_routines need to
Rem        be moved into the tst_routine once the bug is solved.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cchau       08/18/97 - enable dictionary protection
Rem    ramkrish    11/05/96 - addnal tests + misc cleanup
Rem    ramkrish    11/04/96 - interface chg
Rem    mchien      07/17/96 - created
Rem

#initialize environment
set echo on
connect internal
drop user gpld cascade;

#create a new user
grant connect, resource to gpld identified by gpld;

#start session as new user
connect gpld/gpld
set serveroutput on

#create test table
create table gpldt1(c1 number, c2 blob, c3 clob);

#-----------------------
# TEST DBMS_LOB ROUTINES
#-----------------------

#------------
# TEST APPEND
#------------
create or replace procedure tst_append is
  dblob blob;
  sblob blob;
  dclob clob;
  sclob clob;
  spos  integer;
  amt   binary_integer;
  bufb  raw(20);
  bufc  varchar2(20);
begin
  dbms_output.put_line('--------------- APPEND Begin ---------------');

  /* test append for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 1 for update;
  select c2 into sblob from gpldt1 where c1 = 2;
  dbms_lob.append(dblob, sblob);

  /* test append for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 1 for update;
  select c3 into sclob from gpldt1 where c1 = 2;
  dbms_lob.append(dclob, sclob);

  /* end Xn */
  commit;

  /* test sanity of APPEND operation */
  amt := 20;
  spos := 1;
  select c2 into sblob from gpldt1 where c1 = 1;
  dbms_lob.read(sblob, amt, spos, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  select c3 into sclob from gpldt1 where c1 = 1;
  dbms_lob.read(sclob, amt, spos, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- APPEND End   ---------------');
  dbms_output.put_line(' ');
  exception
    when no_data_found
    then dbms_output.put_line('APPEND operation error');  
end tst_append;
/
show errors procedure tst_append;
/
insert into gpldt1 values (1,utl_raw.cast_to_raw('0123456789'),'abcdefghij');
insert into gpldt1 values (2,utl_raw.cast_to_raw('1234567890'),'bcdefghija');
commit;
begin
 tst_append;
end;
/

#-------------
# TEST COMPARE
#-------------
create or replace procedure tst_compare (amt in integer) is
  dblob blob;
  sblob blob;
  dclob clob;
  sclob clob;
  dpos  integer;
  spos  integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- COMPARE Begin ---------------');

  /* test COMPARE for BLOBs */
  dpos := 1; spos := 1; 
  select c2 into dblob from gpldt1 where c1 = 3;
  select c2 into sblob from gpldt1 where c1 = 4;
  ret := -1;
  ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos);
  dbms_output.put_line('Return value for BLOB: ' || ret);

  /* test COMPARE for CLOBs */
  dpos := 1; spos := 1; 
  select c3 into dclob from gpldt1 where c1 = 3;
  select c3 into sclob from gpldt1 where c1 = 4;
  ret := -1;
  ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos);
  dbms_output.put_line('Return value for CLOB: ' || ret);

  dbms_output.put_line('--------------- COMPARE End   ---------------');
  dbms_output.put_line(' ');
end tst_compare;
/
show errors procedure tst_compare;
/
# Case 1: Identical Data
insert into gpldt1 values (3,utl_raw.cast_to_raw('2345678901'),'abcdefghij');
insert into gpldt1 values (4,utl_raw.cast_to_raw('2345678901'),'abcdefghij');
begin
 tst_compare(10);
end;
/
rollback;
# Case 2: lob 1 smaller than lob 2 - differing in the first byte
insert into gpldt1 values (3,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
insert into gpldt1 values (4,utl_raw.cast_to_raw('4567890123'),'efghijabcd');
begin
 tst_compare(10);
end;
/
rollback;
# Case 3: lob 1 greater than lob 2 - differing in the first byte
insert into gpldt1 values (3,utl_raw.cast_to_raw('4567890123'),'efghijabcd');
insert into gpldt1 values (4,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
begin
 tst_compare(10);
end;
/
rollback;
# Case 4: lob 1 greater than lob 2 - but with lob 2 being empty
insert into gpldt1 values (3,utl_raw.cast_to_raw('4567890123'),'efghijabcd');
insert into gpldt1 values (4, EMPTY_BLOB(), EMPTY_CLOB());
begin
 tst_compare(10);
end;
/
rollback;
# Case 5: enter the real world - identical lobs greater than 2K in size
insert into gpldt1 values (3, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'efghijabcd'));
insert into gpldt1 values (4, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'efghijabcd'));
begin
 tst_compare(3000);
end;
/
rollback;
# Case 6: enter the real world - lob1 < lob2 - lobs greater than 2K in size
insert into gpldt1 values (3, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'cdefghijab'));
insert into gpldt1 values (4, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'efghijabcd'));
begin
 tst_compare(3000);
end;
/
rollback;
# Case 7: enter the real world - lob1 > lob2 - lobs greater than 2K in size
insert into gpldt1 values (4, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'cdefghijab'));
insert into gpldt1 values (3, utl_raw.cast_to_raw('4567890123'),
                           lpad('efghijabcd', 3000, 'efghijabcd'));
begin
 tst_compare(3000);
end;
/
commit;

#----------
# TEST COPY
#----------
create or replace procedure tst_copy 
 (amt in out integer, dpos in integer, spos in integer, ramt in out integer) is
  dblob blob;
  sblob blob;
  dclob clob;
  sclob clob;
  bufb  raw(30);
  bufc  varchar2(30);
begin
  dbms_output.put_line('--------------- COPY Begin ---------------');

  /* test COPY for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 5 for update;
  select c2 into sblob from gpldt1 where c1 = 6;
  dbms_lob.copy(dblob, sblob, amt, dpos, spos);
  commit;
    
  /* test COPY for CLOBs */ 
  select c3 into dclob from gpldt1 where c1 = 5 for update;
  select c3 into sclob from gpldt1 where c1 = 6;
  dbms_lob.copy(dclob, sclob, amt, dpos, spos);
  commit;

  /* check sanity of COPY operation */
  select c2 into sblob from gpldt1 where c1 = 5;
  dbms_lob.read(sblob, ramt, 1, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  select c3 into sclob from gpldt1 where c1 = 5;
  dbms_lob.read(sclob, ramt, 1, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- COPY End   ---------------');
  dbms_output.put_line(' ');
  exception
    when no_data_found
    then dbms_output.put_line('no_data_found error in COPY');
end tst_copy;
/
show errors procedure tst_copy;
/
insert into gpldt1 values (5,utl_raw.cast_to_raw('4567890123'),'efghijabcd');
insert into gpldt1 values (6,utl_raw.cast_to_raw('5678901234'),'fghijabcde');
commit;
#Case 1: Append lob2 to lob1
declare
 amt integer;
 ramt integer;
begin
 amt := 10; ramt := 20;
 tst_copy(amt, 11, 1, ramt);
end;
/
#Case 2: Copy 10 bytes of lob2 over 6-EoL bytes of lob1
declare
 amt integer;
 ramt integer;
begin
 amt := 10; ramt := 15;
 tst_copy(amt, 6, 1, ramt);
end;
/
#Case 3: Copy 10 bytes of lob2 starting from 16th position in lob1,
#        creating a hole of 5 bytes.
declare
 amt integer;
 ramt integer;
begin
 amt := 10; ramt := 25;
 tst_copy(amt, 16, 1, ramt);
end;
/

#-----------
# TEST ERASE
#-----------
create or replace procedure tst_erase is
  dblob blob;
  dclob clob;
  amt   integer;
  pos   integer;
  bufb  raw(10);
  bufc  varchar2(10);
  amtread integer;
  posread integer;   
  begin
  dbms_output.put_line('--------------- ERASE Begin ---------------');

  /* test ERASE for BLOBs */
  amt := 5; pos := 3;
  select c2 into dblob from gpldt1 where c1 = 7 for update;
  dbms_lob.erase(dblob, amt, pos);

  /* test ERASE for CLOBs */ 
  select c3 into dclob from gpldt1 where c1 = 7 for update;
  dbms_lob.erase(dclob, amt, pos);

  /* end Xn */
  commit;

  /* check the sanity of ERASE operation */
  
  /* fill in the hole so it can be printable */
  bufb := utl_raw.cast_to_raw('XXXXX');
  bufc := 'XXXXX';

  select c2 into dblob from gpldt1 where c1 = 7 for update; 
  dbms_lob.write(dblob, amt, pos, bufb);
  select c2 into dblob from gpldt1 where c1 = 7; 
  amtread := 10;
  posread := 1;
  dbms_lob.read(dblob, amtread, posread, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  select c3 into dclob from gpldt1 where c1 = 7 for update;
  dbms_lob.write(dclob, amt, pos, bufc);
  select c3 into dclob from gpldt1 where c1 = 7; 
  dbms_lob.read(dclob, amtread, posread, bufc);

  dbms_output.put_line('Result CLOB: ' || bufc); 

  dbms_output.put_line('--------------- ERASE End   ---------------');
  dbms_output.put_line(' ');
  exception
    when no_data_found
    then dbms_output.put_line('COPY operation has some problems');
end tst_erase;
/
show errors procedure tst_erase;
/
insert into gpldt1 values (7,utl_raw.cast_to_raw('6789012345'),'ghijabcdef');
commit;
begin
 tst_erase;
end;
/

#-----------
# TEST INSTR
#-----------
create or replace procedure tst_instr 
 (occ in integer, pos in integer, pat in integer) is
  dblob blob;
  dclob clob;
  bufb  raw(5);
  bufc  varchar2(5);
  retb  integer;
  retc  integer;
begin
  dbms_output.put_line('--------------- INSTR Begin ---------------');
 
  if (pat = 0) then
    begin
     bufb := utl_raw.cast_to_raw('01234'); 
     bufc := 'abcde'; 
    end;
  else
    begin
     bufb := utl_raw.cast_to_raw('43210');
     bufc := 'edcba';
    end;
  end if;
 /* test INSTR for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 8;
  
  retb := dbms_lob.instr(dblob, bufb, pos, occ);
  dbms_output.put_line('Pos: ' || retb);
 
  /* test INSTR for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 8;
   
  retc := dbms_lob.instr(dclob, bufc, pos, occ);
  dbms_output.put_line('Pos: ' || retc);
  dbms_output.put_line('--------------- INSTR End   ---------------');
  dbms_output.put_line(' ');
end tst_instr;
/
show errors procedure tst_instr;
/
insert into gpldt1 values (8,utl_raw.cast_to_raw('7890123456'),'hijabcdefg');
#Case 1: first occurrence starting from first byte
declare
 occ integer;
 pos integer;
begin
 occ := 1; pos := 1;
 tst_instr(occ, pos, 0);
end;
/
 #Case 2: starting offset at last byte of LOB
declare
 occ integer;
 pos integer;
begin
 occ := 1; pos := 10;
 tst_instr(occ, pos, 0);
end;
/
  #Case 3: occurrence not present
declare
 occ integer;
 pos integer;
begin
 occ := 2; pos := 1;
 tst_instr(occ, pos, 0); 
end;
/
#Case 4: no matching string 
declare
 occ integer;
 pos integer;
begin
 occ := 1; pos := 1;
 tst_instr(occ, pos, 1);
end;
/
rollback;
insert into gpldt1 values (8,utl_raw.cast_to_raw('0123401234'),'abcdeabcde');
commit;
#Case 5: occurrence > 1
declare
 occ integer;
 pos integer;
begin 
 occ := 2; pos := 1;
 tst_instr(occ, pos, 0);
end;
/

#------------
# TEST LENGTH
#------------
create or replace procedure tst_length is
  dblob blob;
  dclob clob;
  lenb  integer;
  lenc  integer;
  c4val varchar2(32);
begin
  dbms_output.put_line('--------------- LENGTH Begin ---------------');

  /* test LENGTH for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 9;
  lenb := dbms_lob.getlength(dblob);
  dbms_output.put_line('Length of BLOB is: ' || lenb);
 
  /* test LENGTH for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 9;
  lenc := dbms_lob.getlength(dclob);
  dbms_output.put_line('Length of CLOB is: ' || lenc);
 
  dbms_output.put_line('--------------- LENGTH End   ---------------');
  dbms_output.put_line(' ');
end tst_length;
/
show errors procedure tst_length;
/
insert into gpldt1 values (9,utl_raw.cast_to_raw('8901234567'),'ijabcdefgh');
commit;
begin
 tst_length;
end;
/

#----------
# TEST READ
#----------
create or replace procedure tst_read(amt in out integer, pos in integer) is
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
begin
  dbms_output.put_line('--------------- READ Begin ---------------');
 
  /* test READ for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 10;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('BLOB Read buffer: '|| utl_raw.cast_to_varchar2(bufb));

  /* test READ for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 10;
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('CLOB Read buffer: '|| bufc);

  dbms_output.put_line('--------------- READ End   ---------------');
  dbms_output.put_line(' ');
  exception
    when dbms_lob.invalid_argval
    then dbms_output.put_line('Error in argument value');
    when no_data_found
    then dbms_output.put_line('End of LOB reached');

end tst_read;
/
show errors procedure tst_read;
/
insert into gpldt1 values (10,utl_raw.cast_to_raw('9012345678'),'jabcdefghi');
commit;
#Case 1: Read 10 bytes starting from offset 1
declare 
 amt integer;
 pos integer;
begin
 amt := 10; pos := 1;
 tst_read(amt, pos);
end;
/
#Case 2: Read 10 bytes starting from offset 5
declare 
 amt integer;
 pos integer;
begin
 amt := 10; pos := 5;
 tst_read(amt, pos);
end;
/
#Case 3: Read 10 bytes starting from offset 10 - last byte
declare 
 amt integer;
 pos integer;
begin
 amt := 10; pos := 10;
 tst_read(amt, pos);
end;
/ 
#Case 4: Read 10 bytes starting from offset 11 - end of LOB
declare 
 amt integer;
 pos integer;
begin
 amt := 10; pos := 11;
 tst_read(amt, pos);
end;
/ 
#Case 5: Read 15 bytes into a 10 byte buffer - error
declare 
 amt integer;
 pos integer;
begin
 amt := 15; pos := 1;
 tst_read(amt, pos);
end;
/

#------------
# TEST SUBSTR
#------------
create or replace procedure tst_substr (amt in integer, pos in integer) is 
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
begin
  dbms_output.put_line('--------------- SUBSTR Begin ---------------');

  /* test SUBSTR for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 11;
  bufb := '';
  bufb := dbms_lob.substr(dblob, amt, pos);
  dbms_output.put_line('SUBSTR BLOB: ' || utl_raw.cast_to_varchar2 (bufb));

  /* test SUBSTR for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 11;
  bufc := '';
  bufc := dbms_lob.substr(dclob, amt, pos);
  dbms_output.put_line('SUBSTR CLOB: ' || bufc);

  dbms_output.put_line('--------------- SUBSTR End   ---------------');
  dbms_output.put_line(' ');
end tst_substr;
/
show errors procedure tst_substr;
/
insert into gpldt1 values (11,utl_raw.cast_to_raw('0123456789'),'abcdefghij');
# Case 1: Substr 10 bytes starting from offset 1
declare
 amt integer;
 pos integer;
begin
 amt := 10; pos := 1;
 tst_substr(amt, pos);
end;
/
# Case 2: Substr 10 bytes starting from offset 5
declare
 amt integer;
 pos integer;
begin
 amt := 10; pos := 5;
 tst_substr(amt, pos);
end;
/
# Case 3: Substr 10 bytes starting from offset 10 - last byte
declare
 amt integer;
 pos integer;
begin
 amt := 10; pos := 10;
 tst_substr(amt, pos);
end;
/
# Case 4: Substr 10 bytes starting from offset 11 - end of LOB
declare
 amt integer;
 pos integer;
begin
 amt := 10; pos := 11;
 tst_substr(amt, pos);
end;
/
rollback;
insert into gpldt1 values (11,utl_raw.cast_to_raw('012345678901234'),
                           'abcdefghijabcde');
commit;
declare
 amt integer;
 pos integer;
begin
 -- Case 5: Substr 15 bytes into a 10 byte buffer - PL/SQL error
 amt := 15; pos := 1;  
 tst_substr(amt, pos);
end;
/

#----------
# TEST TRIM
#----------
create or replace procedure tst_trim (nlen in out integer) is
  dblob blob;
  dclob clob;
  bufb  raw(5);
  bufc  varchar2(5);
begin
  dbms_output.put_line('--------------- TRIM Begin ---------------');

  /* test TRIM for BLOBs */
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  dbms_lob.trim(dblob, nlen);
 
  /* test TRIM for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 12 for update;
  dbms_lob.trim(dclob, nlen);
 
  /* end Xn */
  commit;

  /* check sanity of TRIM operation */
  select c2 into dblob from gpldt1 where c1 = 12;
  dbms_lob.read(dblob, nlen, 1, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  select c3 into dclob from gpldt1 where c1 = 12;
  dbms_lob.read(dclob, nlen, 1, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- TRIM End   ---------------');
  dbms_output.put_line(' ');
end tst_trim;
/
show errors procedure tst_trim;
/
insert into gpldt1 values (12,utl_raw.cast_to_raw('1234567890'),'bcdefghija');
commit;
# Case 1: trim to a length of 5 bytes
declare
 nlen integer;
begin
 nlen := 5;
 tst_trim(nlen);
end;
/
delete from gpldt1 where c1 = 12;
insert into gpldt1 values (12,utl_raw.cast_to_raw('1234567890'),'bcdefghija');
# Case 2: trim to a length of 15 bytes, 5 greater than what is present
declare
 nlen integer;
begin
 nlen := 15;
 tst_trim(nlen);
end;
/

#-----------
# TEST WRITE
#-----------
create or replace procedure tst_write 
 (amt in out integer, pos in integer, pat in integer) is
  dblob blob;
  dclob clob;
  bufb  raw(20);
  bufc  varchar2(20);
  ramt integer;
begin
  dbms_output.put_line('--------------- WRITE Begin ---------------');

  if (pat = 1) then
  /*  Cases 1 - 3, and 5 */
    begin
      bufb := utl_raw.cast_to_raw('65432');
      bufc := 'gfedc';
    end;
  else begin
  /* Case 4: */
      bufb := utl_raw.cast_to_raw('0123456789012345678901234');
      bufc := 'abcdefghijabcdefghijabcde';
    end;
  end if;  
  /* test WRITE for BLOBs */

  select c2 into dblob from gpldt1 where c1 = 13 for update;
  dbms_lob.write(dblob, amt, pos, bufb);
 
  /* test WRITE for CLOBs */
  select c3 into dclob from gpldt1 where c1 = 13 for update;
  dbms_lob.write(dclob, amt, pos, bufc);

  /* end Xn */
  commit;

  /* check sanity of WRITE operation */
   
  select c2 into dblob from gpldt1 where c1 = 13;
  ramt := dbms_lob.getlength(dblob);
  dbms_lob.read(dblob, ramt, 1, bufb);
  if (pos < 12) then 
    dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));
  end if;
  dbms_output.put_line('Length is: ' || ramt);

  select c3 into dclob from gpldt1 where c1 = 13;
  ramt := dbms_lob.getlength(dclob);
  dbms_lob.read(dclob, ramt, 1, bufc);
  if (pos < 12) then 
     dbms_output.put_line('Result CLOB: ' || bufc);
  end if;
  dbms_output.put_line('Length is: ' || ramt);

  dbms_output.put_line('--------------- WRITE End   ---------------');
  dbms_output.put_line(' ');
end tst_write;
/
show errors procedure tst_write;
/
insert into gpldt1 values (13,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
commit;
# Case 1: write 5 bytes starting from 6th byte
declare
 amt integer;
 pos integer;
begin  
 amt := 5; pos := 6;
 tst_write(amt, pos, 1);
end;
/
delete from gpldt1 where c1 = 13;
insert into gpldt1 values (13,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
commit;
# Case 2: append 5 bytes starting from the 11th byte
declare
 amt integer;
 pos integer;
begin 
 amt := 5; pos := 11;
 tst_write(amt, pos, 1);
end;
/
delete from gpldt1 where c1 = 13;
insert into gpldt1 values (13,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
commit;
# Case 3: append 5 bytes starting from the 16th byte - 5 byte hole
declare
 amt integer;
 pos integer;
begin 
 amt := 5; pos := 16;
 tst_write(amt, pos, 1);
end;
/
declare
 amt integer;
 pos integer;
begin
 -- Now fill up the 5 byte hole that starts from the 11th byte 
 amt := 5; pos := 11;
 tst_write(amt, pos, 1);
end;
/
delete from gpldt1 where c1 = 13;
insert into gpldt1 values (13,utl_raw.cast_to_raw('2345678901'),'cdefghijab');
commit;
# Case 4: write 25 bytes from a 20 byte buffer - PL/SQL error
declare
 amt integer;
 pos integer;
begin  
 amt := 25; pos := 1;
 tst_write(amt, pos, 0);
end;
/
# Case 5: specify 25 bytes, but provide only 5 bytes from a 20 byte buffer
declare
 amt integer;
 pos integer;
begin 
 amt := 25; pos := 1;
 tst_write(amt, pos, 1);
end;
/

#--------------------
# TEST DATA_INTEGRITY
#--------------------
create or replace procedure tst_data_integrity is
  dblob blob;
  dclob clob;
  amt   integer;
  pos   integer;
  bufb  raw(10);
  bufc  varchar2(10);
begin
  dbms_output.put_line('--------------- DATA_INTEGRITY Begin ---------------');

  /* WRITE data begin */
  amt := 5;
  pos := 1;
  select c2 into dblob from gpldt1 where c1 = 14 for update;
  bufb := utl_raw.cast_to_raw('98765');
  dbms_lob.write(dblob, amt, pos, bufb);
 
  select c3 into dclob from gpldt1 where c1 = 14 for update;
  bufc := 'jihgf';
  dbms_lob.write(dclob, amt, pos, bufc);

  /* READ data begin */
  amt := 10;
  pos := 1;
  select c2, c3 into dblob, dclob from gpldt1 where c1 = 14;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('Output BLOB 14: ' || utl_raw.cast_to_varchar2(bufb));
 
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('Output CLOB 14: ' || bufc);

  dbms_output.put_line('BEFORE UPDATE');
  select c2, c3 into dblob, dclob from gpldt1 where c1 = 11 for update;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('Output BLOB 11: ' || utl_raw.cast_to_varchar2(bufb));
 
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('Output CLOB 11: ' || bufc);

  select c2, c3 into dblob, dclob from gpldt1 where c1 = 14;
  update gpldt1 set c2 = dblob, c3 = dclob where c1 = 11;
  commit; 

  dbms_output.put_line('AFTER UPDATE1');
  select c2, c3 into dblob, dclob from gpldt1 where c1 = 11;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('Output BLOB 11: ' || utl_raw.cast_to_varchar2(bufb));
 
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('Output CLOB 11: ' || bufc);

  update gpldt1 set c2 = (select c2 from gpldt1 where c1 = 14),
                    c3 = (select c3 from gpldt1 where c1 = 14)
          where c1 = 11;
  commit; 

  dbms_output.put_line('AFTER UPDATE2');
  select c2 into dblob from gpldt1 where c1 = 11;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('Output BLOB 11: ' || utl_raw.cast_to_varchar2(bufb));
 
  select c3 into dclob from gpldt1 where c1 = 11;
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('Output CLOB 11: ' || bufc);

  dbms_output.put_line('--------------- DATA_INTEGRITY End   ---------------');
  dbms_output.put_line(' ');
end tst_data_integrity;
/
show errors procedure tst_data_integrity;
/
insert into gpldt1 values (14,utl_raw.cast_to_raw('3456789012'),'defghijabc');
commit;
begin
 tst_data_integrity;
end;
/

#----------------
# TEST EXCEPTIONS
#----------------
declare
  dblob blob;
  sblob blob;
begin
  dbms_output.put_line('--------------- exception append --------------');
  dblob := NULL; sblob := NULL;
  dbms_lob.append(dblob, sblob);
  exception
    when value_error
    then dbms_output.put_line('APPEND: Value Error');
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception compare -------------');
  dblob := NULL; sblob := NULL; amt := 1; posd := 1; poss := 1;
  ret := 1;
  ret := dbms_lob.compare(dblob, sblob, amt, posd, poss);
  if (ret != NULL)
  then
    dbms_output.put_line('COMPARE: Incorrect error - ret: ' || ret);
  else
    dbms_output.put_line('COMPARE: Invalid Inputs' || ret);
  end if;
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception compare -------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := 0; posd := 0; poss := 0;
  ret := dbms_lob.compare(dblob, sblob, amt, posd, poss);
  if (ret != NULL)
  then
    dbms_output.put_line('COMPARE: Incorrect error - ret: ' || ret);
  else
    dbms_output.put_line('COMPARE: Invalid Inputs' || ret);
  end if;
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception compare -------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := -1; posd := -1; poss := -1;
  ret := dbms_lob.compare(dblob, sblob, amt, posd, poss);
  if (ret != NULL)
  then
    dbms_output.put_line('COMPARE: Incorrect error - ret: ' || ret);
  else
    dbms_output.put_line('COMPARE: Invalid Inputs' || ret);
  end if;
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception compare -------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := 4294967295+1; posd := 4294967295+1; poss := 4294967295+1;
  ret := dbms_lob.compare(dblob, sblob, amt, posd, poss);
  if (ret != NULL)
  then
    dbms_output.put_line('COMPARE: Incorrect error - ret: ' || ret);
  else
    dbms_output.put_line('COMPARE: Invalid Inputs' || ret);
  end if;
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception copy -------------');
  dblob := NULL; sblob := NULL; amt := 1; posd := 1; poss := 1;
  dbms_lob.copy(dblob, sblob, amt, posd, poss);
  exception
  when value_error
  then dbms_output.put_line('COPY: Value Error');
  when dbms_lob.invalid_argval
  then dbms_output.put_line('COPY: Invalid argument value');
  when dbms_lob.access_error
  then dbms_output.put_line('COPY: Data overrun error');
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception copy -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := NULL; posd := NULL; poss := NULL;
  dbms_lob.copy(dblob, sblob, amt, posd, poss);
  exception
  when value_error
  then dbms_output.put_line('COPY: Value Error');
  when dbms_lob.invalid_argval
  then dbms_output.put_line('COPY: Invalid argument value');
  when dbms_lob.access_error
  then dbms_output.put_line('COPY: Data overrun error');
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception copy -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := 0; posd := 0; poss := 0;
  dbms_lob.copy(dblob, sblob, amt, posd, poss);
  exception
  when value_error
  then dbms_output.put_line('COPY: Value Error');
  when dbms_lob.invalid_argval
  then dbms_output.put_line('COPY: Invalid argument value');
  when dbms_lob.access_error
  then dbms_output.put_line('COPY: Data overrun error');
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception copy -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := -1; posd := -1; poss := -1;
  dbms_lob.copy(dblob, sblob, amt, posd, poss);
  exception
  when value_error
  then dbms_output.put_line('COPY: Value Error');
  when dbms_lob.invalid_argval
  then dbms_output.put_line('COPY: Invalid argument value');
  when dbms_lob.access_error
  then dbms_output.put_line('COPY: Data overrun error');
end;
/
declare
  dblob blob;
  sblob blob;
  amt integer;
  posd integer;
  poss integer;
  ret  integer;
begin
  dbms_output.put_line('--------------- exception copy -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  select c2 into sblob from gpldt1 where c1 = 13;
  amt := 4294967295+1; posd := 4294967295+1; poss := 4294967295+1;
  dbms_lob.copy(dblob, sblob, amt, posd, poss);
  exception
  when value_error
  then dbms_output.put_line('COPY: Value Error');
  when dbms_lob.invalid_argval
  then dbms_output.put_line('COPY: Invalid argument value');
  when dbms_lob.access_error
  then dbms_output.put_line('COPY: Data overrun error');
end;
/
declare
  dblob blob;
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception erase ----------------');
  dblob := NULL; amt := 1; pos := 1;
  dbms_lob.erase(dblob, amt, pos);
  exception
    when value_error
    then dbms_output.put_line('ERASE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('ERASE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('ERASE: Data overrun error');
end;
/
declare
  dblob blob;
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception erase ----------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  amt := NULL; pos := NULL;
  dbms_lob.erase(dblob, amt, pos);
  exception
    when value_error
    then dbms_output.put_line('ERASE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('ERASE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('ERASE: Data overrun error');
end;
/
declare
  dblob blob;
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception erase ----------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  amt := 0; pos := 0;
  dbms_lob.erase(dblob, amt, pos);
  exception
    when value_error
    then dbms_output.put_line('ERASE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('ERASE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('ERASE: Data overrun error');
end;
/
declare
  dblob blob;
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception erase ----------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  amt := -1; pos := -1;
  dbms_lob.erase(dblob, amt, pos);
  exception
    when value_error
    then dbms_output.put_line('ERASE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('ERASE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('ERASE: Data overrun error');
end;
/
declare
  dblob blob;
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception erase ----------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  amt := 4294967295+1; pos := 4294967295+1;
  dbms_lob.erase(dblob, amt, pos);
  exception
    when value_error
    then dbms_output.put_line('ERASE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('ERASE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('ERASE: Data overrun error');
end;
/
declare
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
  pos   integer;
  occ   integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception instr ------------');
  dblob := NULL; dclob := NULL;
  bufb := '12345'; bufc := 'abcde'; pos := 1; occ := 1;
  ret := dbms_lob.instr(dblob, bufb, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
  ret := dbms_lob.instr(dclob, bufc, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
end;
/
declare
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
  pos   integer;
  occ   integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception instr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c3 into dclob from gpldt1 where c1 = 12;
  bufb := '12345'; bufc := 'abcde'; occ := NULL; pos := NULL;
  ret := dbms_lob.instr(dblob, bufb, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
  ret := dbms_lob.instr(dclob, bufc, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
end;
/
declare
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
  pos   integer;
  occ   integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception instr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c3 into dclob from gpldt1 where c1 = 12;
  bufb := '12345'; bufc := 'abcde'; occ := 0; pos := 0;
  ret := dbms_lob.instr(dblob, bufb, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
  ret := dbms_lob.instr(dclob, bufc, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
end;
/
declare
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
  pos   integer;
  occ   integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception instr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c3 into dclob from gpldt1 where c1 = 12;
  bufb := '12345'; bufc := 'abcde';  occ := -1; pos := -1;
  ret := dbms_lob.instr(dblob, bufb, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
  ret := dbms_lob.instr(dclob, bufc, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
end;
/
declare
  dblob blob;
  dclob clob;
  bufb  raw(10);
  bufc  varchar2(10);
  pos   integer;
  occ   integer;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception instr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  select c3 into dclob from gpldt1 where c1 = 12;
  bufb := '12345'; bufc := 'abcde'; occ := 4294967295+1; pos := 4294967295+1;
  ret := dbms_lob.instr(dblob, bufb, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
  ret := dbms_lob.instr(dclob, bufc, pos, occ);
  if (ret != NULL)
  then
    dbms_output.put_line('INSTR: Incorrect error handling');
  else
    dbms_output.put_line('INSTR: Invalid Inputs');
  end if;
end;
/
declare
  dblob blob;
  dclob clob;
  ret   integer;
begin
  dbms_output.put_line('--------------- exception length ------------');
  dblob := NULL; dclob := NULL;
  ret := dbms_lob.getlength(dblob);
  if (ret != NULL)
  then
    dbms_output.put_line('LENGTH: Incorrect error handling');
  else
    dbms_output.put_line('LENGTH: NULL Input');
  end if;       
  ret := dbms_lob.getlength(dclob);
  if (ret != NULL)
  then
    dbms_output.put_line('LENGTH: Incorrect error handling');
  else
    dbms_output.put_line('LENGTH: NULL Input');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  dblob := NULL;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := NULL; pos := NULL;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 0; pos := 0;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := -1; pos := -1;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 32767+1; pos := 1;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 1; pos := 4294967295+1;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception read --------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 1; pos := 4294967295; bufb := NULL;
  dbms_lob.read(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('READ: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('READ: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('READ: Data overrun error');      
end;
/
# Empty LOB in SUBSTR
select c1, dbms_lob.substr(empty_blob()), dbms_lob.substr(empty_clob())
  from gpldt1;

declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  dblob := NULL; amt := 1; pos := 1;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := NULL; pos := NULL; 
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 0; pos := 0;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := -1; pos := -1;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 4294967295+1; pos := 4294967295+1;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 4294967295; pos := 4294967295; bufb := NULL;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception substr ------------');
  select c2 into dblob from gpldt1 where c1 = 12;
  amt := 4294967295; pos := 4294967295; bufb := NULL;
  bufb := dbms_lob.substr(dblob, amt, pos);
  if (bufb != NULL)
  then
    dbms_output.put_line('SUBSTR: Incorrect error handling');
  else
    dbms_output.put_line('SUBSTR: Invalid Inputs');
  end if;       
end;
/
declare
  dblob blob;
  len   integer;
begin
  dbms_output.put_line('--------------- exception trim ------------');
  dblob := NULL; len := 1;
  dbms_lob.trim(dblob, len);
  exception
    when value_error
    then dbms_output.put_line('TRIM: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('TRIM: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('TRIM: Data overrun error');
end;
/
declare
  dblob blob;
  len   integer;
begin
  dbms_output.put_line('--------------- exception trim ------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  len := NULL;
  dbms_lob.trim(dblob, len);
  exception
    when value_error
    then dbms_output.put_line('TRIM: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('TRIM: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('TRIM: Data overrun error');
end;
/
declare
  dblob blob;
  len   integer;
begin
  dbms_output.put_line('--------------- exception trim ------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  len := 0;
  dbms_lob.trim(dblob, len);
  exception
    when value_error
    then dbms_output.put_line('TRIM: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('TRIM: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('TRIM: Data overrun error');
end;
/
declare
  dblob blob;
  len   integer;
begin
  dbms_output.put_line('--------------- exception trim ------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  len := -1;
  dbms_lob.trim(dblob, len);
  exception
    when value_error
    then dbms_output.put_line('TRIM: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('TRIM: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('TRIM: Data overrun error');
end;
/
declare
  dblob blob;
  len   integer;
begin
  dbms_output.put_line('--------------- exception trim ------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  len := 4294967295+1;
  dbms_lob.trim(dblob, len);
  exception
    when value_error
    then dbms_output.put_line('TRIM: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('TRIM: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('TRIM: Data overrun error');
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  dblob := NULL; bufb := '12345'; pos := 1; amt := 1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := '12345'; pos := NULL; amt := NULL;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := '12345'; pos := 0; amt := 0;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := '12345'; pos := -1; amt := -1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := '12345'; amt := 1; pos := 4294967295+1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := '12345'; amt := 32767+1; pos := 1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := ''; amt := 1; pos := 1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/
declare
  dblob blob;
  bufb  raw(10);
  amt   integer;
  pos   integer;
begin
  dbms_output.put_line('--------------- exception write -------------');
  select c2 into dblob from gpldt1 where c1 = 12 for update;
  bufb := NULL; amt := 1; pos := 1;
  dbms_lob.write(dblob, amt, pos, bufb);
  exception
    when value_error
    then dbms_output.put_line('WRITE: Value Error');
    when dbms_lob.invalid_argval
    then dbms_output.put_line('WRITE: Invalid argument value');
    when dbms_lob.access_error
    then dbms_output.put_line('WRITE: Data overrun error');      
end;
/

####################
# TERMINATE and EXIT
####################
connect internal
drop user gpld cascade;
set serveroutput off;
set echo off;
exit;
