The assignment rules described in this section apply to INSERT/UPDATE statements, the RETURNING clause, function parameters, and PL/SQL variables. Substitutability is the ability of a subtype to stand in for one of its supertypes. Substitution in the other direction, to substitute a supertype for a subtype, raises an error at compile time. Assigning a source of type source_typ to a target of type target_typ must be of one of the following two patterns: Case 1: source_typ and target_typ are the same type Case 2: source_typ is a subtype of target_typ (widening) Case 2 illustrates widening. Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. For example, assigning an employee instance to a variable of person type. An employee is a more narrowly defined, specialized kind of person, so you can put an employee in a slot meant for a person if you do not mind ignoring whatever extra specialization makes that person an employee. All employees are persons, so a widening assignment always works. To illustrate widening, suppose that you have the following table: TABLE T(pers_col person_typ, emp_col employee_typ, stu_col student_typ) The following assignments show widening. The assignments are valid unless perscol has been defined to be not substitutable. UPDATE T set pers_col = emp_col; The following is a PL/SQL example, which first requires you to create a person_typ and an employee_typ: Example 2-31 PL/SQL Assignment DROP TYPE person_typ FORCE; -- if previously created CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)) NOT FINAL; DROP TYPE employee_typ FORCE; -- if previously created CREATE TYPE employee_typ UNDER person_typ ( emp_id NUMBER, mgr VARCHAR2(30)); / -- PL/SQL assignment example
The assignment rules described in this section apply to INSERT/UPDATE statements, the RETURNING clause, function parameters, and PL/SQL variables. Substitutability is the ability of a subtype to stand in for one of its supertypes. Substitution in the other direction, to substitute a supertype for a subtype, raises an error at compile time. Assigning a source of type source_typ to a target of type target_typ must be of one of the following two patterns: Case 1: source_typ and target_typ are the same type Case 2: source_typ is a subtype of target_typ (widening) Case 2 illustrates widening. Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. For example, assigning an employee instance to a variable of person type. An employee is a more narrowly defined, specialized kind of person, so you can put an employee in a slot meant for a person if you do not mind ignoring whatever extra specialization makes that person an employee. All employees are persons, so a widening assignment always works. To illustrate widening, suppose that you have the following table: TABLE T(pers_col person_typ, emp_col employee_typ, stu_col student_typ) The following assignments show widening. The assignments are valid unless perscol has been defined to be not substitutable. UPDATE T set pers_col = emp_col; The following is a PL/SQL example, which first requires you to create a person_typ and an employee_typ: Example 2-31 PL/SQL Assignment DROP TYPE person_typ FORCE; -- if previously created CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)) NOT FINAL; DROP TYPE employee_typ FORCE; -- if previously created CREATE TYPE employee_typ UNDER person_typ ( emp_id NUMBER, mgr VARCHAR2(30)); / -- PL/SQL assignment example