You can assign a
%ROWTYPE record to a user-defined record (and vice-versa) if their fields match in number and order, and corresponding fields have compatible datatypes. This is true even when the record is being passed as an argument to a subprogram.Several players wrote with concerns about this, based on what they found in the documentation. I offersome of their comments below.
1. Nice quiz and another proof that you can't trust the documentation: it says that you can only assign %ROWTYPE-Records to declared records where the names and datatypes are identical.
2. Surprisingly enough, for today's quiz all the choices are right. Four of them seem to contradict what is explicitly stated in the Oracle PL/SQL Documentation namely: "Assigning One Record Variable to Another You can assign the value of one record variable to another record variable only in these cases: 1. The two variables have the same RECORD type (as in Example 5-42). 2. The target variable is declared with a RECORD type, the source variable is declared with %ROWTYPE, their fields match in number and order, and corresponding fields have the same data type (as in Example 5-43)." So, unfortunately, one who is always using best practices and has never tried to check whether the 2-nd restriction above does indeed hold, was mislead in answering this quiz.
My responses:
1. I have not seen anything in the documentation that specifies that names of fields must be identical. So if a player can provide that link, I would much appreciate it.
2. The second comment reflects, I believe, a mis-reading of the documentation (and makes me think they need to tighten it up). The documentation offers two different rules, but they are to be read as OR not AND. In other words, either the two variables can have the same record type OR their fields match in number and order. In fact, I (and, I expect, Oracle) would argue that the clause "only in these cases:" implies the OR. That is, they are presented as different "cases", not as a single set of requirements, all of which must be true.
Your thoughts and experience?
The documentation in "appdev.102/b14261/collections.htm#i20483" states, that even identical records may not be assigned to each other, if they base on two separate declarations:
ReplyDelete"Example 5-48 Assigning All the Fields of a Record in One Statement
DECLARE
-- Two identical type declarations.
TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
dept1_info DeptRec1;
dept2_info DeptRec2;
dept3_info DeptRec2;
BEGIN
-- Not allowed; different datatypes, even though fields are the same.
-- dept1_info := dept2_info;
-- This assignment is OK because the records have the same type.
dept2_info := dept3_info;
END;
/
"
So, for me it looks like another example of difference between documentation and real world ;-)
Regards, Roger
Hello All,
ReplyDeleteOf course the 2 cases presented in the documentation are to be interpreted as OR and NOT as AND.
Also, there is no reference to the NAMES of the record fields.
However, the 2-nd restriction clearly delimits SOURCE and TARGET records in an assignment,
stating that the SOURCE can be a %ROWTYPE and the TARGET can be a user-defined record type,
and NOT the opposite direction ( again, regardless of the field names, only number and datatype
compatibility required ).
This difference also appears in the Oracle10gR2 PL/SQL documentation,
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i20479
that clearly shows that two variables of different user-defined record types CANNOT be assigned
to each other, even if having the same field names and types, while a %ROWTYPE variable
can be assigned to a user-defined record type, that is, again, the source is the %ROWTYPE
and the target is the user-defined type, not the opposite.
The following
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#insertedID11
also has an example showing that a record type defined in a package IS NOT the same type
with another record type defined identically in another PL/SQL block.
In summary, the objection is related to the DIRECTION of the assignment, and not to the different
field names.
Thanks & Best Regards,
Iudith
The error with the identical names was mine (shame on me for not being able to read the documentation correct ;-)
ReplyDeleteBut the documentation says that "You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have the same datatypes" and the example given uses a record that defines its elements with the %TYPE attribute.
For the %ROWTYPE the documentation says "The %ROWTYPE attribute provides a record type that represents a row in a table or view. Columns in a row and corresponding fields in a record have the same names and datatypes.".
And for the %TYPE it says "The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased."
So the question for me is: do two elements of VARCHAR2(30) and VARCHAR2(100) have the same datatype. In my own opinion they don't have the same datatype but are only type-compatible, especially regarding the %TYPE where length-changes are explicitly mentioned.
Kind regards, Niels