Avoiding INSERT glitches with SQL*Plus and SQLcl

Sometimes in data centric applications, we want to store metadata (such as HTML or CSS or other such text) in a CLOB in a database table. For example, I might have a HTML file called “demo.html” that I want to later serve up to clients via APEX.

If I want to simply change that to a SQL script to store the contents into a table, I might compose something along these lines:

but unfortunately, sometimes things are not that simple. Here is how SQL*Plus reacts to that script execution.

By default, SQL*Plus assumes that most of the time you are going to be running simple SQL commands and the default settings are tailored to this behaviour. A common technique to try workaround this is to nest the entire contents in a PL/SQL variable but even that will not always work.

For the majority of the time, you can solve this issue by digging into the SQL*Plus settings that control how things like blank lines, whitespace, semi-colons, hash signs and the like are interpreted. I’ll typically use the 5 settings below to ensure that my script will execute successfully. Note that because I’ve turned off the “sqlterminator”, a semi-colon no longer initiates statement execution, so I need to explicitly code a forward slash at the end of the statement to run it.

However, one small problem still remains that SQ*Plus cannot overcome. If you encounter an “@” sign as the first character on a line, then SQL*Plus is going to interpret that as a “run a script” command. Notice line 25 below:

If you know of a way to workaround this (without changing the original content), please drop me a comment, but even running SQL*Plus with a RESTRICT level of 3 (which blocks “@” commands) will still not work. You’ll get an error about using “@” but that line will still be dropped from the inserted data.

Perhaps the easiest way to avoid the issue is to use SQLcl. It will still need a few tweaks to the default settings, but your data will make it into the database unadulterated.

TL;DR: Use the “SET” commands to ensure your metadata inserts do not get corrupted.