Rem
Rem $Header: lbsdemo.sql 23-feb-97.08:03:33 ramkrish Exp $
Rem
Rem lbsdemo.sql
Rem
Rem  Copyright (c) Oracle Corporation 1997. All Rights Reserved.
Rem
Rem    NAME
Rem      lbsdemo.sql - LOB Buffering Subsystem Demo
Rem
Rem    DESCRIPTION
Rem      This demo file provides examples of reading, writing and flushing
Rem      LOBs while accessing them in the buffering mode.
Rem
Rem    NOTES
Rem      . KOLBBLKSZ is 20 bytes, and # buffers is 5
Rem      . For each test, you have to completely exit svrmgrl, reenter,
Rem        connect sys/knl_test7 as sysdba;
Rem        alone. This problem will go once we change the malloc scheme
Rem        to UGA based (even called based if required).
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cchau       08/18/97 - enable dictionary protection
Rem    ramkrish    03/26/97 - Add rdwr, multiple locator tests
Rem    ramkrish    03/26/97 - chg flushbuffers->flushbuffer + other misc
Rem    ramkrish    03/24/97 - test new LRU semantics
Rem    ramkrish    02/23/97 - Creation
Rem
#initialize environment
set echo on
connect internal
drop user gplbsd cascade;

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

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

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

#-------------------------
# POPULATE THE LOB COLUMNS
#-------------------------
insert into gplbsdt1 values (1, utl_raw.cast_to_raw(
'1234567890234567890134567890124567890123567890123467890123457890123456'),
'abcdefghijbcdefghijacdefghijabdefghijabcefghijabcdfghijabcdeghijabcdef');
insert into gplbsdt1 values (2, empty_blob(), empty_clob());
#
#dummy row to increase data size in row 1 using APPEND
insert into gplbsdt1 values (3, utl_raw.cast_to_raw(
'8901234567901234567811111111112222222222333333333344444444445555555555'),
'hijabcdefgijabcdefghAAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEE');
#
#dummy row to increase data size in row 1 using APPEND
insert into gplbsdt1 values (4, utl_raw.cast_to_raw(
'6666666666777777777788888888889999999999121212121234343434345656565656'),
'FFFFFFFFFFGGGGGGGGGGHHHHHHHHHHIIIIIIIIIIJJJJJJJJJJKKKKKKKKKKLLLLLLLLLL');
#
#dummy row to increase data size in row 1 using APPEND
insert into gplbsdt1 values (5, utl_raw.cast_to_raw(
'7878787878909090909023232323234545454545676767676789898989890101010101'),
'MMMMMMMMMMNNNNNNNNNNOOOOOOOOOOPPPPPPPPPPQQQQQQQQQQRRRRRRRRRRSSSSSSSSSS');
#
#dummy row to increase data size in row 1 using APPEND
insert into gplbsdt1 values (6, utl_raw.cast_to_raw(
'1313131313242424242435353535354646464646575757575768686868687979797979'),
'TTTTTTTTTTUUUUUUUUUUVVVVVVVVVVWWWWWWWWWWXXXXXXXXXXYYYYYYYYYYZZZZZZZZZZ');
commit;
#
# test if the LOB has been populated
declare
  b1 blob;
begin
  select c2 into b1 from gplbsdt1 where c1 = 1;  
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1)));
end;
/
#
# expand data in row 1
declare
  b1 blob;
  b3 blob;
  b4 blob;
  b5 blob;
  b6 blob;
  c1 clob;
  c3 clob;
  c4 clob;
  c5 clob;
  c6 clob;
begin
  select c2, c3 into b1, c1 from gplbsdt1 where c1 = 1 for update;
  select c2, c3 into b3, c3 from gplbsdt1 where c1 = 3;
  select c2, c3 into b4, c4 from gplbsdt1 where c1 = 4;
  select c2, c3 into b5, c5 from gplbsdt1 where c1 = 5;
  select c2, c3 into b6, c6 from gplbsdt1 where c1 = 6;
  dbms_lob.append(b1, b3);
  dbms_lob.append(b1, b4);
  dbms_lob.append(b1, b5);
  dbms_lob.append(b1, b6);
  dbms_lob.append(c1, c3);
  dbms_lob.append(c1, c4);
  dbms_lob.append(c1, c5);
  dbms_lob.append(c1, c6);
  commit;
end;
/
#
# T.B.D - get examples from KODIMG for large images and load up the LOBs
#

#-----------------------------------------------------------------------
# TEST DBMS_LOB ROUTINES IN BUFFERING MODE WITH 16 BUFFERS, KOLBBLKSZ 20
#-----------------------------------------------------------------------

