Searching for Objects

Searching for Objects

The recommended way of searching for objects is using the VY_CONTENTS_MV and VY_INDEX tables. Those tables store reference information about all the database objects. The IDs, names and types of all Vine objects are stored in VY_CONTENTS_MV. All object names, as well as separate words constituting these names, are stored in VY_INDEX.

That is why the most efficient search would start from locating in the VY_INDEX table all occurrences of an object's query name.

select id from vineyarddb.vy_index_view where word like upper('ACME') || '%'

That SELECT will give you a number of IDs that you should check for the object that you are looking for. That should be done using the VY_CONTENTS view. For instance the following SELECT will check all the objects found from the VY_INDEX table for their validity (status='B'), availability (SHOWGROUP<>-2) and correspondence to the given object type (Objecttype=VY_COMPANY):

SELECT c.id, c.name || decode(help, '', '', ' (' || help || ')'), substr(c.objecttype,4), c.status, c.name

FROM vineyarddb.vy_contents_view c

WHERE c.id in (select id from vineyarddb.vy_index_view where word like upper('ACME') || '%')
and c.status = 'B' and c.showgroup <> -2 and c.objecttype = 'VY_COMPANY'

Example: The following example will find and print in a loop all objects of the 'VY_COMPANY' type which names include a string starting from the 'ACME' sequence.

set serveroutput on;

declare

querynamevarchar2(50);

idnumber;

namevarchar2(50);

objecttype varchar2(50);

statechar(1);

shortnamevarchar2(50);

--typical search cursor

CURSOR SEARCH(QNAME varchar2, OTYPE varchar2) IS

SELECT c.id, c.name || decode(help, '', '', ' (' || help || ')'), substr(c.objecttype,4), c.status, c.name

FROM vineyarddb.vy_contents_view c

WHERE c.id in (select id from vineyarddb.vy_index_view where word like upper(QNAME) || '%')
and c.status = 'B' and c.showgroup <> -2 and (c.objecttype = OTYPE or OTYPE is null)

order by c.OBJECTTYPE, c.name;

begin

queryname:=vineyarddb.vyutilapi.makequeryword('ACME');

objecttype:='VY_COMPANY';

OPEN Search( queryname,objecttype);

LOOP

FETCH Search INTO id, name, objecttype, state, shortname;

-- print results

dbms_output.put_line(id||' '||name||' '||objecttype
||'    '||state||'      '||shortname);

EXIT WHEN Search%notfound;

END LOOP;

CLOSE Search;

end;