100分求解：sys.dbms_metadata.get_ddl得到的create table sql無法順利執行，UP三分

Question

再發一次,有些代碼沒刪掉，有點零亂.呵呵如題，程序如下
CREATE OR REPLACE
PROCEDURE PRO_TRAN_DATA 
--  ==========================================================
--  程式類別 :  PROCEDURE
--  程式功能 :  將TEST user所有Table資料(包含table的架構和data)複製一份到TEST1 user
--  程式名稱 :  PRO_TRAN_DATA
--  傳入參數 :  
--  傳出參數 :  無
--  傳入？傳出參數：無
--  原設計者 : cheng_shi_qin      設立日期 : 2004/05/15
--  使用說明 : 如果table 在TEST1 user已經存在，則清空TEST1 user的table並重新新增data
--  ==========================================================
IS   
   vn_counter          NUMBER;   
   vc_sql              VARCHAR2(200);
   vc_ctsql                    varchar2(4000);  
      
BEGIN
   FOR i IN (SELECT table_name FROM user_tables)
   LOOP                                    
      SELECT COUNT(*) INTO vn_counter FROM dba_tables 
                                     WHERE OWNER='TEST1'
        AND table_name=i.table_name;
      IF vn_counter=0 THEN    
         SELECT REPLACE(sys.dbms_metadata.get_ddl('TABLE',i.table_name),'"TEST".','"TEST1".')
           INTO vc_ctsql FROM DUAL;     
         EXECUTE IMMEDIATE 'INSERT INTO test1.'||i.table_name||' SELECT * FROM '||i.table_name||';';
      ELSE     
         vc_sql:='DELETE FROM test1.'||i.table_name;     
         DBMS_OUTPUT.PUT_LINE('DELETE FROM test1.'||i.table_name||';');
         EXECUTE IMMEDIATE vc_sql;
         EXECUTE IMMEDIATE 'INSERT INTO test1.'||i.table_name||' SELECT * FROM '||i.table_name;
      END IF;
   END LOOP;
   COMMIT;
END PRO_TRAN_DATA; 
/
=========================================================================================SQL> set serveroutput on
SQL> set timing on
SQL> exec pro_tran_databegin pro_tran_data; end;ORA-00911: 字元無效
ORA-06512: 在 "TEST.PRO_TRAN_DATA", line 47
ORA-06512: 在 line 1SQL>

Answer 1

SQL> set serveroutput on
SQL> set timing on
在执行之前有什么用?

Answer 2

用pl/sql developer单步调试一下。

Answer 3

先在sql/plus 下,代入一个table_name进行调试
  SELECT REPLACE(sys.dbms_metadata.get_ddl('TABLE',i.table_name),'"TEST".','"TEST1".')
  FROM DUAL;
     

Answer 4

SQL> set serveroutput on
使dbms_output.put_line可以輸出
SQL> set timing on
統計程序執行時間SQL> SET LONG 10000
SQL> /REPLACE(SYS.DBMS_METADATA.GET_
--------------------------------------------------------------------------------  CREATE TABLE "TEST1"."TT"
   ( "X" NUMBER, "Y" NUMBER   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
SQL> 
SQL> CREATE TABLE "TEST1"."TT"
  2     ( "X" NUMBER,
SQL>  "Y" NUMBER
SQL>    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  2    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  3    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  4    TABLESPACE "SYSTEM"
  5  /) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"ORA-00900: SQL 敘述句無效SQL>

Answer 5

SQL> set serveroutput on
使dbms_output.put_line可以輸出
SQL> set timing on
統計程序執行時間SQL> SET LONG 10000
SQL> /REPLACE(SYS.DBMS_METADATA.GET_
--------------------------------------------------------------------------------  CREATE TABLE "TEST1"."TT"
   ( "X" NUMBER, "Y" NUMBER   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
SQL> 
SQL> CREATE TABLE "TEST1"."TT"
  2     ( "X" NUMBER,
SQL>  "Y" NUMBER
SQL>    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  2    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  3    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  4    TABLESPACE "SYSTEM"
  5  /) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"ORA-00900: SQL 敘述句無效SQL>

Answer 6

這個問題估計是把long型值直接賦給VARCHAR2變量的問題。

Answer 7

检查sys.dbms_metadata.get_ddl('TABLE',i.table_name)

Answer 8

EXECUTE IMMEDIATE 'INSERT INTO test1.'||i.table_name||' SELECT * FROM '||i.table_name||';';           insert 语句不要带分号ORA-00911、ORA-06512错误一般是语句的语法错误或者是数据格式不对(比如null,字段过长)

Answer 9

我select sys.dbms_metadata.get_ddl('TABLE','TEST') FROM DUAL
得到的值都不能正確執行呢。