RE: A quick pl/sql datatypes question

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 28 Aug 2001 12:55:42 -0700
Message-ID: <F001.0037A5A3.20010828131323@fatcity.com>

I do
not think there will be any constraints when you declare it as t.col%rowtype.
<SPAN
class=920554318-28082001> 
I
believe Oracle will do a lookup to come with the absolute data type during the compilation of the stored proc or the anonymous block and will establish some dependencies between the validity of the compiled code and the future changes of the referenced table.column so that the right data type is enforced all the time.
<SPAN
class=920554318-28082001> 
I'll
be glad to hear corrections.
<SPAN
class=920554318-28082001> 
<SPAN
class=920554318-28082001>Waleed

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Jacques Kilchoer   [mailto:Jacques.Kilchoer_at_quest.com]Sent: Tuesday, August 28, 2001   3:16 PMTo: Multiple recipients of list ORACLE-LSubject:   RE: A quick pl/sql datatypes question
  > -----Original Message----- >
  From: Jonathan Lewis [<A
  href="mailto:jonathan_at_jlcomp.demon.co.uk">mailto:jonathan_at_jlcomp.demon.co.uk]
> > If you declare a variable

  to be of >    
  table_name.column_name%type > there are implicit   constraints on the > variable and any pl/sql   assignments > (in particular arithmetic   operations) > have to check that the result   matches > those constraints. At one time I   believe > this meant using a temporary   variable > to hold a result, then copying the   result > if the constraint was matched.   

  Could anyone explain this statement more fully? Why would   there be "implicit constraints" when using table_name.column_name%rowtype vs.   datatype? A simple test below showed no time difference.   SQL> create table t (d date not null primary key, n number)   ; Table created.
  SQL> set timing on SQL> -- using
  "datatype" declaration SQL> declare
    2     i pls_integer ;
    3     dd date ; <FONT
  size=2>  4  begin  

  5     for i in 1..100000   
  6     loop   
  7        dd := sysdate ; <FONT 

  size=2>  8     end loop ; <FONT
  size=2>  9  end ;  10  /
  PL/SQL procedure successfully completed. <FONT   size=2>Elapsed: 00:00:09.54
  SQL> -- using "table.column%type" declaration   SQL> declare  
  2     i pls_integer ;  
  3     dd t.d%type ;   4 
  begin   5     for i in
  1..100000   6     loop
    7        dd :=
  sysdate ;   8     end loop
  ;   9  end ; <FONT
  size=2> 10  / PL/SQL procedure successfully   completed. Elapsed: 00:00:09.34 Received on Tue Aug 28 2001 - 14:55:42 CDT