SavePearlHarbor

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

—

от автора

admin

При сопровождении 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$ хранятся числа.

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

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

Практика

На практике всё не так просто.
Когда я залогинился пользователем 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 на коленке, то не факт что у меня получилось бы так легко выполнить подмену имени функции.
Товарищи, не будьте себе врагами, любите себя — следите за своим кодом, пусть он будет написан в одном стиле по одним стандартам!
Аминь.

Ссылки

  1. привилегии для чтения таблицы SYS.SOURCE$
  2. типы объектов для функции DBMS_METADATA.GET_DDL
  3. использование DBMS_SQL для выполнения динамического SQL

ссылка на оригинал статьи https://habrahabr.ru/post/280648/

Комментарии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *