create or replace procedure step_1(part number) is
v_sql varchar2(4000);
err varchar2(512);
num number;
begin
select count(1) into num from amdocs.raise_CRM2 a where a.partition_id = part;
for i in 1 .. num loop
select a.v_exe into v_sql from amdocs.raise_CRM2 a where a.partition_id = part and a.seq = i;
execute immediate v_sql;
err := sqlcode;
err := err || ' ' || sqlerrm;
update amdocs.raise_CRM2 a set a.state = err
where a.seq = i;
dbms_transaction.commit;
end loop;
end step_1;麻烦大家帮我看看哪里错了。
编译通过,测试的时候,execute immediate v_sql中v_sql报为无效的字符。
raise_CRM2 这个表存储了相应的语句。并且确定语句是可执行的。
v_sql varchar2(4000);
err varchar2(512);
num number;
begin
select count(1) into num from amdocs.raise_CRM2 a where a.partition_id = part;
for i in 1 .. num loop
select a.v_exe into v_sql from amdocs.raise_CRM2 a where a.partition_id = part and a.seq = i;
execute immediate v_sql;
err := sqlcode;
err := err || ' ' || sqlerrm;
update amdocs.raise_CRM2 a set a.state = err
where a.seq = i;
dbms_transaction.commit;
end loop;
end step_1;麻烦大家帮我看看哪里错了。
编译通过,测试的时候,execute immediate v_sql中v_sql报为无效的字符。
raise_CRM2 这个表存储了相应的语句。并且确定语句是可执行的。
解决方案 »
- 高分相求:分布式oracle与sqlserver数据库同步方案问题?
- 请问谁有toad for oracle的中文电子教程?我的邮箱是[email protected],谢谢!
- 一个VC++使用ADO连接oracle的问题???
- 一次插入数据后,以后每次根据本月日期更新数据.请教该怎么写?
- like '%%' 和like '%' 的区别!
- 救命呀,项目提交到了客户处却发现问题,客户催死了!HELP ME!
- 表分区的数量有上限吗
- 如何通过v$sql查询是那个机器执行的sql语句!
- 那位知道Oracle分段计算的方法,比较计算第1-10,8-18,16-26。。。记录的平均数
- 毕业生可以考什么数据库方面证书比较有用?
- oracle动态sql跪求帮助
- 求数据表中最大值的SQL语句
如果是上面所说 那么可能出问题的地方就是:
select a.v_exe into v_sql from amdocs.raise_CRM2 a where a.partition_id = part and a.seq = i;这里了
是不是把v_sql中有null?检查下
select a.v_exe into v_sql from amdocs.raise_CRM2 a where a.partition_id = part and a.seq = i;
取前4个的。字符串分别是:
1
create table xy_need_delete_offerinsta_1 as select * from ls8_sid.product_offer_instance_t a
where a.partition_id_region=1012 and exists(select 1 from upgrade_jx.xy_serv_pay_mode_1_detail b
where a.product_offer_instance_id= b.product_offer_instance_id );
2
create index xy_need_delete_offerinsta_1 on xy_need_delete_offerinsta_1(product_offer_instance_id);
3
delete from ls8_sid.product_offer_instance_t a where a.partition_id_region=1012
and exists(select 1 from upgrade_jx.xy_serv_pay_mode_1_detail b
where a.product_offer_instance_id= b.product_offer_instance_id ) ;
4
create table xy_need_delete_offerdetail_1 as select * from ls8_sid.offer_detail_instance_t a
where a.partition_id_region=1012 and exists(select 1 from upgrade_jx.xy_serv_pay_mode_1 b where a.serv_id = b.serv_id );
--动态语句里面不需要分号(;)
--将你的这四个语句的最后的分号去掉
1
create table xy_need_delete_offerinsta_1
as
select * from ls8_sid.product_offer_instance_t a
where a.partition_id_region=1012
and exists(
select 1
from upgrade_jx.xy_serv_pay_mode_1_detail b
where a.product_offer_instance_id= b.product_offer_instance_id )
2
create index xy_need_delete_offerinsta_1
on xy_need_delete_offerinsta_1(product_offer_instance_id)
3
delete from ls8_sid.product_offer_instance_t a
where a.partition_id_region=1012
and exists(
select 1 from upgrade_jx.xy_serv_pay_mode_1_detail b
where a.product_offer_instance_id= b.product_offer_instance_id )
4
create table xy_need_delete_offerdetail_1
as
select * from ls8_sid.offer_detail_instance_t a
where a.partition_id_region=1012
and exists(
select 1 from upgrade_jx.xy_serv_pay_mode_1 b
where a.serv_id = b.serv_id )