title: Converting Sql Server If not exists to Oracle
body: <p>I have a snippet of a SQL Server procedure that looks like this:</p>
  
  <pre><code>IF NOT EXISTS(SELECT ID FROM IDTABLE WHERE ID=@ID)
  BEGIN
      DECLARE @MSG VARCHAR(100)
      SET @MSG = 'The ID (ID: ' + CONVERT(VARCHAR(50), @ID) + ') does not exist'
      EXEC SP_LOGAUDITMESSAGE 'Warning', 'Bill report', @MSG
      SET @RETVAL = 0
      RETURN
  END
  </code></pre>
  
  <p>This runs the select query and if no results are returned, runs the begin/end block. It declares a message variable and sends this, along with some other values, to a logging stored procedure before returning from the method.</p>
  
  <p>I'm on a project now though where I have to convert all of this stuff into Oracle (which I have zero familiarity with). I ran it through the scratch editor in Sql Developer and got this:</p>
  
  <pre><code>BEGIN
      SELECT 1
      INTO v_temp
      FROM DUAL
      WHERE NOT EXISTS
        ( SELECT ID FROM ID WHERE IDTABLE = v_ID
        );
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;
    IF v_temp = 1 THEN
      DECLARE
        v_MSG VARCHAR2(100);
      BEGIN
        v_MSG := 'The ID (ID: ' || UTILS.CONVERT_TO_VARCHAR2(v_ID,50) || ') does not exist' ;
        BEGIN
          SP_LOGAUDITMESSAGE('Warning', 'Bill report', v_MSG) ;
        EXCEPTION
        WHEN OTHERS THEN
          v_sys_error := SQLCODE;
        END;
        v_RETVAL := 0 ;
        RETURN;
      END;
    END IF;
  </code></pre>
  
  <p>Now, I get what it's trying to do - it's just taking a few more steps to do it, saving the results of the select statement into v_temp and then if it is set to equal 1, running the if block.</p>
  
  <p>However, I've a few queries. Firstly, some simple googling has taught me that the first Exception block (when others then null) is terrible practice. What would an alternative be in this situation, or is it acceptable given the following if statement? Secondly, is this entire rewrite necessary, or is it possible to amalgamate the if and select statements (similar to the SQL above) to make things a little more succinct?</p>