Best way to find some value from OBJECT TYPE TABLE

create or replace TYPE r_type  FORCE AS RECORD
                   (
      Code NUMBER(15),
      Description NUMBER(15));


create or replace procedure t1 as
      TYPE tr_type IS TABLE OF r_type;
      tr_type_i tr_type;
begin
      r_type(1).code := '1';
      r_type(1).description := 't1';

      r_type(2).code := '1';
      r_type(2).description := 't1';

      r_type(3).code := '1';
      r_type(3).description := 't1';
end;

If I want to search for code 4 in r_type.code column as is it exist or not .

What is the best way to do that

Thanks and Regards
SudeesSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
You have several syntax errors that must be resolved before you can attempt the search for a particular value

first - your TYPE creation isn't correct.  If you create a SQL type, then it can't be of type RECORD,  it must be an OBJECT
second - your collection references are using the type name not the collection variable
third - you never initialize your collection
fourth - you never intialize each of the objects in your collection
fifth - your object has 2 numbers but you are assigning 2 strings


try this... (just an example, not necessarily ideal syntax, but this at least will compile and run)
CREATE OR REPLACE TYPE r_type
    FORCE AS OBJECT
(
    code NUMBER(15),
    description VARCHAR2(15)
);
/

CREATE OR REPLACE PROCEDURE t1
AS
    TYPE tr_type IS TABLE OF r_type;

    tr_type_i tr_type := tr_type();
BEGIN
    tr_type_i.EXTEND(3);
    tr_type_i(1) := r_type(NULL, NULL);
    tr_type_i(1).code := 1;
    tr_type_i(1).description := 't1';

    tr_type_i(2) := r_type(NULL, NULL);
    tr_type_i(2).code := 1;
    tr_type_i(2).description := 't1';

    tr_type_i(3) := r_type(NULL, NULL);
    tr_type_i(3).code := 1;
    tr_type_i(3).description := 't1';
END;
/

Open in new window


To search for a piece of an object is not as easy as simply using MEMBER OF with regular scalar types.

You'll have to iterate or use sql, but sql would be more a expensive operation.

Using iteration might look something like this...

CREATE OR REPLACE PROCEDURE t1
AS
    TYPE tr_type IS TABLE OF r_type;

    tr_type_i tr_type := tr_type();
    i         INTEGER;
BEGIN
    tr_type_i.EXTEND(3);
    tr_type_i(1) := r_type(NULL, NULL);
    tr_type_i(1).code := 1;
    tr_type_i(1).description := 't1';

    tr_type_i(2) := r_type(NULL, NULL);
    tr_type_i(2).code := 1;
    tr_type_i(2).description := 't1';

    tr_type_i(3) := r_type(NULL, NULL);
    tr_type_i(3).code := 1;
    tr_type_i(3).description := 't1';

    i := tr_type_i.FIRST;

    WHILE i IS NOT NULL AND tr_type_i(i).code != 4
    LOOP
        i := tr_type_i.NEXT(i);
    END LOOP;

    IF i IS NOT NULL AND tr_type_i(i).code = 4
    THEN
        DBMS_OUTPUT.put_line('Found an object in the collection with code=4 at position:' || i);
    ELSE
        DBMS_OUTPUT.put_line('Unable to find object with code=4');
    END IF;
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SudeesSoftware DeveloperAuthor Commented:
Thanks for your effort..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.