Does Oracle PL/SQL have a standard ASSERT routine?

Question

I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...)

ASSERT( <condition>, <msg>)

such that when the <condition> passed in the first argument is false an exception is raised with the specified <msg> descriptive message.

I know this is trivial to do by hand but I'm asking if there's a standard one provided with the DBMS.

Having to write my own one or import one from 3rdy-party packages would be impractical, since I'd need it to be completely portable and transparent to every project I'm working on.

Answer 1

There isn't a built-in assertion procedure in SQL or PL/SQL, so you will have to write your own.

There are two ways of going about this. You can either manually raise an exception, as discussed in this Oracle article, or you can write a wrapper for the raise_application_error procedure, which is documented in the Oracle exception handling section of the documentation.

I'll add that exceptions were designed for this kind of scenario, so you'd be better off taking your programmer hat off for a second & using your DBA hat :)

Answer 2

The built in DBMS_ASSERT package is a narrowly scoped version of what you are looking for. For other asserts Phil is correct, you will have to build your own. Here is a simple demonstration of the second option in Phil's answer⁺¹:

set serveroutput on size 1000000
Declare

   Procedure Assert (pCondition In Number, pMessage In Varchar2) Is
   Begin
      If (pCondition = 1) Then
         Return; 
      End If;
      Raise_Application_Error(-20001, pMessage);
   End Assert;

Begin
   DBMS_Output.Put_Line('Start');

   Assert(Case When 1+1=2 Then 1 Else 0 End,'Something is wrong 1.');
   Assert(Case When 1+1=9 Then 1 Else 0 End,'Something is wrong 2.');

   DBMS_Output.Put_Line('End');
End;
/