Изменение исходников ( DDL ) на лету / forpes.ru

Главная
Изменение исходников ( DDL ) на лету

Изменение исходников ( DDL ) на лету +9

31.03.2016 21:45

SbWereWolf 14 4200 Источник

При сопровождении ERP систем иногда возникает необходимость массового изменения кода процедур, функций, триггеров или пакетов. Например для замены вызова одной процедуры на вызов другой.
Если надо поменять пару процедур, то можно сделать это в ручную, но когда надо изменить несколько сотен объектов, то приходиться задуматься об автоматизации процесса. В статье описан пример автоматизации для СУБД ORACLE 11g.

Теория

DDL скрипты всех объектов ( ORACLE ) храниться в таблице SYS.SOURCE$, казалось бы достаточно сделать:

update source$ set source = replace(source,'old_name','new_name') where source like '%old_name%'

, но на самом деле поменять исходники не достаточно. Исходники надо скомпилировать.
Компиляция выполняется с помощью EXECUTE IMMEDIATE.
До версии 11, для запросов текст которых нельзя было записать в VARCHAR2(32767), надо было использовать функционал пакета DBMS_SQL:

-- To process a SQL statement, you must have an open cursor
nCursorId := DBMS_SQL.OPEN_CURSOR ;

-- Every SQL statement must be parsed
DBMS_SQL.PARSE (nCursorId , SqlStatement_CLOB, DBMS_SQL.NATIVE);
-- DDL statements are run on the parse, which performs the implied commit.

Проблема в том как получить исходник объекта.
Можно курсором пройтись по записям таблицы SYS.SOURCE$ — склеить поля SOURCE каждой записи через оператор "||" и символ конца строки. Но есть способ проще, через пакет DBMS_METADATA, у которого есть функция GET_DDL.
Удобство функции DBMS_METADATA.GET_DDL не только в том что она выдаёт весь исходный текст объекта, но ещё и в том что подставляет имя схемы и добавляет "CREATE OR REPLACE".
Минус в том что функция принимает строковые аргументы в том время как в таблице SYS.OBJ$ хранятся числа.

Алгоритм изменения исходников процедур.

Получить исходники с помощью DBMS_METADATA.GET_DDL;
Изменить текст нужным образом ( в простейшем случае через REPLACE );
Скомпилировать процедуру с помощью EXECUTE IMMEDIATE;
Пользоваться и получать удовольствие;

Практика

На практике всё не так просто.
Когда я залогинился пользователем SYS, у меня не получалось скомпилировать процедуры другой схемы (PROD), потому что у таблиц не были подставлены имена схем, то есть было:

SELECT * FROM TABLE_NAME

, компилятор почему то ожидал

SELECT * FROM PROD.TABLE_NAME

, хотя в начале DDL скрипта было написано

CREATE OR REPLACE PROCEDURE PROD.PROCEDURE_NAME

и когда в "PL/SQL Developer" или "TOAD" компилируешь объекты другой схемы ( не той под которой залогинился ) всё компилируется без ошибок.
Видимо есть нюанс о котором я не догадываюсь, или руки у меня недостаточно прямые.
Когда я залогинился пользователем PROD, у меня была ошибка доступа к таблице SYS.SOURCE$, это вылечилось привилегией

GRANT SELECT ANY DICTIONARY TO PROD;

Для отладки нужна привилегия

GRANT DEBUG ANY PROCEDURE TO PROD;

Автоматизация, скрипты, и процедуры

Анализ исходных данных

У меня стояла задача заменить вызов функции "GET_ACTUAL_DATE" на вызов "SYSDATE". Можно было конечно код функции GET_ACTUAL_DATE заменить на «RETURN SYSDATE», но тогда мне не о чём было бы писать в этой статье :), поэтому приступим.
Первым делом надо посмотреть где встречается подстрока «GET_ACTUAL_DATE»:

  SELECT
      SC.SOURCE
  FROM
    SYS.USER$ UR
    JOIN SYS.OBJ$ OB ON
    UR.USER# = OB.OWNER#
    JOIN SYS.SOURCE$ SC ON     
    SC.OBJ# = OB.OBJ#
  WHERE
    UR.USER# = 50 /* schema id from table USER$ for 'PROD'*/
    AND UPPER(SC.SOURCE) LIKE '%' || 'GET_ACTUAL_DATE' || '%'
  ORDER BY
      SC.OBJ#
    , SC.LINE
  ;

Получилось 1185 строк в 590 объектах.
Я просмотрел выборку и сделал вывод, что для того чтобы заменить именно вызов функции, а не часть имени переменной или процедуры, надо искать

'(' || 'GET_ACTUAL_DATE'

, также перед вызовом функции были другие символы:

'=' ;
' '(пробел);
','(запятая);
'''' (кавычки);

Исходя из этого я написал запрос для генерации шаблона поиска:

WITH 
  PATTERNS AS
  (
    SELECT
        'GET_ACTUAL_DATE' AS ERST /* изначально было */
      , '(' AS OPENING /* открывающий */
      , '' AS CLOSING /* закрывающий */
      , 'SYSDATE' AS BECOME /* в итоге станет */
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ' '
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , '='
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ','
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ''''
      , ''
      , 'SYSDATE'
    FROM
      DUAL
  )
SELECT
    PT.OPENING || PT.ERST || PT.CLOSING /* было */
  , PT.OPENING || PT.BECOME || PT.CLOSING /* стало */
FROM
  PATTERNS PT
;

Теперь можно было посмотреть что же получиться если выполнить подстановки ( REPLACE ):

посмотреть запрос

WITH
  PATTERNS AS
  (
    SELECT
        'GET_ACTUAL_DATE' AS ERST
      , '(' AS OPENING
      , '' AS CLOSING
      , 'SYSDATE' AS BECOME     
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ' '
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , '='
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ','
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ''''
      , ''
      , 'SYSDATE'
    FROM
      DUAL
  )
  SELECT
      SC.OBJ# AS OBJ#
    , SC.LINE AS LINE
    , SC.SOURCE AS SOURCE       
    , REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          ) AS COMPLETE
  FROM
    SYS.USER$ UR
    JOIN SYS.OBJ$ OB ON
    UR.USER# = OB.OWNER#
    JOIN SYS.SOURCE$ SC ON     
    SC.OBJ# = OB.OBJ#
    , PATTERNS PT
  WHERE
    UR.USER# = 50 /* USER PROD */
    AND
    UPPER(SC.SOURCE) LIKE '%' || PT.ERST || '%'
    AND REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          )
        <> UPPER(SC.SOURCE)
  ORDER BY
    OBJ#
  , LINE
;

Просмотрел результат, одна строка была объявлением функции «GET_ACTUAL_DATE », её переделывать в объявление «SYSDATE» не требовалось.
Две другие строки были коментами, их тоже надо было оставить в покое. Дописал скрипт что бы можно было исключить заданные строки и объекты:

посмотреть запрос

WITH
  EXCLUDE_LINE AS /* исключить строки */
  (
    SELECT 105857 AS OBJ# , 321 AS LINE FROM DUAL UNION
    SELECT 82036 , 50 FROM DUAL
  )
  , EXCLUDE_OBJ AS /* исключить объекты */
  (
    SELECT 121939 AS OBJ# FROM DUAL
  )
  , PATTERNS AS
  (
    SELECT
        'GET_ACTUAL_DATE' AS ERST
      , '(' AS OPENING
      , '' AS CLOSING
      , 'SYSDATE' AS BECOME     
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ' '
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , '='
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ','
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ''''
      , ''
      , 'SYSDATE'
    FROM
      DUAL
  )
  SELECT
      SC.OBJ# AS OBJ#
    , SC.LINE AS LINE
    , SC.SOURCE AS SOURCE       
    , REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          ) AS COMPLETE
  FROM
    SYS.USER$ UR
    JOIN SYS.OBJ$ OB ON
    UR.USER# = OB.OWNER#
    JOIN SYS.SOURCE$ SC ON     
    SC.OBJ# = OB.OBJ#
    , PATTERNS PT
  WHERE
    UR.USER# = 50
    AND
    UPPER(SC.SOURCE) LIKE '%' || PT.ERST || '%'
    AND (SC.OBJ# , SC.LINE ) NOT IN (SELECT EL.OBJ# , EL.LINE FROM EXCLUDE_LINE EL )
    AND SC.OBJ# NOT IN (SELECT EO.OBJ# FROM EXCLUDE_OBJ EO )   
    AND REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          )
        <> UPPER(SC.SOURCE)
  ORDER BY
    OBJ#
  , LINE
;

Выполнил запрос, посмотрел выборку — ок.

Сохранение подстановок

Теперь результат «вычисления» подстановок надо было куда то сохранить. Добавим таблицу:

