Avoiding INSERT glitches with SQL*Plus and SQLcl

Posted by

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.


  1. Hi Connor!

    Thanks fpr sharing!

    When I have to insert for example CSS with mediaqueries into a table, I replace the the @ sign with the equivalent chr(64) function value:

    ‘@media screen and (max-width: 991px) { #navbar-ctl { display: none; }’
    ‘ || chr(64) || ‘media screen and (max-width: 991px) { #navbar-ctl { display: none; }’

    another way is to prepend a CSS comment like:
    ‘/* */ @media screen and (max-width: 991px) { #navbar-ctl { display: none; }’

    But I prefer the former.

    Best Regards

  2. Just wondering if something like Q notation (sorry don’t know exact naming for this) could not solve this.

    insert into T values (q'[my sting @ someting else]’);

  3. btw quick googling will reveal:

    Probably not ideal solution, but I didn’t know that it is possible to disable some SQL*Plus commands.

    I’m just wondering about this sentence in documentation “Starting with Oracle Database 19c, the SQL*Plus table PRODUCT_USER_PROFILE (PUP table) is desupported. Oracle recommends that you protect data by using Oracle Database settings, so that you ensure consistent security across all client applications.” and how it should be handled by database settings. Also it is not clear from docs if it impacts also SQLcl

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.