@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display all hidden parameters
*  Parameters : 1 - Parameter name (Use % as wild card and \ as escape character
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-06  Vishal Gupta  First Draft
*  
*
*/


DEFINE param_name="&&1"

COLUMN inst_id          HEADING "I#"      FORMAT 99  ON
COLUMN name             HEADING "Name"    FORMAT a36 ON
COLUMN value            HEADING "Value"   FORMAT a20 ON
COLUMN type             HEADING "Type"    FORMAT a8  ON
COLUMN description      HEADING "Desc"    FORMAT a80 ON 

SELECT 
      -- x.inst_id Inst_id, 
         x.ksppinm NAME
       , DECODE(x.ksppity
              , 1, 'Boolean'
              , 2, 'String'
              , 3, 'Integer'
              , 4, 'Parameter File'
              , 5, 'Reserved'
              , 6, 'Big Int'
              , x.ksppity
              ) TYPE
       , y.ksppstvl VALUE
       , y.ksppstdf isdefault
       , DECODE ( BITAND ( x.ksppiflg / 256, 1 )
                , 1, 'TRUE'
                , 'FALSE'
                ) isses_modifiable
       , DECODE ( BITAND ( x.ksppiflg / 65536, 3 )
                , 1, 'IMMEDIATE'
                , 2, 'DEFERRED'
                , 3, 'IMMEDIATE'
                , 'FALSE'
                ) issys_modifiable
       , DECODE ( BITAND ( y.ksppstvf, 7 )
                , 1, 'MODIFIED'
                , 4, 'SYSTEM_MOD'
                , 'FALSE'
                ) ismodified
       , DECODE ( BITAND ( y.ksppstvf, 2 )
                , 2, 'TRUE'
                , 'FALSE'
                ) isadjusted 
       , x.ksppdesc description
    FROM sys.x$ksppi x
       , sys.x$ksppcv y
   WHERE ( x.indx = y.indx )
   AND   x.inst_id = y.inst_id
   AND   ( LOWER(x.ksppinm) LIKE LOWER('&&param_name') ESCAPE '\'
           OR  LOWER(x.ksppdesc) LIKE LOWER('&&param_name') ESCAPE '\' )
ORDER BY LOWER(REPLACE ( x.ksppinm, '_', '')) asc
/

@footer