declare
str varchar2(50);
begin
select count(1) into variable from sys.link$ where name='ZdtcMoveData';
IF variable>0 THEN
str:='drop database link ZdtcMoveData';//提示这句子出错了
execute immediate str;
END if;
str varchar2(50);
begin
select count(1) into variable from sys.link$ where name='ZdtcMoveData';
IF variable>0 THEN
str:='drop database link ZdtcMoveData';//提示这句子出错了
execute immediate str;
END if;
使用动态SQL改为:
select count(1) into variable from sys.link$ where name='ZdtcMoveData';
IF variable>0 THEN
execute immediate 'drop database link ZdtcMoveData';
END if;
我改成以下的写些了
create or replace procedure CreateDbLink(UserName IN VARCHAR2,PassWord IN VARCHAR2,ConnStr IN VARCHAR2)
isvariable varchar2(50);
v_sql VARCHAR2 (4000) := NULL;
begin
v_sql:='select count(1) into variable from sys.link$ where name='||'ZdtcMoveData';
execute immediate v_sql;
IF variable>0
then
execute immediate 'drop database link ZdtcMoveData';
end if;
v_sql:='CREATE DATABASE LINK ZdtcMoveData CONNECT TO ';
v_sql:=v_sql||UserName;
v_sql:=v_sql||' IDENTIFIED BY ';
v_sql:=v_sql||PassWord;
v_sql:=v_sql||' USING ';
v_sql:=v_sql||''';
v_sql:=v_sql||ConnStr;
v_sql:=v_sql||''';
execute immediate v_sql;
end CreateDbLink;
execute immediate v_sql into variable;
createdblink 'system','manager','jian'
exec createdblink 'system','manager','jian'
这两句都是一样的错误
select count(1) from sys.link$ where name='ZdtcMoveData';
中的'号怎么在字符串里表示出来
v_sql:='select count(1) from sys.link$ where name='||'ZdtcMoveData';
这样的话v_sql的内容:select count(1) from sys.link$ where name=ZdtcMoveData;
所以出错了
下面的语编译不出错,但每次执行时出错
create or replace procedure CreateDbLink(UserName IN VARCHAR2,PassWord IN VARCHAR2,ConnStr IN VARCHAR2)
isvariable VARCHAR2(50);
v_sql VARCHAR2(4000) := NULL;
begin
v_sql:='select count(1) from sys.link$ where name=';
v_sql:=v_sql||'''';
v_sql:=v_sql||'ZdtcMoveData';
v_sql:=v_sql||'''';
execute immediate v_sql into variable;//查找是否存'ZdtcMoveDate'链接
/*这里出错了提示说不存在sys.CreateDbLink视图中不存在*/
IF variable>0//有删除
then
execute immediate 'drop database link ZdtcMoveData';
end if;
v_sql:='CREATE DATABASE LINK ZdtcMoveData CONNECT TO ';
v_sql:=v_sql||UserName;
v_sql:=v_sql||' IDENTIFIED BY ';
v_sql:=v_sql||PassWord;
v_sql:=v_sql||' USING ';
v_sql:=v_sql||'''';
v_sql:=v_sql||ConnStr;
v_sql:=v_sql||'''';
execute immediate v_sql;//建立链接
/*此处出错,提示权限不够。我是用system帐号进入了*/
end CreateDbLink;
sys.link$ 加了用户名也不对。
谁知道呀?