急急急 幫我看下oracle 存儲過程
執行報如下錯誤:
ORA-00955: name is already used by an existing object
ORA-06512: at "SMP.SUNSET", line 28
ORA-06512: at line 1
十分感謝!!!!
/* Formatted on 2012/02/16 09:27 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE sunset
AS
str VARCHAR2 (3000);
BEGIN
str :=
'CREATE TABLE SMP.TEMP_DM_defect
(
ID VARCHAR2(30 BYTE),
WORK_MONTH VARCHAR2(2 BYTE),
WORK_WEEK VARCHAR2(2 BYTE),
WORK_DATE VARCHAR2(2 BYTE),
WEEKDAY VARCHAR2(9 BYTE),
SHIFT VARCHAR2(30 BYTE),
SUBLINE_NAME VARCHAR2(90 BYTE),
MODEL VARCHAR2(50 BYTE),
PART_NO VARCHAR2(50 BYTE),
WO_NO VARCHAR2(50 BYTE),
TYPE VARCHAR2(50 BYTE),
HOUR VARCHAR2(30 BYTE),
ROUTE_CODE VARCHAR2(50 BYTE),
PORT VARCHAR2(50 BYTE),
DEFECT_CODE VARCHAR2(50 BYTE),
REASON_CODE VARCHAR2(10 BYTE),
CHINESE VARCHAR2(50 BYTE),
QTY NUMBER
)';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'insert into TEMP_DM_defect (SELECT 1 AS ID, TO_CHAR (create_time - 23 / 72, ''MM'') work_month,
TO_CHAR (create_time - 23 / 72, ''WW'') work_week,
TO_CHAR (create_time - 23 / 72, ''DD'') work_date,
TO_CHAR (create_time, ''day'') weekday, shift, subline subline_name,
pro_model model, part_no, ''0'' wo_no, 0 TYPE,
TO_CHAR (a.create_time, ''hh24'') HOUR, description route_code, port,
code defect_code, '' reason_code, chinese,
COUNT (DISTINCT cellsn) qty
FROM k93_cellerrorcode a, sfcs_time_subarea_apple b
WHERE TO_CHAR (create_time, ''hh24:mi:ss'') >= begin_t
AND TO_CHAR (create_time, ''hh24:mi:ss'') <= end_t
AND SUBSTR (port, 1, 4) = ''port''
AND create_time BETWEEN TO_DATE (''2012/1/1 08:00:00'',
''yyyy-mm-dd hh24:mi:ss''
)
AND TO_DATE ( TO_CHAR (SYSDATE, ''yyyy-mm-dd'')
|| '' 7:59:59'',
''yyyy-mm-dd hh24:mi:ss''
)
GROUP BY TO_CHAR (create_time - 23 / 72, ''MM''),
TO_CHAR (create_time - 23 / 72, ''WW''),
TO_CHAR (create_time - 23 / 72, ''DD''),
TO_CHAR (create_time, ''day''),
shift,
subline,
pro_model,x
part_no,
TO_CHAR (a.create_time, ''hh24''),
description,
port,
code,
chinese)';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'
CREATE TABLE SMP.TEMP_SFCS_DEFECTS
(
ORG_ID NUMBER NOT NULL,
SN_KEY NUMBER,
MODEL VARCHAR2(50 BYTE),
PART_NO VARCHAR2(20 BYTE),
WO_KEY NUMBER,
DEFECT_KEY NUMBER,
DEFECT_CODE VARCHAR2(10 BYTE),
QTY NUMBER,
DEFECT_KIND NUMBER DEFAULT 0,
CUSTOMER_DEFECT_CODE VARCHAR2(10 BYTE),
WS_ID NUMBER,
CHECKER_ID NUMBER,
I_TIME DATE DEFAULT SYSDATE,
RS_ID NUMBER,
REPAIRER_ID NUMBER,
O_TIME DATE,
OP_LOT_KEY NUMBER,
MAIN_POWER VARCHAR2(20 BYTE),
SENSITIVITY VARCHAR2(20 BYTE),
DELTA_1 VARCHAR2(20 BYTE),
DELTA_2 VARCHAR2(20 BYTE),
TX_1 VARCHAR2(20 BYTE),
TX_2 VARCHAR2(20 BYTE),
RX_1 VARCHAR2(20 BYTE),
RX_2 VARCHAR2(20 BYTE),
RS_KEY NUMBER,
BATCH_KEY NUMBER,
ALERTED VARCHAR2(1 BYTE),
HALT VARCHAR2(1 BYTE),
FAIL_NUMBER NUMBER,
R_FLAG NUMBER DEFAULT 0
)';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'insert into TEMP_SFCS_DEFECTS (select * from sfcs_defects where
i_time BETWEEN TO_DATE (''2012/1/1 08:00:00'',
''yyyy-mm-dd hh24:mi:ss''
)
AND TO_DATE ( TO_CHAR (SYSDATE,
''yyyy-mm-dd'')
|| '' 7:59:59'',
''yyyy-mm-dd hh24:mi:ss''
))';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'insert into TEMP_DM_defect (SELECT 2 AS ID, TO_CHAR (i_time - 23 / 72, ''MM'') work_month,
TO_CHAR (i_time - 23 / 72, ''WW'') work_week,
TO_CHAR (i_time - 23 / 72, ''DD'') work_date,
TO_CHAR (i_time, ''day'') weekday, shift, b.subline_name,
a.model, a.part_no, c.wo_no, c.TYPE,
TO_CHAR (a.i_time, ''hh24'') HOUR,
SUBSTR (b.route_code, 6, LENGTH (b.route_code)) route_code,
''0'' port, a.defect_code, e.reason_code, d.chinese,
COUNT (DISTINCT sn_key) qty
FROM TEMP_SFCS_DEFECTS a,
sfcs_time_subarea_apple f,
sh_site_view b,
wip_wo c,
sys_lookup_table d,
sfcs_reasons e
WHERE a.org_id = 51
AND TO_CHAR (i_time, ''hh24:mi:ss'') >= begin_t
AND TO_CHAR (i_time, ''hh24:mi:ss'') <= end_t
AND a.ws_id = b.ws_id
AND a.wo_key = c.wo_key
AND a.defect_code = d.code
AND a.defect_key = e.defect_key
GROUP BY TO_CHAR (i_time - 23 / 72, ''MM''),
TO_CHAR (i_time - 23 / 72, ''WW''),
TO_CHAR (i_time - 23 / 72, ''DD''),
TO_CHAR (i_time, ''day''),
shift,
b.subline_name,
a.model,
a.part_no,
c.wo_no,
c.TYPE,
TO_CHAR (a.i_time, ''hh24''),
b.route_code,
a.defect_code,
d.chinese,
e.reason_code
ORDER BY qty DESC)';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'CREATE TABLE SMP.TEMP_DM_D_FRT_DEFECT
(
WORK_MONTH VARCHAR2(2 BYTE),
WORK_WEEK VARCHAR2(2 BYTE),
WORK_DATE VARCHAR2(2 BYTE),
WEEKDAY VARCHAR2(9 BYTE),
SHIFT VARCHAR2(30 BYTE),
SUBLINE_NAME VARCHAR2(90 BYTE),
MODEL VARCHAR2(50 BYTE),
PART_NO VARCHAR2(50 BYTE),
WO_NO VARCHAR2(50 BYTE),
TYPE VARCHAR2(50 BYTE),
HOUR VARCHAR2(30 BYTE),
SHIFTDAY DATE,
ROUTE_CODE VARCHAR2(50 BYTE),
PORT VARCHAR2(50 BYTE),
DEFECT_CODE VARCHAR2(50 BYTE),
REASON_CODE VARCHAR2(10 BYTE),
CHINESE VARCHAR2(50 BYTE),
QTY NUMBER
)';
EXECUTE IMMEDIATE str;
COMMIT;
str :=
'insert into TEMP_DM_D_FRT_DEFECT(SELECT work_month, work_week, work_date, weekday, shift, subline_name,
model, part_no, wo_no,
DECODE (TYPE,
1, ''正常工單?'',
0, ''正常工單'',
102, ''重工工單'',
2, ''重工工單''
) TYPE,
HOUR, TO_CHAR (SYSDATE - 1, ''yyyy-mm-dd'') shiftday, route_code, port,
defect_code, reason_code, chinese, qty
FROM TEMP_DM_defect)';
EXECUTE IMMEDIATE str;
COMMIT;
--Truncate data
EXECUTE IMMEDIATE 'truncate table DM_D_FRT_DEFECT';
str :=
'INSERT INTO DM_D_FRT_DEFECT
(SELECT model, part_no, wo_no, subline_name, route_code, port, defect_code,
reason_code, chinese, qty, work_month, work_week, work_date,
weekday, shiftday, shift, HOUR, TYPE
FROM TEMP_DM_D_FRT_DEFECT)';
EXECUTE IMMEDIATE str;
COMMIT;
EXECUTE IMMEDIATE 'drop table TEMP_DM_defect';
EXECUTE IMMEDIATE 'drop table TEMP_SFCS_DEFECTS';
EXECUTE IMMEDIATE 'drop table TEMP_DM_D_FRT_DEFECT';
END;
[解决办法]
已经创建过了一个不是存储过程但是与这个存储过程同名的schema对象,
你的语法是CREATE OR REPLACE ,如果同类型且同名一定会重新建立的.
你用select * from all_objects where object_name='SUNSET'