#--------------------------------------------------------------
# VANILLA READ BLOB - the correct way to buffer read from a LOB
#--------------------------------------------------------------
create or replace procedure tst_read is
  b0 blob;
  r0 raw(20);
  r1 raw(20);
  r2 raw(20);
  r3 raw(20);
  r4 raw(20);
  r5 raw(20);
  r6 raw(20);
  r7 raw(20);
  r8 raw(20);
  r9 raw(20);
  r10 raw(20);
  r11 raw(20);
  r12 raw(20);
  r13 raw(20);
  r14 raw(20);
  r15 raw(20);
  r16 raw(20);
  amt integer;
  pos integer;
begin
  dbms_output.put_line('--------------- READ Begin ---------------');
 
  /* set bind variables */
  select c2 into b0 from gplbsdt1 where c1 = 1;

  /* enable locator for buffering */
  dbms_lob.enablebuffering(b0);

  /* read from the LOB into 16 buffers */
  amt := 20; pos := 1; r0 := '';
  dbms_lob.read(b0, amt, pos, r0);
  dbms_output.put_line('BLOB Read 0: '|| utl_raw.cast_to_varchar2(r0));
  -- This gets a new block (0)

  amt := 20; pos := 11; r1 := '';
  dbms_lob.read(b0, amt, pos, r1);
  dbms_output.put_line('BLOB Read 1: '|| utl_raw.cast_to_varchar2(r1));
  -- This should read from block 0, and also get a new block (1)

  amt := 20; pos := 41; r2 := '';
  dbms_lob.read(b0, amt, pos, r2);
  dbms_output.put_line('BLOB Read 2: '|| utl_raw.cast_to_varchar2(r2));
  -- This gets a new block (2)

  amt := 20; pos := 51; r3 := '';
  dbms_lob.read(b0, amt, pos, r3);
  dbms_output.put_line('BLOB Read 3: '|| utl_raw.cast_to_varchar2(r3));
  -- This should read from block 2, and also get a new block (3)

  amt := 20; pos := 81; r4 := '';
  dbms_lob.read(b0, amt, pos, r4);
  dbms_output.put_line('BLOB Read 4: '|| utl_raw.cast_to_varchar2(r4));
  -- This gets a new block (4)

  amt := 20; pos := 91; r5 := '';
  dbms_lob.read(b0, amt, pos, r5);
  dbms_output.put_line('BLOB Read 5: '|| utl_raw.cast_to_varchar2(r5));
  -- This should read from block 4, and also get a new block (5)

  amt := 20; pos := 121; r6 := '';
  dbms_lob.read(b0, amt, pos, r6);
  dbms_output.put_line('BLOB Read 6: '|| utl_raw.cast_to_varchar2(r6));
  -- This gets a new block (6)

  amt := 20; pos := 131; r7 := '';
  dbms_lob.read(b0, amt, pos, r7);
  dbms_output.put_line('BLOB Read 7: '|| utl_raw.cast_to_varchar2(r7));
  -- This should read from block 6, and also get a new block (7)

  amt := 20; pos := 161; r8 := '';
  dbms_lob.read(b0, amt, pos, r8);
  dbms_output.put_line('BLOB Read 8: '|| utl_raw.cast_to_varchar2(r8));
  -- This gets a new block (8)

  amt := 20; pos := 171; r9 := '';
  dbms_lob.read(b0, amt, pos, r9);
  dbms_output.put_line('BLOB Read 9: '|| utl_raw.cast_to_varchar2(r9));
  -- This should read from block 8, and also get a new block (9)

  amt := 20; pos := 201; r10 := '';
  dbms_lob.read(b0, amt, pos, r10);
  dbms_output.put_line('BLOB Read 10: '|| utl_raw.cast_to_varchar2(r10));
  -- This gets a new block (10)

  amt := 20; pos := 211; r11 := '';
  dbms_lob.read(b0, amt, pos, r11);
  dbms_output.put_line('BLOB Read 11: '|| utl_raw.cast_to_varchar2(r11));
  -- This should read from block 10, and also get a new block (11)

  amt := 20; pos := 241; r12 := '';
  dbms_lob.read(b0, amt, pos, r12);
  dbms_output.put_line('BLOB Read 12: '|| utl_raw.cast_to_varchar2(r12));
  -- This gets a new block (12)

  amt := 20; pos := 251; r13 := '';
  dbms_lob.read(b0, amt, pos, r13);
  dbms_output.put_line('BLOB Read 13: '|| utl_raw.cast_to_varchar2(r13));
  -- This should read from block 12, and also get a new block (13)

  amt := 20; pos := 281; r14 := '';
  dbms_lob.read(b0, amt, pos, r14);
  dbms_output.put_line('BLOB Read 14: '|| utl_raw.cast_to_varchar2(r14));
  -- This gets a new block (14)

  amt := 20; pos := 301; r15 := '';
  dbms_lob.read(b0, amt, pos, r15);
  dbms_output.put_line('BLOB Read 15: '|| utl_raw.cast_to_varchar2(r15));
  -- This should read from block 14, and also get a new block (15)

  amt := 20; pos := 321; r16 := '';
  dbms_lob.read(b0, amt, pos, r16);
  dbms_output.put_line('BLOB Read 16: '|| utl_raw.cast_to_varchar2(r16));
  -- This gets allocated block 0 after releasing the first read

  -- TESTING - Maria: The same thing has to happen with two locators now.
  -- Let locator A and its 7 eqvts take 8 buffers, and locator B at its
  -- eqvts take 8 buffers, the correct behavior must be noticed. i.e.
  -- when it comes time to release a buffer, the 0th buffer associated
  -- with only that specific locator - A or B, has to be released.

  /* disable locators */
  dbms_lob.disablebuffering(b0);
  --
  -- Disable buffering frees up the buffer (i.e all the pages in the buffer)
  -- associated **with the LOB**.
  -- So an intelligent user will actually work off of just one locator per
  -- LOB, so that he does not end up doing something like the following.
  -- All the subsequent disable buffering operations do remove the buffering
  -- flag from the locator, but they go through empty cycles trying to free
  -- up essentially non-existent buffer pages.
  --  
  -- dbms_lob.disablebuffering(b1);
  -- dbms_lob.disablebuffering(b2);
  -- dbms_lob.disablebuffering(b3);
  -- dbms_lob.disablebuffering(b4);
  -- dbms_lob.disablebuffering(b5);
  -- dbms_lob.disablebuffering(b6);
  -- dbms_lob.disablebuffering(b7);
  -- dbms_lob.disablebuffering(b8);
  -- dbms_lob.disablebuffering(b9);
  -- dbms_lob.disablebuffering(b10);
  -- dbms_lob.disablebuffering(b11);
  -- dbms_lob.disablebuffering(b12);
  -- dbms_lob.disablebuffering(b13);
  -- dbms_lob.disablebuffering(b14);
  -- dbms_lob.disablebuffering(b15);
  -- dbms_lob.disablebuffering(b16);

  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;
