Wednesday, September 15, 2010

Substitution Variable XML Gotcha

You might have been using XML in your PL/SQL script and tried to include an XML special character such as < or ' as described at the W3C:
XML 1.0 Specification 
When testing your script in sqlplus or the Oracle SQL Developer you may have been confused by an input question or popup window asking for the "lt" or "apos" variable definition.  What is actually happening here is Oracle thinks that your XML character is actually a subsitution variable:

Oracle Substitution Variables

The substitution variable will be matched for anything beginning with an ampersand "&" character and ending in a termination character.  Because of the XML specification, you sometimes need to use the literal special character format and there is no way around this. However, you can suppress the substitution variable matching in Oracle by using the command:

   set define off


As described in the substitution variable usage guide:

Substitution Variable Usage

This will then let your script run correctly.  You can also use substitution variables in conjunction with an escape character, as described in the link above, although it is a little messier and only necessary if you are actually using substitution variables in your script.

No comments:

Post a Comment