The RENAME_OBJECT stored procedure can be used to rename a table or index. This procedure will verify the availability of the new object name before attempting to rename the object.
| Parameter | Description | |
|---|---|---|
| OBJECT_TYPE | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The type of object being renamed. | |
| Options |
TABLE INDEX |
|
| SCHEMA_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The schema name that owns the object being renamed. | |
| OBJECT_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | None | |
| Description | The current name of the object. | |
| NEW_NAME | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NULL | |
| Description | The new name for the object. | |
Exceptions
Examples
SET SERVEROUTPUT ON;
EXEE IN_DB_UTIL.RENAME_OBJECT([TABLE|INDEX], 'INUSER', [CURRENT_NAME], [NEW_NAME]);
EXEC IN_DB_UTIL.RENAME_OBJECT('INDEX','INUSER', 'AUDIT_IDX_01', 'NEW_INDEX_NAME');
SET SERVEROUTPUT ON;
DECLARE
V_OBJECT_TYPE VARCHAR2(128);
V_OBJECT_NAME VARCHAR2(128);
V_NEW_NAME VARCHAR2(128);
-- Exceptions
NAME_UNAVAILABLE EXCEPTION; PRAGMA EXCEPTION_INIT(NAME_UNAVAILABLE, -20002);
INVALID_OBJECT_TYPE EXCEPTION; PRAGMA EXCEPTION_INIT(INVALID_OBJECT_TYPE,-20005);
INVALID_OBJECT EXCEPTION; PRAGMA EXCEPTION_INIT(INVALID_OBJECT, -20006);
BEGIN
V_OBJECT_TYPE := 'INDEX';
V_OBJECT_NAME := 'AUDIT_IDX_01';
V_NEW_NAME := 'NEW_INDEX_NAME';
IN_DB_UTIL.RENAME_OBJECT(V_OBJECT_TYPE, 'INUSER', V_OBJECT_NAME, V_NEW_NAME);
EXCEPTION WHEN INVALID_OBJECT_TYPE THEN NULL;
WHEN INVALID_OBJECT THEN NULL;
WHEN NAME_UNAVAILABLE THEN NULL;
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('There was an unhandled exception.');
END;
/
Example Output
SQL> EXEC IN_DB_UTIL.RENAME_OBJECT('INDEX','INUSER', 'AUDIT_IDX_01', 'NEW_INDEX_NAME');
ALTER INDEX INUSER.AUDIT_IDX_01 RENAME TO NEW_INDEX_NAME
The index has been renamed.
PL/SQL procedure successfully completed.