/

#-------------------------------------------------------------------
# VANILLA READ BLOB - the incorrect/expensive way to read from a LOB
#
# I am leaving it here because this is the way I tested it first.
# This test should work functionally, but we should not guide the
# user to write code like this. The aim is to explain disablebuffering
# semantics.
#-------------------------------------------------------------------
create or replace procedure tst_read2 is
  b0 blob;
  b1 blob;
  b2 blob;
  b3 blob;
  b4 blob;
  b5 blob;
  b6 blob;
  b7 blob;
  b8 blob;
  b9 blob;
  b10 blob;
  b11 blob;
  b12 blob;
  b13 blob;
  b14 blob;
  b15 blob;
  b16 blob;
  r0 raw(20);
  r1 raw(20);
  r2 raw(20);
  r3 raw(20);
  r4 raw(20);
  r5 raw(20);
  r6 raw(20);
  r7 raw(20);
  r8 raw(20);
  r9 raw(20);
  r10 raw(20);
  r11 raw(20);
  r12 raw(20);
  r13 raw(20);
  r14 raw(20);
  r15 raw(20);
  r16 raw(20);
  amt integer;
  pos integer;
begin
  dbms_output.put_line('--------------- READ Begin ---------------');
 
  /* set bind variables */
  select c2 into b0 from gplbsdt1 where c1 = 1;
  b1 := b0; b2 := b0; b3 := b0; b4 := b0; b5 := b0; b6 := b0; b7 := b0;
  b8 := b0; b9 := b0; b10 := b0; b11 := b0; b12 := b0; b13 := b0;
  b14 := b0; b15 := b0; b16 := b0;

  /* enable locators */
  dbms_lob.enablebuffering(b0);
  dbms_lob.enablebuffering(b1);
  dbms_lob.enablebuffering(b2);
  dbms_lob.enablebuffering(b3);
  dbms_lob.enablebuffering(b4);
  dbms_lob.enablebuffering(b5);
  dbms_lob.enablebuffering(b6);
  dbms_lob.enablebuffering(b7);
  dbms_lob.enablebuffering(b8);
  dbms_lob.enablebuffering(b9);
  dbms_lob.enablebuffering(b10);
  dbms_lob.enablebuffering(b11);
  dbms_lob.enablebuffering(b12);
  dbms_lob.enablebuffering(b13);
  dbms_lob.enablebuffering(b14);
  dbms_lob.enablebuffering(b15);
  dbms_lob.enablebuffering(b16);

  /* read from the LOB into 16 buffers */
  amt := 20; pos := 1; r0 := '';
  dbms_lob.read(b0, amt, pos, r0);
  dbms_output.put_line('BLOB Read 0: '|| utl_raw.cast_to_varchar2(r0));
  -- This gets a new block (0)

  amt := 20; pos := 11; r1 := '';
  dbms_lob.read(b1, amt, pos, r1);
  dbms_output.put_line('BLOB Read 1: '|| utl_raw.cast_to_varchar2(r1));
  -- This should read from block 0, and also get a new block (1)

  amt := 20; pos := 41; r2 := '';
  dbms_lob.read(b2, amt, pos, r2);
  dbms_output.put_line('BLOB Read 2: '|| utl_raw.cast_to_varchar2(r2));
  -- This gets a new block (2)

  amt := 20; pos := 51; r3 := '';
  dbms_lob.read(b3, amt, pos, r3);
  dbms_output.put_line('BLOB Read 3: '|| utl_raw.cast_to_varchar2(r3));
  -- This should read from block 2, and also get a new block (3)

  amt := 20; pos := 81; r4 := '';
  dbms_lob.read(b4, amt, pos, r4);
  dbms_output.put_line('BLOB Read 4: '|| utl_raw.cast_to_varchar2(r4));
  -- This gets a new block (4)

  amt := 20; pos := 91; r5 := '';
  dbms_lob.read(b5, amt, pos, r5);
  dbms_output.put_line('BLOB Read 5: '|| utl_raw.cast_to_varchar2(r5));
  -- This should read from block 4, and also get a new block (5)

  amt := 20; pos := 121; r6 := '';
  dbms_lob.read(b6, amt, pos, r6);
  dbms_output.put_line('BLOB Read 6: '|| utl_raw.cast_to_varchar2(r6));
  -- This gets a new block (6)

  amt := 20; pos := 131; r7 := '';
  dbms_lob.read(b7, amt, pos, r7);
  dbms_output.put_line('BLOB Read 7: '|| utl_raw.cast_to_varchar2(r7));
  -- This should read from block 6, and also get a new block (7)

  amt := 20; pos := 161; r8 := '';
  dbms_lob.read(b8, amt, pos, r8);
  dbms_output.put_line('BLOB Read 8: '|| utl_raw.cast_to_varchar2(r8));
  -- This gets a new block (8)

  amt := 20; pos := 171; r9 := '';
  dbms_lob.read(b9, amt, pos, r9);
  dbms_output.put_line('BLOB Read 9: '|| utl_raw.cast_to_varchar2(r9));
  -- This should read from block 8, and also get a new block (9)

  amt := 20; pos := 201; r10 := '';
  dbms_lob.read(b10, amt, pos, r10);
  dbms_output.put_line('BLOB Read 10: '|| utl_raw.cast_to_varchar2(r10));
  -- This gets a new block (10)

  amt := 20; pos := 211; r11 := '';
  dbms_lob.read(b11, amt, pos, r11);
  dbms_output.put_line('BLOB Read 11: '|| utl_raw.cast_to_varchar2(r11));
  -- This should read from block 10, and also get a new block (11)

  amt := 20; pos := 241; r12 := '';
  dbms_lob.read(b12, amt, pos, r12);
  dbms_output.put_line('BLOB Read 12: '|| utl_raw.cast_to_varchar2(r12));
  -- This gets a new block (12)

  amt := 20; pos := 251; r13 := '';
  dbms_lob.read(b13, amt, pos, r13);
  dbms_output.put_line('BLOB Read 13: '|| utl_raw.cast_to_varchar2(r13));
  -- This should read from block 12, and also get a new block (13)

  amt := 20; pos := 281; r14 := '';
  dbms_lob.read(b14, amt, pos, r14);
  dbms_output.put_line('BLOB Read 14: '|| utl_raw.cast_to_varchar2(r14));
  -- This gets a new block (14)

  amt := 20; pos := 301; r15 := '';
  dbms_lob.read(b15, amt, pos, r15);
  dbms_output.put_line('BLOB Read 15: '|| utl_raw.cast_to_varchar2(r15));
  -- This should read from block 14, and also get a new block (15)

  amt := 20; pos := 321; r16 := '';
  dbms_lob.read(b16, amt, pos, r16);
  dbms_output.put_line('BLOB Read 16: '|| utl_raw.cast_to_varchar2(r16));
  -- This gets allocated block 0 after releasing the first read

  -- TESTING - Maria: The same thing has to happen with two locators now.
  -- Let locator A and its 7 eqvts take 8 buffers, and locator B at its
  -- eqvts take 8 buffers, the correct behavior must be noticed. i.e.
  -- when it comes time to release a buffer, the 0th buffer associated
  -- with only that specific locator - A or B, has to be released.

  /* disable locators */
  dbms_lob.disablebuffering(b0);
  --
  -- Disable buffering frees up the buffer (i.e all the pages in the buffer)
  -- associated **with the LOB**.
  -- So an intelligent user will actually work off of just one locator per
  -- LOB, so that he does not end up doing something like the following.
  -- All the subsequent disable buffering operations do remove the buffering
  -- flag from the locator, but they go through empty cycles trying to free
  -- up essentially non-existent buffer pages.
  --  
  -- dbms_lob.disablebuffering(b1);
  -- dbms_lob.disablebuffering(b2);
  -- dbms_lob.disablebuffering(b3);
  -- dbms_lob.disablebuffering(b4);
  -- dbms_lob.disablebuffering(b5);
  -- dbms_lob.disablebuffering(b6);
  -- dbms_lob.disablebuffering(b7);
  -- dbms_lob.disablebuffering(b8);
  -- dbms_lob.disablebuffering(b9);
  -- dbms_lob.disablebuffering(b10);
  -- dbms_lob.disablebuffering(b11);
  -- dbms_lob.disablebuffering(b12);
  -- dbms_lob.disablebuffering(b13);
  -- dbms_lob.disablebuffering(b14);
  -- dbms_lob.disablebuffering(b15);
  -- dbms_lob.disablebuffering(b16);

  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_read2;
