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
阅读(4024) | 评论(0) | 转发(0) |