[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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
0
Sudees
Asked:
Sudees
1 Solution
 
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
 
SudeesSoftware DeveloperAuthor Commented:
Thanks for your effort..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now