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>

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!

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.