Steven, in the explanation for this choice you write "You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype." You show an anonymous block illustrated this, i don't know if this is "almost" true, coz i can create a procedure, compiled, modified the table, and i don't need to compiled again to take effect the %ROWTYPE. i think is more like "that every time you -execute- the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype."
This is definitely not true. The "lookup" of the structure of the table, view or cursor, to then use to define the fields of a record type in PL/SQL definitely happens at compile time. When you are executing an anonymous block, the PL/SQL compiler must first compile that block before it is executed. That's when the lookup occurs. If you have defined a stored program unit, like a procedure with a %ROWTYPE declaration, then whenever that program unit is re-compiled, Oracle again "looks up" the structure of table, view or cursor.
Some of the confusion might arise from the fact that you do not have to explicitly recompile your program unit after you change a table referenced in %ROWTYPE. Oracle automatically marks that program unit as invalid and will also automatically attempt to recompile it the next time you try to use that program unit.
But there is no doubt that this is compile-time and not run-time behavior. There is no way that you or Oracle would want to slow down production performance by looking up the structure of tables and views every time the program unit is executed!
Cheers, SF
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
05 October 2010
When is %ROWTYPE used to define the record structure? (1461)
The 4 October quiz recorded as correct the following statement:
"You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype."
One player wrote to me with the following comment (I bolded the key words):
Subscribe to:
Post Comments (Atom)
In order to demonstrate that this lookup is done at compile-time, following example can be used. In this a database link is used so that the procedure is not invalidated upon table alteration.
ReplyDeleteHad the lookup been done at run-time, then execution of procedure for the second time in the below example would not have failed.
SQL>create user u1 identified by u1 default tablespace users quota unlimited on users;
User created.
SQL>create user u2 identified by u2;
User created.
SQL>grant create session,create table to u1;
Grant succeeded.
SQL>grant create session,create procedure, create database link to u2;
Grant succeeded.
SQL>conn u1/u1
Connected.
SQL>create table t (x varchar2(1));
Table created.
SQL>conn u2/u2
Connected.
SQL>create database link dbl connect to u1 identified by u1 using 'TEST';
Database link created.
SQL>create procedure p is
2 x t.x@dbl%type;
3 begin
4 x := 'XX';
5 end;
6 /
Procedure created.
SQL>exec p
BEGIN p; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "U2.P", line 4
ORA-06512: at line 1
SQL>conn u1/u1
Connected.
SQL>alter table t modify x varchar2(2);
Table altered.
SQL>conn u2/u2
Connected.
SQL>select status from user_objects where object_name = 'P';
STATUS
-------
VALID
1 row selected.
SQL>exec p
BEGIN p; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "U2.P", line 4
ORA-06512: at line 1
SQL>alter procedure p compile;
Procedure altered.
SQL>exec p
PL/SQL procedure successfully completed.
SQL>