10 Replies Latest reply: Feb 8, 2013 7:36 AM by Etbin RSS

    How to compare values in collection.

    Sudhir_Meru
      Hi,

      I am using oracle 11g XE, I am doing a validation using bulk collect collection.
      DECLARE
      TYPE TEM IS TABLE OF VARCHAR2(1000);
      EM TEM;
      P_MAIL VARCHAR2(1000);
      
      BEGIN
      
      P_MAIL := UPPER('sudhir@gmail.com');
      
      SELECT EMAIL BULK COLLECT INTO EM FROM
      (
      SELECT UPPER(EMAIL_ID)   AS  EMAIL FROM SONARA_CONSULTANT_DETAILS
      UNION
      SELECT EMAIL_ID_2 AS  EMAIL FROM SONARA_CONSULTANT_DETAILS
      ) WHERE EMAIL IS NOT NULL;
      
      
        FOR I IN EM.FIRST..EM.LAST LOOP   
          
          IF  P_MAIL = EM(I) THEN    // Here i am comparing the mail id with collection but it always goes to INVALID 
          DBMS_OUTPUT.PUT_LINE('VALID');        
      
           ELSE
           DBMS_OUTPUT.PUT_LINE('INVALID');        
      
          END IF;
          
        END LOOP;
        
      
      END;
      } 
      Please suggest me how to do the comparison

      Thanks
      Sudhir
        • 1. Re: How to compare values in collection.
          793996
          Here MEMBER OF operator for a collection can be used. Not sure how much it fits your requirement.

          IF P_MAIL MEMBER OF EM THEN
          DBMS_OUTPUT.PUT_LINE('VALID');
          ELSE
          DBMS_OUTPUT.PUT_LINE('INVALID');
          END IF;


          ...Vivek
          • 2. Re: How to compare values in collection.
            Billy~Verreynne
            Sudhir_Meru wrote:

            I am using oracle 11g XE, I am doing a validation using bulk collect collection.
            Your code does not show any validation. It shows filtering. By doing it the most inefficient, slow and unscalable way possible.

            It reads, and duplicates, the SQL data into very expensive PL/SQL memory array (aka collection). It then steps through the collection an item at a time, looking for a specific matching value.

            The correct way to filter is to use a SQL predicate:
            select * from table where column = 'some-value'
            If the entire table wants to be compared to a specific value:
            select
               pk,
               case when column = 'value' then 'VALID'
               else 'INVALID'
               end                  as MATCH_RESULT
            from table
            • 3. Re: How to compare values in collection.
              dariyoosh
              It seems that you don't compare everywhere in uppercase, so for me this is rather a letter case problem in your string addresses. The following works for me
              DECLARE
                  TYPE TEM IS TABLE OF VARCHAR2(1000);
                  EM TEM;
                  P_EMAIL VARCHAR2(1000) := 'sudhir@gmail.com';
              BEGIN
                  WITH SONARA_CONSULTANT_DETAILS AS
                  (
                      SELECT 'email1@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'email2@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'sudhir@gmail.com'  AS emailAddr FROM DUAL UNION
                      SELECT 'email4@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'email5@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'email6@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'email7@domain.com' AS emailAddr FROM DUAL UNION
                      SELECT 'email8@domain.com' AS emailAddr FROM DUAL
                  )
                  SELECT emailAddr BULK COLLECT INTO EM
                  FROM SONARA_CONSULTANT_DETAILS;
                  
                  FOR indx IN EM.FIRST .. EM.LAST 
                  LOOP
                      IF UPPER(P_EMAIL) = UPPER(EM(indx))
                      THEN
                          DBMS_OUTPUT.PUT_LINE('VALID');
                      ELSE
                          DBMS_OUTPUT.PUT_LINE('INVALID');
                      END IF;
                  END LOOP;
              END;
              /
              Which gives me the following result
              INVALID
              INVALID
              INVALID
              INVALID
              INVALID
              INVALID
              INVALID
              VALID
              
              PL/SQL procedure successfully completed.
              
              SQL> 
              Regards,
              Dariyoosh
              • 4. Re: How to compare values in collection.
                Etbin
                Generally you can't be sure the em table does not contain null values.
                I don't remember ever doing something alike so consider it as template/pseudocode
                ix := em.first;
                while true
                loop 
                
                  if upper(em(ix)) = upper(test_value) then
                    ... /* valid */
                  else
                    ... /* invalid */
                  end if;
                  
                  if ix >= em.last then
                    exit;
                  else
                    ix ;= em.next;
                  end if;
                  
                end loop;
                Regards

                Etbin
                • 5. Re: How to compare values in collection.
                  Billy~Verreynne
                  dariyoosh wrote:
                  It seems that you don't compare everywhere in uppercase, so for me this is rather a letter case problem in your string addresses.
                  So never mind that this approach is wrong, consumes private process memory unnecessarily, is slow, cannot scale, and fails to use the SQL language correctly?

                  Code that compiles, executes, and produce the expected output, does NOT imply code that is correctly designed and correctly written.
                  • 6. Re: How to compare values in collection.
                    dariyoosh
                    @ Billy Verreynne      
                    The question was not about the approach and I don't remember to have given any idea about this (as you already did in fact properly) yet, he didn't understand what was the problem with his (non efficient) solution which I believe was the letter case I just wanted to point that out (as it may appear in his future problems).

                    So take a Cafe and calm down.

                    Edited by: dariyoosh on Feb 8, 2013 2:06 PM
                    • 7. Re: How to compare values in collection.
                      Billy~Verreynne
                      Etbin wrote:
                      Generally you can't be sure the em table does not contain null values.
                      I don't remember ever doing something alike so consider it as template/pseudocode
                      The sample code is for processing an associative array - makes little sense to step through a normal array in this fashion.

                      A bulk collect always instantiates the array. It will thus not be null (nor will a NO_DATA_FOUND exception be raised):
                      SQL> declare
                        2          type TObjectNames is table of varchar2(30);
                        3          nameArray       TObjectNames;
                        4  begin
                        5          select
                        6                  object_name bulk collect into nameArray
                        7          from    all_objects
                        8          where   1=2;
                        9  
                       10          if nameArray is null then
                       11                  dbms_output.put_line( 'nameArray is null' );
                       12          else
                       13                  dbms_output.put_line( 'nameArray is not null' );
                       14          end if;
                       15  end;
                       16  /
                      nameArray is not null
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> 
                      You can therefore safely use a FOR loop to step though the array as follows:
                      for i in 1..nameArray.Count loop
                        // do stuff
                      end loop;
                      • 8. Re: How to compare values in collection.
                        Billy~Verreynne
                        dariyoosh wrote:
                        @ Billy Verreynne      
                        The question was not about the approach and I don't remember to have given any idea about this (as you already did in fact properly) yet, he didn't understand what was the problem with his (non efficient) solution which I believe was the letter case I just wanted to point that out (as it may appear in his future problems).
                        Then point out that string comparison is case sensitive. But you elected to post FLAWED code showing that. Which the OP may very well take as the correct way to do it, copy-and-paste it, and use it - not understanding that code that seems to work does not imply correctly designed and written code.
                        So take a Cafe and calm down.
                        Coffee you mean? How about you first deleting that flawed sample code?
                        • 9. Re: How to compare values in collection.
                          Sudhir_Meru
                          Thanks Billy your suggestion was straight forward.
                          • 10. Re: How to compare values in collection.
                            Etbin
                            Appreciating the clarification.
                            I'll try to remember although I cannot imagine a situation when I'll have to use something alike (seems breaking never say never).

                            Regards

                            Etbin