oracle用append添加存储过程,insert append的问题（百思不得其解）

大家好，最近遇到了关于insert append是否要先提交再做别的操作的奇事！具体是这样的。

先看这两个过程：

create or replace procedure test_insertappend is

v_sql VARCHAR2(4000);

begin

v_sql := 'insert   /* +append*/

into ITF_ZYTB_EXCH

(ID,SOURCEID,STATION_ID,PROCESSTYPE,PRJ_NO,EDITE,SUPER_STATION_NAME,STATION_NO,CHINA_NAME_AB,DISTRICT_NAME,CHILDREGION_NAME,UNION_STATION_NO,UNION_CHINA_NAME,REGION2_NAME,BUILDING_KIND_NAME,MNT_SPEC_NAME,LOCATION,TYPE_NAME,GRADE_NAME,ISSTATION,LINKMAN,PHONE,BUILDDATE,BUILDING_WIRING,INCLUDING_RANGE,BUILDING_FGHS,BULIDING_XQSX,BUILDING_JRSX,BUSI_PERMIT_RANGE,OPEN_TIME,CHINA_NAME_FULL,BUILDING_UP_POINT,BUILDING_UP_RME,BUILDING_UP_METHOD,BUILDING_PM,BUILDING_RME,OPEN_NO,SUBMIT_MAN,X,Y,STATION_TYPE_NAME)

select ID,SOURCEID,STATION_ID,PROCESSTYPE,PRJ_NO,EDITE,SUPER_STATION_NAME,STATION_NO,CHINA_NAME_AB,DISTRICT_NAME,CHILDREGION_NAME,UNION_STATION_NO,UNION_CHINA_NAME,REGION2_NAME,BUILDING_KIND_NAME,MNT_SPEC_NAME,LOCATION,TYPE_NAME,GRADE_NAME,ISSTATION,LINKMAN,PHONE,BUILDDATE,BUILDING_WIRING,INCLUDING_RANGE,BUILDING_FGHS,BULIDING_XQSX,BUILDING_JRSX,BUSI_PERMIT_RANGE,OPEN_TIME,CHINA_NAME_FULL,BUILDING_UP_POINT,BUILDING_UP_RME,BUILDING_UP_METHOD,BUILDING_PM,BUILDING_RME,OPEN_NO,SUBMIT_MAN,POS_X,POS_Y,STATION_TYPE_NAME

from ITF_N9_EXCH@RESOURCE_LINK d

where not exists (select 1 from ITF_ZYTB_EXCH where id = d.id)';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update ITF_ZYTB_EXCH a set a.g3e_fid=g3e_fid_seq.nextval where a.processtype=''INSERT'' and a.g3e_fid is null';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update ITF_ZYTB_EXCH a set a.g3e_fid = (select g3e_fid from ITF_ZYTB_EXCH where a.sourceid = sourceid and processtype = ''INSERT'') where a.processtype = ''UPDATE'' and a.g3e_fid is null';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update ITF_ZYTB_EXCH a set a.g3e_fid = (select g3e_fid from ITF_ZYTB_EXCH where a.sourceid = sourceid and processtype = ''INSERT'') where a.processtype = ''DELETE'' and a.g3e_fid is null';

EXECUTE IMMEDIATE v_sql;

commit;

end test_insertappend;

create or replace procedure test_insertappend2 is

v_sql VARCHAR2(4000);

begin

v_sql := 'insert /* +append*/ into t_fyzh (OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY) select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY from user_objects a where not exists (select 1 from t_fyzh where object_id=a.object_id)';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update t_fyzh a set a.data_object_id=AAA.Nextval where a.data_object_id is null AND  A.object_type=''TABLE''';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update t_fyzh a set a.data_object_id=2 where a.data_object_id is null AND  A.object_type=''PACKAGE BODY''';

EXECUTE IMMEDIATE v_sql;

v_sql := 'update t_fyzh a set a.data_object_id=3 where a.data_object_id is null AND  A.object_type=''PACKAGE''';

EXECUTE IMMEDIATE v_sql;

COMMIT;

end test_insertappend2;

以上这两个过程分别进行了测试，第一个过程在执行到update的第一个语句时提示“ORA-12838: 无法在并行模式下修改之后读/修改对象”，必须在insert后先commit才行；但是第二个过程却不需要在insert后先commit，这个到底是怎么回事呢？还请达人们帮忙看看！

还有一点要说明，如果我是把sql语句直接放在plsql窗口执行，上面的两个过程的的sql语句都可以在insert append执行完后不commit可直接执行update操作或select操作(plsql窗口的提交不是自动的)。

我用的oracle是10.2.1版本的。

[本帖最后由 fuyinzhen 于 2011-5-23 16:20 编辑]