Solved

# Best way to find some value from NUMBER TYPE TABLE

Posted on 2014-07-14
Medium Priority
313 Views
TYPE NT IS TABLE OF NUMBER;
NTa NT := NT(1,2,3,4);

What is the best way to find that number 7 (Seven) is exist in the list NTa or not.

Thanks and Regards,
0
Question by:Sudees
1 Comment

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 40195355
EXISTS checks to see if the "element" is in the collection, that is, is there a value at position 7, not if there is a value of 7 somewhere in the collection.

for example...

``````SQL> DECLARE
2      TYPE nt IS TABLE OF NUMBER;
3
4      nta nt
5              := nt(
6                     10,
7                     20,
8                     30,
9                     40,
10                     50,
11                     60,
12                     70,
13                     80,
14                     90,
15                     100
16                 );
17  BEGIN
18      IF nta.EXISTS(7)
19      THEN
20          DBMS_OUTPUT.put_line('Found a value at position 7: ' || nta(7));
21      END IF;
22  END;
23  /
Found a value at position 7: 70

PL/SQL procedure successfully completed.
``````

the correct way to check existence of specific values in a collection is use MEMBER OF

``````SQL> DECLARE
2      TYPE nt IS TABLE OF NUMBER;
3
4      nta nt
5              := nt(
6                     4,
7                     3,
8                     1,
9                     7,
10                     2,
11                     9,
12                     6,
13                     8,
14                     5,
15                     0
16                 );
17  BEGIN
18      IF 7 MEMBER OF nta
19      THEN
20          DBMS_OUTPUT.put_line('Found a value 7 within collection');
21      END IF;
22  END;
23  /
Found a value 7 within collection

PL/SQL procedure successfully completed.
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month16 days, 14 hours left to enroll

#### 862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.