FETCH CURSOR_SR INTO STR_FDT,STR_FTM,STR_CHANGE_FN;
的問題
的問題
解决方案 »
- Oracle9i数据库逆向工程到Powerdesign(12.5)里面,comment列中文成乱码怎么解决?
- oracle 11g 服务启动后 监听程序当前无法识别SID的问题
- 征集ORACLE方面的面试题
- 建触发器引发的update失败问题!
- Oracle支持MSSQL的left outer join(左外聯接),right outer join(右外聯接)么?
- SGA大小调高,PGA不变,高速缓存命中百分比下降?
- 导入导出库的问题
- 如何装 ORACLE9i, ?????
- 帮我看看这个查询,总报错!!!
- select出的两个集合的操作,一个是另外一个的子集合
- Net Assistant 不能正常运行问题
- 导数据-菜鸟问题-操作成功马上给分
重起数据库试下
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
欢迎,问题解决了吗?
問題我已經解決!