@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display valid values for init.ora parameters
*  Parameters : 1 - Parameter Name  (Use % as wild card and \ as escape character)
*               2 - Where Clause ( Default NULL)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  ------------------------------------------------------
*  22-Jan-14  Vishal Gupta  Created
*
*
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE PARAM_NAME
UNDEFINE WHERE_CLAUSE

DEFINE PARAM_NAME="&&1"
DEFINE WHERE_CLAUSE="&2"



COLUMN  _PARAM_NAME       NEW_VALUE  PARAM_NAME         NOPRINT
COLUMN  _INST_ID          NEW_VALUE  INST_ID            NOPRINT

set term off
SELECT DECODE('&&PARAM_NAME','','%','&&PARAM_NAME')       "_PARAM_NAME"
FROM DUAL;
set term on


PROMPT **************************************************************
PROMPT * Valid values for init.ora parameters
PROMPT *                                                   
PROMPT * Input Parameters 
PROMPT *    - Parameter Name = "&&PARAM_NAME"
PROMPT *    - Where Clause   = "&&WHERE_CLAUSE"
PROMPT **************************************************************


COLUMN parno_kspvld_values          HEADING "Parameter|Number"          FORMAT 9999999
COLUMN name_kspvld_values           HEADING "Parameter|Name"            FORMAT a30
COLUMN ordinal_kspvld_values        HEADING "Ordinal|Name"              FORMAT 999999
COLUMN value_kspvld_values          HEADING "Valid Values"              FORMAT a20
COLUMN isdefault_kspvld_values      HEADING "ISDEFAULT"                 FORMAT a5

BREAK ON parno_kspvld_values ON name_kspvld_values SKIP 1 

SELECT parno_kspvld_values
     , name_kspvld_values      
     , ordinal_kspvld_values
     , value_kspvld_values
     , isdefault_kspvld_values 
  FROM sys.x$kspvld_values p
       JOIN v$instance i ON i.instance_number = p.inst_id
 WHERE 1=1
  -- AND TRANSLATE(name_kspvld_values,'_','#') NOT LIKE '#%' /* Exclude hidden parameters */
  AND UPPER(name_kspvld_values) LIKE UPPER('&&PARAM_NAME')
ORDER BY name_kspvld_values
       , ordinal_kspvld_values
;

@@footer