1

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)?

  • SET DEFINE OFF should escape the ampersand globally...stackoverflow.com/questions/12961215/… – kevinsky Jul 16 at 16:39
  • 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) – Michael Kutz Jul 16 at 17:29
  • @MichaelKutz I've added a small POC script and a sample execution. – Daniel Gray Jul 17 at 7:01
  • Your actual problem is that sqlplus does not deal with multi line string literals. – eckes Jul 24 at 22:03
3

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.

  • 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. – Daniel Gray Jul 17 at 9:19
  • 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 Jul 18 at 9:29
  • 1
    Or prefix each @xxx clause with an empty comment (/* */ @xxx) – Albert Godfrind Jul 18 at 9:39
  • 1
    Great idea!! Thank you for your help! – Daniel Gray Jul 18 at 12:49
1

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;
    }
}
';
1

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 SQL
Plus Security
9.1 Disabling SQL*Plus, SQL, and PL/SQL Commands

  • 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! – Daniel Gray Jul 26 at 10:08
  • You don't disable it system-wide, but you disable it for a user. But you can't disable it on session level. – miracle173 Jul 26 at 10:29
  • Thanks for the clarification! But still, that would be an undesirable side-effect. It's still good info though! – Daniel Gray Jul 26 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.