Re: owa_pattern.pattern as a type

From: Mike Reed <news_at_amadron.com>
Date: Tue, 20 Dec 2005 15:26:32 GMT
Message-ID: <pan.2005.12.20.15.25.29.962541@amadron.com>

On Tue, 20 Dec 2005 06:27:35 -0800, stephen O'D wrote:

>
> Mike Reed wrote:

>> Hi,
>> Why can't I use owa_pattern.pattern as a column type?
>>
>> SQL> create table temp(
>>   2      names owa_pattern.pattern
>>   3  ) nested table names store as temp_names;
>>     names owa_pattern.pattern
>>                       *
>> ERROR at line 2:
>> ORA-00902: invalid datatype
>>
>>
>> SQL>

>
> I don't think you can create a table based on a PLSQL type. It has to
> be an object type:-
>
> create or replace type pattern as object ( p1 varchar2(4) );
> /
>
> create or replace type pattern_a as table of pattern;
> /
>
> create table test_pat ( names pattern_a ) nested table names store as
> names;
>
> That works!

It does work. Thanks. However, it doesn't solve my real problem. I have a fixed set of regular expressions that I am matching. So for speed I wanted to store the precompiled patterns in a table. i.e. I wanted to store the out param from owa_pattern.getpat in a table so that I could do something like:

select id from temp where owa_pattern.amatch('some text', 1, precomp) > 1;

and it would call the version of amatch that took a precompiled regular expression. This requires the column precomp to be of type owa_pattern.pattern. Oh well! It seemed like a good idea. Received on Tue Dec 20 2005 - 09:26:32 CST