Conditional Compilation Errors in Pl/SQL

Here’s a list of common Pl/SQL Conditional compilation errors, along with what may have caused them. This should allow you to know where to look in your code for errors.

This isn’t a complete list, but it should be enough to help you get started. If you have an error that’s not listed below, post it in the comments, and I’ll update this article (and give you credit!). That way, you can feel awesome about helping others. 🙂

So go ahead–if you have another Pl/SQL conditional compilation issue that might be common to others, post it in the comments and I’ll tag you when I update the article.

PLS-00176: unexpected preprocessor token ‘$THEN’

• You may have used $ELSEIF rather than $ELSIF (no “E” in the middle)

-- Wrong
begin
  $if $$my_flag $then
    null;
  $elseif $$my_other_flag $then -- Use $ELSIF not $ELSEIF
    null;
  $else
    null;
  $end
end;
/

-- Right
begin
  $if $$my_flag $then
    null;
  $elsif $$my_other_flag $then
    null;
  $else
    null;
  $end
end;
/

• You might have used $$IF instead of $IF

-- Wrong
begin
  $$if $$my_flag $then  -- Use $IF not $$IF
    null;
  $else 
    null;
  $end
end;
/

-- Right
begin
  $if $$my_flag $then
    null;
  $else 
    null;
  $end
end;
/

PLS-00177: ‘$IF’ preprocessor directive does not end properly

• You may have accidentally ended a conditional compilation in $END $IF rather than $END

-- Wrong
begin
  $if $$my_flag $then
    null;
  $elsif $$my_other_flag $then 
    null;
  $else
    null;
  $end $if -- Just use $END here...no need for $IF
end;
/

-- Right
begin
  $if $$my_flag $then
    null;
  $elsif $$my_other_flag $then 
    null;
  $else
    null;
  $end 
end;
/

• It’s possible that you forgot to end an $ERROR in an $END

-- Wrong
begin
  $if $$my_flag $then
    dbms_output.put_line('you set my_flag!');
  $elsif $$my_other_flag $then 
    $error 'Do not set MY_OTHER_FLAG! Set MY_FLAG instead!!' -- Remember to add an $end to complete your error statement, in addition to the $end on your $if statement
  $end  
  null;
end;
/

-- Right
begin
  $if $$my_flag $then
    dbms_output.put_line('you set my_flag!');
  $elsif $$my_other_flag $then 
    $error 'Do not set MY_OTHER_FLAG! Set MY_FLAG instead!!' $end
  $end  
  null;
end;
/

• You might have used $$END rather than $END

-- Wrong
begin
  $if $$my_flag $then
    null;
  $else 
    null;
  $$end -- Use $END instead
end;
/

-- Right
begin
  $if $$my_flag $then
    null;
  $else 
    null;
  $end
end;
/

• You might have used $ELSE $IF instead of $ELSIF

-- wrong
begin
  $if $$my_flag $then
    null;
  $else $if $$my_other_flag $then  -- Use $ELSIF rather than $ELSE $IF
    null;
  $else
    null;
  $end
end;
/

-- right
begin
  $if $$my_flag $then
    null;
  $elsif $$my_other_flag $then
    null;
  $else
    null;
  $end
end;
/

PLS-00181: unsupported preprocessor directive

• You may have accidentally put a single $ in front of a variable name, instead of two $

-- Wrong
begin
  $if $my_flag $then -- MY_FLAG should have $$ in front of it
    null;
  $else 
    null
  $end
end;
/

-- Right
begin
  $if $$my_flag $then
    null;
  $else 
    null
  $end
end;
/

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets