sql - View 到物化 View 的脚本化转换 (Oracle)

Question

我们有某些环境更喜欢使用物化 View ，但常规应用程序使用常规 View 。为了让事情变得更简单，我们希望我们的应用程序能够根据配置参数自动将所有常规 View 迁移到物化 View 。

我已经写了我认为是让这个脚本正常工作所需的大部分内容，但我正在努力进行最后的润色。可能还有一些我需要解决的转义问题。

目前，脚本创建了一个名为“magic”的 View ，然后尝试转换它，但到目前为止，它无法转换为物化 View 阶段。我不确定我做错了什么。非常感谢任何帮助。

我看到的错误如下。

Error report:
ORA-00911: invalid character
ORA-06512: at line 49
ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:
Attempting to drop materialized view named MAGIC
No materialized view found with name MAGIC
Attempting to drop view named MAGIC
Success.
Attempting to create materialized view named MAGIC
  CREATE MATERIALIZED VIEW "MYDB"."MAGIC" ("MAGIC") AS 
  SELECT 'MAGIC' FROM DUAL;

Failed to create materialized view, recreating original view MAGIC
ERROR: Could not recreate view named MAGIC.
SQL was:
  CREATE OR REPLACE FORCE VIEW "MYDB"."MAGIC" ("MAGIC") AS 
  SELECT 'MAGIC' FROM DUAL;

PL/SQL 代码如下。

clear;
SET serveroutput ON size 1000000;
/**
* Converts all views in the database to materialized views.
*/

CREATE OR REPLACE VIEW "MAGIC" ("MAGIC") AS SELECT 'MAGIC' FROM DUAL;

BEGIN
  FOR cur_rec IN ( SELECT object_name, object_type FROM user_objects WHERE object_type='VIEW' and object_name='MAGIC' )
  LOOP
    BEGIN
      FOR cur_view IN
      (SELECT TRIM( REPLACE( REPLACE( DBMS_METADATA.GET_DDL('VIEW', cur_rec.object_name), 'CREATE OR REPLACE FORCE VIEW', 'CREATE MATERIALIZED VIEW' ), 'CREATE OR REPLACE VIEW', 'CREATE MATERIALIZED VIEW' ) ) "MATERIALIZED_VIEW",
        TRIM( DBMS_METADATA.GET_DDL('VIEW', cur_rec.object_name) ) "VIEW"
      FROM DUAL
      )
      LOOP
        BEGIN
          BEGIN
            DBMS_OUTPUT.PUT_LINE( 'Attempting to drop materialized view named ' || cur_rec.object_name );
            EXECUTE IMMEDIATE 'drop materialized view ' || cur_rec.object_name;
            DBMS_OUTPUT.PUT_LINE( 'Success.' );
          EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE( 'No materialized view found with name ' || cur_rec.object_name );
            IF SQLCODE != -12003 THEN
              RAISE;
            END IF;
          END;
          BEGIN
            DBMS_OUTPUT.PUT_LINE( 'Attempting to drop view named ' || cur_rec.object_name );
            EXECUTE IMMEDIATE 'drop view ' || cur_rec.object_name;
            DBMS_OUTPUT.PUT_LINE( 'Success.' );
          EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE( 'No view found with name ' || cur_rec.object_name );
            IF SQLCODE != -942 THEN
              RAISE;
            END IF;
          END;
          -- create the view as a materialized view.
          BEGIN
            DBMS_OUTPUT.PUT_LINE( 'Attempting to create materialized view named ' || cur_rec.object_name );
            DBMS_OUTPUT.PUT_LINE( cur_view."MATERIALIZED_VIEW" );
            EXECUTE IMMEDIATE cur_view."MATERIALIZED_VIEW";
            DBMS_OUTPUT.PUT_LINE( 'Success.' );
          EXCEPTION
          WHEN OTHERS THEN
            BEGIN
              DBMS_OUTPUT.PUT_LINE( 'Failed to create materialized view, recreating original view ' || cur_rec.object_name );
              EXECUTE IMMEDIATE cur_view."VIEW";
            EXCEPTION
            WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE( 'ERROR: Could not recreate view named ' || cur_rec.object_name || '.' );
              DBMS_OUTPUT.PUT_LINE( 'SQL was:' || cur_view."VIEW" );
              RAISE;
            END;
          END;
        END;
      END LOOP;
    END;
  END LOOP;
END;

Answer 1

问题出在它试图执行的 SQL 上:

CREATE MATERIALIZED VIEW "MYDB"."MAGIC" ("MAGIC") AS 
SELECT 'MAGIC' FROM DUAL;

动态SQL应该是单条语句，不能有语句终止符/分隔符；这是最后的 ; 它不喜欢。

您可以首先通过添加对 set_transform_param() procedure 的调用来停止将 dbms_metadata 包含在 DDL 中。在您的代码块中，在调用 get_ddl() 之前:

dbms_metadata.set_transform_param(dbms_metadata.session_transform,
  'SQLTERMINATOR', false);