10g qoute operator

[Dennis Schafthuizen]

In 10g it's possible to use the quote operator q.
For example:
select q'!John's shop!' title from dual;

In SQL*Plus the result is:
TITLE
-----------
John's shop

In PL/SQL Developer 7.0.2.1076 (SQL Window and Command Window) the following error occurs:
ORA-01756: quoted string not properly terminated.

When you use an extra quote it's working fine in PL/SQL Developer.
For example:
select q'!John's shop'!' title from dual;

Result:
TITLE
------------
John's shop'

The error also occurs using PL/SQL. For example:

begin
dbms_output.put_line(q'!John's shop !');
end;
/

 

[Marco Kalter]

This is probably an Oracle Client version issue. My guess is that you are using SQL*Plus 10g, and that PL/SQL Developer is using an Oracle9 (or earlier) client.

Could this be the case?

 

[Dennis Schafthuizen]

Yes, that's the case.
Using the 10g Oracle home solved the problem.

Thanks.

 

[Dennis Schafthuizen]

It works using the 10g ORACLE_HOME, but syntax highlighting in the SQL Window doesn't work in the following case:

select q'!John's shop!' from dual;

The following (in bold) is highlighted as comment:
select q'!John's shop!' from dual;
instead of:
select q'!John's shop!' from dual;

 

[Marco Kalter]

This is indeed a known restriction.

 

[Dennis Schafthuizen]

Can you put it on the list of enhancement requests? Above is just a simple example and then it's not a big deal, but when you use the quote operator in PL/SQL then the code could be unreadable.

 

[Marco Kalter]

This is on the list of enhancement requests. We will have to extend the syntax highlighting mechanism of the editor though.

 

[agent86]

This syntax works in packages with a 9i home with Version 7.0.1.1066.

In versions 7.0.2 and 7.0.3 it starts acting funky and even removes the procedure names from the contents window when viewing package body section.

Note this package. No procedure list shows in the package body until your remove the line where we use multiple q' syntax to add quotes around names. The package compiles and the procs still work but definitely strange.

In fact it seems in the package this is the only time this happens. Again this package works fine in version 7.0.1.1066.

create or replace package test is

procedure a;
--
--
procedure b;

end test;
/

create or replace package body test is

procedure a
is
v_str varchar2(234);
begin
v_str := q'#sdfkjdslfe'ssdfsdf#';
end a;
--
--
procedure b
is
v_sql varchar2(200);
v_name varchar2(2000) := 'marco';
begin
v_sql := q'#pass this in #'||''''||v_name||'''';
dbms_output.put_line(v_sql);

--comment this line to see the procedures names in the content window
v_sql := q'#pass this in '#'||v_name||q'#'#';

dbms_output.put_line(v_sql);
end b;

end test;
/

thanks

 

[Marco Kalter]

It's odd that this works in 7.0.1. We'll look into this.