Avoiding INSERT glitches with SQL*Plus and SQLcl

March 7, 2023

2 minutes

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.

9 responses to “Avoiding INSERT glitches with SQL*Plus and SQLcl”

Maurice

March 7, 2023 at 8:56 pm

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; }’
becomes
‘ || 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
Maurice

Reply
Peter

March 7, 2023 at 8:58 pm

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]’);

Reply
Peter

March 7, 2023 at 9:07 pm

btw quick googling will reveal:
https://dba.stackexchange.com/questions/243007/how-can-i-disable-the-symbol-in-sql-scripts-for-sqlplus

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

Reply
1. Connor McDonald
  
  March 7, 2023 at 9:18 pm
  
  sqlcl does not use that table to my knowledge
  
  Reply
Connor McDonald

March 7, 2023 at 9:17 pm

it does, but in all these cases, we’re now forced to examine/modify the content before we script it …. that’s a pain in my book (and risks errors)

Reply
What’s New? – Oracle Analytics by Adrian Ward

March 18, 2023 at 9:08 pm

[…] 8. Avoiding INSERT glitches with SQL*Plus and SQLcl […]

Reply
Yuri

April 27, 2023 at 10:07 pm

Hi Connor,

” 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.”

I heavily use “@” sign rather like an include directive. For example
============================================
SQL>host type dual.sql
select * from dual;
SQL>declare
2 cursor a is
3 @dual
4 begin
5 open a;
6 close a;
7 end;
8 /

PL/SQL procedure successfully completed.
============================================
Above code works on both SQL*Plus and SQLCli

But I have cases like the following
============================================
SQL>set serveroutput on
SQL>declare
2 a varchar2(100) :=
3 q'[
4 @dual
5 ]’;
6 begin
7 dbms_output.put_line(a);
8 end;
9 /

select * from dual;

PL/SQL procedure successfully completed.
============================================
This works perfectly in SQL*Plus,
but in SQLCli the output is:

@dual

And all my script set gets useless for SQLCli 😦
Is there any way to fix it for SQLCli?

Reply
1. Connor McDonald
  
  April 28, 2023 at 12:02 am
  
  I’ll ask around and see what I can find out
  
  Reply
2. Jared
  
  June 6, 2023 at 12:46 am
  
  I have found the sqlcl team pretty responsive to issues where sqlplus does something that sqlcl cannot. Just ask them.
  
  Reply