oracle 并行插入语句中带函数报错问题，求高手指教，在线等。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
本帖最后由 v0lcano0l 于 2012-10-22 10:37:35 编辑 报错：
ftth_get_vlan_withport(rp.port_id) cvlan （行数名）
*
第 14 行出现错误:
ORA-12840: 在并行/插入直接加载 txn 之后无法访问远程表
ORA-06512: 在 "COMP.FTTH_GET_VLAN_WITHPORT", line 5
sql语句：create table RIM_DSLAM_INFO parallel(degree 4) as
select sss_region_name 地市名,
re.eqp_name 设备名称,
'DSLAM设备' 设备类型,
re.manage_ipaddress 设备IP,
rsh.position 机框序号,
rc.position 板卡序号,
rp.position 端口序号,
rp.port_no,
rp.exchange_module_no 设备号,
(select desc_china
from rimdb.pub_restriction@rim
where serial_no = rp.opr_state_id) 端口状态,
ftth_get_vlan_withport(rp.port_id) cvlan
from rimdb.rme_eqp@rim re,
rimdb.rme_port@rim rp,
rimdb.rme_res_union@rim rru,
rimdb.rme_shelf@rim rsh,
rimdb.rme_slot@rim rs,
rimdb.rme_slot_card@rim rsc,
rimdb.rme_card@rim rc,
noc_mrj.vw_region_new@rim r
where re.eqp_id = rru.a_res_id
and r.region_id = re.region_id
and rru.z_res_id = rsh.shelf_id
and rsh.shelf_id = rs.shelf_id
and rs.slot_id = rsc.slot_id
and rsc.card_id = rc.card_id
and rc.card_id = rp.card_id
and rp.res_type_id = 2415
and re.res_type_id = 2422
AND EXISTS (SELECT 1
FROM rimdb.PUB_RES_UNION@rim Q, rimdb.PUB_FIXED_ASSETS@rim R
where to_char(R.ASSETS_ID) = Q.A_RES_ID
AND R.DELETE_STATE = '0'
and Q.Z_RES_ID = re.EQP_ID
AND Q.DELETE_STATE = '0'
AND Q.A_RES_TYPE_ID = 168
and R.STATE_ID IN (1680101, 1680105))
and re.delete_state = '0'
and rru.delete_state = '0'
and rsh.delete_state = '0'
and rs.delete_state = '0'
and rsc.delete_state = '0'
and rc.delete_state = '0'
and rp.delete_state = '0';
commit;
函数：
create or replace function ftth_get_vlan_withport(portid char) RETURN CHAR is
pvlan_num rimdb.DAT_VLAN.vlan_num@rim%type;
begin
begin
select dv.vlan_num
into pvlan_num
from rimdb.dat_vlan_port_union@rim dvp, rimdb.DAT_VLAN@rim dv
where dvp.port_id = portid
and dvp.vlan_id = dv.vlan_id
and dv.buz_type = '17150110'
and dvp.delete_state = '0'
and dv.delete_state = '0'
and rownum = 1;
exception
when no_data_found then
pvlan_num := '';
end;
return pvlan_num;
end ftth_get_vlan_withport;
[解决办法]
1.一个小错，别名有可能混淆：
FROM中：
noc_mrj.vw_region_new@rim r
EXISTS FROM中：
rimdb.PUB_FIXED_ASSETS@rim R
2.函数ftth_get_vlan_withport中SQL的条件rownum = 1
表示对行数据的针对性弱，所以，该函数可以用下面SQL替代，而不必引用函数：
(SELECT MIN(dv.vlan_num)
--注意：MIN( )保证一定能返回一行，且no_data_found时，返回NULL。
FROM rimdb.dat_vlan_port_union@rim dvp,
rimdb.DAT_VLAN@rim dv
WHERE dvp.port_id = rp.port_id --注意，portid 已改为rp.port_id
AND dvp.vlan_id = dv.vlan_id
AND dv.buz_type = '17150110'
AND dvp.delete_state = '0'
AND dv.delete_state = '0') --注意rownum = 1已取消
3.建议使用存储过程实现。
[解决办法]
建议：
1。把函数建在远程数据库rim，SQL中如下引用：
ftth_get_vlan_withport@rim(rp.port_id)
2。先把表建好，然后再用并行插入：
insert /*+ parallel(degree 4)*/ into RIM_DSLAM_INFO
select /*+ parallel(degree 4)*/ * from table@rim;
[解决办法]
进行INSERT，UPDATE，DELETE操作，如果数据量大，最好使用存储过程，单记录操作。避免中途数据问题或意外后数据恢复自动操作，特别是回滚段溢出。