使用存储过程创建同义词

在给数据库建同义词的时候原来表比较少的时候还手工建.试着写个存储过程结果里面不让执行DDL语句,郁闷ing.还好找到一个变通的方法,使用execute immediate方法.

create or replace procedure xmds.xmds_set_sy(
ownername IN varchar2
) is
table_name varchar2(50):=”;
sqltext varchar2(100):=”;
  cursor cur_tablename is
    select TABLE_NAME from ALL_TABLES where owner=ownername;
begin
  open cur_tablename;
  loop
    fetch cur_tablename into table_name;
    exit when cur_tablename%notfound;
  sqltext:=’create or replace public synonym ‘||table_name||’ for ‘||ownername||’.’||table_name;
  dbms_output.put_line(sqltext) ;
  execute immediate sqltext;
  commit;
  end loop;
  close cur_tablename;  
end xmds_set_sy;