CREATE TABLE SWAP_SOURCE_CODE (
  BATCH  NUMBER, /* номер прогона / номер генерации данных для подстановки */
  OBJ#   NUMBER, /* объект */
  LINE   NUMBER, /* строка */
  SOURCE VARCHAR2(4000 BYTE), /* текст исходника SYS.SOURCE$.SOURCE%TYPE */
  OUTPUT VARCHAR2(4000 BYTE), /* текст подмены */
  CONSTRAINT PK_SWAP_SOURCE_CODE PRIMARY KEY (BATCH, OBJ#, LINE) USING INDEX TABLESPACE PROD_INDEX STORAGE (INITIAL 80 K NEXT 1 M  MAXEXTENTS UNLIMITED) 
)

Заполнение таблицы SWAP_SOURCE_CODE, данными:

INSERT INTO SWAP_SOURCE_CODE
WITH
  EXCLUDE_LINE AS /* исключить строки */
  (
    SELECT 105857 AS OBJ# , 321 AS LINE FROM DUAL UNION
    SELECT 82036 , 50 FROM DUAL
  )
  , EXCLUDE_OBJ AS /* исключить объекты */
  (
    SELECT 121939 AS OBJ# FROM DUAL
  )
  , BATCH_NUMBER AS /* генерация номера прогона */
  (
    SELECT (COALESCE (MAX(BATCH),0) +1) AS BATCH# FROM PROD.SWAP_SOURCE_CODE
  )
  , PATTERNS AS /* генерация подстановок */
  (
    SELECT
        'GET_ACTUAL_DATE' AS ERST
      , '(' AS OPENING
      , '' AS CLOSING
      , 'SYSDATE' AS BECOME     
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ' '
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , '='
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ','
      , ''
      , 'SYSDATE'
    FROM
      DUAL
    UNION ALL
    SELECT
        'GET_ACTUAL_DATE'
      , ''''
      , ''
      , 'SYSDATE'
    FROM
      DUAL
  )
  SELECT
      BATCH_NUMBER.BATCH#
    , SC.OBJ# AS OBJ#
    , SC.LINE AS LINE
    , SC.SOURCE AS SOURCE       
    , REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          ) AS COMPLETE
  FROM
    SYS.USER$ UR
    JOIN SYS.OBJ$ OB ON
    UR.USER# = OB.OWNER#
    JOIN SYS.SOURCE$ SC ON     
    SC.OBJ# = OB.OBJ#
    , PATTERNS PT
    , BATCH_NUMBER
  WHERE
    UR.USER# = 50 /* работа с объектами только схемы PROD */
    AND
    UPPER(SC.SOURCE) LIKE '%' || PT.ERST || '%'
    AND (SC.OBJ# , SC.LINE ) NOT IN (SELECT EL.OBJ# , EL.LINE FROM EXCLUDE_LINE EL )
    AND SC.OBJ# NOT IN (SELECT EO.OBJ# FROM EXCLUDE_OBJ EO )   
    AND REPLACE
          (
              UPPER(SC.SOURCE)
            , PT.OPENING || PT.ERST || PT.CLOSING
            , PT.OPENING || PT.BECOME || PT.CLOSING
          )
        <> UPPER(SC.SOURCE)
  ORDER BY
    OBJ#
  , LINE
;

Проверяем результат подстановок:

SELECT * FROM SWAP_SOURCE_CODE;

Я не стал делать генерацию подстановок процедурой потому, что условия подстановок каждый раз разные и не факт, что в очередной раз с помощью PL/SQL будет удобно описать алгоритм преобразования исходного DDL скрипта в целевой, обычно это делается внешней программой ( написанной на C#,Ruby,Perl).

Выполнение подстановок

Теперь мы имеем подстановки в таблице SWAP_SOURCE_CODE, и можно выполнить подстановки, при этом надо сохранить исходник до выполнения подстановок и, конечно, надо сохранить исходник после подстановок. Для этого добавим таблицу SOURCE_CODE_BACKUP:

CREATE TABLE SOURCE_CODE_BACKUP (
  BATCH       NUMBER, /* номер прогона */
  OBJ         NUMBER, /* объект */
  CODE_BACKUP CLOB, /* текст объекта до внесения изменений*/
  CODE_UPDATE CLOB, /* текст после изменений */
  CONSTRAINT PK_SOURCE_CODE_BACKUP PRIMARY KEY (BATCH, OBJ) USING INDEX TABLESPACE PROD_INDEX STORAGE (INITIAL 80 K NEXT 1 M MAXEXTENTS NLIMITED)
)

Подстановки выполняем процедурой P_REPLACE_SOURCE_WITH_OUTPUT:

CREATE OR REPLACE PROCEDURE P_REPLACE_SOURCE_WITH_OUTPUT
(
  N_BATCH_IN IN NUMBER /* номер прогона с источником подстановок */
)
AS 
/* курсор для получения списка объектов*/
  CURSOR GetObjFromSwap_Source_Code  
  (
    nBatchIn IN NUMBER
  )
  IS
  SELECT
    SW.OBJ# AS OBJ
  FROM
    SWAP_SOURCE_CODE SW
  WHERE
    SW.BATCH = nBatchIn
  GROUP BY
    SW.OBJ#
  ORDER BY
    SW.OBJ#
  ;

  TYPE  T_OBJ_TABLE IS TABLE OF GetObjFromSwap_Source_Code%ROWTYPE;
  OBJ_TABLE T_OBJ_TABLE := T_OBJ_TABLE();

  nObjCount NUMBER ;
  nObjFirstIndex NUMBER ;
  nObjLastIndex NUMBER ;

  nObj NUMBER;

  ObjBackup_CLOB CLOB ; /* оригинальный исходник */
  ObjUpdate_CLOB CLOB ; /* исходник после выполнения подстановок*/

  nBATCH NUMBER; /* номер прогона по генерации исходников */

  CURSOR GetNextBatchNumber
  IS
    SELECT
      COALESCE( MAX(SB.BATCH),0 ) + 1
    FROM
       SOURCE_CODE_BACKUP SB
  ;

  nIsEqual NUMBER ; /* результат сравнения исходника после подстановок с оригинальным исходником */

/* процедура для выполнения подстановки */
PROCEDURE PARSE_SOURCE_CODE_WITH_OUTPUT
(
      nObjIn IN NUMBER /* объект для обработки */
    , nBatchIn IN NUMBER /* номер прогона с подстановкой */
    , ObjBackupOut_CLOB OUT CLOB /* оригинальный исходник */
    , ObjUpdateOut_CLOB OUT CLOB /* обработанный исходник */
)
AS
/* Курсор для получения Имени Типа и Схемы объекта */
  CURSOR GetObjNameTypeSchema
  (
    nObjIn IN NUMBER 
  )
  IS
/* полный список соответствия номера типа к его наименованию в представлении SYS.DBA_OBJECTS , у функции DBMS_METADATA.GET_DDL свои обозначения типов, они отличаются от тех что в DBA_OBJECTS  тем что пробелы заменены на символ "_" , полный список типов по ссылке http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBBIEGA */
  WITH OBJ_TYPE AS
  (
    SELECT 0 AS TYPE#, 'NEXT_OBJECT' AS NAME FROM DUAL UNION ALL 
    SELECT  1, 'INDEX' FROM DUAL UNION ALL
    SELECT 2, 'TABLE' FROM DUAL UNION ALL
    SELECT  3, 'CLUSTER' FROM DUAL UNION ALL
    SELECT  4, 'VIEW' FROM DUAL UNION ALL
    SELECT  5, 'SYNONYM' FROM DUAL UNION ALL
    SELECT  6, 'SEQUENCE' FROM DUAL UNION ALL
    SELECT  7, 'PROCEDURE' FROM DUAL UNION ALL
    SELECT  8, 'FUNCTION' FROM DUAL UNION ALL
/* если запросить объект с типом 'PACKAGE' то будет возвращён и заголовок пакета и тело пакета, что бы получить только заголовок надо запрашивать 'PACKAGE_SPEC' */
    SELECT  9, 'PACKAGE_SPEC' FROM DUAL UNION ALL -- 'PACKAGE'
    SELECT  11, 'PACKAGE_BODY' FROM DUAL UNION ALL
    SELECT  12, 'TRIGGER' FROM DUAL UNION ALL
/* если запросить объект с типом 'TYPE' то будет возвращён и заголовок и тело , что бы получить только заголовок надо запрашивать 'TYPE_SPEC' */
    SELECT  13, 'TYPE_SPEC' FROM DUAL UNION ALL --TYPE
    SELECT  14, 'TYPE_BODY' FROM DUAL UNION ALL
    SELECT  19, 'TABLE_PARTITION' FROM DUAL UNION ALL
    SELECT  20, 'INDEX_PARTITION' FROM DUAL UNION ALL
    SELECT  21,  'LOB' FROM DUAL UNION ALL
    SELECT  22, 'LIBRARY' FROM DUAL UNION ALL
    SELECT  23, 'DIRECTORY' FROM DUAL UNION ALL
    SELECT  24, 'QUEUE' FROM DUAL UNION ALL
    SELECT  28, 'JAVA_SOURCE' FROM DUAL UNION ALL
    SELECT  29, 'JAVA_CLASS' FROM DUAL UNION ALL
    SELECT  30, 'JAVA_RESOURCE' FROM DUAL UNION ALL
    SELECT  32, 'INDEXTYPE' FROM DUAL UNION ALL
    SELECT  33, 'OPERATOR' FROM DUAL UNION ALL
    SELECT  34, 'TABLE_SUBPARTITION' FROM DUAL UNION ALL
    SELECT  35, 'INDEX_SUBPARTITION' FROM DUAL UNION ALL
    SELECT  39, 'LOB_PARTITION' FROM DUAL UNION ALL
    SELECT  40, 'LOB_SUBPARTITION' FROM DUAL UNION ALL
    SELECT  43, 'DIMENSION' FROM DUAL UNION ALL
    SELECT  44, 'CONTEXT' FROM DUAL UNION ALL
    SELECT  47, 'RESOURCE_PLAN' FROM DUAL UNION ALL
    SELECT  48, 'CONSUMER_GROUP' FROM DUAL UNION ALL
    SELECT  51, 'SUBSCRIPTION' FROM DUAL UNION ALL
    SELECT  52, 'LOCATION' FROM DUAL UNION ALL
    SELECT  56, 'JAVA_DATA' FROM DUAL
  )
  SELECT       
        OB.NAME
      , TP.NAME
      , UR.NAME
  FROM
      SWAP_SOURCE_CODE SW
      JOIN SYS.OBJ$ OB ON
      SW.OBJ# = OB.OBJ#
      JOIN SYS.USER$ UR  ON
      UR.USER# = OB.OWNER#       
      LEFT JOIN OBJ_TYPE TP ON
      OB.TYPE# = TP.TYPE#
  WHERE
    SW.OBJ# = nObjIn
  ;

  ObjRaw_CLOB CLOB; /* не обработанный объект */
  ObjParsed_CLOB CLOB; /* DDL скрипт создания объекта после обработки */

  sObjName VARCHAR2(30); /* имя объекта */
  sTypeName VARCHAR2(30); /* тип объекта */
  sSchemaName VARCHAR2(30); /* схема объекта */

/* Курсор для выборки строки и подстановки */
  CURSOR GetSourceOutputFromSwap_Source
  (
      nObjectNumberIn IN NUMBER
    , nBatchNumberIn IN NUMBER
  )
  IS
  SELECT
      SW.SOURCE AS SOURCE
    , SW.OUTPUT AS OUTPUT
  FROM
    SWAP_SOURCE_CODE SW
  WHERE
        SW.BATCH = nBatchNumberIn
    AND SW.OBJ# = nObjectNumberIn
  ORDER BY
    SW.LINE
  ;

  TYPE  T_SOURCE_AND_OUTPUT_TABLE IS TABLE OF GetSourceOutputFromSwap_Source%ROWTYPE;
  SourceAndOutput_TABLE T_SOURCE_AND_OUTPUT_TABLE := T_SOURCE_AND_OUTPUT_TABLE();

  nSourceCount NUMBER ;
  nSourceFirstIndex NUMBER ;
  nSourceLastIndex NUMBER ;

  sPlaceholder SYS.SOURCE$.SOURCE%TYPE; /* подстрока для замены  */
  sSubstitute SYS.SOURCE$.SOURCE%TYPE; /* строка для подстановки  */

BEGIN

/* для выполнения GET_DDL получаем имя объекта, его тип и его схему */
    OPEN GetObjNameTypeSchema(nObjIn);
    FETCH GetObjNameTypeSchema INTO sObjName, sTypeName, sSchemaName;
    CLOSE GetObjNameTypeSchema;

/* получаем исходник объекта */
    ObjRaw_CLOB := DBMS_METADATA.GET_DDL
      (
          OBJECT_TYPE => sTypeName
        , NAME => sObjName
        , SCHEMA => sSchemaName
      );

/* сохраняем оригинальный исходник */
    ObjBackupOut_CLOB := ObjRaw_CLOB ;
/* инициализируем переменную для обработки исходника */
    ObjParsed_CLOB := ObjRaw_CLOB;

    OPEN GetSourceOutputFromSwap_Source
      (
          nObjectNumberIn => nObjIn
        , nBatchNumberIn => nBatchIn
      );
    FETCH GetSourceOutputFromSwap_Source BULK COLLECT INTO SourceAndOutput_TABLE;
    CLOSE GetSourceOutputFromSwap_Source ;

    nSourceCount := SourceAndOutput_TABLE.COUNT;

    IF ( nSourceCount > 0 )
    THEN
      nSourceFirstIndex := SourceAndOutput_TABLE.FIRST;
      nSourceLastIndex := SourceAndOutput_TABLE.LAST;

      FOR indx IN nSourceFirstIndex .. nSourceLastIndex
      LOOP

        sPlaceholder := SourceAndOutput_TABLE(indx).SOURCE ;
        sSubstitute := SourceAndOutput_TABLE(indx).OUTPUT ;

/* выполняем подстановку */
        ObjParsed_CLOB :=
          REPLACE
          (
              ObjParsed_CLOB
            , sPlaceholder
            , sSubstitute
          );

      END LOOP;

/* записываем исходник после подстановок */
      ObjUpdateOut_CLOB := ObjParsed_CLOB ;

    END IF ; 

END PARSE_SOURCE_CODE_WITH_OUTPUT ;

BEGIN

/* получаем номер прогона */
OPEN GetNextBatchNumber;
FETCH GetNextBatchNumber INTO nBATCH;
CLOSE GetNextBatchNumber;

/* получаем список объектов для выполнения подстановок */
OPEN GetObjFromSwap_Source_Code(N_BATCH_IN);
FETCH GetObjFromSwap_Source_Code BULK COLLECT INTO OBJ_TABLE;
CLOSE GetObjFromSwap_Source_Code;

nObjCount := OBJ_TABLE.COUNT;

IF ( nObjCount > 0 )
THEN
  nObjFirstIndex := OBJ_TABLE.FIRST;
  nObjLastIndex := OBJ_TABLE.LAST;

  FOR indx IN nObjFirstIndex .. nObjLastIndex
  LOOP

/* очередной объект для обработки */
    nObj := OBJ_TABLE(indx).OBJ;

/* выполняем подстановку */
    PARSE_SOURCE_CODE_WITH_OUTPUT
    (
        nObjIn => nObj
      , nBatchIn => N_BATCH_IN
      , ObjBackupOut_CLOB =>  ObjBackup_CLOB
      , ObjUpdateOut_CLOB => ObjUpdate_CLOB
    );

/* сравниваем исходник до и после подстановок */
    nIsEqual := DBMS_LOB.COMPARE(ObjBackup_CLOB,ObjUpdate_CLOB);

    IF(
        nIsEqual IS NOT NULL /* NULL возвращается при кривых CLOB_ах для сравнения */
        AND nIsEqual <> 0 /* если CLOB _ы равны то 0, если не равны то не 0 */
      )
    THEN
/* если CLOB _ы не равны то сохраняем их в SOURCE_CODE_BACKUP  */
      INSERT INTO SOURCE_CODE_BACKUP
      (BATCH,OBJ,CODE_BACKUP,CODE_UPDATE)
      VALUES(nBATCH,nObj,ObjBackup_CLOB,ObjUpdate_CLOB)
      ;     

    END IF ;

  END LOOP;
END IF ;

END P_REPLACE_SOURCE_WITH_OUTPUT ;

Выполняем процедуру:

BEGIN
  P_REPLACE_SOURCE_WITH_OUTPUT( N_BATCH_IN => 1 );
END;

После этого, в теории, можно проверить, что там нагенерилось.

SELECT * FROM SOURCE_CODE_BACKUP;

Компиляция объектов

И вот мы на финишной прямой — осталось откомпилировать все объекты — выполнить DDL скрипты.
Выполняем тоже процедурой — P_EXECUTE_CODE_UPDATE:

CREATE OR REPLACE PROCEDURE P_EXECUTE_CODE_UPDATE
(
  N_BATCH_IN NUMBER /* номер прогона который надо откомпилировать */
)
AS
/* Курсор для чтения таблицы с исходниками , объект подтягивается для отображения в логе */
CURSOR GetUpdateFromSourceCodeBackup
(
  nBatchNumberIn IN NUMBER
)
IS
SELECT
    SB.CODE_UPDATE AS CodeUpdate
  , SB.OBJ AS Obj
FROM
  SOURCE_CODE_BACKUP SB
  JOIN SYS.OBJ$ OB ON
  SB.OBJ = OB.OBJ#
WHERE
  SB.BATCH = nBatchNumberIn
    AND OB.TYPE# <> 12 -- 12, 'TRIGGER' /* с триггерами беда DBMS_METADATA.GET_DDL для триггеров возвращает строчку с альтером для включения триггера, и на этой строке DDL скрипт падает с ошибкой , пришлось триггеры комплировать в ручную, наверное можно настроить DBMS_METADATA так что бы он эту строчку с альтером не добавлял */
ORDER BY
    SB.BATCH
  , SB.OBJ
;

TYPE T_CodeUpdate IS TABLE OF GetUpdateFromSourceCodeBackup%ROWTYPE;
CodeUpdate_TABLE T_CodeUpdate := T_CodeUpdate();

nCodeUpdateCount NUMBER;
nCodeUpdateFirst NUMBER;
nCodeUpdateLast NUMBER;

/* переменная с текстом для компиляции */
SqlText_CLOB CLOB;
nObj NUMBER;

/* Курсор для чтения имени объекта */
CURSOR GetObjName( ObjIn IN NUMBER )
IS
SELECT
  OB.NAME AS ObjectName
FROM
  SYS.OBJ$ OB
WHERE
  OB.OBJ# = ObjIn
;

/* имя объекта в ORACLE не может быть длинней 30-ти символов, но мне памяти не жалко - пусть будет 32767  */
sObjectName VARCHAR2(32767);

BEGIN
/* для логирования "на экране" включаем вывод, буфер делаем безлимитным */
DBMS_OUTPUT.ENABLE(NULL);

/* читаем все DDL скрипты для выполнения */
OPEN GetUpdateFromSourceCodeBackup(N_BATCH_IN);
FETCH GetUpdateFromSourceCodeBackup BULK COLLECT INTO CodeUpdate_TABLE;
CLOSE GetUpdateFromSourceCodeBackup;

nCodeUpdateCount := CodeUpdate_TABLE.COUNT;

IF ( nCodeUpdateCount > 0 )
THEN

  nCodeUpdateFirst := CodeUpdate_TABLE.FIRST;
  nCodeUpdateLast := CodeUpdate_TABLE.LAST; 

  FOR indx IN nCodeUpdateFirst .. nCodeUpdateLast
  LOOP
/* записали текст DDL скрипта */
    SqlText_CLOB := CodeUpdate_TABLE(indx).CodeUpdate;
/* записали номер объекта */
    nObj := CodeUpdate_TABLE(indx).Obj;

/* прочитали наименование объекта */
    OPEN GetObjName(nObj);
    FETCH GetObjName INTO sObjectName ;
    CLOSE GetObjName;
/* делаем запись в журнале о том что приступаем к обработке объекта */
    DBMS_OUTPUT.PUT_LINE( '#' || LPAD (indx, 3,'0' )|| ' Process .. ' || sObjectName || ' ' || nObj );
/* выполняем DDL скрипт */
    EXECUTE IMMEDIATE SqlText_CLOB;
/* делаем запись об успешном выполнении скрипта , поскольку нет отлова эксепшенов то процедура упадёт на первой ошибке выполнения скрипта */
    DBMS_OUTPUT.PUT_LINE( '#' || LPAD (indx, 3,'0' )|| ' Complete ' || sObjectName || ' ' || nObj );

  END LOOP;

END IF;

END;

Выполняем:

BEGIN
  PARUS.P_EXECUTE_CODE_UPDATE( N_BATCH_IN => 1 );
END;

Откомпилировали все объекты, кроме триггеров, выбираем триггеры из нашего прогона:

SELECT
  *
FROM
  SWAP_SOURCE_CODE SW
  JOIN SYS.OBJ$ OB ON
  OB.OBJ# = SW.OBJ#
WHERE
  OB.TYPE# = 12
  AND SW.BATCH = 1
;

Выполняем скрипты триггеров по частям, сначала ту часть где триггер создаётся, затем ту часть где триггер включается.

Заключение

Исходники изменены нужным образом и скомпилированы. Оригинальные исходники сохранены, из них можно восстановить оригинальные объекты, без обращения к админу СУБД с заявкой на развёртывание резервной копии.
Почему мне так просто удалось выполнить подмену одной функции на другую? Потому что эта ERP из коробки с 20-ти летней историей, за кодом следят, код оформлен в едином стиле, если бы это была ERP на коленке, то не факт что у меня получилось бы так легко выполнить подмену имени функции.
Товарищи, не будьте себе врагами, любите себя — следите за своим кодом, пусть он будет написан в одном стиле по одним стандартам!
Аминь.

Ссылки

Комментарии (14)

skssxf
01.04.2016 19:47
#8835586
Почему не regexp_replace и dbms_utility.compile_schema?
1. SbWereWolf
  01.04.2016 20:08
  #8835632
  Не REGEXP_REPLACE — потому что я регулярными выражениями пользоваться не умею.
  Не dbms_utility.COMPILE_SCHEMA — потому что это имело бы смысл при прямом изменении исходников в таблице SYS.SOURCE$, но мне не по себе от такого вмешательства в лоб.
  Но конечно после того как все исходники будут обработаны — будет какое то количество INVALID объектов, поэтому как завершающий этап dbms_utility.COMPILE_SCHEMA — будет верным шагом, можно добавить в конец процедуры P_EXECUTE_CODE_UPDATE.

SbWereWolf
01.04.2016 20:08
#8835628
.

BitLord
02.04.2016 10:17
#8836056
Можно прояснить пару моментов: 1) при таком подходе, если во время компиляции (шаг 3) к объекту идут обращения, он нормально скомпилится. 2) Как объект будет вести себя при обращениях к нему после замены исходников, но до компиляции (т.е. между 2 и 3 шагами)?
1. SbWereWolf
  02.04.2016 10:31
  #8836070
  Компиляция только на третьем шаге происходит, непосредственной подмены исходников — изменения записей таблицы SYS.SOURCE$ в алгоритме нет.
  Собственно компиляция выполняется через "API" сервера, что бы сервер сам как ему надо все вопросы разрулил, и вообще был в курсе что у него исходники поменялись.
  Пока не произошло компиляции ( CREATE OR REPLACE ) объекты себя ведут как всегда :)
  После компиляции в некоторых сессиях могут вызываться старые версии объектов, поэтому если у пользователя что то "глючит", то ему просто надо перелогинится.
1. obvn
  02.04.2016 15:08
  #8836440
  Oracle не даст скомпилировать код, пока есть хотя бы один сеанс, его выполняющий.
  Сессия компиляции в таком случае будет ждать освобождения DDL блокировки, что будет видно в словаре данных: dba_ddl_locks.
  1. SbWereWolf
    02.04.2016 15:15
    #8836448
    Из моего опыта, когда у тебя сваливается вся база, тысяч 15 процедур/пакетов/функций и ты пытаешься всё инвалидное откомпилить, да иногда приходиться убивать пользовательские сессии, а иногда без этого обходиться, да иногда пользователям надо перелогинится, а иногда без этого обходиться.
    Мой опыт это год работы на Oracle 10g, за это время мы базу раз 10 роняли, раз на раз не приходиться, почему так — я не вникал.
    
    obvn
    02.04.2016 18:09
    #8836594
    Не спорю, в моей практике тоже бывает приходится убивать сессию или целую пачку, чтобы скомпилить пакет.
    Просто немного не понял фразу про старые версии объектов, которые могут выполняться некоторыми сеансами. Насколько мне известно, в Oracle обеспечивается консистентность выполняемого PL/SQL кода. То есть весь выполняемый код пакета одной и той же версии и Oracle не даст обновить пакет, пока хотя бы один сеанс выполняет код из этого пакета. Я ошибаюсь?
    
    SbWereWolf
    02.04.2016 19:26
    #8836664
    Без понятия :)) считай эту сентенцию "старые версии объектов" авторской выдумкой, ради красного словца.
    Может быть это не Oracle что то кэширует в сессии, а клиент ERP системы, и пока ERP клиент работает с устаревшими данными у пользователя происходят ошибки.

zhekappp
02.04.2016 14:11
#8836348
По прочтении сразу вопросы:
1. почему нельзя было использовать dba_source (all_source,user_source) вместо недокументированного sys.source$? И dba_objects вместо sys.obj$ ?
2. на счет компиляции из-под другого пользователя — есть такая штука alter session set current_schema=
3. компиляцию также можно выполнить alter <object_type> <object_name> compile. При этом, возможно, исходник возьмется из source ?
1. zhekappp
  02.04.2016 14:21
  #8836366
  вообще, после прочтения таких статей начинаешь понимать, зачем разработчики warpят исходники и считают контрольные суммы :)
1. SbWereWolf
  02.04.2016 15:34
  #8836478
  В представлении DBA_SOURCE есть типы объектов но у DBMS_METADATA.GET_DDL свои правила написания имён типов, и надо будет делать подмену 'PACKAGE' на 'PACKAGE_SPEC'
  "alter session set current_schema=" — прикольно, это наверное и есть тот нюанс о котором знает "TOAD", но которого не знаю я.
  "При этом, возможно, исходник возьмется из source ?" — без понятия, для себя я эту задачу решил и дополнительно тратить на неё время пока не хочется.

agathis
04.04.2016 12:45
#8838330
Охохо. Чего только не учиняют люди, когда оригиналы сохранены "где-то на всякий случай" вместо системы контроля версий.
Как это должно делаться на самом деле: однострочный replace по исходному коду, коммит в репозиторий, деплой на тестовую среду (она у вас есть, кстати?), деплой на продуктовую среду.
И да, я когда-то делал и разработку "по живому" и много чего еще :(
Лучше good practices смолоду прививать.
Кстати, Oracle-based EPR из коробки с 20-летней историей — это Парус или Галактика?
1. SbWereWolf
  04.04.2016 22:11
  #8839178
  Решение любой задачи подразумевает выделение ресурсов — рабочего времени, были выделены сутки, в сутки я уложился.
  В стратегическом плане конечно код будет в SVN / Mercurial храниться, опять же не раньше чем мне кто то выделит недельку на изучение этой темы применительно к СУБД ( PL/SQL Developer типа умеет работать с SVN, dbForge вообще с чем угодно, но что и как пока у нас ни кто не разбирался ).
  Конечно у нас есть тестовый сервер, у нас даже есть сервер разработки, схема работы конечно такая: DEV — TEST — PROD.
  Про "good practices" ни чего не слышал, мы в своей работе руководствуемся "best practices" :)
  Oracle-based EPR из коробки с 20-летней историей — это Парус.
  "Экземпляров" Паруса у нас кстати два, один клиент это завод с дискретным производством, другой клиент — продаёт услуги, у каждого своя специфика и за 10+ лет чуть более чем полностью переписанный функционал, так что от Паруса — там только название осталось.