User-Generated Exceptions

You can generate your own error using the RAISE EXCEPTION statement, as Figure 469 shows.

BEGIN
  ON EXCEPTION SET esql, eisam   -- trap all errors
    IF esql = -206 THEN          -- table not found
      -- recover somehow
    ELSE
      RAISE exception esql, eisam;  -- pass the error up
    END IF
  END EXCEPTION
    -- do something
END

In Figure 469, the ON EXCEPTION statement uses two variables, esql and eisam, to hold the error numbers that the database server returns. The IF clause executes if an error occurs and if the SQL error number is -206. If any other SQL error is caught, it is passed out of this BEGINEND block to the last BEGINEND block of the previous example.

Simulating SQL Errors

You can generate errors to simulate SQL errors, as Figure 470 shows. If the user is pault, then the SPL routine acts as if that user has no update privileges, even if the user really does have that privilege.

BEGIN
  IF user = 'pault' THEN
     RAISE EXCEPTION -273;  -- deny Paul update privilege
  END IF
END

Using RAISE EXCEPTION to Exit Nested Code

Figure 471 shows how you can use the RAISE EXCEPTION statement to break out of a deeply nested block.

BEGIN
  ON EXCEPTION IN (1)
  END EXCEPTION WITH RESUME -- do nothing significant (cont)

  BEGIN
    FOR i IN (1 TO 1000)
      FOREACH select ..INTO aa FROM t
        IF aa < 0 THEN
          RAISE EXCEPTION 1;     -- emergency exit
        END IF
      END FOREACH
    END FOR
    RETURN 1;
  END 
  
  --do something;               -- emergency exit to
                                -- this statement.
  TRACE 'Negative value returned';
  RETURN -10;
END

If the innermost condition is true (if aa is negative), then the exception is raised and execution jumps to the code following the END of the block. In this case, execution jumps to the TRACE statement.

Remember that a BEGINEND block is a single statement. If an error occurs somewhere inside a block and the trap is outside the block, the rest of the block is skipped when execution resumes, and execution begins at the next statement.

Unless you set a trap for this error somewhere in the block, the error condition is passed back to the block that contains the call and back to any blocks that contain the block. If no ON EXCEPTION statement exists that is set to handle the error, execution of the SPL routine stops, creating an error for the routine that is executing the SPL routine.