Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3922672
  • 博文数量: 534
  • 博客积分: 10470
  • 博客等级: 上将
  • 技术积分: 4800
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-26 14:08
文章分类

全部博文(534)

文章存档

2024年(1)

2021年(1)

2019年(1)

2017年(1)

2016年(2)

2013年(2)

2012年(10)

2011年(43)

2010年(10)

2009年(17)

2008年(121)

2007年(252)

2006年(73)

分类: Oracle

2007-02-02 18:27:15

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
    Cause: DDL operations like creation tables, views etc. and transaction control statements such as commit/rollback cannot be performed inside a query or a DML statement.
    Action: Ensure that the offending operation is not performed or use autonomous transactions to perform the operation within the query/DML operation.

eg:
CREATE OR REPLACE FUNCTION OSM_DML_3SP.OSM_TAB_FUNCFUNC1(t IN INTEGER)
  RETURN INTEGER IS rt INTEGER;
  col0 OSM_TAB_FUNC.COL_0%TYPE := 48;
BEGIN
  SAVEPOINT do_del;
  DELETE FROM OSM_DML_3SP.OSM_TAB_FUNC WHERE COL_0 < col0+t;

  IF SQL%FOUND THEN
    ROLLBACK TO do_del;
    INSERT INTO OSM_DML_3SP.OSM_TAB_FUNC SELECT * FROM OSM_DML_3SP.OSM_TAB_FUNC;
    COMMIT COMMENT 'Insert data two times.';
  END IF;

  SELECT COUNT(*) INTO rt FROM OSM_DML_3SP.OSM_TAB_FUNC;
  RETURN rt;
END;

-- this is the correct write
DECLARE
  CURSOR c1(tc VARCHAR2) IS SELECT COL_0 FROM OSM_DML_3SP.OSM_TAB_FUNC
    WHERE COL_3 LIKE '%'||tc||'%';
  rfunt INTEGER;
BEGIN
  FOR tc IN c1('6') LOOP
    rfunt := OSM_DML_3SP.OSM_TAB_FUNCFUNC1(52);
    UPDATE OSM_DML_3SP.OSM_TAB_FUNC SET COL_0=rfunt WHERE COL_0=tc.COL_0;
  END LOOP;
END;
/

-- this is the error write
DECLARE
  CURSOR c1(tc VARCHAR2) IS SELECT COL_0 FROM OSM_DML_3SP.OSM_TAB_FUNC
    WHERE COL_3 LIKE '%'||tc||'%';
BEGIN
  FOR tc IN c1('6') LOOP
    UPDATE OSM_DML_3SP.OSM_TAB_FUNC SET COL_0=OSM_DML_3SP.OSM_TAB_FUNCFUNC1(52)
      WHERE COL_0=tc.COL_0;
  END LOOP;
END;
/

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "OSM_DML_3SP.OSM_TAB_FUNCFUNC1", line 1
ORA-06512: at line 1
阅读(3910) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~