/
show errors procedure tst_read2;
/

#-------------------
# VANILLA WRITE BLOB
#-------------------

create or replace procedure tst_write is
  b0  blob;
  r0  raw(10);
  r1  raw(30);
  r2  raw(10);
  r3  raw(30);
  r4  raw(10);
  r5  raw(30);
  r6  raw(10);
  r7  raw(30);
  r8  raw(10);
  r9  raw(30);
  r10 raw(10);
  r11 raw(30);
  r12 raw(10);
  r13 raw(30);
  r14 raw(10);
  r15 raw(30);
  r16 raw(10);
  amt integer;
  pos integer;
begin
  dbms_output.put_line('--------------- WRITE Begin ---------------');
 
  /* set bind variables */
  select c2 into b0 from gplbsdt1 where c1 = 2 for update;

  /* enable locators */
  dbms_lob.enablebuffering(b0);

  /* write into the LOB through 16 buffers */
  amt := 10; pos := 1; r0 := utl_raw.cast_to_raw('1234567890');
  dbms_lob.write(b0, amt, pos, r0);
  dbms_output.put_line('BLOB Write 0: '|| utl_raw.cast_to_varchar2(r0));
  -- This gets a new block (0)

  amt := 30; pos := 11;
  r1 := utl_raw.cast_to_raw('234567890134567890124567890123');
  dbms_lob.write(b0, amt, pos, r1);
  dbms_output.put_line('BLOB Write 1: '|| utl_raw.cast_to_varchar2(r1));
  -- This should write into block 0, and also get a new block (1)

  amt := 10; pos := 41; r2 := utl_raw.cast_to_raw('5678901234');
  dbms_lob.write(b0, amt, pos, r2);
  dbms_output.put_line('BLOB Write 2: '|| utl_raw.cast_to_varchar2(r2));
  -- This gets a new block (2)

  amt := 30; pos := 51;
  r3 := utl_raw.cast_to_raw('678901234578901234568901234567');
  dbms_lob.write(b0, amt, pos, r3);
  dbms_output.put_line('BLOB Write 3: '|| utl_raw.cast_to_varchar2(r3));
  -- This should write into block 2, and also get a new block (3)

  amt := 10; pos := 81; r4 := utl_raw.cast_to_raw('9012345678');
  dbms_lob.write(b0, amt, pos, r4);
  dbms_output.put_line('BLOB Write 4: '|| utl_raw.cast_to_varchar2(r4));
  -- This gets a new block (4)

  amt := 30; pos := 91;
  r5 := utl_raw.cast_to_raw('111111111122222222223333333333');
  dbms_lob.write(b0, amt, pos, r5);
  dbms_output.put_line('BLOB Write 5: '|| utl_raw.cast_to_varchar2(r5));
  -- This should write into block 4, and also get a new block (5)

  amt := 10; pos := 121; r6 := utl_raw.cast_to_raw('4444444444');
  dbms_lob.write(b0, amt, pos, r6);
  dbms_output.put_line('BLOB Write 6: '|| utl_raw.cast_to_varchar2(r6));
  -- This gets a new block (6)

  amt := 30; pos := 131;
  r7 := utl_raw.cast_to_raw('555555555566666666667777777777');
  dbms_lob.write(b0, amt, pos, r7);
  dbms_output.put_line('BLOB Write 7: '|| utl_raw.cast_to_varchar2(r7));
  -- This should write into block 6, and also get a new block (7)

  amt := 10; pos := 161; r8 := utl_raw.cast_to_raw('8888888888');
  dbms_lob.write(b0, amt, pos, r8);
  dbms_output.put_line('BLOB Write 8: '|| utl_raw.cast_to_varchar2(r8));
  -- This gets a new block (8)

  amt := 30; pos := 171;
  r9 := utl_raw.cast_to_raw('999999999912121212123434343434');
  dbms_lob.write(b0, amt, pos, r9);
  dbms_output.put_line('BLOB Write 9: '|| utl_raw.cast_to_varchar2(r9));
  -- This should write into block 8, and also get a new block (9)

  amt := 10; pos := 201; r10 := utl_raw.cast_to_raw('5656565656');
  dbms_lob.write(b0, amt, pos, r10);
  dbms_output.put_line('BLOB Write 10: '|| utl_raw.cast_to_varchar2(r10));
  -- This gets a new block (10)

  amt := 30; pos := 211;
  r11 := utl_raw.cast_to_raw('787878787890909090902323232323');
  dbms_lob.write(b0, amt, pos, r11);
  dbms_output.put_line('BLOB Write 11: '|| utl_raw.cast_to_varchar2(r11));
  -- This should write into block 10, and also get a new block (11)

  amt := 10; pos := 241; r12 := utl_raw.cast_to_raw('4545454545');
  dbms_lob.write(b0, amt, pos, r12);
  dbms_output.put_line('BLOB Write 12: '|| utl_raw.cast_to_varchar2(r12));
  -- This gets a new block (12)

  amt := 30; pos := 251;
  r13 := utl_raw.cast_to_raw('676767676789898989890101010101');
  dbms_lob.write(b0, amt, pos, r13);
  dbms_output.put_line('BLOB Write 13: '|| utl_raw.cast_to_varchar2(r13));
  -- This should write into block 12, and also get a new block (13)

  amt := 10; pos := 281; r14 := utl_raw.cast_to_raw('1313131313');
  dbms_lob.write(b0, amt, pos, r14);
  dbms_output.put_line('BLOB Write 14: '|| utl_raw.cast_to_varchar2(r14));
  -- This gets a new block (14)

  amt := 30; pos := 291;
  r15 := utl_raw.cast_to_raw('242424242435353535354646464646');
  dbms_lob.write(b0, amt, pos, r15);
  dbms_output.put_line('BLOB Write 15: '|| utl_raw.cast_to_varchar2(r15));
  -- This should write into block 14, and also get a new block (15)

  amt := 10; pos := 321; r16 := utl_raw.cast_to_raw('5757575757');
  dbms_lob.write(b0, amt, pos, r16);
  dbms_output.put_line('BLOB Write 16: '|| utl_raw.cast_to_varchar2(r16));
  -- This should complain that there are no more free blocks and go into
  -- the exception handler, where the existing buffers will be flushed.

  -- TESTING - Maria: The same thing has to happen with two locators now.
  -- Let locator A and its 7 eqvts take 8 buffers, and locator B and its
  -- eqvts take 8 buffers, the correct behavior must be noticed. i.e.
  -- when it comes time to release a buffer, the 0th buffer associated
  -- with only that specific locator - A or B, has to be released.

  /* disable locators */
  dbms_lob.disablebuffering(b0);

  dbms_output.put_line('--------------- WRITE End   ---------------');
  dbms_output.put_line(' ');
  exception
    when others
    then
      begin
        dbms_output.put_line('No more buffers');
        dbms_lob.flushbuffer(b0);
        dbms_lob.disablebuffering(b0);
        commit;
      end;
