Biotics 6:

Function which returns a table of data. Each row represents a database table that is out of alignment with the sequence used to assign its ID values.

  • The columns returned are:
    • schema
    • table_name
    • pk_column
    • sequence_name
    • last_sequence_value - the last value returned by the sequence
    • max_id_value - the highest ID value stored in the table. Values in corresponding deleted schema tables are not considered.
    • sql_to_fix - a SQL statement which can be used to fix the sequence. It is possible that the user may lack permissions to run the statement.
  • The input parameter (p_start_with) was changed to an optional parameter. If set, the generated sql statements to fix sequence values will never be lower than this value.
  • This function inspects all tables in all Biotics schemas which use an identity sequence to assign primary key values, including tables which the user may lack permissions to directly query. Special logic is included to use the core.element_id_seq sequence for the element_subnational, element_national, and element_global tables.
  • Example usage:
    • select * from shared.check_next_seq(500);

If any rows are returned, run the statement in the sql_to_fix column to resolve the problem(s).



Biotics 5:

The attached stored procedure checks your NEXT_SEQ table's values against the maximum primary key for each table in the NEXT_SEQ table. Install it by running the SQL script as the biotics_dlink user, and run it by typing:


SQL>SET SERVEROUTPUT ON
SQL>EXEC CHECK_NEXT_SEQ