執行報如下錯誤：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;
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;
解决方案 »
- 存储过程抛出异常，怎样产生日志信息？？？
- ORACLE 10列和表合并问题，麻烦大家帮忙
- 求一存储过程sql
- oracle表中如何按字段名排序？
- 如何使用通配符like，限定一个字符只能是英文字母或者阿拉伯数字－help
- PLSQL Developer中，补充加入的字段，如何能放到两个字段之间，保存时不会被放到最后？在线等待
- SQL Server中的 convert(varchar(20),getdate(),101) , 在Oracle中该怎么表示?
- TNS无法处理服务名
- oracle8.1.6能否安裝在win2000上？急救？
- oracle带参数的语句查询遇到问题
- 关于授权
- Oracle RAC Install on Windows 2008
应该是有一个处于edit的状态的SUNSET吧……
关掉……