我想把从SQL SERVER 2000导到ORACLE数据库的表名 全改成大写的 在PL/SQL D...里面写了个脚本 报错 帮我看看 问题出在哪 declare tablename1 varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
rename tablename1 to upper(tablename1);
exit when c_mycursor%notfound;
end;
end loop;end;
上面是我最初的写法 报错 ora-06550 然后我改写成
declare tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
str:='rename tablename1 to upper(tablename1)';
execute immediate str;
exit when c_mycursor%notfound;
end;
end loop;end;
也报错 谁知道应该怎么写
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
rename tablename1 to upper(tablename1);
exit when c_mycursor%notfound;
end;
end loop;end;
上面是我最初的写法 报错 ora-06550 然后我改写成
declare tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
str:='rename tablename1 to upper(tablename1)';
execute immediate str;
exit when c_mycursor%notfound;
end;
end loop;end;
也报错 谁知道应该怎么写
alter table oldname rename to newname;
alter table emp rename to emp1;
还有就是exit when最好放fetch下面.这样当cursor为空时不会执行execute immediate,避免错误出现.
declare
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
str:='alter table '|| tablename1||' rename to '|| upper(tablename1)||'1';
execute immediate str;
end;
end loop;
end;
cursor c_mycursor is
select table_name from all_tab_comments where owner ='SYSTEM' and table_type = 'TABLE';--取表名字
注意都要大写.
PLS-00103 出现符号“TABLENAME1”在需要下列之一时::=(@第二个报错信息 ora -00933 SQL命令未正确结束
ora-06512 在LINE 12
表好像不分大小写的.不能把自己重命名为自己,
SQL> alter table emp rename to emp;
alter table emp rename to emp
ORA-00955: name is already used by an existing object
SQL>
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner =upper('system') and table_type =upper('table');--取表名字
begin
open c_mycursor ;
loop
fetch c_mycursor into tablename1;
str:='rename tablename1 to upper(tablename1)';
execute immediate str;
exit when c_mycursor%notfound;
end loop;end;
非法的ALTER TABLE 选项
--其实本身存在oralce 的就是大写的
declare
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner =upper('system') and table_type =upper('table');--取表名字
begin
open c_mycursor ;
loop
fetch c_mycursor into tablename1;
str:='rename '||tablename1||' to '||upper(tablename1)';
execute immediate str;
exit when c_mycursor%notfound;
end loop;end;
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='SYSTEM' and table_type = 'TABLE';
begin
open c_mycursor;
loop
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
if tablename1 <> upper(tablename1) then
str:='alter table system."'||tablename1||'" rename to '||tablename1;
execute immediate str;
end if;
end loop;
close c_mycursor;
end;
/
你用的是dts 导入的? 是的是要加""
alter table emp rename to EMP
ORA-00955: name is already used by an existing object
SQL> alter table EMP rename to emp;
alter table EMP rename to emp
ORA-00955: name is already used by an existing object
SQL>
这块拼接字符串的问题 当然了 上面那个l 我是拷贝3楼的 我也不明白具体代表什么
所以加个1就 alter table system.emp rename to emp1 就可以
alter table system."t_user" rename to T_USER
像这么写 我要写200多条语句啊
但是改完后,如果要引用表名时,必须使用"emp",因为oracle会自动转换,如果写emp,会自动转换为EMP,这样跟数据字典比对的时候就没法找到对象.建议非必要情况不要使用"的方式定义表,会给使用造成很大麻烦.
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from all_tab_comments where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
str:='alter table "'|| tablename1||'" rename to '|| tablename1;
execute immediate str;
end;
end loop;
end;
哦哦,cursor得改下
declare
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from user_tables where owner ='system' and table_type = 'table';--取表名字
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
if tablename1=lower(tablename1) then
str:='alter table "'|| tablename1||'" rename to '|| tablename1;
execute immediate str;
end if;
end;
end loop;
end;
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from user_tables where owner ='system' ;--用户取你要改的用户
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
if tablename1=lower(tablename1) then
str:='alter table "'|| tablename1||'" rename to '|| tablename1;
execute immediate str;
end if;
end;
end loop;
end;
名称 是否为空? 类型
----------------------------------------------------------------------------------------- -------- --------------------------------------
--------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)scott@SZTYORA> DECLARE
2 cursor c_ur IS select table_name FROM user_tables WHERE table_name<>upper(table_name);
3 v_oldname varchar2(30); -- 原表的名字
4 v_newname varchar2(30); -- 更新成新表的名字
5 v_sql VARCHAR2(100);
6 BEGIN
7 v_sql := 'ALTER TABLE :v_oldname RENAME TO :v_newname';
8 FOR i_cur IN c_ur LOOP
9 v_oldname := i_cur.table_name;
10 v_newname := upper(v_oldname);
11 v_sql := 'ALTER TABLE "'||v_oldname||'"'||' RENAME TO "'||v_newname||'"';
12 DBMS_OUTPUT.PUT_LINE(v_sql);
13 EXECUTE IMMEDIATE v_sql;
14 END LOOP;
15 END;
16 /
ALTER TABLE "mytb" RENAME TO "MYTB"PL/SQL 过程已成功完成。scott@SZTYORA> desc "mytb";
ERROR:
ORA-04043: 对象 "mytb" 不存在
scott@SZTYORA> desc "mytb";
ERROR:
ORA-04043: 对象 "mytb" 不存在
scott@SZTYORA> desc mytb;
名称 是否为空? 类型
----------------------------------------------------------------------------------------- -------- --------------------------------------
--------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)scott@SZTYORA>
declare
tablename1 varchar2(100);
str varchar2(100);
cursor c_mycursor is
select table_name from user_tables ;
begin
open c_mycursor ;
loop
begin
fetch c_mycursor into tablename1;
exit when c_mycursor%notfound;
if tablename1=lower(tablename1) then
str:='alter table "'|| tablename1||'" rename to '|| tablename1;
execute immediate str;
end if;
end;
end loop;
end;
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
-- 因为 alter table older_tb rename to new_tb;
-- 其 older_tb 不能跟 new_tb 的名字完全一样!
DECLARE
cursor c_ur IS select table_name FROM user_tables WHERE table_name<>upper(table_name);
v_oldname varchar2(30); -- 原表的名字
v_newname varchar2(30); -- 更新成新表的名字
v_sql VARCHAR2(100);
BEGIN
v_sql := 'ALTER TABLE :v_oldname RENAME TO :v_newname';
FOR i_cur IN c_ur LOOP
v_oldname := i_cur.table_name;
v_newname := upper(v_oldname);
v_sql := 'ALTER TABLE "'||v_oldname||'"'||' RENAME TO "'||v_newname||'"';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/-- 注意:必须加个 where 条件排除当表名已经是大写的情况,否则会报错:
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
ora-06512
cursor c_ur IS select table_name FROM user_tables WHERE table_name<>upper(table_name);
v_oldname varchar2(30); -- 原表的名字
v_newname varchar2(30); -- 更新成新表的名字
v_sql VARCHAR2(100);
BEGIN
FOR i_cur IN c_ur LOOP
v_oldname := i_cur.table_name;
v_newname := upper(v_oldname);
v_sql := 'ALTER TABLE "'||v_oldname||'"'||' RENAME TO "'||v_newname||'"';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/-- 注意:必须加个 where 条件排除当表名已经是大写的情况,否则会报错:
*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用