Re: How to refer to an element of a col defined as varray of an object datatype

On Tue, 02 May 2000 21:07:43 GMT, kaushalg_at_my-deja.com wrote:

>Hi I need help.
>
>I have a column col_1 in a table tbl_A of varray names INSTRING WHICH
>inturn is defined of the an object type OSTRING, size 8.
>
>OSTRING has 2 attributes
>languageid of the type number(4,0)
>and
>string of the type varchar2(255).
>
>
>from this table I want to compare string to an input parameter.
>
>I was trying to reference it as follows:
>
>where tbl_A.col_1.string = inputparam;
>
>I get the error, it is thinking col_1 is a table.
>
>then I tried using VALUE clause as follows:
>
>where VALUE(tbl_A.col_1.string)= inputparam;
>
>i also tried using DREF, WITHOUT SUCCESS.
>
>iF ANYBODY CAN HELP, I'll really appreciate that.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

It wasn't completely clear what you meant, so I am making some assumptions :

create type OSTRING as object (
  languageid number(4, 0),
  string varchar2(255)
);

create or replace type names as VARRAY(10) of OSTRING;

create or replace table tbl_A (
  col_1 names
);

select
  t.string
from
  TABLE(
    select
      col_1
    from
      tbl_A) t
where
  t.string = inputparam

The TABLE function converts a collection or varray into a pseudo table, but note that table aliases (e.g. "t" in this case) seem to be imperative when working with object tables.

The operator VALUE returns the value of an object, so it isn't suitable in your case.

As for DEREF, it only works on a REF to an object stored in an object table (a pointer to a persistent object).

e.g.

create or replace tbl_b (
  namesref REF OSTRING
);

select
  DEREF(t2)
from
  tbl_b t2

Again, the table alias t2 is imperative.

Hope this helps

Paul Received on Tue May 02 2000 - 00:00:00 CDT