Matching a collection with a dataset

From: Alexander Miroshnikov <alexander_miroshnikov_at_hotmail.com>
Date: Thu, 23 Jan 2003 06:15:43 -0000
Message-Id: <1043302640.95789.0@demeter.uk.clara.net>

Hi,

My PLSQL code prepares an in-memory collection of text strings associated with some document. It then searches a database table for an existent document, that has a matching collection of text strings (stored in another database table) associated with it. I would like to find how to do it in the most efficient fashion.

Let me illustrate this task with an example.

1. I have a string collection type -

create or replace type strings as table of varchar2(4000)
/

2. My PLSQL code prepares an in-memory collection of text strings associated with some document.

declare
  data strings;
begin
  data := strings(

    'Hi,',
    '',
    'My PLSQL code prepares ...'

  );
end;
/

3. I have a table that stores documents and a table that stores text strings associated with documents.

create table document(
  id integer primary key,
  subject varchar2(100)
)
/

create table document_text(
  id integer not null,
  line integer not null,
  text varchar2(4000),
  constraint pk_document_text primary key(id,line),   constraint fk_document_text foreign key (id) references document(id) )
/

4. And now I would like to find all documents from DOCUMENT table that have associated collection of strings from DOCUMENT_TEXT table so that this associated collection matches my in-memory collection.

This pseudocode does what I want -

select * from document D where
  cast( multiset(select text from document_text T where T.id=D.id) as strings)=
  strings(

    'Hi,',
    '',
    'My PLSQL code prepares ...'

  )
/

Now I need to find a way of doing it in Oracle 9i.

As I mentioned earlier the comparison happens in PLSQL code, so the procedural logic is not ruled out as long as it performs and scales.

Could you give me a hand with it please?

Many Thanks,
Alex

Oracle 9.2.0 for Solaris Received on Thu Jan 23 2003 - 00:15:43 CST