plsqldeveloper - 将新列添加到表时需要更改所有相关对象（简而言之）

Question

我在表中有一个数据模型更改：添加一个新列。但是有 4-5 个procedure/pkg/views表的所有列都存在。我们必须将该新列添加为proc/pkg/view. 我们怎样才能做到这一点？

应该是这样，我将输入作为新列提供，并且新列应该像 in all 一样添加到任何地方proc/pkg/view。

我尝试从表中获取行号并操纵Dba_sourcepkg 。DBMS_METADATA.GET_DDL()但是格式不正确。使用循环，新行也在程序结束时添加，这不是必需的。

代码： 开发至今：

DECLARE
   PROC_v       VARCHAR2 (4000);
   l_sql        VARCHAR2 (9000 CHAR);
   V_NEW_COL    VARCHAR2 (10) := 'INST_NAME';
   v_byte       NUMBER (5);
   v_byte_st    NUMBER (5) := 1;
   v_blnk_cnt   NUMBER;
   v_line_ed    NUMBER (5);
   v_line_st    NUMBER (5);

   TYPE src_line IS TABLE OF NUMBER (5);

   l_src_line   src_line;
BEGIN
   SELECT line
     BULK COLLECT INTO l_src_line
     FROM DBA_SOURCE
    WHERE     OWNER = 'TEST'
          AND TYPE = 'PROCEDURE'
          AND NAME = 'PROCESS_EMP'
          AND (TEXT LIKE '%CURRENCY%' OR TEXT = 'END;'); --Currency is last column in the table

   v_line_st := 1;

   FOR j IN l_src_line.FIRST .. l_src_line.LAST
   LOOP
      DBMS_OUTPUT.put_line ('Line start' || v_line_st);
      v_line_ed := l_src_line (j);
      DBMS_OUTPUT.put_line ('Line End' || v_line_ed);

      SELECT COUNT (1)
        INTO v_blnk_cnt
        FROM DBA_SOURCE
       WHERE     OWNER = 'TEST'
             AND TYPE = 'PROCEDURE'
             AND NAME = 'PROCESS_EMP'
             AND text = CHR (10)
             AND LINE BETWEEN v_line_st AND v_line_ed;

      SELECT SUM (LENGTH (TEXT)) + v_blnk_cnt * 10
        INTO v_byte
        FROM DBA_SOURCE
       WHERE     OWNER = 'TEST'
             AND TYPE = 'PROCEDURE'
             AND NAME = 'PROCESS_EMP'
             AND LINE BETWEEN v_line_st AND v_line_ed;

      v_line_st := l_src_line (j) + 1;
      --dbms_output.put_line(v_line_st);
      DBMS_OUTPUT.put_line (v_byte);
      DBMS_OUTPUT.put_line (v_byte_st);

      PROC_v: =
         DBMS_LOB.SUBSTR (
            (DBMS_METADATA.get_ddl ('PROCEDURE', 'PROCESS_EMP')),
            v_byte,
            v_byte_st); -- to extract Source code and convert HUGECLOB into VARCHAR
      PROC_v := PROC_v || '                           ' || V_NEW_COL;
      /*CASE WHEN l_src_line.LAST THEN
      PROC_v:= PROC_v || CHR(10) ||'/';
      ELSE
      PROC_v:= PROC_v||'                           '|| V_NEW_COL;
      END CASE;*/ -- This part did not work
      DBMS_OUTPUT.put_line (PROC_v);

      v_byte_st := v_byte + v_byte_st;
   --execute immediate l_sql;
   END LOOP;
END;
/

我将输入作为新列提供，并且应该在所有 proc/pkg/view 中添加新列。稍后我需要概括解决方案

标签： plsqldeveloper