/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUT ON

DECLARE

  l_profile_name varchar2(1000) := '&1';
  l_sql        varchar2(400);
  l_profile_value     varchar2(400) := '0';

BEGIN

  --
  -- Retrieve the current profile value using dynamic SQL
  --
  l_sql := ' SELECT fnd_profile.value (''' || l_profile_name || ''') '
        || ' FROM   dual ';

  EXECUTE IMMEDIATE l_sql INTO l_profile_value;
  
  IF l_profile_value IS NULL
  THEN
  
  l_profile_value := '0';
  dbms_output.put_line(l_profile_value);
  
  ELSE
  
  dbms_output.put_line(l_profile_value); 
  
  END IF;
  
EXCEPTION

  WHEN others THEN

    dbms_output.put_line(l_profile_value);

END;
/
COMMIT;
EXIT;