10 Replies Latest reply: Jul 21, 2011 12:37 PM by Sven W. RSS

    Using quote delimiter with define off.

    877126
      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
          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
            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.
              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
                @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
                  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
                    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
                      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
                        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.
                          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
                            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.