How can I disable the @ symbol in SQL scripts for sqlplus?

Question

In Oracle's sqlplus tool, when the script contains an @ symbol, it loads an SQL script with that name and executes it. I'm inserting a VARCHAR value which contains some CSS code, so when it encounters a @media query or @keyframes it tries to execute media.sql, which is not the desired effect. This does not happen when I execute the script in the Oracle SQL Developer window.

A small proof-of-concept script:

SET DEFINE OFF
SET SQLBLANKLINES ON
SET SQLTERMINATOR OFF
SET SQLPREFIX '%'
SET SCAN OFF
DECLARE
 loginId NUMBER;
 ncount NUMBER;
 css VARCHAR2(32767) DEFAULT '
@media only screen and (min-width: 980px) {
 .footer-column-copy {
 margin: 0;
 padding-top: 0;
 padding-bottom: 80px;
 background-size: 55px;
 background-position: top right;
 float: left;
 width: 260px;
 }
}
';

This triggers the following error:

$ echo @poc.sql | sqlplus username/password@localhost
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 17 08:53:16 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SP2-0310: unable to open file "LOGIN.SQL"
SQL> SP2-0310: unable to open file "media.sql"
 16
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

In the same way that SET SQLPREFIX can disable the parsing of the # symbol, how can I disable the parsing of the @ symbol (or change it to something else like the SET SQLPREFIX command does)?

• kevinskio: SET DEFINE OFF should escape the ampersand globally...stackoverflow.com/questions/12961215/…
• Michael Kutz: I've ran into this issue with PL/Doc tags ( @headcom, @params, etc). I need to see your INSERT statement with offending CSS text before I can give advice. Please update your post. Thanks. (oh.. and all 4 digits of SQL Plus)
• Daniel Gray: @MichaelKutz I've added a small POC script and a sample execution.
• eckes: Your actual problem is that sqlplus does not deal with multi line string literals.

Answer 1

The problem really only occurs because you placed the @media directive at the beginning of a new line. If you change your code like this:

css VARCHAR2(32767) DEFAULT '@media only screen and (min-width: 980px) {

or like this:

css VARCHAR2(32767) DEFAULT 
'@media only screen and (min-width: 980px) {

then it works fine.

I know it does not answer your specific question about ignoring the @ character, but it does solve your problem ...

More generally, all you need is to avoid having any @xxxx at the beginning of a line you pass to sqlplus. Just place it at the back of the previous line.

Note that just adding white space at the beginning of the line won't work.

Another possibility is to add a comment in front of each @xxxx clause, no matter where it is located. Something like this:

css VARCHAR2(32767) DEFAULT '
/* */ @media only screen and (min-width: 980px) {

sqlplus will handle that as regular text. And your CSS parser should ignore it as a comment.

• Daniel Gray: Not a solution to my problem. The @media entry is inside the VARCHAR value, not at the beginning, so I cannot move the ' next to it.
• Albert Godfrind: I see. Still, all you need is to make sure the @media is not at the beginning of a line you pass to sqlplus. The location of the ' is not important.
• Albert Godfrind: Or prefix each @xxx clause with an empty comment (/* */ @xxx)
• Daniel Gray: Great idea!! Thank you for your help!

Answer 2

Concatenation might do the trick. That would keep the CSS clean.

SET DEFINE OFF
SET SQLBLANKLINES ON
SET SQLTERMINATOR OFF
SET SQLPREFIX '%'
SET SCAN OFF
DECLARE
 loginId NUMBER;
 ncount NUMBER;
 css VARCHAR2(32767) DEFAULT '
' || '@media only screen and (min-width: 980px) {
 .footer-column-copy {
 margin: 0;
 padding-top: 0;
 padding-bottom: 80px;
 background-size: 55px;
 background-position: top right;
 float: left;
 width: 260px;
 }
}
';

Answer 3

You can disable the sqlplus START command and thereby the functionality of @ and @@ by inserting an appropriate entry in the PRODUCT_USER_PROFILE table in the SYSTEM schema

connect system@tnsname
INSERT INTO PRODUCT_USER_PROFILE
 VALUES ('SQL*Plus', 'MYUSER', 'START', NULL, NULL, 'DISABLED', NULL, NULL);

MYUSER must be substituted by the appropriate user name.

Starting with Oracle Database 19c, the SQL*Plus table PRODUCT_USER_PROFILE (PUP table) is desupported.

SQLPlus
User's Guide and Reference
19c
E96459-02
April 2019
9 SQLPlus Security
9.1 Disabling SQL*Plus, SQL, and PL/SQL Commands

• Daniel Gray: Good data to have, but I do not really want to disable it system-wide... just for my session. In any case, this is excellent information to have. Thank you!
• miracle173: You don't disable it system-wide, but you disable it for a user. But you can't disable it on session level.
• Daniel Gray: Thanks for the clarification! But still, that would be an undesirable side-effect. It's still good info though!