package程序如下:create or replace package scada.GETWINDENERGYDENSITY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetDenSity
(
wf_id IN VARCHAR2,
sdate IN VARCHAR2,
edate IN VARCHAR2,
eqid IN VARCHAR2,
WindCount OUT T_CURSOR
);
end GETWINDENERGYDENSITY;create or replace package body scada.GETWINDENERGYDENSITY AS
PROCEDURE GetDenSity
(
wf_id IN VARCHAR2,
sdate IN VARCHAR2,
edate IN VARCHAR2,
eqid IN VARCHAR2,
WindCount OUT T_CURSOR
)
is
temp_sql CLOB:='select ';begin
temp_sql :=temp_sql ||'* from test'; OPEN WindCount FOR temp_sql ; END GetDenSity;
end GETWINDENERGYDENSITY;上面的代码报错信息如下:
Error: PLS-00382: 表达式类型错误
Line: 33
Text: OPEN WindCount FOR temp_sql ;如果我将temp_sql参数定义为varchar2类型,则运行通过, 可是我的SQL语句是拼接出来,并且字符串超过varchar2的4000, 只好换成了CLOB类型, 问:如何游标运行CLOB类型参数运行正常。 在线等。谢谢!!!
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetDenSity
(
wf_id IN VARCHAR2,
sdate IN VARCHAR2,
edate IN VARCHAR2,
eqid IN VARCHAR2,
WindCount OUT T_CURSOR
);
end GETWINDENERGYDENSITY;create or replace package body scada.GETWINDENERGYDENSITY AS
PROCEDURE GetDenSity
(
wf_id IN VARCHAR2,
sdate IN VARCHAR2,
edate IN VARCHAR2,
eqid IN VARCHAR2,
WindCount OUT T_CURSOR
)
is
temp_sql CLOB:='select ';begin
temp_sql :=temp_sql ||'* from test'; OPEN WindCount FOR temp_sql ; END GetDenSity;
end GETWINDENERGYDENSITY;上面的代码报错信息如下:
Error: PLS-00382: 表达式类型错误
Line: 33
Text: OPEN WindCount FOR temp_sql ;如果我将temp_sql参数定义为varchar2类型,则运行通过, 可是我的SQL语句是拼接出来,并且字符串超过varchar2的4000, 只好换成了CLOB类型, 问:如何游标运行CLOB类型参数运行正常。 在线等。谢谢!!!
2 TYPE T_CURSOR IS REF CURSOR;
3 PROCEDURE GetDenSity
4 (
5 wf_id IN VARCHAR2,
6 sdate IN VARCHAR2,
7 edate IN VARCHAR2,
8 eqid IN VARCHAR2,
9 WindCount OUT T_CURSOR
10 );
11 end GETWINDENERGYDENSITY;
12 /程序包已创建。SQL> CREATE OR REPLACE package BODY GETWINDENERGYDENSITY AS
2 PROCEDURE GetDenSity
3 (
4 wf_id IN VARCHAR2,
5 sdate IN VARCHAR2,
6 edate IN VARCHAR2,
7 eqid IN VARCHAR2,
8 WindCount OUT T_CURSOR
9 )
10 is
11 temp_sql CLOB :='select ';
12
13 begin
14 temp_sql :=temp_sql ||'* from test';
15
16 OPEN WindCount FOR to_char(temp_sql);
17
18 END GetDenSity;
19 end GETWINDENERGYDENSITY;
20 /程序包体已创建。SQL> show error
没有错误。
SQL>
但是to_char函数要求参数也不能超过32767所以,使用CLOB类型是不能使open后的sql突破32767字节的长度