怎么将dbms_sql的结果输出到ref cursor中.

Question

A few days ago, My developer encounted a problem when he develop a procedure. He met a dynamic select sql statement more than 32K. 
I try to use dbms_sql package to solve this problem. 
However, this brings new problems.
Our procedure used to develop as the following ways.
  procedure DRP_RPT_SALES_QTY_ALL_MULTI
  (
    o_cursor_rst       out  ref cursor
  ) is
  begin
  
    open o_cursor_rst for
    'select * from dual';
  end;Now, I do not know how to achieve the same results when I use dbms_sql package instead execute immediate sql statement.
Plz help me. 
Thanks.

Answer 1

看下DBMS_SQL.PARSE这个过程，STATEMENT参数支持集合类型的：
SQL> desc dbms_sql.parse
Parameter Type                  Mode Default? 
------------- ------------------------ ---- -------- 
C            NUMBER           IN          
STATEMENT VARCHAR2        IN          
LANGUAGE_FLAG NUMBER           IN          
C            NUMBER           IN          
STATEMENT TABLE OF VARCHAR2(32767) IN          
LB          NUMBER           IN          
UB          NUMBER           IN          
LFFLG     BOOLEAN          IN          
LANGUAGE_FLAG NUMBER           IN          
C            NUMBER           IN          
STATEMENT TABLE OF VARCHAR2(256) IN          
LB          NUMBER           IN          
UB          NUMBER           IN          
LFFLG     BOOLEAN          IN          
LANGUAGE_FLAG NUMBER           IN 因此，可以用这个存储过程封装实现：
PROCEDURE proc_exec_clob(in_tc_sql IN CLOB)
AS
n_sqlcur_name NUMBER;
n_sqlrows_proc NUMBER;
n_len NUMBER;
tv_sql dbms_sql.varchar2a;
BEGIN 
n_len := dbms_lob.getlength(in_tc_sql);
FOR i IN 0..n_len/16383 LOOP --双字节
tv_sql(i) := dbms_lob.substr(in_tc_sql,16383,16383*i+1);
END LOOP;
n_sqlcur_name := dbms_sql.open_cursor;    
DBMS_SQL.PARSE(n_sqlcur_name, tv_sql, tv_sql.first, tv_sql.last, FALSE, DBMS_SQL.NATIVE);
n_sqlrows_proc := DBMS_SQL.EXECUTE(n_sqlcur_name);
DBMS_SQL.CLOSE_CURSOR(n_sqlcur_name);
EXCEPTION    
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(n_sqlcur_name) THEN 
   DBMS_SQL.CLOSE_CURSOR(n_sqlcur_name);
   END IF;
DBMS_SQL.CLOSE_CURSOR(n_sqlcur_name);
RAISE;
END proc_exec_clob;

Answer 2

create a temporary table  saving the query result, then "open o_cursor_rst for 'select * from temp_table'"

Answer 3

create a temporary table is a good idea. 
Are there any other better idea?

Answer 4

楼主，你说：He met a dynamic select sql statement more than 32K. 
又说Now, I do not know how to achieve the same results when I use dbms_sql package instead execute immediate sql statement. 
难道你不是想说：有一个动态SQL的长度超过32k，无法使用execute immediate执行这个SQL，不晓得怎么用dbms_sql来实现吗？
呵呵，那参考我提的方法就可以了嘛，入口参数是CLOB，传入VARCHAR2也会自动转换。

Answer 5

我不是执行不了结果. 
而是dbms_sql包执行的结果不支持输出到弱游标 ref cursor.我不知道怎么将用dbms_sql程序包select出来的结果传给一个cursor类型的out参数.

Answer 6

dbms_sql sql indeed accepts only dbms_sql cursor handle and not ref cursor before 11g.

Answer 7

ok,
I kown that. 
thanks.