7 Replies Latest reply: Jul 31, 2012 4:32 PM by damorgan RSS

    Dynamic Queryies and Single Quotes in Variable. how to escape?

    bostonmacosx
      What I'm dealing with is in the GROUPS variable. It comes in from a shuttle and one of the options has a single quote in it. I'm not sure how to escape the quote inside of the variable....thanks.
      Rob



      create or replace FUNCTION mobile_device_support(varstart IN VARCHAR,varstop IN VARCHAR,howgroup IN VARCHAR,hastotal IN VARCHAR,devices IN VARCHAR,groups IN VARCHAR)
      RETURN VARCHAR2
      is
      l_var VARCHAR2(32767);
      s_var VARCHAR2(32767);
      BEGIN
      l_var := q'!SELECT NULL LINK,to_char(open_time,'YYYY-!';
      l_var := l_var || howgroup;
      l_var := l_var || q'!') DATIME,!';

      IF hastotal = 'Y' THEN
      l_var := l_var || q'!sum(decode(LOGICAL_NAME,LOGICAL_NAME,'1',0)) "TOTAL",!';
      END IF;

      for r1 in (
      SELECT DISTINCT LOGICAL_NAME as STATE

      from

      SMINCREQ

      where

      LOGICAL_NAME LIKE '%mobile%'
      ORDER BY STATE

      )


      LOOP
      s_var :=substr(r1.state,0,29);
      l_var := l_var || 'sum(decode(LOGICAL_NAME,''' || r1.state || ''',''1'',0))"' || s_var || '",';
      END LOOP;
      l_var := rtrim(l_var, ',');
      l_var := l_var || ' FROM SMINCREQ WHERE open_time between to_date('''|| varstart ||''',''DD-MON-YYYY'') and to_date('''|| varstop ||''',''DD-MON-YYYY'') and ';
      l_var := l_var || '(INSTR('':';
      l_var := l_var || devices;
      l_var := l_var || ':'', '':''||LOGICAL_NAME||'':'') > 0) and';
      l_var := l_var || '(INSTR('':';
      l_var := l_var || groups;
      l_var := l_var || ':'', '':''||ASSIGNMENT||'':'') > 0)';

      l_var := l_var || 'GROUP BY to_char(open_time,''YYYY-'||howgroup||''') order by to_char(open_time,''YYYY-'||howgroup||''')';
      RETURN l_var;
      END;
        • 1. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
          damorgan
          1. Please read the FAQ and learn how to post listings.
          2. And your version number is?
          3. There is no conditional processing. Why are you using a cursor FOR loop?

          You can find examples of escaping characters here:
          http://www.morganslibrary.org/reference/wildcards.html
          full library of demos at:
          http://www.morganslibrary.org/library.html
          • 2. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
            Frank Kulash
            Hi,

            Instead of:
            l_var := l_var || groups;
            use
            l_var := l_var || REPLACE ( groups
                                            , ''''     -- 1 single-quote
                             , ''''''     -- 2 single-quotes
                             );
             

            I hope this answers your question.
            If not, post a few function calls, with different arguments, and the results that you want the function to return in each case.
            If any tables, such as SMINCREQ, are really necessary to illustrate the problem, then post CREATE TABLE and INSERT statements for them. (I suggest simplifying the function to get rid of them.)
            • 3. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
              bostonmacosx
              Thanks damorgan.

              As I'm just learning more advances SQL and just been introduced to Oracle and APEX

              "There is no conditional processing. Why are you using a cursor FOR loop?"

              are concepts which are still foreign to me. Sorry.
              If you look at my tags you will see the version of the database being used...thanks
              • 4. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
                bostonmacosx
                Thanks Frank.

                there is only one value which is -- TCPresident's Office -- which is causing the issue. I will try different forms of the replace to see what works.
                Just curious is there any way of using the string literal "Q" function to clean up the values brought in my the shuttle.

                Thanks.

                Robert
                • 5. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
                  Frank Kulash
                  Hi, Robert,
                  bostonmacosx wrote:
                  ... Just curious is there any way of using the string literal "Q" function to clean up the values brought in my the shuttle.
                  Yes, if I undertand the problem, the Q-notation (it's not a function) is another possibility. When you post the sample inputs and desired outputs, then I can show you.
                  • 6. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
                    rp0428
                    >
                    Just curious is there any way of using the string literal "Q" function to clean up the values brought in my the shuttle.
                    >
                    Perhaps you mean the alternative quoting mechanism; it is not a function and doesn't do any 'cleanup'.

                    It is a way to append a prefix and suffix to a string so that embedded single quotes are not interpreted.

                    See the Text Literals section of the SQL Reference
                    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#sthref335
                    >
                    In the bottom branch of the syntax:

                    •Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string.

                    •The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing quote_delimiter.

                    •c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.

                    •quote_delimiter is any single- or multibyte character except space, tab, and return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark.

                    If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.

                    Here are some valid text literals using the alternative quoting mechanism:

                    q'!name LIKE '%DBMS_%%'!'
                    q'<'So,' she said, 'It's finished.'>'
                    q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
                    nq'ï Ÿ1234 ï'
                    q'"name like '['"'
                    {quote}
                    • 7. Re: Dynamic Queryies and Single Quotes in Variable. how to escape?
                      damorgan
                      Never use PL/SQL to do what you can do in SQL.
                      Never open an explicit cursor if you do not need to do so.

                      PS: Frank is in Boston and one of the most knowledgeable people I have ever had the pleasure of meeting over seafood at Rabias (or anywhere else).