SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we continue examining the behaviour of the solution presented in the Generator With UPDATE Commands tutorial.

Before proceeding however, let's clean up old test data from prior tutorials and create some fresh test data.

Test Data Setup

delete from serial_number_controls ;

insert into serial_number_controls
  ( key, last_serial_number, increment_size )
values
  ( 'T.SERIAL_NUMBER' , 0 , 1 )
;

commit;

delete from t;

insert into t ( key, name, serial_number )
  values ( 1, 'a', next_serial_number( 'T.SERIAL_NUMBER' ) );
insert into t ( key, name, serial_number )
  values ( 2, 'b', next_serial_number( 'T.SERIAL_NUMBER' ) );
insert into t ( key, name, serial_number )
  values ( 3, 'c', next_serial_number( 'T.SERIAL_NUMBER' ) );
insert into t ( key, name, serial_number )
  values ( 4, 'd', next_serial_number( 'T.SERIAL_NUMBER' ) );
insert into t ( key, name, serial_number )
  values ( 5, 'e', next_serial_number( 'T.SERIAL_NUMBER' ) );

commit;
 

Zero Rows

If Transaction 2 is performed when no rows with null values exist in T, the control row in SERIAL_NUMBER_CONTROLS simply gets incremented and then decremented, leaving the table in its original state.

select count(*) as null_serial_numbers from t where serial_number is null ;
 

NULL_SERIAL_NUMBERS
-------------------
                  0
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 5              1
 

----------------------------------------------------------------------
-- Transaction 2
-- (this time no rows in T are updated)
----------------------------------------------------------------------

set serveroutput on

declare

  v_next_serial_number number ;
  v_row_count          binary_integer ;

begin

  v_next_serial_number := next_serial_number( 'T.SERIAL_NUMBER' ) ;

  update t
  set    serial_number =  v_next_serial_number + rownum - 1
  where  serial_number is null ;

  v_row_count := sql%rowcount ;

  dbms_output.put_line( 'Row Count: ' || to_char( v_row_count ) );

  update serial_number_controls
  set    last_serial_number = v_next_serial_number + v_row_count - 1
  where  key = 'T.SERIAL_NUMBER' ;

  commit;

end;
/
Row Count: 0
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 5              1
 

Exception Handling

If Transaction 2 encounters an error, SERIAL_NUMBER_CONTROLS is rolled back to its original state.

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 5              1
 

----------------------------------------------------------------------
-- Transaction 2
----------------------------------------------------------------------

update t set serial_number = null ;

set serveroutput on

declare

  v_next_serial_number number ;
  v_row_count          binary_integer ;

begin

  v_next_serial_number := next_serial_number( 'T.SERIAL_NUMBER' ) ;

  update t
  set    serial_number =  v_next_serial_number + rownum - 1
  where  serial_number is null ;

  v_row_count := sql%rowcount ;

  dbms_output.put_line( 'Row Count: ' || to_char( v_row_count ) );

  update serial_number_controls
  set    last_serial_number = v_next_serial_number + v_row_count - 1
  where  key = 'T.SERIAL_NUMBER' ;

  UPDATE T SET NAME = 'VALUE TOO BIG' ;

  commit;

end;
/
Row Count: 5
declare
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."NAME" (actual: 13, maximum: 5)
ORA-06512: at line 22


 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 5              1