Serial Numbers

Generator With UPDATE - Exceptions

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
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11915.html]SQL Snippets: Serial Numbers - Generator With UPDATE - Exceptions[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11915.html">SQL Snippets: Serial Numbers - Generator With UPDATE - Exceptions</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Serial Numbers - Generator With UPDATE - Exceptions
  • URL (href): http://www.sqlsnippets.com/en/topic-11915.html