我执行一个存储过程,执行了大概一天多时间(有2个循环),早上上班的时候,看到报ORA-00372
file string cannot be modified at this time.错误指向的位置是execute immediate sql.
sql是一 个 update语句.
请问这个是什么原因?新人没分可用,各位帮帮忙
file string cannot be modified at this time.错误指向的位置是execute immediate sql.
sql是一 个 update语句.
请问这个是什么原因?新人没分可用,各位帮帮忙
CREATE OR REPLACE PROCEDURE P_SJJH_BDDH AS lv_select varchar2(1000);
lv_sql_update varchar2(4000);
lv_bfxx varchar2(4000);
type cursor_type is ref cursor;
LV_CURSOR_DS cursor_type;
LV_LOOP INTEGER; --查询tbl_sjjh_bddh表的结果变量
lv_id number;
lv_nsrmc tbl_sjjh_bd.nsrmc%type;
lv_yyzzzch tbl_sjjh_bd.yyzzzch%type;
lv_frdb tbl_sjjh_bd.frdb%type;
lv_frsfzhm tbl_sjjh_bd.frsfzhm%type;
lv_dz tbl_sjjh_bd.dz%type;
lv_yzbm tbl_sjjh_bd.yzbm%type;
lv_lxdh tbl_sjjh_bd.lxdh%type;
lv_hydm tbl_sjjh_bd.hydm%type;
lv_qylx tbl_sjjh_bd.qylx%type;
lv_gsdjjg tbl_sjjh_bd.gsdjjg%type;
lv_gsdjrq tbl_sjjh_bd.gsdjrq%type;
lv_nsrzt tbl_sjjh_bd.nsrzt%type;
lv_dzxianid tbl_sjjh_bd.dzxianid%type;
--查询出lv_select的结果变量
lv_nsrdzdah number;
lv_yyzzzch1 varchar2(50);
lv_frdb1 varchar2(500);
lv_frsfzhm1 varchar2(50);
lv_dz1 varchar2(1000);
lv_yzbm1 varchar2(50);
lv_lxdh1 varchar2(100);
lv_hydm1 varchar2(100);
lv_qylx1 varchar2(100);
lv_gsdjjg1 varchar2(100);
lv_gsdjrq1 varchar2(100);
lv_nsrzt1 varchar2(100);
CURSOR LV_CURSOR_GS IS
select id,
trim(nsrmc),
trim(yyzzzch),
trim(frdb),
trim(frsfzhm),
trim(dz),
trim(yzbm),
trim(lxdh),
trim(hydm),
trim(qylx),
trim(gsdjjg),
trim(gsdjrq),
trim(nsrzt),
trim(dzxianid)
from tbl_sjjh_bddh;BEGIN OPEN LV_CURSOR_GS;
LOOP
FETCH LV_CURSOR_GS
INTO lv_id,lv_nsrmc, lv_yyzzzch, lv_frdb, lv_frsfzhm, lv_dz, lv_yzbm, lv_lxdh, lv_hydm, lv_qylx, lv_gsdjjg, lv_gsdjrq, lv_nsrzt,lv_dzxianid;
EXIT WHEN LV_CURSOR_GS%NOTFOUND;
lv_select := 'select n.aaa,trim(K.a),trim(n.b),trim(N.c),trim(n.d),trim(K.e),trim(N.f),trim(N.g),trim(K.h),to_char(K.i,''yyyy-mm-dd''),trim(N.j) from dj_nd n,DJ_nd_KZ k where n.aaa=k.aaa and trim(n.f)=' || '''' ||
lv_nsrmc || ''' and substr(n.g,2,6)=' || '''' || lv_dzxianid || '''';
LV_LOOP := LV_LOOP + 1;
lv_bfxx := '';
OPEN LV_CURSOR_DS FOR lv_select;
LOOP
FETCH LV_CURSOR_DS INTO lv_nsrdzdah,lv_yyzzzch1, lv_frdb1, lv_frsfzhm1, lv_dz1, lv_yzbm1, lv_lxdh1, lv_hydm1, lv_gsdjjg1, lv_gsdjrq1, lv_nsrzt1;
EXIT WHEN LV_CURSOR_DS%NOTFOUND;
lv_bfxx := lv_bfxx || '(' || lv_nsrdzdah || ')' || '不符信息：';
IF (lv_yyzzzch1 != lv_yyzzzch) THEN
lv_bfxx := lv_bfxx || '#################，';
END IF;
IF (lv_frdb1 != lv_frdb) THEN
lv_bfxx := lv_bfxx || '##############，';
END IF;
IF (lv_frsfzhm1 != lv_frsfzhm) THEN
lv_bfxx := lv_bfxx || '################，';
END IF;
IF (lv_dz1 != lv_dz) THEN
lv_bfxx := lv_bfxx || '#############，';
END IF;
IF (lv_yzbm1 != lv_yzbm) THEN
lv_bfxx := lv_bfxx || '#################，';
END IF;
IF (lv_lxdh1 != lv_lxdh) THEN
lv_bfxx := lv_bfxx || '############，';
END IF;
IF (lv_hydm1 != lv_hydm) THEN
lv_bfxx := lv_bfxx || '##########，';
END IF;
IF (lv_qylx1 != lv_qylx) THEN
lv_bfxx := lv_bfxx || '##################，';
END IF;
/*
IF (lv_gsdjjg1 != lv_gsdjjg) THEN
lv_bfxx := lv_bfxx || '################，';
END IF;
*/
IF (lv_gsdjrq1 != lv_gsdjrq) THEN
lv_bfxx := lv_bfxx || '############，';
END IF;
END LOOP;
lv_sql_update := 'update tbl_sjjh_bddh set bfxx = ' || '''' ||
lv_bfxx || ''' where id=' || lv_id;
EXECUTE IMMEDIATE lv_sql_update;
commit;
END LOOP;END;
程序执行了一天多,有不少记录已经更新(bfxx字段)
执行的过程中有没有对tablespace进行操作。或者进行热备份。
是不是按照这篇文章进行热备分?
还有在存储过程中定义字符串超过4000长度,应该用什么类型定义它?