用动态sql吧,如果表名(你这里是数据链路,也一样)是参数时,需要使用动态sql
procedure proc_Receive (vInstitid in varchar2, vDBLink in varchar2, Num out Number, Msg out varchar2)
is
sql varchar2(1000);
v_ErrorText varchar2(200);
begin
sql='insert into tw_defect select * from tw_defect@' || vDBLink;
execute immediate sql;
Msg:= 'OK';
commit;
EXCEPTION
WHEN OTHERS THEN
--这里有必要再用sql块吗?
v_errortext:=substrb(sqlerrm,1,200);
Msg:= v_errortext;
rollback;
END proc_Receive;
---------------------------
如果是8,执行动态sql需要用dbma_sql包,以上适用于8i以上
procedure proc_Receive (vInstitid in varchar2, vDBLink in varchar2, Num out Number, Msg out varchar2)
is
sql varchar2(1000);
v_ErrorText varchar2(200);
begin
sql='insert into tw_defect select * from tw_defect@' || vDBLink;
execute immediate sql;
Msg:= 'OK';
commit;
EXCEPTION
WHEN OTHERS THEN
--这里有必要再用sql块吗?
v_errortext:=substrb(sqlerrm,1,200);
Msg:= v_errortext;
rollback;
END proc_Receive;
---------------------------
如果是8,执行动态sql需要用dbma_sql包,以上适用于8i以上
1、execute immediate string 2、dbms_ddl
DECLARE
v_count INTEGER;
v_table VARCHAR(32);
v_sql VARCHAR(225);
BEGIN
v_table := 'Subscriber ';
v_sql :=' select count(*) into v_count from '||v_table;
execute immediate v_sql ;
dbms_output.put_line('Count :'||to_char(v_count));
END;
SQL> var v number;
SQL> set serverout on
SQL> begin
2 execute immediate 'select count(*) from tab' into :v;
3 dbms_output.put_line(to_char(:v));
4 end;
5 /
17PL/SQL 过程已成功完成。SQL>