Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

0

积分

1

好友

7

主题
1#
发表于 2014-8-19 12:44:49 | 查看: 2353| 回复: 0
环境:
10.2.0.1  linux x86->linux 11.2.0.4 x86_64

这个属于测试的pl/sql

请问

我有需要一个带数组参数的存储过程在服务器b,b自己调这个存储过程没问题,但a机器通过dblink调用 就会报
PLS-00306: wrong number or types of arguments in call to 'IN_HEBIN3'


具体如下

b机器上:

CREATE or replace TYPE type_vc AS TABLE OF VARCHAR2 (2000);
/
CREATE or replace TYPE type_nb AS TABLE OF VARCHAR2 (2000);
/


create table hebin2 (id varchar2(2000),dd date,name varchar2(2000),idd number);

begin
for i in 1..1000000 loop
insert into hebin2 values('hh'||i,to_char(sysdate,'yyyymmdd'),'fd'||i||'f',i);
end loop;
commit;
end;
/



CREATE OR REPLACE PROCEDURE  in_hebin3(p1 type_vc,p2 type_vc,p3 type_vc,p4 type_nb ) AS
BEGIN   
  FOR i IN 1 .. p1.COUNT
  LOOP  
  if p4(i)=5 then
  update hebin3 set id='hebinbin' where idd=2;
  end if;
  insert into hebin3@shensu2 values(p1(i),p2(i),p3(i),p4(i));
  commit;
END LOOP;
END;
/  


直接在b机器上

declare
ff1 type_vc;
ff2 type_vc;
ff3 type_vc;
ff4 type_nb;
type refcur is ref cursor;
c1 refcur;
begin
open c1 for 'select id,dd,name,idd  from hebin2 ';
loop
  fetch c1  bulk collect into ff1,ff2,ff3,ff4 limit 956450  ;
  in_hebin3(ff1,ff2,ff3,ff4);
  exit when c1%notfound;
  end loop;
     close c1;
end;
/

这样是可以的

###########################

a机器

CREATE or replace TYPE type_vc AS TABLE OF VARCHAR2 (2000);
/
CREATE or replace TYPE type_nb AS TABLE OF VARCHAR2 (2000);
/


create table hebin2 (id varchar2(2000),dd date,name varchar2(2000),idd number);

begin
for i in 1..1000000 loop
insert into hebin2 values('hh'||i,to_char(sysdate,'yyyymmdd'),'fd'||i||'f',i);
end loop;
commit;
end;
/




declare
ff1 type_vc;
ff2 type_vc;
ff3 type_vc;
ff4 type_nb;
type refcur is ref cursor;
c1 refcur;
begin
open c1 for 'select id,dd,name,idd  from hebin2 ';
loop
  fetch c1  bulk collect into ff1,ff2,ff3,ff4 limit 956450  ;
  in_hebin3@shensu2(ff1,ff2,ff3,ff4);
  exit when c1%notfound;
  end loop;
     close c1;
end;
/



用这个调用就会报

ERROR at line 12:
ORA-06550: line 12, column 3:
PLS-00306: wrong number or types of arguments in call to 'IN_HEBIN3'
ORA-06550: line 12, column 3:
PLS-00306: wrong number or types of arguments in call to 'IN_HEBIN3'
ORA-06550: line 12, column 3:
PLS-00306: wrong number or types of arguments in call to 'IN_HEBIN3'
ORA-06550: line 12, column 3:
PLS-00306: wrong number or types of arguments in call to 'IN_HEBIN3'
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored



请问各位大仙,这个怎么弄

如何实现,远程给一个带数组的存储过程 调用并传参数?
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-5-20 07:52 , Processed in 0.044547 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569