1 CHANGE_FIELD Changes an existing global field definition. When you use the CHANGE FIELD statement to change the attributes of a global field, Oracle Rdb changes only the attributes you mention in the statement. All other attributes remain the same. Example: RDO> CHANGE FIELD POSTAL_CODE cont> DATATYPE IS TEXT SIZE IS 9 CHARACTERS. The CHANGE FIELD statement changes the field definition: o In all the relations that refer to that field o In the data dictionary (if you use INVOKE with the PATHNAME qualifier) o In the Oracle Rdb system relations 2 More To change a field using the CHANGE FIELD statement, you need the Oracle Rdb CHANGE privilege for the field. When you use CHANGE FIELD, you should be aware of the following conditions: o If an index is defined on a field that requires a collating sequence change, the index must be deleted first, then redefined after the collating sequence change. o If the database is created with the DICTIONARY IS REQUIRED option, you must invoke the database by path name, rather than file name, before you issue this statement. o You cannot issue a CHANGE FIELD statement to change the data type or collating sequence for a field that is used in an index or view definition. Oracle Rdb returns an error message in these instances and does not change the field. To change the data type or collating sequence of a field used in an index or view, first delete the index or view definition, then change the field, and finally, redefine the index or view. o If you issue a CHANGE FIELD statement that results in a conversion error when you attempt to retrieve the record, do the following: change the domain back to the original data type; then remove or change the offending records; then reissue the CHANGE FIELD statement to alter the domain to the new data type. Do not attempt to correct these type of conversion errors by just deleting the record (without first changing the domain back to the orginal data type). Doing so results in a conversion error when RDO attempts the delete operation. When you change a field definition, this change will be visible to other users only after they invoke the database the next time. By default, a database can be opened automatically (that is, by any user who invokes the database and executes a data manipulation language statement). If the database was modified so that it must be manually opened, the RMU/OPEN command must be used to open it. Depending on the type of change you make, you may have to reprocess the application programs that refer to the changed fields. You can change a field definition only if you have invoked the database that includes the field definition. You must execute this statement in a read/write transaction. If there is no active transaction, and you issue this statement, Oracle Rdb starts a read/write transaction implicitly. Other users are allowed to be attached to the database when you issue the CHANGE FIELD statement. 2 Format (B)0CHANGE FIELD qq> name qqk lqqqqqqqqqqqj mqqwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk mq> DESCRIPTION IS /* text */ qqj x lqqqqqqqqqqqqqqqqqq field-attributes qq> . 3 field-attributes (B)0field-attributes = DATATYPE IS qqq> data-type qqk lqqqqqqqqqqqqqqqqqqqqqqqqqj mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq> mwqwqq> validity-clauseqqqqqqqqqqqqqqqqqqqqqqqwqwqj x tqq> missing-value-clauseqqqqqqqqqqqqqqqqqqu x x tqq> dtr-clauseqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> COLLATING_SEQUENCE IS sequence-nameqqqu x x mqq> NO COLLATING_SEQUENCE qqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqq INVOKE DATABASE FILENAME 'MF_PERSONNEL' RDO> SHOW FIELDS POSTAL_CODE POSTAL_CODE text size is 5 Description: Postal code (in US = ZIP) RDO> CHANGE FIELD POSTAL_CODE cont> DATATYPE IS TEXT SIZE IS 9 CHARACTERS cont> DEFAULT_VALUE FOR DTR IS "000000000" cont> EDIT_STRING FOR DTR IS "XXXXX-XXXX". RDO> SHOW FIELD POSTAL_CODE POSTAL_CODE text size is 9 Description: Postal code (in US = ZIP) Edit string: XXXXX-XXXX Default value: "000000000" RDO> FOR E IN EMPLOYEES cont> PRINT E.POSTAL_CODE cont> END_FOR 03817 03817 03301 03456 . . . 03301 03809 RDO> STORE E IN EMPLOYEES USING cont> E.LAST_NAME = "Forester"; cont> E.EMPLOYEE_ID = "00876"; cont> E.POSTAL_CODE = "039875573" cont> END_STORE RDO> FOR E IN EMPLOYEES cont> WITH E.LAST_NAME = "Forester" OR cont> E.LAST_NAME = "Toliver" cont> PRINT cont> E.EMPLOYEE_ID, cont> E.POSTAL_CODE cont> END_FOR 00164 03817 00876 039875573 The field remains a text field, but the length is increased to nine characters, and an edit string and default value are specified for DATATRIEVE. When you display the data, existing field values are padded on the right with spaces. Thus an existing postal code would appear as "03104 ". Newly stored values can have nine characters. Example 2 The accounting department has decided that the BUDGET field will now include pennies: RDO> SHOW FIELDS BUDGET BUDGET signed longword scale 0 Description: Generic budget data Edit string: $$$,$$$,$$$ RDO> FOR D IN DEPARTMENTS cont> WITH D.DEPARTMENT_NAME = 'Manufacturing' cont> PRINT D.BUDGET_ACTUAL cont> END_FOR BUDGET_ACTUAL 0 RDO> CHANGE FIELD BUDGET cont> DATATYPE IS SIGNED LONGWORD SCALE -2 cont> EDIT_STRING FOR DTR IS "$$$$,$$9.99". RDO> SHOW FIELDS BUDGET BUDGET signed longword scale -2 Description: Generic budget data Edit string: $$$$,$$9.99 RDO> FOR D IN DEPARTMENTS cont> WITH D.DEPARTMENT_NAME = 'Manufacturing' cont> PRINT D.BUDGET_ACTUAL cont> END_FOR BUDGET_ACTUAL 0.00 Example 3 You can add or change a VALID IF clause for a field. However, if the database contains any data that violates the specification of the VALID IF clause, the clause is rejected. The following example shows two CHANGE FIELD... VALID IF statements. In the first instance, the specification is rejected because existing records contain EMPLOYEE_ID values less than "98765"; in the second instance, the specification is accepted. RDO> CHANGE FIELD ID_NUMBER VALID IF ID_NUMBER > "98765". %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-NOT_VALID_FR, field ID in relation CURRENT_INFO fails validation RDO> CHANGE FIELD ID_NUMBER VALID IF ID_NUMBER > "00050". RDO> SHOW FIELD ID_NUMBER ID_NUMBER text size is 5 Description: Generic employee ID Missing value: Valid: IF ID_NUMBER > "00050"