Erp100论坛 » Oracle ERP DBA + Oracle Database » Oracle开发 » 这样动态执行SQL错在那里

xiangzhao 发表于 2008-7-12 14:14

这样动态执行SQL错在那里

declare
  cursor cc is
  SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY ;' as diable
  FROM USER_CONSTRAINTS
  WHERE CONSTRAINT_TYPE = 'P';
  ccrec cc%rowtype;
  var_sql   varchar2(100);   
  v_temp varchar2(200);
begin
    for ccrec in cc loop
            var_sql:=ccrec.diable;
            dbms_output.put_line(var_sql);
            execute immediate  var_sql;
         end loop;
end;

xiangzhao 发表于 2008-7-12 14:14

using后面加参数，可我这个没有参数传递
SQL> /
ALTER TABLE EASYDW.TODAYS_SPECIAL_OFFERS DISABLE PRIMARY KEY ;
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 13

我执行候的错误

xiangzhao 发表于 2008-7-12 14:14

在sqlplus 里运行下
  SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY ;' as diable
  FROM USER_CONSTRAINTS
  WHERE CONSTRAINT_TYPE = 'P';

xiangzhao 发表于 2008-7-12 14:14

你去掉SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY ;'  中最后的分号试下！！
应该是这个问题
改成SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY '

xiangzhao 发表于 2008-7-12 14:15

SQL> SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY ' as diable
  2  FROM USER_CONSTRAINTS
  3  WHERE CONSTRAINT_TYPE = 'P' AND TABLE_NAME='T';

DIABLE
--------------------------------------------------------------------------------
ALTER TABLE SCOTT.T DISABLE PRIMARY KEY

SQL> declare
  2  cursor cc is
  3  SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE PRIMARY KEY ' as diable
  4  FROM USER_CONSTRAINTS
  5   WHERE CONSTRAINT_TYPE = 'P' AND TABLE_NAME='T';
  6  ccrec cc%rowtype;
  7  var_sql   varchar2(100);
  8   v_temp varchar2(200);
  9  begin
10  for ccrec in cc loop
11  var_sql:=ccrec.diable;
12  dbms_output.put_line(var_sql);
13  execute immediate  var_sql;
14  end loop;
15  end;
16  /

PL/SQL procedure successfully completed.

SQL>

查看完整版本: 这样动态执行SQL错在那里