Home » SQL & PL/SQL » SQL & PL/SQL » Searching a collection for a value (Oracle 10g2, Solaris)
Searching a collection for a value [message #527543] Tue, 18 October 2011 13:40 Go to next message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
Hi,

I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?

So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.

The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?

The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.

DECLARE
  CURSOR c_type_id_usg
  IS
    SELECT param_value
      FROM parameters
     WHERE proc_id = 112301
       AND parameter_name LIKE 'ID_LIST%';

  type type_id_tt is table of NUMBER;
  l_type_ids type_id_tt;
   
BEGIN
  
  -- get the list of sms type id usg values
  OPEN c_type_id_usg;
  FETCH c_type_id_usg bulk collect into l_type_ids;
  CLOSE c_type_id_usg;
  
  -- the above returns value 300,301,310,320,400,410

  IF l_type_ids.exists(310)
  then
    dbms_output.put_line('I have found an entry ');
  else
    dbms_output.put_line('I have NOT found an entry ' ); 
  end if;
  
END;


Re: Searching a collection for a value [message #527546 is a reply to message #527543] Tue, 18 October 2011 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I have not your table so I used ALL_USERS:
SQL> SELECT user_id, username
  2  FROM all_users
  3  WHERE username like 'M%';
   USER_ID USERNAME
---------- ------------------------------
        50 MICHEL
        46 MDSYS
        49 MGMT_VIEW
       235 MESDVD$PROPRIO
       236 MESDVD$LECTEUR
       237 MESDVD$TOUT

6 rows selected.

SQL> -- Not correct code
SQL> DECLARE
  2    CURSOR c_type_id_usg
  3    IS
  4      SELECT user_id
  5        FROM all_users
  6       WHERE username like 'M%';
  7  
  8    type type_id_tt is table of NUMBER;
  9    l_type_ids type_id_tt;
 10     
 11  BEGIN
 12    
 13    -- get the list of sms type id usg values
 14    OPEN c_type_id_usg;
 15    FETCH c_type_id_usg bulk collect into l_type_ids;
 16    CLOSE c_type_id_usg;
 17    
 18    IF l_type_ids.exists(50)
 19    then
 20      dbms_output.put_line('I have found an entry ');
 21    else
 22      dbms_output.put_line('I have NOT found an entry ' ); 
 23    end if;
 24    
 25  END;
 26  /
I have NOT found an entry

PL/SQL procedure successfully completed.

SQL> -- Correct code
SQL> DECLARE
  2    CURSOR c_type_id_usg
  3    IS
  4      SELECT user_id
  5        FROM all_users
  6       WHERE username like 'M%';
  7  
  8    type type_id_tt is table of NUMBER;
  9    l_type_ids type_id_tt;
 10     
 11  BEGIN
 12    
 13    -- get the list of sms type id usg values
 14    OPEN c_type_id_usg;
 15    FETCH c_type_id_usg bulk collect into l_type_ids;
 16    CLOSE c_type_id_usg;
 17    
 18    IF 50 member of l_type_ids 
 19    then
 20      dbms_output.put_line('I have found an entry ');
 21    else
 22      dbms_output.put_line('I have NOT found an entry ' ); 
 23    end if;
 24    
 25  END;
 26  /
I have found an entry

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Tue, 18 October 2011 13:59]

Report message to a moderator

Re: Searching a collection for a value [message #527550 is a reply to message #527546] Tue, 18 October 2011 14:11 Go to previous message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
Hi Michel,

Thanks.. member of is exactly what I am looking for.

Regards,

M
Previous Topic: Oracle Sql
Next Topic: parametric cursors pl sql
Goto Forum:
  


Current Time: Sat Aug 23 17:54:55 CDT 2025