Home » SQL & PL/SQL » SQL & PL/SQL » Updating objects (10g, win 2000)
Updating objects [message #317933] Mon, 05 May 2008 01:03 Go to next message
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member

I am working a project to make a recruiting management system. I have a table 'EMPLOYER' which is made of object ob_employer. An attribute of ob_employer if employer_address which is another object ob_address which in turn has an attribute 'STATE' (for US states). I had put its size as char(2) but now I want to change it to varchar2(50). when I gave this command it gave me an error

alter type ob_address modify attribute state varchar2(50) cascade

ERROR at line 1:
ORA-22324: altered type has compilation errors
ORA-22328: object "DAS"."OB_ADDRESS" has errors.
PLS-00719: only widening of attribute 'STATE' constraints is allowed
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Then I dropped the attribute and recreated with CASCADE option.

Is there a way to incorporate this change with changes being reflected in all the other objects related to it?

- Das
Re: Updating objects [message #317935 is a reply to message #317933] Mon, 05 May 2008 01:11 Go to previous message
Michel Cadot
Messages: 65155
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PLS-00719: only widening of attribute "string" constraints is allowed
Cause: Modification to the attribute did not widen its constraints or attempted to change the type of the attribute. For NUMBER type attributes, scale and precision can be changed to allow increase in number of digits after and before the decimal point. For VARCHAR2 and RAW type attributes the size can be increased. Other type attributes are not allowed to be modified. Change of type of attribute is also not allowed.
Action: Change the constraint specification so as to widen the existing constraints.To narrow the constraints or to change the type of the attribute, you must drop the attribute and add it with new constraints or new type. In that case you must also take appropriate steps to preserve your existing data based on the type (if you want to preserve it).Typically this will involve backing up the data and restoring it after the ALTER TYPE.


[Updated on: Mon, 05 May 2008 01:11]

Report message to a moderator

Previous Topic: select *, "app" as Status from tablename
Next Topic: cONCAT ISSUE
Goto Forum:

Current Time: Wed Aug 23 14:34:40 CDT 2017

Total time taken to generate the page: 0.09138 seconds