create or replace procedure emis_to_nc_change(
av_emis in varchar2,
av_type in varchar2,
av_user in varchar2,
av_password in varchar2,
av_connect in varchar2,
av_nc out char,
av_errid out integer,
av_errtext out varchar2) is
v_sql varchar2(400);
begin
v_sql := 'CREATE DATABASE LINK nc.us.oracle.com CONNECT TO ufadmin IDENTIFIED BY ufadmin USING "' ¦ ¦'emiswanshan' ¦ ¦'"';
EXECUTE IMMEDIATE v_sql
select pk_deptdoc
into av_nc
from [email protected]
where deptcode = '2H12D30';
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK nc.us.oracle.com';
EXECUTE IMMEDIATE 'DROP DATABASE LINK nc.us.oracle.com';
end emis_to_nc_change;
在编译的时候 提示Compilation errors for PROCEDURE EMIS.EMIS_TO_NC_CHANGE
Error: PL/SQL: ORA-04052: 在查找远程对象 [email protected] 时出错
ORA-00604: 递归 SQL 层 1 出现错误
ORA-12154: TNS: 无法处理服务名
av_emis in varchar2,
av_type in varchar2,
av_user in varchar2,
av_password in varchar2,
av_connect in varchar2,
av_nc out char,
av_errid out integer,
av_errtext out varchar2) is
v_sql varchar2(400);
begin
v_sql := 'CREATE DATABASE LINK nc.us.oracle.com CONNECT TO ufadmin IDENTIFIED BY ufadmin USING "' ¦ ¦'emiswanshan' ¦ ¦'"';
EXECUTE IMMEDIATE v_sql
select pk_deptdoc
into av_nc
from [email protected]
where deptcode = '2H12D30';
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK nc.us.oracle.com';
EXECUTE IMMEDIATE 'DROP DATABASE LINK nc.us.oracle.com';
end emis_to_nc_change;
在编译的时候 提示Compilation errors for PROCEDURE EMIS.EMIS_TO_NC_CHANGE
Error: PL/SQL: ORA-04052: 在查找远程对象 [email protected] 时出错
ORA-00604: 递归 SQL 层 1 出现错误
ORA-12154: TNS: 无法处理服务名
查看你访问的数据库服务器的配置是否正确
一臺server要創建的DBLink應該沒幾個吧,
直接先Create,再直接引用比較好吧....
就算你在存储过程中写上建DBLINK的代码,也同样是一次创建,之后再运行会提示已经存在该对象;而且用户名密码以及TNS服务名一旦变了的话你不得不修改存储过程。
把那个select语句也改用动态sql可能就行了。