Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Nope, you will have to build your own. Impracticality has a low threshold nowadays :) – Vincent Malgrat Sep 5 '12 at 12:40

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 :)

share|improve this answer

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+1:

set serveroutput on size 1000000

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


   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.');

share|improve this answer
DBMS_ASSERT is targeted at SQL parsing, hence me not mentioning it. – Phil Sep 5 '12 at 13:25
@Phil and that's why I said narrowly scoped. Perhaps that should be extremely narrowly scoped. :) – Leigh Riffel Sep 5 '12 at 15:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.