set echo off
set term off
store set backupenv replace
set term on


set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : Display all hidden parameters
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-06  Vishal Gupta  First Draft
*
*
*/
set term on


set lines 200
set pages 50

DEFINE param_name = &1

COLUMN name      HEADING "Name"    FORMAT a32 ON
COLUMN value     HEADING "Value"   FORMAT a20 ON
COLUMN type      HEADING "Type"    FORMAT a8 ON
COLUMN description      HEADING "Desc"    FORMAT a55 ON

SELECT /*
         x.inst_id Instance_id
       , x.indx + 1 num
       , 
       */
         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   lower(TRANSLATE ( x.ksppinm, '_', '#')) LIKE lower('%&&param_name%')
ORDER BY upper(NAME)
/