end tst_write;
/
show errors procedure tst_write;
/
#Test the result of WRITEs
declare
  b1 blob;
  b2 blob;
begin
  select c2 into b1 from gplbsdt1 where c1 = 1;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1, 255)));
  select c2 into b2 from gplbsdt1 where c1 = 2;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b2, 255)));
end;
/
rollback;

#-------------------
# VANILLA FLUSH BLOB
#-------------------

create or replace procedure tst_flush is
  b0 blob;
  b1 blob;
  r0 raw(10);
  amt integer;
  pos integer;
begin
  dbms_output.put_line('--------------- FLUSH Begin ---------------');

  /* set bind variables */
  select c2 into b0 from gplbsdt1 where c1 = 2 for update;

  /* enable locators */
  dbms_lob.enablebuffering(b0);

  /* write into LOB */
  amt := 10; pos := 1; r0 := utl_raw.cast_to_raw('1234567890');
  dbms_lob.write(b0, amt, pos, r0);
  -- This gets a new block (0)
  -- You should not see the write to the lob now
  select c2 into b1 from gplbsdt1 where c1 = 2;  
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1)));

  /* flush the LOB */
  dbms_lob.flushbuffer(b0);
  -- now you should see the write to the LOB
  select c2 into b1 from gplbsdt1 where c1 = 2;  
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1)));

  /* disable locators */
  dbms_lob.disablebuffering(b0);

  /* commit the changes */
  commit;

  dbms_output.put_line('--------------- WRITE End   ---------------');
  dbms_output.put_line(' ');
