在sqlplus執行下面的會出錯嗎? SELECT A.SR_DATE,A.START_TIME,A.DIAL_NO FROM SERVICE_RECORD A WHERE A.FLAG='1' AND A.FNO IS NULL AND A.SR_DATE=S_DATE FOR UPDATE;
: LGQDUCKY(飘) ( 你的辦法我試驗過 不行的!還是THANKS
jiezhi(西域浪子) 不會出錯 整個PROCEDURE編譯沒有問題的 提示SQL> execute aaaa('20030609'); BEGIN aaaa('20030609'); END;* ERROR 在行 1: ORA-01002: fetch out of sequence ORA-06512: at "PBX.AAAA", line 58 ORA-06512: at line 1
maybe a bug. Check out note # 133424.1 on Metalink.
在ORACLE816下執行沒有問題的但是在ORACLE817下編譯沒有問題的!
Fetching Across Commits The FOR UPDATE clause acquires exclusive row locks. All rows are locked when you open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an exception. In the following example, the cursor FOR loop fails after the tenth insert: DECLARE CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal; ctr NUMBER := 0; BEGIN FOR emp_rec IN c1 LOOP -- FETCHes implicitly ... ctr := ctr + 1; INSERT INTO temp VALUES (ctr, 'still going'); IF ctr >= 10 THEN COMMIT; -- releases locks END IF; END LOOP; END; If you want to fetch across commits, do not use the FOR UPDATE and CURRENT OF clauses. Instead, use the ROWID pseudocolumn to mimic the CURRENT OF clause. Simply select the rowid of each row into a UROWID variable. Then, use the rowid to identify the current row during subsequent updates and deletes. An example follows: DECLARE CURSOR c1 IS SELECT ename, job, rowid FROM emp; my_ename emp.ename%TYPE; my_job emp.job%TYPE; my_rowid UROWID; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_job, my_rowid; EXIT WHEN c1%NOTFOUND; UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid; -- this mimics WHERE CURRENT OF c1 COMMIT; END LOOP; CLOSE c1; END; Be careful. In the last example, the fetched rows are not locked because no FOR UPDATE clause is used. So, other users might unintentionally overwrite your changes. Also, the cursor must have a read-consistent view of the data, so rollback segments used in the update are not released until the cursor is closed. This can slow down processing when many rows are updated. The next example shows that you can use the %ROWTYPE attribute with cursors that reference the ROWID pseudocolumn: DECLARE CURSOR c1 IS SELECT ename, sal, rowid FROM emp; emp_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... IF ... THEN DELETE FROM emp WHERE rowid = emp_rec.rowid; END IF; END LOOP; CLOSE c1; END;ORACLE的解释,我建议去掉commit,或调到另一位置,不知可行否?
commit后面又出現update是有些問題。
commit后面又出現update是有些問題。如何改?
使用結構: ... -- do something commit; exception -- deal with exception rollback; ...
Sometimes you get ORA-1002 in forms when you try to select or select for update in the trigger, and the table you select from is the base table of the block. Check if that's not the issue. Also check if several triggers of the same block try to do select for update on the same table. For example, pre-update and when-validate-record.This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error.
ORACLE说“you cannot fetch from a FOR UPDATE cursor after a commit” 你不能在commit之后从一个FOR UPDATE cursor中fetch数据。 把commit放在close cursor之后,应该没问题吧?
重起数据库试下
SELECT A.SR_DATE,A.START_TIME,A.DIAL_NO
FROM SERVICE_RECORD A
WHERE A.FLAG='1'
AND A.FNO IS NULL
AND A.SR_DATE=S_DATE FOR UPDATE;
你的辦法我試驗過
不行的!還是THANKS
不會出錯
整個PROCEDURE編譯沒有問題的
提示SQL> execute aaaa('20030609');
BEGIN aaaa('20030609'); END;*
ERROR 在行 1:
ORA-01002: fetch out of sequence
ORA-06512: at "PBX.AAAA", line 58
ORA-06512: at line 1
The FOR UPDATE clause acquires exclusive row locks. All rows are locked when you open the cursor, and they are unlocked when you commit your transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an exception. In the following example, the cursor FOR loop fails after the tenth insert: DECLARE
CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;
ctr NUMBER := 0;
BEGIN
FOR emp_rec IN c1 LOOP -- FETCHes implicitly
...
ctr := ctr + 1;
INSERT INTO temp VALUES (ctr, 'still going');
IF ctr >= 10 THEN
COMMIT; -- releases locks
END IF;
END LOOP;
END;
If you want to fetch across commits, do not use the FOR UPDATE and CURRENT OF clauses. Instead, use the ROWID pseudocolumn to mimic the CURRENT OF clause. Simply select the rowid of each row into a UROWID variable. Then, use the rowid to identify the current row during subsequent updates and deletes. An example follows: DECLARE
CURSOR c1 IS SELECT ename, job, rowid FROM emp;
my_ename emp.ename%TYPE;
my_job emp.job%TYPE;
my_rowid UROWID;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_job, my_rowid;
EXIT WHEN c1%NOTFOUND;
UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;
-- this mimics WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
Be careful. In the last example, the fetched rows are not locked because no FOR UPDATE clause is used. So, other users might unintentionally overwrite your changes. Also, the cursor must have a read-consistent view of the data, so rollback segments used in the update are not released until the cursor is closed. This can slow down processing when many rows are updated. The next example shows that you can use the %ROWTYPE attribute with cursors that reference the ROWID pseudocolumn: DECLARE
CURSOR c1 IS SELECT ename, sal, rowid FROM emp;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
...
IF ... THEN
DELETE FROM emp WHERE rowid = emp_rec.rowid;
END IF;
END LOOP;
CLOSE c1;
END;ORACLE的解释,我建议去掉commit,或调到另一位置,不知可行否?
...
-- do something
commit;
exception
-- deal with exception
rollback;
...
update in the trigger, and the table you select from is the base table
of the block. Check if that's not the issue. Also check if several
triggers of the same block try to do select for update on the same
table. For example, pre-update and when-validate-record.This may be caused by fetching from a SELECT FOR UPDATE
cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may
also cause this error.
你不能在commit之后从一个FOR UPDATE cursor中fetch数据。
把commit放在close cursor之后,应该没问题吧?
老兄也上这里来了,
i am piner
欢迎,问题解决了吗?
問題我已經解決!