tag:blogger.com,1999:blog-8677649049588007585.post6279828575598078178..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Record Assignments and Compability - Doc vs. Reality (2900)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-8677649049588007585.post-3929173727249033992011-06-08T20:25:41.432+01:002011-06-08T20:25:41.432+01:00The error with the identical names was mine (shame...The error with the identical names was mine (shame on me for not being able to read the documentation correct ;-)<br /><br />But 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.<br /><br />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.".<br /><br />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."<br /><br />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.<br /><br />Kind regards, NielsAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-90948523230196149522011-06-08T14:14:13.631+01:002011-06-08T14:14:13.631+01:00Hello All,
Of course the 2 cases presented in the...Hello All,<br /><br />Of course the 2 cases presented in the documentation are to be interpreted as OR and NOT as AND.<br /><br />Also, there is no reference to the NAMES of the record fields.<br /><br />However, the 2-nd restriction clearly delimits SOURCE and TARGET records in an assignment,<br />stating that the SOURCE can be a %ROWTYPE and the TARGET can be a user-defined record type, <br />and NOT the opposite direction ( again, regardless of the field names, only number and datatype <br />compatibility required ).<br /><br />This difference also appears in the Oracle10gR2 PL/SQL documentation,<br />http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i20479<br /><br />that clearly shows that two variables of different user-defined record types CANNOT be assigned <br />to each other, even if having the same field names and types, while a %ROWTYPE variable<br />can be assigned to a user-defined record type, that is, again, the source is the %ROWTYPE<br />and the target is the user-defined type, not the opposite.<br /><br />The following<br />http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/composites.htm#insertedID11<br /><br />also has an example showing that a record type defined in a package IS NOT the same type<br />with another record type defined identically in another PL/SQL block.<br /><br />In summary, the objection is related to the DIRECTION of the assignment, and not to the different<br />field names.<br /><br />Thanks & Best Regards,<br />IudithAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-68654294588698120242011-06-08T13:31:28.729+01:002011-06-08T13:31:28.729+01:00The documentation in "appdev.102/b14261/colle...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:<br /><br />"Example 5-48 Assigning All the Fields of a Record in One Statement<br /><br />DECLARE<br />-- Two identical type declarations.<br /> TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));<br /> TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));<br /> dept1_info DeptRec1;<br /> dept2_info DeptRec2;<br /> dept3_info DeptRec2;<br />BEGIN<br />-- Not allowed; different datatypes, even though fields are the same.<br />-- dept1_info := dept2_info; <br />-- This assignment is OK because the records have the same type.<br /> dept2_info := dept3_info;<br />END;<br />/<br />"<br /><br />So, for me it looks like another example of difference between documentation and real world ;-)<br /><br />Regards, RogerAnonymousnoreply@blogger.com