| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10.1.0.4 patchset 2 catcpu.sql slop
In order to deal with this kind of problems ("name is already used" or
"object doesn't exist")
I wrote a small function, which checks the existance of the the object:
CREATE OR REPLACE FUNCTION object_exists (
object_type VARCHAR2,
object1_name VARCHAR2 DEFAULT NULL,
object2_name VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN
SELECT COUNT(*) INTO lCounter
FROM user_triggers WHERE trigger_name = UPPER(object1_name);END IF;
SELECT COUNT(*) INTO lCounter
FROM user_tables WHERE table_name = UPPER(object1_name);END IF;
SELECT COUNT(*) INTO lCounter
FROM user_tab_columns
WHERE table_name = UPPER(object1_name)
AND column_name = UPPER(object2_name);
END IF;
SELECT COUNT(*) INTO lCounter
FROM user_indexes WHERE index_name = UPPER(object1_name);END IF;
SELECT COUNT(*) INTO lCounter
FROM user_ind_columns
WHERE table_name = UPPER(object1_name)
AND column_name = UPPER(object2_name);
END IF;
lRet := TRUE;
END IF;
RETURN lRet;
END;
/
And in my upgrade scripts when I need to add/remove/modify table/column/index I use this function along with dynamic sql:
REM Remove column HEART_BEAT from GP_ROBOT
begin
IF (object_exists('COLUMN','GP_ROBOT', 'HEART_BEAT')) THEN
EXECUTE IMMEDIATE 'alter table GP_ROBOT drop column HEART_BEAT';
END IF;
end;
/
Or
REM Add column CHART_WIDTH to GP_STATUS_CHART
begin
IF (object_exists('COLUMN','GP_STATUS_CHART', 'CHART_WIDTH') = FALSE) THEN
EXECUTE IMMEDIATE 'alter table GP_STATUS_CHART add CHART_WIDTH
INTEGER NULL';
END IF;
end;
/
Or
REM GP_VF_SYMMETRY_DISPLAY
begin
IF (object_exists('TABLE','GP_VF_SYMMETRY_DISPLAY') = FALSE) THEN
EXECUTE IMMEDIATE 'create table GP_VF_SYMMETRY_DISPLAY ( VISUAL_FIXTURE_ID INTEGER not null, FIRST_COMPONENT_ID INTEGER not null, SECOND_COMPONENT_ID INTEGER not null,PHYSICAL_REPRESENTATION INTEGER,
using index
pctfree 3
initrans 3
maxtrans 20
tablespace AFS_TS,
constraint FK_VF_SYMMETRY__VISUAL_FIXTU foreign key (VISUAL_FIXTURE_ID)
references GP_VISUAL_FIXTURE (VISUAL_FIXTURE_ID)
on delete cascade
minextents 1
maxextents unlimited
)
tablespace AFS_TS';
End;
/
As for views and synonyms, I always use: "create or replace ..." Why can't Oracle do something like this in their scripts?
Igor
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Paul Drake
Sent: Tuesday, June 28, 2005 3:35 PM
To: Oracle-L (oracle-l_at_freelists.org)
Subject: 10.1.0.4 patchset 2 catcpu.sql slop
w2k3 server std ed, 32 bit.
Oracle 10g R1, 10.1.0 patchset 3 applied (10.1.0.4)
While running the 10.1.0.4 patchset 2 post-installation script (catcpu.sql) against a newly created database there were a rather large amount of ORA-00955 (457) and ORA-02303 (63) errors.
These can sometimes be ignored, but basically, this means having to compare the previous definitions of the objects against the new definition on a line by line basis in order to determine if in fact the object definition has changed. This is a huge waste of time, IMHO.
I was thinking of rather than taking that approach, of manually dropping all of these objects prior to re-running catcpu.sql and examining the results (HUGE hammer approach).
this is in testing in a db with no users.
yeah, I could just ignore the patchset and stick with 10.1.0.4 patchset 1. we've hit an issue covered by 10.1.0.4 patchset 2 so I'd rather get the bugfix out there than wait for the next iteration.
just trolling for some thoughts before I vent into an iTAR.
Paul
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 28 2005 - 16:31:29 CDT
 |
 |