From Waleed.Khedr@FMR.COM Tue, 28 Aug 2001 12:55:42 -0700
From: "Khedr, Waleed" <Waleed.Khedr@FMR.COM>
Date: Tue, 28 Aug 2001 12:55:42 -0700
Subject: RE: A quick pl/sql datatypes question
Message-ID: <F001.0037A5A3.20010828131323@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: A quick pl/sql datatypes question



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@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@jlcomp.demon.co.uk">mailto:jonathan@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