@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display list of proxy users
*  Parameters : 1 - Username (Use % as wildcard, Default value '%')
*               2 - Proxy    (Use % as wildcard, Default value '%') 
*               3 - Flags 
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  19-Sep-16  Vishal Gupta  Created
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE USERNAME
UNDEFINE PROXY 
UNDEFINE FLAGS 


DEFINE USERNAME="&&1"  
DEFINE PROXY="&&2"  
DEFINE FLAGS="&&3"  


set term off
COLUMN  _USERNAME          NEW_VALUE  USERNAME             NOPRINT
COLUMN  _PROXY             NEW_VALUE  PROXY                NOPRINT
COLUMN  _FLAGS             NEW_VALUE  FLAGS                NOPRINT

SELECT DECODE('&&USERNAME','','%',UPPER('&&USERNAME'))   "_USERNAME"
     , DECODE('&&PROXY','','%',UPPER('&&PROXY'))         "_PROXY"
     , DECODE('&&FLAGS','','%',UPPER('&&FLAGS'))         "_FLAGS"
FROM DUAL;
set term on


PROMPT *****************************************************************
PROMPT *  D A T A B A S E    U S E R  S  
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - User Name = '&&USERNAME'
PROMPT *  - Proxy     = '&&PROXY'
PROMPT *  - Flags     = '&&FLAGS'
PROMPT *****************************************************************
PROMPT 
PROMPT 

COLUMN client                        HEADING "UserName"                          FORMAT a30
COLUMN proxy                         HEADING "Proxy"                             FORMAT a30
COLUMN flags                         HEADING "Flags"                             FORMAT a60


SELECT u.client
     , u.proxy
     , u.authentication
     , u.flags
 FROM proxy_users u
WHERE 1=1
  AND UPPER(u.proxy) LIKE UPPER('&&PROXY') 
  AND UPPER(u.client) LIKE UPPER('&&USERNAME') 
  AND UPPER(u.flags) LIKE UPPER('&&FLAGS') 
ORDER BY u.client
       , u.proxy
;


@@footer