Rem
Rem $Header: admin_check_init_parms.sql 25-feb-2003.12:53:54 rpinnama Exp $
Rem
Rem admin_check_init_parms.sql
Rem
Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      admin_check_init_parms.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
rem     This script checks whether the connected database has the necessary
rem     init parameter settings for a specified set of parameters.
rem
rem     If the parameter settings satisfy the criteria, then the script exits
rem     with SUCCESS status.
rem     Otherwise, it issues an error message (via an application exception)
rem     and exits with FAILURE status.  The message includes details on which
rem     init parameters did not satisfy the criteria, and what they need to be.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rpinnama    02/25/03 - Remove unnecessary exits
Rem    rpinnama    12/26/02 - Check for UNDO parameters also
Rem    rpinnama    10/18/02 - Add db_block_size check
Rem    skini       07/22/02 - Remove check for global_context_pool_size
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

--
-- Set things up so that any exception thrown in the PL/SQL below
-- will cause the script to exit with FAILURE status.
--
WHENEVER SQLERROR EXIT FAILURE;

--
-- Anonymous PL/SQL block that does all the work.
--
DECLARE

  -- Types of parameters
  INTEGER_PARAM_TYPE    CONSTANT INTEGER := 1;
  STRING_PARAM_TYPE     CONSTANT INTEGER := 2;

  -- Types of checks
  NOT_NULL_CHECK       CONSTANT INTEGER := 1;
  MIN_VALUE_CHECK      CONSTANT INTEGER := 2;
  EQUALS_CHECK         CONSTANT INTEGER := 3;

  -- Record to store init parameter names and values
  TYPE InitParamType IS RECORD
    (
      name                VARCHAR2(64),
      type                INTEGER := INTEGER_PARAM_TYPE,
      check_type          INTEGER := NOT_NULL_CHECK,
      min_value           INTEGER,
      str_value           VARCHAR2(64) := null,
      value_read          VARCHAR2(64) := null
    );


  -- When adding additional init parameters to check for, be sure to update the
  -- following three lines to reflect the total number of parameters.
  -- (Note: The INIT_PARAM_COUNT cannot be used in the second line.)
  INIT_PARAM_COUNT        CONSTANT INTEGER := 2;
  TYPE InitParamArray IS VARRAY(2) OF InitParamType;
  init_params   InitParamArray := InitParamArray(null, null);

  -- Init parameter names and values
  -- (Note: Add other parameter information here, as necessary.
  --        Also, add the necessary names to the cursor below, and
  --        the necessary additional lines following the BEGIN, below.)
  JOB_QUEUE_PROCESSES       CONSTANT VARCHAR2(64) := 'job_queue_processes';
  JQP_MIN_VALUE             CONSTANT INTEGER      := 10;

  DB_BLOCK_SIZE             CONSTANT VARCHAR2(64) := 'db_block_size';
  DBS_MIN_VALUE             CONSTANT INTEGER      := 8192;

  -- Other declarations

  NEWLINE CONSTANT CHAR(1) := CHR(10);
  
  name    VARCHAR2(64);   -- Local copy of parameter name
  value   VARCHAR2(64);        -- Local copy of parameter value
  errs    INTEGER := 0;   -- Count of errors encountered
  message VARCHAR2(512);  -- Message to be constructed on errors,
                          -- (passed on raise of application exception)

  -- Cursor for query against V$PARAMETER
  -- When adding additional init parameter checks, be sure to add
  -- the appropriate parameter names in the WHERE clause below.
  CURSOR init_param_cursor IS
    SELECT name, value
    FROM v$parameter
    WHERE name IN (JOB_QUEUE_PROCESSES, DB_BLOCK_SIZE);
BEGIN
  -- Assign values to the array
  -- (Couldn't find any way to initialize them in the declaration).
  -- When adding additional init parameter checks, be sure to add the
  -- appropriate assignments following these lines.
  init_params(1).name       := JOB_QUEUE_PROCESSES;
  init_params(1).type       := INTEGER_PARAM_TYPE;
  init_params(1).min_value  := JQP_MIN_VALUE;
  init_params(1).check_type := MIN_VALUE_CHECK;

  init_params(2).name       := DB_BLOCK_SIZE;
  init_params(2).type       := INTEGER_PARAM_TYPE;
  init_params(2).min_value  := DBS_MIN_VALUE;
  init_params(2).check_type := MIN_VALUE_CHECK;

  -- Loop through cursor, reading each init parameter name and value
  FOR init_param_rec IN init_param_cursor
  LOOP
    name := init_param_rec.name;
    value := init_param_rec.value;
    
    -- Try to match parameter with one of the ones we're looking for
    FOR param IN 1..INIT_PARAM_COUNT
    LOOP
      IF name = init_params(param).name
      THEN
        -- Found a match, so save the value
        init_params(param).value_read := value;
        EXIT; -- inner loop
      END IF;
    END LOOP;
  END LOOP;


  -- When we get here, we have saved all the init parameter values we've seen.
  -- Now, check that they're valid.

  -- Set up for message to be issued if they're not all valid
  -- We go through all of the parameters, reporting on all invalid ones;
  -- we construct the message as we go along.
  message := 'Database does not satisfy minimum init parameter criteria:' || NEWLINE;
  errs := 0;
  FOR param IN 1..INIT_PARAM_COUNT 
  LOOP

    IF init_params(param).value_read IS NULL
    THEN
      -- No matching parameter found.
      message := message || init_params(param).name || 
                 ' init parameter value not found; ';

      IF ( (init_params(param).type = STRING_PARAM_TYPE) AND
           (init_params(param).check_type = EQUALS_CHECK) )
      THEN
        message := message || ' required to be ' || 
                   init_params(param).str_value || NEWLINE;

      ELSIF ( (init_params(param).type = INTEGER_PARAM_TYPE) AND
              (init_params(param).check_type = MIN_VALUE_CHECK) ) 
      THEN
        message := message || ' required to be at least ' || 
                   TO_CHAR(init_params(param).min_value) || NEWLINE;
      ELSE
        message := message || NEWLINE;
      END IF;

      errs := errs + 1;

    ELSIF ( (init_params(param).type = STRING_PARAM_TYPE) AND
            (init_params(param).check_type = EQUALS_CHECK) AND
            (init_params(param).value_read <> init_params(param).str_value) ) 
    THEN
      -- Parameter value does not match
      message := message || init_params(param).name || '=' || 
                 init_params(param).value_read || '; must be equal to ' ||
                 init_params(param).str_value || NEWLINE;

      errs := errs + 1;

    ELSIF ( (init_params(param).type = INTEGER_PARAM_TYPE) AND
            (init_params(param).check_type = MIN_VALUE_CHECK) AND
            (TO_NUMBER(init_params(param).value_read) < init_params(param).min_value) )
    THEN
      -- Parameter value too low
      message := message || init_params(param).name || '=' || 
                 init_params(param).value_read || '; must be at least ' || 
                 TO_CHAR(init_params(param).min_value) || NEWLINE;
      errs := errs + 1;
    END IF;

  END LOOP;
  
  -- If any were invalid, raise an exception with a message
  IF errs > 0
  THEN
    raise_application_error(-20001, message);
  END IF;
END; 
/