ORACLE 本地数据库存储过程 调用远程数据库存储过程

步骤1：建立一个远程数据库的连接服务名
D:oracleora92networkadmintnsnames.ora 添加如下代码：

Java代码

1. SDEC =
2. (DESCRIPTION =
3. (ADDRESS_LIST =
4. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
5. )
6. (CONNECT_DATA =
7. (SERVICE_NAME = DEC)
8. )
9. )

SDEC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEC)
    )
  )

步骤2：本地数据库建立一个db link

Java代码

1. CREATE PUBLIC DATABASE LINK doko CONNECT TO gz IDENTIFIED BY GZ USING 'SDEC';

  CREATE PUBLIC DATABASE LINK doko CONNECT TO gz IDENTIFIED BY GZ USING 'SDEC';

ps：这里的SDEC 就是上面配置的服务名

步骤3：编写远程数据库写个测试的存储过程

Java代码

1. create or replace procedure test(a in integer,b out integer) is
2. begin
3. b:=a+1000;
4. end test;

create or replace procedure test(a in integer,b out integer) is
begin
  b:=a+1000;
end test;

步骤4：编写本地数据库的存储过程

Java代码

1. create or replace procedure test2 is
2. p integer;
3. begin
4. test@doko(1,p);
5. dbms_output.put_line('p='||p);
6. end test2;

create or replace procedure test2 is
p integer;
begin
  test@doko(1,p);
  dbms_output.put_line('p='||p); 
end test2;

步骤5：本地测试存储过程

Java代码

1. SQL> set serveroutput on
2. SQL> exec test2;
4. p=1001
6. PL/SQL procedure successfully completed
8. SQL>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25556429/viewspace-1052308/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25556429/viewspace-1052308/