首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

急 幫小弟我看下oracle 存儲過程

2012-04-14 
急急急幫我看下oracle 存儲過程執行報如下錯誤:ORA-00955: name is already used by an existing objectOR

急急急 幫我看下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'

热点排行