下面的语编译不出错,但每次执行时出错
create or replace procedure CreateDbLink(UserName IN VARCHAR2,PassWord IN VARCHAR2,ConnStr IN VARCHAR2)
is
variable 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'链接
/*这里出错了提示说不存在表名*/
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;
create or replace procedure CreateDbLink(UserName IN VARCHAR2,PassWord IN VARCHAR2,ConnStr IN VARCHAR2)
is
variable 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'链接
/*这里出错了提示说不存在表名*/
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;
等与
v_sql:=v_sql||'''';
由上时出错了
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 * from pingnt.test';
/*出错。表名不存*/
execute immediate v_sql;
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;
/*出错。表名不存*/
IF variable=0
then
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 if;
end CreateDbLink;
execute immediate v_sql;//建立链接在过程中执行ddl语句,角色起不到作用,要直接赋予给用户
grant drop database link to 当前用户;
grant create database link to 当前用户;忠告楼主,人人平等,当一名程序首先良好素质!!