各位大侠,帮小弟一个忙
我写了一个存储过程,其中有一段语句:
if(is_nbr_acct='0') then
v_temp_sql:='insert into wzg_acct_item_t select a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id,min(a.acct_month) min_acct_month,max(a.acct_month) max_acct_month,sum(a.acct_charge) sum_acct_month
from wlmq.lmf_acct_item_'||v_temp_time||'@ods a,wlmq.wenjie_serv_ftp@ods b
where a.serv_id=b.serv_id and b.partition_id_region=1010 and b.acc_nbr='||str_code||'
group by a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id';
end if;单独拿出来执行没有问题(insert into wzg_acct_item_t select a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id,min(a.acct_month) min_acct_month,max(a.acct_month) max_acct_month,sum(a.acct_charge) sum_acct_month
from wlmq.lmf_acct_item_20080629@ods a,wlmq.wenjie_serv_ftp@ods b
where a.serv_id=b.serv_id and b.partition_id_region=1010 and b.state='F0A' and b.acc_nbr='4632066'
group by a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id)
但放到存储过程中执行时报错信息为
ORA-01722: 无效数字
ORA-02063: 紧接着line(源于ODS)
ORA-06512: 在"BILL.ACCT_ITEM_P", line 32
ORA-06512: 在line 1
那位大侠帮我一下撒
我写了一个存储过程,其中有一段语句:
if(is_nbr_acct='0') then
v_temp_sql:='insert into wzg_acct_item_t select a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id,min(a.acct_month) min_acct_month,max(a.acct_month) max_acct_month,sum(a.acct_charge) sum_acct_month
from wlmq.lmf_acct_item_'||v_temp_time||'@ods a,wlmq.wenjie_serv_ftp@ods b
where a.serv_id=b.serv_id and b.partition_id_region=1010 and b.acc_nbr='||str_code||'
group by a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id';
end if;单独拿出来执行没有问题(insert into wzg_acct_item_t select a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id,min(a.acct_month) min_acct_month,max(a.acct_month) max_acct_month,sum(a.acct_charge) sum_acct_month
from wlmq.lmf_acct_item_20080629@ods a,wlmq.wenjie_serv_ftp@ods b
where a.serv_id=b.serv_id and b.partition_id_region=1010 and b.state='F0A' and b.acc_nbr='4632066'
group by a.serv_id,a.acct_id,a.region_id,a.product_offer_id,a.product_id)
但放到存储过程中执行时报错信息为
ORA-01722: 无效数字
ORA-02063: 紧接着line(源于ODS)
ORA-06512: 在"BILL.ACCT_ITEM_P", line 32
ORA-06512: 在line 1
那位大侠帮我一下撒
你这的单引号个数好像有问题。
你先用dbms_output.put_line把v_temp_sql打印出来,看看形成的SQL语句是否正确。