oracle ora-01001,ORA-01001: invalid cursor

这个情况我碰到过，数据库版本是9206及10204，运行一段时间后就报这个错，原因查看代码实在查不出来。

最后经过查看文档及实践，在存储过程前面加上

dbms_session.modify_package_state(action_flags => 1);

该问题消失。

下面是有关modify_package_state的说明。如果实践用REINITIALIZE可以的话，建议还是用REINITIALIZE，实在不行再用FREE_ALL_RESOURCES.

--------------------------------------------------------------------

--  action_flags (bit flags) for MODIFY_PACKAGE_STATE  procedure ---

--------------------------------------------------------------------

FREE_ALL_RESOURCES   constant PLS_INTEGER := 1;

REINITIALIZE         constant PLS_INTEGER := 2;

procedure modify_package_state(action_flags IN PLS_INTEGER);

--  The MODIFY_PACKAGE_STATE procedure can be used to perform

--  various actions (as specified by the 'action_flags' parameter)

--  on the session state of ALL PL/SQL program units active in the

--  session. This takes effect only after the PL/SQL call that

--  made the current invokation finishes running.

--

--  Parameter(s):

--   action_flags:

--     Determines what action is taken on the program units.

--     The following action_flags are supported:

--

--     * DBMS_SESSION.FREE_ALL_RESOURCES:

--         This frees all the memory associated with each of the

--         previously run PL/SQL programs from the session, and,

--         consequently, clears the current values of any package

--         globals and closes any cached cursors. On subsequent use,

--         the PL/SQL program units are re-instantiated and package

--         globals are reinitialized. This is essentially the

--         same as DBMS_SESSION.RESET_PACKAGE() interface.

--

--     * DBMS_SESSION.REINITIALIZE:

--         In terms of program semantics, the DBMS_SESSION.REINITIALIZE

--         flag is similar to the DBMS_SESSION.FREE_ALL_RESOURCES flag

--         in that both have the effect of re-initializing all packages.

--

--         However, DBMS_SESSION.REINITIALIZE should exhibit much better

--         performance than the DBMS_SESSION.FREE_ALL_RESOURCES option

--         because:

--

--           - packages are reinitialized without actually being freed

--           and recreated from scratch. Instead the package memory gets

--           reused.

--

--           - any open cursors are closed, semantically speaking. However,

--           the cursor resource is not actually freed. It is simply

--           returned to the PL/SQL cursor cache. And more importantly,

--           the cursor cache is not flushed. Hence, cursors

--           corresponding to frequently accessed static SQL in PL/SQL

--           will remain cached in the PL/SQL cursor cache and the

--           application will not incur the overhead of opening, parsing

--           and closing a new cursor for those statements on subsequent use.

--

--           - the session memory for PL/SQL modules without global state

--           (such as types, stored-procedures) will not be freed and

--           recreated.

--

--

--  Usage Example:

--    begin

--      dbms_session.modify_package_state(DBMS_SESSION.REINITIALIZE);

--    end;

--

我是把这个问题当成疑难杂症的(也许是bug？)，既然解决了就算了。希望对你有帮助。

msn:dayspring_chen@yahoo.com.cn

[本帖最后由 dayspring_chen 于 2011-2-28 10:27 编辑]