代码:BEGIN
sqlstr:='OPEN cur_name FOR select B.re,A.CHANNELID,A.AGENTTYPE,
to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') as COUNTIME, count(*) AS CountALL
from t_base_callagentlist A inner join T_BASE_AUTHORITY B on A.USERID=B.FUNID
group by A.CHANNELID,B.re,A.AGENTTYPE,to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')
order by to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') desc;';
execute immediate sqlstr;
END ;
这样写调用的时候就会报错说:无效的SQL语句!
但如果我直接写BEGIN
OPEN cur_name FOR select B.re,A.CHANNELID,A.AGENTTYPE,
to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') as COUNTIME, count(*) AS CountALL
from t_base_callagentlist A inner join T_BASE_AUTHORITY B on A.USERID=B.FUNID
group by A.CHANNELID,B.re,A.AGENTTYPE,to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')
order by to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') desc;';
END ; 就会顺利显示结果!
请问这是为什么?难道是:to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')中双引号出错,还还是本身有什么问题???
sqlstr:='OPEN cur_name FOR select B.re,A.CHANNELID,A.AGENTTYPE,
to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') as COUNTIME, count(*) AS CountALL
from t_base_callagentlist A inner join T_BASE_AUTHORITY B on A.USERID=B.FUNID
group by A.CHANNELID,B.re,A.AGENTTYPE,to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')
order by to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') desc;';
execute immediate sqlstr;
END ;
这样写调用的时候就会报错说:无效的SQL语句!
但如果我直接写BEGIN
OPEN cur_name FOR select B.re,A.CHANNELID,A.AGENTTYPE,
to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') as COUNTIME, count(*) AS CountALL
from t_base_callagentlist A inner join T_BASE_AUTHORITY B on A.USERID=B.FUNID
group by A.CHANNELID,B.re,A.AGENTTYPE,to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')
order by to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') desc;';
END ; 就会顺利显示结果!
请问这是为什么?难道是:to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')中双引号出错,还还是本身有什么问题???
to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') as COUNTIME, count(*) AS CountALL
from t_base_callagentlist A inner join T_BASE_AUTHORITY B on A.USERID=B.FUNID
group by A.CHANNELID,B.re,A.AGENTTYPE,to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'')
order by to_char(A.CALLAGENTSTARTTIME,''yyyy-mm-dd'') desc;';
把desc后面的分号;去掉试一下吧
v_sql:='to_char(A.CALLAGENTSTARTTIME,'||chr(39)||'yyyy-mm-dd'||chr(39)||')';
--此处只给出了和日期相关的一段字符串拼接示例,把他和你的其他字符串拼接起来看看,是否可以运行。
BEGIN
sqlstr:='select ...';
OPEN cur_name FOR sqlstr;
END ;