Search code examples
oracle-databaseplsqlalter

Oracle: Rename/replace object type and its references

I have created an object type as below and has used in several stored procedures and in tables as a column.

CREATE OR REPLACE TYPE SS_EMP_TP FORCE AS OBJECT (
    NAME VARCHAR2(30),
    AGE NUMBER
);
/
CREATE OR REPLACE TYPE SS_EMP_TAB_TP AS TABLE OF SS_EMP_TP;
/

Example:

ALTER TABLE EMP_S ADD (EMP_DETAIL_TABLE **SS_EMP_TAB_TP**)  
      NESTED TABLE EMP_DETAIL_TABLE STORE AS  NESTED_EMP_DETAIL_TABLE RETURN AS LOCATOR;

Now I have to rename the object types from SS_EMP_TP, SS_EMP_TAB_TP to EMP_TP, EMP_TAB_TP respectively. And also have to replace all the places where it has been referenced.

Is there a rename alter object type statement?

How can I do this? And what are all the things I need to consider while doing this?

Solution

  • I'd suggest you not to do that. Why would you? What's the difference (apart from SS_)? What benefit do you expect?

    Anyway: rename SS_EMP_TP to EMP_TP. Everything, that references SS_EMP_TP, will be invalidated. Then modify each invalid object in order to fix that. Repeat.

    Once again - don't, unless you have a very good reason.