Re: initializing objects and using the object to insert.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 11 Aug 2001 05:02:31 -0700
Message-ID: <9l36sn024da@drn.newsguy.com>

In article <f3159b28.0108100711.464e494c_at_posting.google.com>, sgelberg_at_optonline.net says...
>
>Is it possible to initialize an object without having to specify every
>attribute, this seems to be an over sight by oracle..every time I
>change my object I'll have to change my code.
>

sigh, never a version in sight. Ok, i'll assume oracle8i

>Doing this stinks...:
>v_msg RORS_MESSAGE_T := RORS_MESSAGE_T(NULL, NULL, NULL, NULL,
>NULL,
> NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
> NULL,
>NULL);
>

this is what static member functions are excellent for:

ops$tkyte_at_ORA8I.WORLD> create or replace type myType   2 as object

  3  (       attr1 int,
  4          attr2 varchar2(25),
  5          attr3 date,
  6  
  7          static function new return myType,
  8          static function new( d in date ) return myType

  9 )
 10 /

Type created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace type body myType   2 as
  3
  4 static function new return myType
  5 is
  6 begin
  7 return myType(null,null,null);   8 end;
  9
 10 static function new( d in date ) return myType  11 is
 12 begin
 13 return myType(to_char(d,'j'),d,d);  14 end;
 15
 16 end;
 17 /

Type body created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> declare
  2          v1 myType := myType.new;
  3          v2 myType := myType.new(sysdate);
  4          v3 myType := myType(null,null,null);
  5  begin
  6          null;

  7 end;
  8 /

PL/SQL procedure successfully completed.

>
>Also, why can't I set up an object as %ROWTYPE to use with AQ, another
>oversight in my opinion.
>

its more the sql object types are not based on rowtypes, nothing to do with AQ really. The sql object type cannot use the rowtype.

>Lastly, can I insert into a table such as INSERT INTO TEST
>VALUES(object) if the object looks just like the row..is setup as
>%ROWTYPE...
You have to be willing to have a collection type of your scalar type and if you do -- then yes, you can although this seems to run counter with #1 above (which is that you are changing your type definition all of the time. It would be considered a much safer/better practice to be explicit here to make sure the right columns end up in the right place):

ops$tkyte_at_ORA8I.WORLD> create or replace type myCollectionType   2 as table of myType
  3 /

Type created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> declare
  2 v myCollectionType := myCollectionType( myType.new(sysdate) );   3 begin

  4          insert into t
  5          select * from TABLE( cast( v as myCollectionType ) );

  6 end;
  7 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select * from t;

         X Y                    Z
---------- -------------------- ---------
   2452133 11-AUG-01            11-AUG-01

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 

Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Sat Aug 11 2001 - 07:02:31 CDT