CODEWITHSUNDEEP
sqloracle-databaseddl
TriHard8
TriHard8

Reputation: 592

Oracle Alter Object?

I'm trying to modify a number(7,0) attribute inside of an oracle object using the following command:

alter type myObjectFormat
  modify ATTRIBUTE (
    A NUMBER(7,2)) CASCADE FORCE;

ERROR:

Error report -
SQL Error: ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 10, column 32:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   exceptions
ORA-06550: line 0, column 0:
PLS-00565: MYOBJECTFORMAT must be completed as a potential REF target (object type)
06545. 00000 -  "PL/SQL: compilation error - compilation aborted"
*Cause:    A pl/sql compilation error occurred and the compilation was
           aborted completely without the compilation unit being written
           out to the backing store.  Unlike ora-06541, the user will always
           see this error along with the accompaning PLS-nnnnn error messages.
*Action:   See accompanying PLS-nnnnn error messages.

My object is as follows:

create or replace type myObjectFormat
as object (
  A   number(7,0)
);

Function using (obviously just an example to recreate error):

CREATE OR REPLACE FUNCTION STACKOVERFLOW RETURN MYOBJECTFORMAT AS 
BEGIN
  RETURN NULL;
END STACKOVERFLOW;

I've tried to manually change the datatype in the object, but get the error Error(1,1): ORA-02303: can't drop or replace a type with type or table dependents. SQLDev advises setting "Drop Type Force" preference error message. The "easy" thing to do at this point is to drop the function using this object, make the change, then recreate the function. The "easy" way won't be feasible as this database grows.

Oracle References:

Type Evolution

ALTER TYPE Statement

I would prefer to use the alter type statement and just know if my syntax is off? It seems to me that my FORCE command is in the incorrect place.

Upvotes: 2

Views: 2003

Answers (1)

GavinCattell
GavinCattell

Reputation: 3963

Looks like you're missing the exceptions_clause.

dependent_handling_clause ::=

enter image description here exceptions_clause ::=

enter image description here

Specify FORCE if you want the database to ignore the errors from dependent tables and indexes and log all errors in the specified exception table. The exception table must already have been created by executing the DBMS_UTILITY.CREATE_ALTER_TYPE_ERROR_TABLE procedure.

Meaning, if you have FORCE then you need to say which table you want the exceptions to flow into.

Upvotes: 1

Related Questions