upd_target_state.sql

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