MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Colons are PL/SQL gnats

with one comment

Today, one of my students made a common error working in a PL/SQL programming unit. The stumped student called the tutor over, and then the tutor called me over. The tutor asked me how the DBMS_OUTPUT package could be out of scope. When I saw the SP2-0552 error, it was straightforward to remove a stray colon, but I realized that recognizing the colon as an error wasn’t straightforward.

The error picks the string that follows a colon inside a program unit. This behavior is the same regardless of whether the colon is found in the declaration, execution, or exception block. Most likely, the string is the beginning of the next line because the colon is most frequently a mistyped semicolon.

SP2-0552: Bind variable "DBMS_OUTPUT" NOT declared.

The easiest way to find it is not to look for the string but simply for a colon with the find feature of the editor. Then, you must either replace it or give it a valid bind variable name. You define bind variables in the SQL*Plus environment with the VARIABLE keyword.

You may also be interested in how to put a colon inside an NDS statement. If so, you can check this blog post.

Written by maclochlainn

March 10th, 2009 at 2:51 pm

Posted in Oracle,sql