This discussion is archived
10 Replies Latest reply: Jul 21, 2011 10:37 AM by Sven W. RSS

Using quote delimiter with define off.

877126 Newbie
Currently Being Moderated
I am having issues with the the quote delimiter picking up the colon as a bind variable. For some reason when there is a tick infront of the colon it will break (ex. 2) but in (ex. 3) it is behind the colon it will work fine.

 set define off
DECLARE
 s1 VARCHAR2(20);
 s2 VARCHAR2(20);
 s3 VARCHAR2(20);
BEGIN
  s1 := 'Isn''t t:his cool';
  --s2 := q'{Isn't th:is cool}';
  s3 := q'{Isnt th:is co'ol}';
  dbms_output.put_line(s1);
  dbms_output.put_line(s2);
  dbms_output.put_line(s3);
END;
/
  • 1. Re: Using quote delimiter with define off.
    Solomon Yakobson Guru
    Currently Being Moderated
    Version??? I can't reproduce it on:
    SQL> select * from v$version
      2  /
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> set define off
    SQL> DECLARE
      2   s1 VARCHAR2(20);
      3   s2 VARCHAR2(20);
      4   s3 VARCHAR2(20);
      5  BEGIN
      6    s1 := 'Isn''t t:his cool';
      7    s2 := q'{Isn't th:is cool}';
      8    s3 := q'{Isnt th:is co'ol}';
      9    dbms_output.put_line(s1);
     10    dbms_output.put_line(s2);
     11    dbms_output.put_line(s3);
     12  END;
     13  /
    Isn't t:his cool
    Isn't th:is cool
    Isnt th:is co'ol
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> set define off
    SQL> DECLARE
      2   s1 VARCHAR2(20);
      3   s2 VARCHAR2(20);
      4   s3 VARCHAR2(20);
      5  BEGIN
      6    s1 := 'Isn''t t:his cool';
      7    s2 := q'{Isn't th:is cool}';
      8    s3 := q'{Isnt th:is co'ol}';
      9    dbms_output.put_line(s1);
     10    dbms_output.put_line(s2);
     11    dbms_output.put_line(s3);
     12  END;
     13  /
    Isn't t:his cool
    Isn't th:is cool
    Isnt th:is co'ol
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.
  • 2. Re: Using quote delimiter with define off.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Don't confuse bind variables with SQL*Plus substitution variables. The two are often used for similar purposes, but they work in quite different ways.

    A colon inside single-quotes quotes is just the punctuation character; has no special meaning.
    For example:
    VARIABLE  his  VARCHAR2 (20)
    EXEC     :his  := 'hrowing the discus';
    
    SELECT      q'{Isn't t:his cool}'     AS txt
    FROM      dual;
    produces this output:
    TXT
    ----------------
    Isn't t:his cool
    If you want to use the bind variable :his, don't enclose it in single quotes. For example:
    SELECT      q'{Isn't t}' ||     -- This is a string literal
          :his            ||     -- this is a bind variable
           q'{ cool}'     AS txt
    FROM      dual;
    Output:
    TXT
    --------------------------------------------
    Isn't throwing the discus cool
  • 3. Re: Using quote delimiter with define off.
    Sven W. Guru
    Currently Being Moderated
    me too can not reproduce it on a 10.2.0.5.
    SQL2> DECLARE
      2   s1 VARCHAR2(20);
      3   s2 VARCHAR2(20);
      4   s3 VARCHAR2(20);
      5  BEGIN
      6    s1 := 'Isn''t t:his cool';
      7    s2 := q'{Isnt th:is co'ol}';
      8     s3 := q'{Isnt th:is co'ol}';
      9     dbms_output.put_line(s1);
     10     dbms_output.put_line(s2);
     11     dbms_output.put_line(s3);
     12  END;
     13  / 
    Isn't t:his cool
    Isnt th:is co'ol
    Isnt th:is co'ol
    
    PL/SQL procedure successfully completed.
    
    SQL>  
    Which client tool did you test with? Maybe there is a problem in that tool.
  • 4. Re: Using quote delimiter with define off.
    768636 Explorer
    Currently Being Moderated
    @SY,

    Can you please explain the difference ?
    SQL> select  * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE    9.2.0.8.0       Production
    TNS for HPUX: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    
    SQL> SET define off
    SQL> 
    SQL> DECLARE
      2     s1   VARCHAR2 (20);
      3     s2   VARCHAR2 (20);
      4     s3   VARCHAR2 (20);
      5  BEGIN
      6     s1 := 'Isn''t t:his cool';
      7     s2 := q'{Isn't th:is cool}';
      8     s3 := q'{Isnt th:is co'ol}';
      9     DBMS_OUTPUT.put_line (s1);
     10     DBMS_OUTPUT.put_line (s2);
     11     DBMS_OUTPUT.put_line (s3);
     12  END;
     13  /
       s2 := q'{Isn't th:is cool}';
              *
    ERROR at line 7:
    ORA-06550: line 7, column 11:
    PLS-00103: Encountered the symbol "{Isn" when expecting one of the following:
    . ( * @ % & = - + ; < / > at in is mod not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like
    between ||
    
    
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    
    SQL> SET define off
    SQL> 
    SQL> DECLARE
      2     s1   VARCHAR2 (20);
      3     s2   VARCHAR2 (20);
      4     s3   VARCHAR2 (20);
      5  BEGIN
      6     s1 := 'Isn''t t:his cool';
      7     s2 := q'{Isn't th:is cool}';
      8     s3 := q'{Isnt th:is co'ol}';
      9     DBMS_OUTPUT.put_line (s1);
     10     DBMS_OUTPUT.put_line (s2);
     11     DBMS_OUTPUT.put_line (s3);
     12  END;
     13  /
    Isn't t:his cool
    Isn't th:is cool
    Isnt th:is co'ol
    
    PL/SQL procedure successfully completed.
    Thanks in advance.
  • 5. Re: Using quote delimiter with define off.
    AdamMartin Pro
    Currently Being Moderated
    What version of SQL*Plus are you using? What Oracle Client are you using?

    I can get the error or success in the same database depending on the client.

    11g database with an older client:
    SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 21 13:09:08 2011
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select q'{Let's meet.}' from dual;
    ERROR:
    ORA-01756: quoted string not properly terminated
    And it works (still same database) with a modern client:
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 21 13:10:25 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select q'{Let's meet.}' from dual;
    
    Q'{LET'SMEE
    -----------
    Let's meet.
    
    SQL>
  • 6. Re: Using quote delimiter with define off.
    877126 Newbie
    Currently Being Moderated
    Here is the version through SQL Plus. Looks like I need to get to a higher realease.
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> SET define off
    SQL>
    SQL> DECLARE
      2   s1 VARCHAR2(20);
      3   s2 VARCHAR2(20);
      4   s3 VARCHAR2(20);
      5  BEGIN
      6    s1 := 'Isn''t t:his cool';
      7    s2 := q'{Isn't th:is cool}';
      8    s3 := q'{Isnt th:is co'ol}';
      9    dbms_output.put_line(s1);
     10    dbms_output.put_line(s2);
     11    dbms_output.put_line(s3);
     12  END;
     13  /
    SP2-0552: Bind variable "IS" not declared.
    Edited by: user10241673 on Jul 21, 2011 10:19 AM
  • 7. Re: Using quote delimiter with define off.
    AdamMartin Pro
    Currently Being Moderated
    No, that is not showing you your local Oracle client version.

    Look at my results for the 9.2 client (the one that failed above):
    SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 21 13:21:15 2011
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from v$version
      2  /
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for Solaris: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
  • 8. Re: Using quote delimiter with define off.
    877126 Newbie
    Currently Being Moderated
    SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 21 12:26:27 2011
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> SET define off
    SQL>
    SQL> DECLARE
      2   s1 VARCHAR2(20);
      3   s2 VARCHAR2(20);
      4   s3 VARCHAR2(20);
      5  BEGIN
      6    s1 := 'Isn''t t:his cool';
      7    s2 := q'{Isn't th:is cool}';
      8    s3 := q'{Isnt th:is co'ol}';
      9    dbms_output.put_line(s1);
     10    dbms_output.put_line(s2);
     11    dbms_output.put_line(s3);
     12  END;
     13  /
    SP2-0552: Bind variable "IS" not declared.
  • 9. Re: Using quote delimiter with define off.
    Sven W. Guru
    Currently Being Moderated
    In this case simply update your client. Since your database in on version 10 already you should also use a client version 10.

    9i and 10g client can be installed alnongside on one PC without problems. It is just that the 10g client is installed with a little more sub folders. maybe you already have it installed and just still use the old client (old link?).

    On a windows system the 10g client would be in a structure like this: file://c:/oracle/product/10.2.0/client_1/bin

    whereus the 9i client is in : file://c:/oracle/ora92/bin

    Edited by: Sven W. on Jul 21, 2011 7:33 PM

    Edited by: Sven W. on Jul 21, 2011 7:36 PM
  • 10. Re: Using quote delimiter with define off.
    Solomon Yakobson Guru
    Currently Being Moderated
    user10241673 wrote:
    SQL*Plus: Release *9.2.0.1.0* - Production on Thu Jul 21 12:26:27 2011
    I am pretty sure you need to ugrade client software. 9i does not support Q literals and SQL*Plus parser fails. Try:
    DECLARE
        s1 VARCHAR2(20);
    BEGIN
        s1 := q'{Isn't this cool}';
    END;
    /
    and see if it fails.

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points