批量生成oracle同义词

-- Created on 2010-2-2 by XCY
declare
  -- Local variables here
  i           integer;
  countTb     integer;
  v_sql       varchar2(1023);
  v_basename  varchar2(200);
  v_tablename varchar2(200);
  v_synonym   varchar2(1023);
begin

  select count(*) into countTb from tbl_smc_tables;
  i := 0;
  for i in 1 .. countTb loop
    v_sql := 'select base_name,table_name from(  select t.base_name,t.table_name,rownum rn from  tbl_smc_tables  t ) where rn=:rn';
 
    execute immediate v_sql
      into v_basename, v_tablename
      using i;
 
    dbms_output.put_line(v_basename || '.' || v_tablename);
 
    v_synonym := 'create  synonym  ::v_tablename for ::v_basename.::v_tablename';
    v_synonym := replace(v_synonym, '::v_tablename', v_tablename);
    v_synonym := replace(v_synonym, '::v_basename', v_basename);
 
    execute immediate v_synonym;
 
  end loop;

end;