3 Replies Latest reply: Feb 19, 2013 11:46 PM by jeneesh RSS

    Error in block:

    878054
      The following error is occuring please resolve this error

      ORA-00936: missing expression

      DECLARE                                                                        
         V_CNL     VARCHAR2 (10)   := 'APDFGDFG';
         V_CMMMDE   VARCHAR2 (10)   := 'SMS';
         v_sql                 VARCHAR2 (2000);
         v_x                   VARCHAR2 (10);
         p_rc                  SYS_REFCURSOR;
      BEGIN
         v_sql := 'SELECT  COUNT(*) FROM  LOG tl WHERE ';
      
         SELECT SUBSTR (V_CNL, 1, 2)
           INTO v_x
           FROM DUAL;
      
         IF (v_x = 'AP')
         THEN
            IF (V_CMMMDE = 'GPRS')
            THEN
               v_sql := v_sql || ' and tl.commMode = ''GPRS''';
            ELSIF (V_CMMMDE = 'SMS')
            THEN
               v_sql := v_sql || 'and tl.commMode = ''SMS''';
            ELSE
               v_sql :=
                     v_sql
                  || ' and tl.commMode = ''GPRS'' OR and tl.commMode = ''SMS''';
            END IF;
         ELSIF (V_CNL = 'ALL')
         THEN
            IF (V_CMMMDE = 'GPRS')
            THEN
               v_sql := v_sql || ' and tl.commMode = ''GPRS''';
            ELSIF (V_CMMMDE = 'SMS')
            THEN
               v_sql := v_sql || ' and tl.commMode = ''SMS''';
            ELSE
               v_sql := v_sql || ' and tl.commMode = ''SMS''';
            END IF;
         END IF;
      
         DBMS_OUTPUT.put_line (v_sql);
      
         OPEN p_rc FOR v_sql;
      END;
      /
        • 1. Re: Error in block:
          jeneesh
          You have WHERE and AND together.
          Use this (Beware of SQL injection)
          DECLARE                                                                        
             V_CNL     VARCHAR2 (10)   := 'APDFGDFG';
             V_CMMMDE   VARCHAR2 (10)   := 'SMS';
             v_sql                 VARCHAR2 (2000);
             v_x                   VARCHAR2 (10);
             p_rc                  SYS_REFCURSOR;
          begin
             v_sql := 'SELECT  COUNT(*) FROM  LOG tl WHERE 1=1 '; --"Changed"
           
             SELECT SUBSTR (V_CNL, 1, 2)
               INTO v_x
               FROM DUAL;
           
             IF (v_x = 'AP')
             THEN
                IF (V_CMMMDE = 'GPRS')
                THEN
                   v_sql := v_sql || ' and tl.commMode = ''GPRS''';
                ELSIF (V_CMMMDE = 'SMS')
                THEN
                   v_sql := v_sql || 'and tl.commMode = ''SMS''';
                ELSE
                   v_sql :=
                         v_sql
                      || ' and tl.commMode = ''GPRS'' OR and tl.commMode = ''SMS''';
                END IF;
             ELSIF (V_CNL = 'ALL')
             THEN
                IF (V_CMMMDE = 'GPRS')
                THEN
                   v_sql := v_sql || ' and tl.commMode = ''GPRS''';
                ELSIF (V_CMMMDE = 'SMS')
                THEN
                   v_sql := v_sql || ' and tl.commMode = ''SMS''';
                ELSE
                   v_sql := v_sql || ' and tl.commMode = ''SMS''';
                END IF;
             END IF;
           
             DBMS_OUTPUT.put_line (v_sql);
           
             OPEN p_rc FOR v_sql;
          END;
          • 2. Re: Error in block:
            Billy~Verreynne
            This code will unnecessarily increase CPU utilisation of the database server. This code will cause fragmentation of the shared pool and cause ORA-04031 shared memory allocation errors. This code will open the database to SQL injection.

            Is that the intention behind this code?
            • 3. Re: Error in block:
              jeneesh
              And do you actually need dynamic SQL?
              Assuming the variables used in code wont have values other than you handled in the IF condition, the below will suffice your requirement
              SELECT  COUNT(*) 
              FROM  LOG tl 
              WHERE 
                  (
                    SUBSTR (V_CNL, 1, 2) = 'AP'
                    and 
                   ( 
                    (V_CMMMDE in ('GPRS','SMS') and tl.commMode = V_CMMMDE )
                       or
                       ( V_CMMMDE not in ('GPRS','SMS') and tl.commMode in ('GPRS','SMS')  ) 
                      )
                   )
              or (
                  V_CNL = 'ALL'
                  and 
                   ( 
                    (V_CMMMDE in ('GPRS','SMS') and tl.commMode = V_CMMMDE )
                       or
                       ( V_CMMMDE not in ('GPRS','SMS') and tl.commMode = 'SMS'  ) 
                      )
                 );