Can I change an attribute name from a table derived from a type?

Question

Folowing the Object-Relational Database model, I wanted to create the tables or_doctor and or_recepcionist derived from the type t_employee. Here, follows the type structure:

DROP TYPE t_employee FORCE;
CREATE OR REPLACE TYPE t_employee AS OBJECT (
    num_employee INTEGER,
    name_employee VARCHAR2(50),
    birthdate_employee DATE
);

And here, the tables' structure:

DROP TABLE or_doctor CASCADE CONSTRAINTS;
CREATE TABLE or_doctor OF t_employee (
    PRIMARY KEY (num_employee),
    name_employee NOT NULL,
    birthdate_employee NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED;

DROP TABLE or_recepcionist CASCADE CONSTRAINTS;
CREATE TABLE or_recepcionist OF t_employee (
    PRIMARY KEY (num_employee),
    name_employee NOT NULL,
    birthdate_employee NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED;

Doing so, the attributes names, on both tables, will end up with "employee". Could I change the attribute name so they are specific in each table at the moment I'm creating the table? E.G.:

Table or_doctor: num_doct, name_doct, birthdate_doct. Table or_recepcionist: num_recep, name_recep, birthdate_recep.

Answer 1

As a frame challenge, don't add a suffix to your identifiers then you don't need to worry about the suffix being incorrect:

CREATE TYPE t_employee AS OBJECT (
    num       INTEGER,
    name      VARCHAR2(50),
    birthdate DATE
);

CREATE TABLE or_doctor OF t_employee (
    PRIMARY KEY (num),
    name      NOT NULL,
    birthdate NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED;

CREATE TABLE or_receptionist OF t_employee (
    PRIMARY KEY (num),
    name      NOT NULL,
    birthdate NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED;

If you try to rename the column:

ALTER TABLE or_doctor RENAME COLUMN name TO name_doctor;

Then you will get the error:

ORA-23291: Only base table columns may be renamed

If you are using object-derived tables then you appear to be stuck with the identifiers from the object; so, make the object names generic so that they are appropriate in every place they are going to be used.