The last statement returns error for me:
Here it is:-

SQL> select t.pt data, t.pt.dist_from_orgin() from t ;
select t.pt data, t.pt.dist_from_orgin() from t
                       *
ERROR at line 1:
ORA-00904: invalid column name

Im running 8.1.7 in windows 2000 

Thanks tom..it worked.That was fast...u r not overloaded r u??(kidding)
I hav another query:
In the member function if I pass a parameter & write code to check a condition and return 1 or 0, will this be faster than directly retreiving the column? Here is an example-

create type mytype as object
( ind1 varchar2(2),
ind2 varchar2(2)
member function check1(myvar IN number) return number
)

create type body mytype as
member function check1(myvar IN number) return number
is
b varchar2(2);
begin
select t.col1.mytype.ind1 into b from t t where col2 = myvar;
if b='A' then
return 1;
else
return 0;
end if;
end;
end;

create table t ( col1 mytype, col2 number,col3..col4....etc);

insert into t (col1,col2) values (mytype('A','B'),5);
insert into t (col1,col2) values (mytype('A','B'),6);
insert into t (col1,col2) values (mytype('A','C'),7);

Now from the front end(JSP) if link1 is clicked, i pass the value 'myvar' (say 5) to my member function which will query my table and return 1 . If it is 1 i will query the same table to get more columns to be displayed on the screen.If it is 0 i will query with some lesser cols .

Now my question is - If this is possible then,
1. How do i call the member function from the front end (the syntax...)?
2. Will this approach be faster than directly querying the col1.ind1 column. Meaning ..i just say this in the front end-
select t.col1.ind1 into variable from t t where col2 = myvar;
If variable = 'A' then
select col4,col5.....
else
null;
end if;
3.Is there anyother way to do this to reduce query time.
A direct sql statement will be faster than a function /stored proc right?
4.Also how do i estimate the table size if i use abstract datatypes? Is it more or less?
In the above table if i had seperate cols for ind1 and ind2 instead of an object then what wud be the difference in table size?

Hi Tom

In which circumstance we should use this functinality.
It is looking complacted.

Tom i've got a BIG pb about OO with Oracle :
Check this out with Oracle 9i 9.0.2 :

Create or replace TYPE TYPE_A as object
(
value_ NUMBER,
MEMBER FUNCTION setValue(v_value NUMBER) RETURN TYPE_A
) INSTANTIABLE NOT FINAL;
/

create or replace TYPE BODY TYPE_A IS
member function setValue(v_value NUMBER) RETURN TYPE_A IS
v_inst TYPE_A := SELF;
v_old_inst TYPE_A := SELF;
begin
if v_old_inst.value_ != v_value then
v_inst.value_ := v_value;
dbms_output.put_line('setValue executed');
end if;
return v_inst;
end;

END;
/

create table TEST (ATTR_ TYPE_A);

insert into TEST values(TYPE_A(55));

// This line writes "setValue executed" 3 times, why !!??

update TEST a set ATTR_ = a.ATTR_.setValue(56);

the setValue method will be a great functionnality to keep data coherence, and i would not just use SET ATTR_.value_ = xxx

Any idea please ?

hi tom,

thx for the nice example. i was playing around a little bit with your foo type and i ran into a problem i do not understand and i cant find an explantaion ... 

here is my ( your ) example... 

SQL> create type foo as object(
  2     x    number,
  3     y        number,
  4
  5    member function dist_from_orgin return number,
  6
  7    member procedure set_x (  in_x in number ),
  8
  9    member function set_y ( in_y in number ) return number
 10
 11  )
 12  /

Type created.

SQL>
SQL> create type body foo
  2    as
  3      member function dist_from_orgin return number
  4     is
  5     begin
  6         return sqrt( x*x+y*y );
  7     end;
  8
  9     member procedure set_x ( in_x in number )
 10    is
 11    begin
 12         x := in_x;
 13    end;
 14
 15     member function set_y ( in_y in number)
 16     return number
 17     is
 18     begin
 19
 20       y := in_y;
 21
 22       return y;
 23     end;
 24
 25   end;
 26  /

Warning: Type Body created with compilation errors.

SQL> show error
Errors for TYPE BODY FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/6     PLS-00363: expression 'SELF.Y' cannot be used as an assignment
         target

20/6     PL/SQL: Statement ignored

i added a new function and a procedure to set the x and 
y values... but for some reason i can not assign a value to a attriptute in a function. why does it not work ... and is 
there a work around or do i have to move to procedures using out-parameters. ( i tried this also and it resulted in no error ).
i do not understand the difference behavioure of a member function and a memeber procedure. why is the assigment in
the procedure fine but an error in the function?

thx

joachim "hannibal" mayer 

p.s.: when will your new book come out? 

h. 

to each non-static member procedure/function there is an implicit parameter SELF.  By default for procedures, self it passed IN OUT.  For functions -- only as IN -- this is to maximize your ability to call that function from SQL.  You cannot call a function with out parameters from SQL so we default it to IN.

Here is how to do what you need:

ops$tkyte@ORA920> create type foo as object(
  2     x    number,
  3     y        number,
  4
  5    member function dist_from_orgin return number,
  6
  7    member procedure set_x (  in_x in number ),
  8
  9    member function set_y (<b> SELF in out foo,</b> in_y in number ) return number
 10
 11  )
 12  /

Type created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create type body foo
  2    as
  3      member function dist_from_orgin return number
  4     is
  5     begin
  6         return sqrt( x*x+y*y );
  7     end;
  8
  9     member procedure set_x ( in_x in number )
 10    is
 11    begin
 12         x := in_x;
 13    end;
 14
 15     member function set_y ( <b>SELF in out foo,</b> in_y in number)
 16     return number
 17     is
 18     begin
 19
 20       y := in_y;
 21
 22       return y;
 23     end;
 24
 25   end;
 26  /

Type body created.


August'ish for the book