How about looking at intermedia in 9i?
Jim
"Alexander Miroshnikov" <alexander_miroshnikov_at_hotmail.com> wrote in message
news:1043302640.95789.0_at_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:28:22 CST
