ORA-00904 :标识符无效

  • 执行如下SQL 出现以下ERROR

 1 create or replace procedure P_DISTABLE_FK(IN_TABLE in varchar2) is
 2 --失效 输入表名称的外键
 3 V_SQL VARCHAR2(200) ;
 4 V_FK  VARCHAR2(60); 
 5 begin
 6       --查询外键名称
 7       V_SQL :='SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='|| 'R'||  '  and  table_name='||IN_TABLE;
 8        dbms_output.put_line(  ' V_SQL IS ' ||  V_SQL);
 9     
10       execute immediate V_SQL into V_FK;
11       v_sql:='alter table'|| IN_TABLE ||'disable constraint '||V_FK;  
12       EXECUTE IMMEDIATE v_sql;
13 end P_DISTABLE_FK;
  • 打印 V_SQL 变量　：

 V_SQL IS SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE=R  and  table_name='B';

  • 发现问题出在：CONSTRAINT_TYPE=R

 SQL 应该为：SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R'  and  table_name='B';

修改存储过程：

create or replace procedure P_DISTABLE_FK(IN_TABLE in varchar2,IN_TYPE IN VARCHAR2) is
--失效 输入表名称的外键
V_SQL VARCHAR2(200) ;
V_FK  VARCHAR2(60);  
begin
      --查询外键名称
      V_SQL :='SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='|| IN_TYPE||  '  and  table_name='||IN_TABLE;
       dbms_output.put_line(  ' V_SQL IS ' ||  V_SQL);
    
      execute immediate V_SQL into V_FK;
      v_sql:='alter table'|| IN_TABLE ||'disable constraint '||V_FK;  
      EXECUTE IMMEDIATE v_sql;
end P_DISTABLE_FK;

这样输出的SQL 就为：正确形式了

SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='R'  and  table_name='B'

posted @ 2013-01-03 16:31  王超_cc  阅读(4400)  评论(0)  编辑  收藏  举报