whenever sqlerror exit sql.sqlcode
whenever oserror exit 99

-- Usage from SQL*Plus: @upd_target_state.sql <target> <state>
-- Example: @upd_target_state.sql APP01 YES

declare
  pragma autonomous_transaction;
  v_targetid targets.targetid%type;
  v_target   targets.target%type;
  v_state    targets.active%type;
begin
  v_target := '&1';
  v_state := '&2';

  if v_state not in ('YES', 'NO') then
    raise_application_error(-20004, 'Invalid state: ' || v_state || '. Use YES or NO.');
  end if;

  select targetid
    into v_targetid
    from targets
   where target = v_target;

  update targets
     set active = v_state
   where targetid = v_targetid;

  commit;
exception
  when no_data_found then
    rollback;
    raise_application_error(-20001, 'Target not found: ' || v_target);
  when too_many_rows then
    rollback;
    raise_application_error(-20002, 'Multiple targets found for: ' || v_target);
  when others then
    rollback;
    raise_application_error(
      -20003,
      'Failed to update target state for ' || v_target || ': ' || sqlerrm
    );
end;
/

exit 0