ora-22979: cannot INSERT object view REF or user-defined REF

Question

I try to insert/update a Ref via Java JDBC into an oracle12c db.

The type is defined as:

CREATE OR REPLACE TYPE AngestellterTyp UNDER PersonTyp (
 PersonalNr      INTEGER,
 Rang            VARCHAR(200),
 Einstellung     DATE,
 TelefonBuero    TelefonnummerVarrayTyp,
 Bemerkungen     VARCHAR(2000),
 Vorgesetzter    REF AngestellterTyp
) NOT FINAL;

Using this update statement

 public void setVorgesetzter(Long angestellterId, Long vergesetzterId) throws SQLException {
        Ref vorgesetzter = this.getAngestellterRef(vergesetzterId);

        Connection connection = this.dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("UPDATE ANGESTELLTE an SET an.Vorgesetzter = ? WHERE an.PersonalNr = ?");

        statement.setRef(1, vorgesetzter);
        statement.setInt(2, angestellterId.intValue());

        statement.executeUpdate();
    }

    public Ref getAngestellterRef(Long personalNummer) {
        try {
            return getOracleRef("SELECT REF(a) FROM Angestellte a WHERE a.PersonalNr=" + personalNummer);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Ref getOracleRef(String query) throws SQLException {
       Ref reference = null;
        Connection connection = this.dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(query);

        if(resultSet.next()) {
            reference = resultSet.getRef(1);
        }
        resultSet.close();
        statement.close();
        return reference;
    }

causes:

ava.sql.SQLException: ORA-22979: Objekt-View-REF oder benutzerdefinierte REF kann nicht mit INSERT eingefügt werden


at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)

I use readSQL and writeSQL from SQLData. Inserting the Ref within writeSQL causes the same Exception.

Oracle suggests:

Blockquote Make sure the REF to be inserted is not from an object view or from a user-defined REF column Blockquote

But how?

Answer 1

The solution was to change the table definition from:

CREATE TABLE Angestellte OF AngestellterTyp (
CONSTRAINT angestellter_pk PRIMARY KEY (PersonalNr)
) OBJECT IDENTIFIER IS PRIMARY KEY;

to:

CREATE TABLE Angestellte OF AngestellterTyp (
CONSTRAINT angestellter_pk PRIMARY KEY (PersonalNr)
);