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

Improve this question
4
  • SET DEFINE OFF should escape the ampersand globally...stackoverflow.com/questions/12961215/… Commented Jul 16, 2019 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) Commented Jul 16, 2019 at 17:29
  • @MichaelKutz I've added a small POC script and a sample execution. Commented Jul 17, 2019 at 7:01
  • Your actual problem is that sqlplus does not deal with multi line string literals. Commented Jul 24, 2019 at 22:03

3 Answers 3

Reset to default
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.

Improve this answer
4
  • 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. Commented Jul 17, 2019 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. Commented Jul 18, 2019 at 9:29
  • 1
    Or prefix each @xxx clause with an empty comment (/* */ @xxx) Commented Jul 18, 2019 at 9:39
  • 1
    Great idea!! Thank you for your help! Commented Jul 18, 2019 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;
    }
}
';
Improve this answer
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 SQLPlus Security
9.1 Disabling SQL*Plus, SQL, and PL/SQL Commands

Improve this answer
3
  • 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! Commented Jul 26, 2019 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. Commented Jul 26, 2019 at 10:29
  • Thanks for the clarification! But still, that would be an undesirable side-effect. It's still good info though! Commented Jul 26, 2019 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.