在存储过程A中执行另一个存储过程B,由于B的sql过长,采用clob类型接受,
当在A中执行execute immediate v_sql,v_sql是clob类型,就出错。
我试过了用long类型,和用clob分割成几个varchar2类型都没成功.
急,在线等高手帮忙,谢谢!
以下是源码:
create or replace procedure SP_QYZX_PROC_UPDATE(p_procname in varchar2 --存储过程名称
,
v_sqlcode OUT NUMBER -- 返回代码
) IS
v_sql clob;
v_linesql varchar2(4000);
v_line number;
v_count number;
v_allcount number;
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_sql3 varchar2(4000);
v_sql4 varchar2(4000);
v_sql5 varchar2(4000);
v_sql6 varchar2(4000);
v_sql7 varchar2(4000);
v_sql8 varchar2(4000);
v_sql9 varchar2(4000);
v_sql10 varchar2(4000);begin ---1.从all_source取数,插入中间表 EXECUTE IMMEDIATE 'TRUNCATE TABLE PRO_CODE_TO_UPDATE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE qijie_20130314_01';
insert into PRO_CODE_TO_UPDATE
select a.name, a.line, a.text
from all_source a
where a.name = p_procname
order by a.line;
commit;
--2.修改存储过程,即更新PRO_CODE_TO_UPDATE表
update PRO_CODE_TO_UPDATE
set text = ' 责任人 : AAAAAAAAAAAAAAAAAAA'
where line = 8;
commit;
--3.从中间表中取sql
select count(*) into v_line from PRO_CODE_TO_UPDATE;
FOR v_count IN 1 .. v_line LOOP
select ''''||text||''''
into v_linesql
from PRO_CODE_TO_UPDATE a
where a.line = v_count;
v_sql := v_sql || ' ' || v_linesql || ' ';
END LOOP;
--insert into qijie_20130314_01 values(v_sql);
--commit;
--4.执行修改后的存储过程
v_allcount:= dbms_lob.getlength(v_sql);
v_sql1:=to_char(substr(v_sql,1,2000));
/* v_sql2:=to_char(substr(v_sql,2001,2000));
v_sql3:=to_char(substr(v_sql,4001,2000));
v_sql4:=to_char(substr(v_sql,6001,2000));
v_sql5:=to_char(substr(v_sql,8001,2000));
v_sql6:=to_char(substr(v_sql,10001,2000));
v_sql7:=to_char(substr(v_sql,12001,2000));
v_sql8:=to_char(substr(v_sql,14001,2000));
v_sql9:=to_char(substr(v_sql,16001,2000));
v_sql10:=to_char(substr(v_sql,18001,v_allcount-18000));*/
dbms_output.put_line(v_sql1);
/*dbms_output.put_line(v_sql2);*/
--execute immediate v_sql1||v_sql2||v_sql3||v_sql4||v_sql5||v_sql6||v_sql7||v_sql8||v_sql9||v_sql10;
execute immediate v_sql;
commit;
v_sqlcode := 0;end SP_QYZX_PROC_UPDATE;
当在A中执行execute immediate v_sql,v_sql是clob类型,就出错。
我试过了用long类型,和用clob分割成几个varchar2类型都没成功.
急,在线等高手帮忙,谢谢!
以下是源码:
create or replace procedure SP_QYZX_PROC_UPDATE(p_procname in varchar2 --存储过程名称
,
v_sqlcode OUT NUMBER -- 返回代码
) IS
v_sql clob;
v_linesql varchar2(4000);
v_line number;
v_count number;
v_allcount number;
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_sql3 varchar2(4000);
v_sql4 varchar2(4000);
v_sql5 varchar2(4000);
v_sql6 varchar2(4000);
v_sql7 varchar2(4000);
v_sql8 varchar2(4000);
v_sql9 varchar2(4000);
v_sql10 varchar2(4000);begin ---1.从all_source取数,插入中间表 EXECUTE IMMEDIATE 'TRUNCATE TABLE PRO_CODE_TO_UPDATE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE qijie_20130314_01';
insert into PRO_CODE_TO_UPDATE
select a.name, a.line, a.text
from all_source a
where a.name = p_procname
order by a.line;
commit;
--2.修改存储过程,即更新PRO_CODE_TO_UPDATE表
update PRO_CODE_TO_UPDATE
set text = ' 责任人 : AAAAAAAAAAAAAAAAAAA'
where line = 8;
commit;
--3.从中间表中取sql
select count(*) into v_line from PRO_CODE_TO_UPDATE;
FOR v_count IN 1 .. v_line LOOP
select ''''||text||''''
into v_linesql
from PRO_CODE_TO_UPDATE a
where a.line = v_count;
v_sql := v_sql || ' ' || v_linesql || ' ';
END LOOP;
--insert into qijie_20130314_01 values(v_sql);
--commit;
--4.执行修改后的存储过程
v_allcount:= dbms_lob.getlength(v_sql);
v_sql1:=to_char(substr(v_sql,1,2000));
/* v_sql2:=to_char(substr(v_sql,2001,2000));
v_sql3:=to_char(substr(v_sql,4001,2000));
v_sql4:=to_char(substr(v_sql,6001,2000));
v_sql5:=to_char(substr(v_sql,8001,2000));
v_sql6:=to_char(substr(v_sql,10001,2000));
v_sql7:=to_char(substr(v_sql,12001,2000));
v_sql8:=to_char(substr(v_sql,14001,2000));
v_sql9:=to_char(substr(v_sql,16001,2000));
v_sql10:=to_char(substr(v_sql,18001,v_allcount-18000));*/
dbms_output.put_line(v_sql1);
/*dbms_output.put_line(v_sql2);*/
--execute immediate v_sql1||v_sql2||v_sql3||v_sql4||v_sql5||v_sql6||v_sql7||v_sql8||v_sql9||v_sql10;
execute immediate v_sql;
commit;
v_sqlcode := 0;end SP_QYZX_PROC_UPDATE;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货