end tst_flush;
/
show errors procedure tst_flush;
/
#Test the result of WRITEs
declare
  b1 blob;
  b2 blob;
begin
  select c2 into b1 from gplbsdt1 where c1 = 1;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1, 255)));
  select c2 into b2 from gplbsdt1 where c1 = 2;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b2, 255)));
end;
/
rollback;

#-------------------
# VANILLA WRITE BLOB
#-------------------

create or replace procedure tst_write is
  b0  blob;
  r0  raw(10);
  r1  raw(30);
  r2  raw(10);
  r3  raw(30);
  r4  raw(10);
  r5  raw(30);
  r6  raw(10);
  r7  raw(30);
  r8  raw(10);
  r9  raw(30);
  r10 raw(10);
  r11 raw(30);
  r12 raw(10);
  r13 raw(30);
  r14 raw(10);
  r15 raw(30);
  r16 raw(10);
  amt integer;
  pos integer;
begin
  dbms_output.put_line('--------------- WRITE Begin ---------------');
 
  /* set bind variables */
  select c2 into b0 from gplbsdt1 where c1 = 2 for update;

  /* enable locators */
  dbms_lob.enablebuffering(b0);

  /* write into the LOB through 16 buffers */
  amt := 10; pos := 1; r0 := utl_raw.cast_to_raw('1234567890');
  dbms_lob.write(b0, amt, pos, r0);
  dbms_output.put_line('BLOB Write 0: '|| utl_raw.cast_to_varchar2(r0));
  -- This gets a new block (0)

  amt := 30; pos := 11;
  r1 := utl_raw.cast_to_raw('234567890134567890124567890123');
  dbms_lob.write(b0, amt, pos, r1);
  dbms_output.put_line('BLOB Write 1: '|| utl_raw.cast_to_varchar2(r1));
  -- This should write into block 0, and also get a new block (1)

  amt := 10; pos := 41; r2 := utl_raw.cast_to_raw('5678901234');
  dbms_lob.write(b0, amt, pos, r2);
  dbms_output.put_line('BLOB Write 2: '|| utl_raw.cast_to_varchar2(r2));
  -- This gets a new block (2)

  amt := 30; pos := 51;
  r3 := utl_raw.cast_to_raw('678901234578901234568901234567');
  dbms_lob.write(b0, amt, pos, r3);
  dbms_output.put_line('BLOB Write 3: '|| utl_raw.cast_to_varchar2(r3));
  -- This should write into block 2, and also get a new block (3)

  amt := 10; pos := 81; r4 := utl_raw.cast_to_raw('9012345678');
  dbms_lob.write(b0, amt, pos, r4);
  dbms_output.put_line('BLOB Write 4: '|| utl_raw.cast_to_varchar2(r4));
  -- This gets a new block (4)

  amt := 30; pos := 91;
  r5 := utl_raw.cast_to_raw('111111111122222222223333333333');
  dbms_lob.write(b0, amt, pos, r5);
  dbms_output.put_line('BLOB Write 5: '|| utl_raw.cast_to_varchar2(r5));
  -- This should write into block 4, and also get a new block (5)

  amt := 10; pos := 121; r6 := utl_raw.cast_to_raw('4444444444');
  dbms_lob.write(b0, amt, pos, r6);
  dbms_output.put_line('BLOB Write 6: '|| utl_raw.cast_to_varchar2(r6));
  -- This gets a new block (6)

  amt := 30; pos := 131;
  r7 := utl_raw.cast_to_raw('555555555566666666667777777777');
  dbms_lob.write(b0, amt, pos, r7);
  dbms_output.put_line('BLOB Write 7: '|| utl_raw.cast_to_varchar2(r7));
  -- This should write into block 6, and also get a new block (7)

  amt := 10; pos := 161; r8 := utl_raw.cast_to_raw('8888888888');
  dbms_lob.write(b0, amt, pos, r8);
  dbms_output.put_line('BLOB Write 8: '|| utl_raw.cast_to_varchar2(r8));
  -- This gets a new block (8)

  amt := 30; pos := 171;
  r9 := utl_raw.cast_to_raw('999999999912121212123434343434');
  dbms_lob.write(b0, amt, pos, r9);
  dbms_output.put_line('BLOB Write 9: '|| utl_raw.cast_to_varchar2(r9));
  -- This should write into block 8, and also get a new block (9)

  amt := 10; pos := 201; r10 := utl_raw.cast_to_raw('5656565656');
  dbms_lob.write(b0, amt, pos, r10);
  dbms_output.put_line('BLOB Write 10: '|| utl_raw.cast_to_varchar2(r10));
  -- This gets a new block (10)

  amt := 30; pos := 211;
  r11 := utl_raw.cast_to_raw('787878787890909090902323232323');
  dbms_lob.write(b0, amt, pos, r11);
  dbms_output.put_line('BLOB Write 11: '|| utl_raw.cast_to_varchar2(r11));
  -- This should write into block 10, and also get a new block (11)

  amt := 10; pos := 241; r12 := utl_raw.cast_to_raw('4545454545');
  dbms_lob.write(b0, amt, pos, r12);
  dbms_output.put_line('BLOB Write 12: '|| utl_raw.cast_to_varchar2(r12));
  -- This gets a new block (12)

  amt := 30; pos := 251;
  r13 := utl_raw.cast_to_raw('676767676789898989890101010101');
  dbms_lob.write(b0, amt, pos, r13);
  dbms_output.put_line('BLOB Write 13: '|| utl_raw.cast_to_varchar2(r13));
  -- This should write into block 12, and also get a new block (13)

  amt := 10; pos := 281; r14 := utl_raw.cast_to_raw('1313131313');
  dbms_lob.write(b0, amt, pos, r14);
  dbms_output.put_line('BLOB Write 14: '|| utl_raw.cast_to_varchar2(r14));
  -- This gets a new block (14)

  amt := 30; pos := 291;
  r15 := utl_raw.cast_to_raw('242424242435353535354646464646');
  dbms_lob.write(b0, amt, pos, r15);
  dbms_output.put_line('BLOB Write 15: '|| utl_raw.cast_to_varchar2(r15));
  -- This should write into block 14, and also get a new block (15)

  amt := 10; pos := 321; r16 := utl_raw.cast_to_raw('5757575757');
  dbms_lob.write(b0, amt, pos, r16);
  dbms_output.put_line('BLOB Write 16: '|| utl_raw.cast_to_varchar2(r16));
  -- This should complain that there are no more free blocks and go into
  -- the exception handler, where the existing buffers will be flushed.

  -- TESTING - Maria: The same thing has to happen with two locators now.
  -- Let locator A and its 7 eqvts take 8 buffers, and locator B and its
  -- eqvts take 8 buffers, the correct behavior must be noticed. i.e.
  -- when it comes time to release a buffer, the 0th buffer associated
  -- with only that specific locator - A or B, has to be released.

  /* disable locators */
  dbms_lob.disablebuffering(b0);

  dbms_output.put_line('--------------- WRITE End   ---------------');
  dbms_output.put_line(' ');
  exception
    when others
    then
      begin
        dbms_output.put_line('No more buffers');
        dbms_lob.flushbuffer(b0);
        dbms_lob.disablebuffering(b0);
        commit;
      end;
end tst_write;
/
show errors procedure tst_write;
/
#Test the result of WRITEs
declare
  b1 blob;
  b2 blob;
begin
  select c2 into b1 from gplbsdt1 where c1 = 1;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1, 255)));
  select c2 into b2 from gplbsdt1 where c1 = 2;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b2, 255)));
end;
/
rollback;

###############
# EXECUTE TESTS
###############
begin
  tst_read;
  tst_write;
  tst_flush;
  tst_rdwr;
  commit;
end;
/
#
#Test the result of WRITEs
declare
  b1 blob;
  b2 blob;
begin
  select c2 into b1 from gplbsdt1 where c1 = 1;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b1, 255)));
  select c2 into b2 from gplbsdt1 where c1 = 2;
  dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(b2, 255)));
end;
/

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