for n in A1 loop
m_cnt := 0;
m_val := 0;
s_sql:='select count(*),sum(a.duration) from
ocsbill.ocs_event_tgcdr_9010'||month1||
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='||n.dn_no||' and a.billing_nbr='||n.dn_no||' group by a.billing_nbr';
execute immediate s_sql into m_cnt, m_val;
update lydate
set bjbd_val = m_val, bjbd_cnt = m_cnt
where dn_no = n.dn_no;
commit;
end loop;
--以上是我程序脚本的一部分,运行提示错误,提示这一行execute immediate s_sql
invalid number! 可是我以前都可以运行,现在却不行了。谁帮我看看是哪里的问题吗?
m_cnt := 0;
m_val := 0;
s_sql:='select count(*),sum(a.duration) from
ocsbill.ocs_event_tgcdr_9010'||month1||
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='||n.dn_no||' and a.billing_nbr='||n.dn_no||' group by a.billing_nbr';
execute immediate s_sql into m_cnt, m_val;
update lydate
set bjbd_val = m_val, bjbd_cnt = m_cnt
where dn_no = n.dn_no;
commit;
end loop;
--以上是我程序脚本的一部分,运行提示错误,提示这一行execute immediate s_sql
invalid number! 可是我以前都可以运行,现在却不行了。谁帮我看看是哪里的问题吗?
s_sql:='select count(*),sum(a.duration) from ocsbill.ocs_event_tgcdr_9010'||month1||
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='||n.dn_no||' and a.billing_nbr='''||n.dn_no||''' group by a.billing_nbr';
execute immediate s_sql into m_cnt,m_val;
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='||n.dn_no||' and a.billing_nbr='''||n.dn_no||''' group by a.billing_nbr';
execute immediate s_sql into m_cnt,m_val;
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='''||n.dn_no||''' and a.billing_nbr='''||n.dn_no||''' group by a.billing_nbr';
execute immediate s_sql into m_cnt,m_val;
s_sql:='select count(*),sum(a.duration) from
ocsbill.ocs_event_tgcdr_9010'||month1||
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and ( a.calling_belong_region<>''0790'' or called_visit_region<>''0790'')
and a.called_nbr='||n.dn_no||' and a.billing_nbr='||n.dn_no||' group by a.billing_nbr'; 这里就没报错
'@billdb a,ocsbill.ocs_event_tgcdr_fee_9010'||month1||'@billdb b
where a.event_inst_id= b.event_inst_id and calling_visit_region=''0790'' and
a.calling_nbr='||n.dn_no||' and a.billing_nbr='''||n.dn_no||''' group by a.billing_nbr';
execute immediate s_sql into m_cnt,m_val;
你说你的n.dn_no 是字符型的肯定的'''||n.dn_no||'''这样拼接没数据不好测
那就先输出看看
dbms_output.put_line(s_sql);
先别执行execute immediate s_sql
a.calling_nbr='||n.dn_no||' --这里 calling_nbr 加了to_char 转换了下,就没报错了。