OutSystems Boot Camps: customer satisfaction 4.4/5
Finding hidden characters in SQL files
Oracle DBAs love to use SQL*Plus when installing database objects. As a developer, I make SQL install scripts. Which then are executed by these DBAs. These scripts contain references to other files with the actual SQL statements. Below is an example of an install script, here the user will be prompted with the step and then the file will be executed.
Prompt install tables
Prompt install triggers
Prompt install views
Normally the script runs and all the statements are executed without problems. Lately, I’ve encountered some problems after I started to use the IDE PL/SQL Developer for writing my scripts.
When executing the install script using sqlplus, errors start to appear. Listed below, is a snippet of the logging. The error “SP2-0734:” is a general error for statements that aren’t recognized.
SP2-0734: unknown command beginning "create ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
When I look at the create table statement nothing seems incorrect. Even when I copy the statement and execute this in sqlplus the statement is accepted and the table is created. This means something should be wrong with the file and not with the statement.
When searching for the SP2-0734 error most of the solutions lay in correcting its statement. But here it wasn’t the case. When I delved deeper into the logging file I found some interesting errors:
SP2-0734: unknown command beginning "ï»¿create ..." - rest of line ignored. SP2-0734: unknown command beginning "for ..." - rest of line ignored.
For the first time, I noticed an error in my statement, namely ï»¿. I don’t see these weird characters in my statement, but sqlplus does see them. This must mean that there is an error in the file itself. Opening the file with SQL Developer or PL/SQL Developer gave me no further insight.
Notepad++, on the other hand, does. Notepad++ gives you the option to save a file with a certain encoding. When I looked at this encoding I saw that my files were saved with the UTF-8 BOM encoding instead of UTF-8.
BOM (Byte Order Mark) is a particular usage of the special Unicode character, whose appearance as a magic number at the start of a text stream can signal several things to a program reading the text. BOM use is optional. Its presence interferes with the use of UTF-8 by software that does not expect non-ASCII bytes at the start of a file but that could otherwise handle the text stream.  The use of BOM, therefore, is discouraged.
Here you have it, software that does not expect non-ASCII bytes at the start of a file can behave differently. Saving all the files with encoding UTF-8 instead of UTF-8 BOM and running the install script again resulted in 0 errors.
Now that the install script was working correctly I needed to find out why PL/SQL Developer saves files with BOM encoding. When searching the preferences I found in Files > Format the option “Encoding”. Default the checkbox “Save with BOM (Unicode Byte Order Mark)” is checked. By unchecking this option the files will now be saved correctly.
When faced with the “SP2-0734: unknown command beginning ..” error in SQL*Plus and you’re sure the statement is valid, please look for the encoding of your file. When files have the encoding UTF-8 BOM change this to UTF-8.