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
1 – 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)0[m[4mCHANGE[m [4mFIELD[m qq> name qqk
lqqqqqqqqqqqj
mqqwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk
mq> [4mDESCRIPTION[m IS /* text */ qqj x
lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqj
mq> field-attributes qq> .
2.1 – field-attributes
(B)0[mfield-attributes =
[4mDATATYPE[m IS qqq> data-type qqk
lqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>
mwqwqq> validity-clauseqqqqqqqqqqqqqqqqqqqqqqqwqwqj
x tqq> missing-value-clauseqqqqqqqqqqqqqqqqqqu x
x tqq> dtr-clauseqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x
x tqq> [4mCOLLATING_SEQUENCE[m IS sequence-nameqqqu x
x mqq> [4mNO[m [4mCOLLATING_SEQUENCE[m qqqqqqqqqqqqqqqqj x
mqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj
A list of definitions that indicate what type of data you can
store in the field and how Oracle Rdb uses that data. If you do
not change a field attribute in the CHANGE FIELD statement, the
attribute remains the same. The field attributes include the data
type and the following optional clauses:
o VALID IF
o MISSING_VALUE
o DATATRIEVE support clauses
o COLLATING_SEQUENCE
Do not use multiple, conflicting clauses in a field definition.
For a complete description of field attributes, ask for HELP on
Field_attr.
3 – Examples
Example 1
The following example expands the POSTAL_CODE field to nine
characters and adds DATATRIEVE support characteristics:
RDO> 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"