- 最后登录
- 2015-9-23
- 在线时间
- 24 小时
- 威望
- 0
- 金钱
- 86
- 注册时间
- 2013-3-6
- 阅读权限
- 10
- 帖子
- 23
- 精华
- 0
- 积分
- 0
- UID
- 946
|
1#
发表于 2014-8-19 12:44:49
|
查看: 2574 |
回复: 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
请问各位大仙,这个怎么弄
如何实现,远程给一个带数组的存储过程 调用并传参数?
|
|