SET ECHO ON TERM ON NUMF "";
SPOOL sqreseq.lis;
REM
REM $Header: 215187.1 sqreseq.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqreseq.sql
REM
REM DESCRIPTION
REM   This script refreshes sequence sqlt$_statement_id_s, and
REM   recompiles libraries owned by SQLTXPLAIN
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected as SQLTXPLAIN
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN
REM   3. Execute script sqreseq.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus sqltxplan/[pwd]
REM   SQL> start sqreseq.sql
REM
REM NOTES
REM   1. For possible errors see sqctab.lis file
REM
SET ECHO OFF TERM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PRO
PRO NOTE:
PRO If you get an ORA-06550 followed by PLS-00302: component 'INITIALIZATION' must be declared,
PRO then review *.lis files created during installation and fix errors reported.
PRO
EXEC sqltxplain.sqlt$r.initialization;
DECLARE
  rdbms_release NUMBER;
BEGIN
  IF USER != 'SQLTXPLAIN' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Install failed - SQLTXPLAIN objects should be created connected as SQLTXPLAIN, not as '||USER);
  END IF;
  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;
  IF rdbms_release < 9.2 THEN
    RAISE_APPLICATION_ERROR(-20200, 'Install failed - SQLTXPLAIN should be installed in 9i(9.2) or higher, not in '||rdbms_release);
  END IF;
END;
/
SET ECHO ON;

/* ------------------------------------------------------------------------- */

COL start_with NEW_V start_with NOPRI;
SELECT NVL(MAX(statement_id+100),1000) start_with FROM sqlt$_statement;

DROP SEQUENCE sqlt$_statement_id_s;
CREATE SEQUENCE sqlt$_statement_id_s START WITH &&start_with;

EXEC DBMS_UTILITY.COMPILE_SCHEMA(USER);

/* ------------------------------------------------------------------------- */

SPOOL OFF;
CL COL;
SET ECHO OFF TERM ON;
PRO SQRESEQ completed. Please check sqreseq.lis for any errors.
