Sql terminator ; in q syntax query block

[Michael D O'Shea/Woodward Informatics Ltd]

Hi chaps, it’s an anonymised example but does represent an ongoing issue I have at the moment.

Other than replacing the semicolon with another character, does anyone know whether the SQL terminator can be turned off? There is nothing obvious showing up with a „help set“ in sqlplus. I am surprised that this is even an issue given the multiline string is inside a q syntax block, but it is.

Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>




SQL>
SQL>
SQL>
SQL> select q'[A,B,C,D;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> E,F,G,H;
SP2-0042: unknown command "E,F,G,H" - rest of line ignored.
SQL> I,J]' x
SQL> from dual;
SP2-0042: unknown command "from dual" - rest of line ignored.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL>

[Michael D O'Shea/Woodward Informatics Ltd]

Perfect! Thanks.

Mike

http://www.strychnine.co.uk <http://www.strychnine.co.uk/>

You could assign sqlterminator to another character, or is this, what you mean with "replace" ?
SQL> set sqlterminator /
SQL> select q'{A,B,C,D;
2 E,F,G,H;
3 I,J}' x
4 from dual
5 /
X
---------------------
A,B,C,D;
E,F,G,H;
I,J
Regards
Maxim
Hi chaps, it’s an anonymised example but does represent an ongoing issue I have at the moment.
Other than replacing the semicolon with another character, does anyone know whether the SQL terminator can be turned off? There is nothing obvious showing up with a „help set“ in sqlplus. I am surprised that this is even an issue given the multiline string is inside a q syntax block, but it is.
Mike
http://www.strychnine.co.uk <http://www.strychnine.co.uk/>
SQL>
SQL>
SQL>
SQL> select q'[A,B,C,D;
ORA-01756: quoted string not properly terminated
SQL> E,F,G,H;
SP2-0042: unknown command "E,F,G,H" - rest of line ignored.
SQL> I,J]' x
SQL> from dual;
SP2-0042: unknown command "from dual" - rest of line ignored.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL>

[Jeff Smith]

You can turn if off with
set sqlterminator

SQL> set sqlterminator off
SQL> select q'[A,B,C,D
2 E,F,G,H;
3 I,J]' x
4 from dual;
5
SQL>


From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> On Behalf Of Michael D O'Shea/Woodward Informatics Ltd
Sent: Wednesday, March 17, 2021 2:31 PM
To: Oracle L <oracle-***@freelists.org>
Subject: [External] : Sql terminator ; in q syntax query block

Hi chaps, it’s an anonymised example but does represent an ongoing issue I have at the moment.

Other than replacing the semicolon with another character, does anyone know whether the SQL terminator can be turned off? There is nothing obvious showing up with a „help set“ in sqlplus. I am surprised that this is even an issue given the multiline string is inside a q syntax block, but it is.

Mike
http://www.strychnine.co.uk<https://urldefense.com/v3/__http:/www.strychnine.co.uk__;!!GqivPVa7Brio!Ir2IFv5lAgzTPz-WBT25i3X4T5R7IXovOH3h9QZYRufT3xWAtK6YKNykqLfBrEly0LY$>




SQL>
SQL>
SQL>
SQL> select q'[A,B,C,D;
ERROR:
ORA-01756: quoted string not properly terminated


SQL> E,F,G,H;
SP2-0042: unknown command "E,F,G,H" - rest of line ignored.
SQL> I,J]' x
SQL> from dual;
SP2-0042: unknown command "from dual" - rest of line ignored.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL>