DBMS_SQL & ORA01000: Too many open cursors

好久没有写博了，懒得不行。

最近碰到一个问题，在访问一个View的时候居然报出“ORA-01000: Too many open cursors”的错误信息！仔细分析了下这个VIEW，最后发现导致这个问题的原因是有个函数中用到了DBMS_SQL这个包，但是杯具的是没有保证打开的cursor最后都会被关闭，特别是忘了在异常处理中加入如下关闭游标的语句...

EXCEPTION WHEN OTHERS THEN 
 IF DBMS_SQL.IS_OPEN(CURSOR_NAME) THEN 
  DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME); 
 END IF; 
 .. 
 .. 
END;

解决了这个问题之后，却偶然遇到了如下的错误....

ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1528
ORA-06512: at line 1

后来看了David Litchfield的文章"Oracle 11g DBMS_SQL Security Changes" ，知道DBMS_SQL在11g中的安全性得到了加强。而且如果出现了ORA-29471的错误之后，只有断开当前这个session, 然后重新连接数据库才能正常调用DBMS_SQL包。如果想关闭security check，需要将一个隐含参数_dbms_sql_security_level设置成0 （默认值为1），然后重新启动数据库，如下...

alter system set "_dbms_sql_security_level"=0 scope=spfile;

shutdown immediate

startup

说到隐含参数，不能通过show parameter的方式查询到，需要查询oracle内存的一些struct才能得到，如下...

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'

David Litchfield 同时提到了忘记调用DBMS_SQL.CLOSE_CURSOR出了会造成”内存泄露“的问题，同时也会带来很大的安全隐患。于是简单尝试了下...

-- create a test table
create table test_table(msg varchar2(10));
insert into test_table values('test');
commit;

-- Call DBMS_SQL to delete records from table test_table
-- Note that the code print out the cursor number and doesn't close it!

declare
   l_cur number;
   l_dummy number;
begin
   l_cur := dbms_sql.open_cursor;
   DBMS_SQL.PARSE(l_cur, 'delete from test_table', DBMS_SQL.NATIVE);
   l_dummy := DBMS_SQL.EXECUTE(l_cur);
   dbms_output.put_line(l_cur);
   -- 
   --dbms_sql.close_cursor(l_cur);
/*EXCEPTION
   WHEN OTHERS THEN
        IF dbms_sql.is_open(l_cur) THEN
           dbms_sql.close_cursor(l_cur);
        END IF;*/
end;

-- Use the cursor to execute again! 
-- Hacking!!

declare
  l_dummy number;
begin
 l_dummy := dbms_sql.execute(1575810569);
end;

-- In the end, close the "dangling" cursor
declare 
   l_cur number := 1575810569;
begin
  dbms_sql.close_cursor(l_cur);
end;

/*
 Please note that the following code doesn't work...
 dbms_sql.close_cursor(1575810569);

you will get the following error otherwise...
ORA-06550: line 1, column 29:
PLS-00363: expression '1575810569' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

*/