Change source code (DDL) on the fly
With the maintenance of ERP systems, it is sometimes necessary to massively change the code of procedures, functions, triggers or packages. For example, to replace a call from one procedure to a call to another.
If you need to change a couple of procedures, you can do it manually, but when you need to change several hundred objects, you have to think about automating the process. The article describes an automation example for the ORACLE 11g DBMS.
The DDL scripts of all objects (ORACLE) stored in the SYS.SOURCE $ table would seem to be enough to do:
but actually changing the source is not enough. Sources must be compiled.
Compilation is performed using EXECUTE IMMEDIATE .
Prior to version 11, for queries the text of which could not be written to VARCHAR2 (32767), it was necessary to use the functionality of the DBMS_SQL package:
The problem is how to get the source of the object.
You can use the cursor to go through the records of the table SYS.SOURCE $ - glue the SOURCE fields of each record through the operator " || " and the end of line character. But there is a simpler way, through the DBMS_METADATA package, which has the GET_DDL function.
The convenience of the DBMS_METADATA.GET_DDL function is not only that it gives all the source code of the object, but also that it substitutes the name of the schema and adds " CREATE OR REPLACE ".
The disadvantage is that the function accepts string arguments while numbers are stored in the SYS.OBJ $ table.
In practice, everything is not so simple.
When I logged in with the user SYS , I could not compile the procedures of another scheme ( PROD ), because the tables were not substituted for the names of the schemes, that is, it had:
Apparently there is a nuance about which I do not guess, or my hands are not straight enough.
When I logged in to the PROD user, I had an error accessing the SYS.SOURCE $ table, it was cured by the privilege
')
My task was to replace the function call " GET_ACTUAL_DATE " with the call " SYSDATE ". Of course, it was possible to replace the code of the GET_ACTUAL_DATE function with “RETURN SYSDATE”, but then I wouldn’t have anything to write about in this article :), so let's get started.
First of all, you need to see where the substring "GET_ACTUAL_DATE" occurs:
It turned out 1185 lines in 590 objects.
I looked through the sample and concluded that in order to replace the function call, and not part of the name of a variable or procedure, we must look for
Based on this, I wrote a query to generate a search pattern:
Now you could see what happens if you perform substitutions (REPLACE):
I looked at the result, one line was the declaration of the “GET_ACTUAL_DATE” function, it was not required to be converted into a “SYSDATE” declaration.
The other two lines were comments, they also had to be left alone. I added a script to exclude the specified lines and objects:
Executed the query, looked at the sample - approx.
Now the result of the “calculation” of substitutions had to be saved somewhere. Add a table:
Populating the SWAP_SOURCE_CODE table with data:
Check the result of substitutions:
I did not do the generation of substitutions by the procedure because the conditions of substitutions are different each time and it’s not a fact that once again using PL / SQL it will be convenient to describe the algorithm for converting the original DDL script to the target, usually this is done by an external program (written in C #, Ruby Perl).
Now we have substitutions in the SWAP_SOURCE_CODE table, and you can perform substitutions, while you need to save the source before performing the substitutions and, of course, you need to save the source after the substitutions. To do this, add the table SOURCE_CODE_BACKUP:
Perform substitution procedure P_REPLACE_SOURCE_WITH_OUTPUT:
Perform the procedure:
After that, in theory, you can check what was accumulated there.
And here we are at the finish line - it remains to compile all the objects - execute DDL scripts.
Perform the same procedure - P_EXECUTE_CODE_UPDATE:
We carry out:
We compiled all the objects except for the triggers, select the triggers from our run:
We execute trigger scripts in parts, first the part where the trigger is created, then the part where the trigger is turned on.
Sources are changed as necessary and compiled. The original sources are preserved, you can restore the original objects from them without contacting the administrator of the DBMS with a request to deploy the backup.
Why was it so easy for me to perform the substitution of one function for another? Because this ERP is out of the box with a 20-year history, the code is monitored, the code is designed in the same style, if it was an ERP on my knee, it’s not a fact that I would be able to perform the substitution of the function name so easily.
Comrades, do not be your enemies, love yourself - watch your code, let it be written in the same style according to the same standards!
Amen.
If you need to change a couple of procedures, you can do it manually, but when you need to change several hundred objects, you have to think about automating the process. The article describes an automation example for the ORACLE 11g DBMS.
Theory
The DDL scripts of all objects (ORACLE) stored in the SYS.SOURCE $ table would seem to be enough to do:
update source$ set source = replace(source,'old_name','new_name') where source like '%old_name%' but actually changing the source is not enough. Sources must be compiled.
Compilation is performed using EXECUTE IMMEDIATE .
Prior to version 11, for queries the text of which could not be written to VARCHAR2 (32767), it was necessary to use the functionality of the DBMS_SQL package:
-- 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. The problem is how to get the source of the object.
You can use the cursor to go through the records of the table SYS.SOURCE $ - glue the SOURCE fields of each record through the operator " || " and the end of line character. But there is a simpler way, through the DBMS_METADATA package, which has the GET_DDL function.
The convenience of the DBMS_METADATA.GET_DDL function is not only that it gives all the source code of the object, but also that it substitutes the name of the schema and adds " CREATE OR REPLACE ".
The disadvantage is that the function accepts string arguments while numbers are stored in the SYS.OBJ $ table.
Algorithm for changing the source code of procedures.
- Get the sources using DBMS_METADATA.GET_DDL;
- Change the text as necessary (in the simplest case through REPLACE);
- Compile the procedure using EXECUTE IMMEDIATE;
- Enjoy and have fun;
Practice
In practice, everything is not so simple.
When I logged in with the user SYS , I could not compile the procedures of another scheme ( PROD ), because the tables were not substituted for the names of the schemes, that is, it had:
SELECT * FROM TABLE_NAME , the compiler for some reason expected SELECT * FROM PROD.TABLE_NAME , although at the beginning of the DDL script it was written CREATE OR REPLACE PROCEDURE PROD.PROCEDURE_NAME and when in " PL / SQL Developer " or " TOAD " you compile objects of another scheme (not the one under which you log in), everything compiles without errors.Apparently there is a nuance about which I do not guess, or my hands are not straight enough.
When I logged in to the PROD user, I had an error accessing the SYS.SOURCE $ table, it was cured by the privilege
GRANT SELECT ANY DICTIONARY TO PROD; Privilege needed for debugging GRANT DEBUG ANY PROCEDURE TO PROD; ')
Automation, scripts, and procedures
Source Data Analysis
My task was to replace the function call " GET_ACTUAL_DATE " with the call " SYSDATE ". Of course, it was possible to replace the code of the GET_ACTUAL_DATE function with “RETURN SYSDATE”, but then I wouldn’t have anything to write about in this article :), so let's get started.
First of all, you need to see where the substring "GET_ACTUAL_DATE" occurs:
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 ; It turned out 1185 lines in 590 objects.
I looked through the sample and concluded that in order to replace the function call, and not part of the name of a variable or procedure, we must look for
'(' || 'GET_ACTUAL_DATE' , also before calling the function there were other characters:- '=';
- '' (space);
- ','(comma);
- '' '' (quotes);
Based on this, I wrote a query to generate a search pattern:
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 ; Now you could see what happens if you perform substitutions (REPLACE):
view request
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 ; I looked at the result, one line was the declaration of the “GET_ACTUAL_DATE” function, it was not required to be converted into a “SYSDATE” declaration.
The other two lines were comments, they also had to be left alone. I added a script to exclude the specified lines and objects:
view request
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 ; Executed the query, looked at the sample - approx.
Preservation of substitutions
Now the result of the “calculation” of substitutions had to be saved somewhere. Add a table:
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) ) Populating the SWAP_SOURCE_CODE table with data:
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 ; Check the result of substitutions:
SELECT * FROM SWAP_SOURCE_CODE; I did not do the generation of substitutions by the procedure because the conditions of substitutions are different each time and it’s not a fact that once again using PL / SQL it will be convenient to describe the algorithm for converting the original DDL script to the target, usually this is done by an external program (written in C #, Ruby Perl).
Perform substitution
Now we have substitutions in the SWAP_SOURCE_CODE table, and you can perform substitutions, while you need to save the source before performing the substitutions and, of course, you need to save the source after the substitutions. To do this, add the table 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) ) Perform substitution procedure 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 ; Perform the procedure:
BEGIN P_REPLACE_SOURCE_WITH_OUTPUT( N_BATCH_IN => 1 ); END; After that, in theory, you can check what was accumulated there.
SELECT * FROM SOURCE_CODE_BACKUP; Compilation of objects
And here we are at the finish line - it remains to compile all the objects - execute DDL scripts.
Perform the same procedure - 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; We carry out:
BEGIN PARUS.P_EXECUTE_CODE_UPDATE( N_BATCH_IN => 1 ); END; We compiled all the objects except for the triggers, select the triggers from our run:
SELECT * FROM SWAP_SOURCE_CODE SW JOIN SYS.OBJ$ OB ON OB.OBJ# = SW.OBJ# WHERE OB.TYPE# = 12 AND SW.BATCH = 1 ; We execute trigger scripts in parts, first the part where the trigger is created, then the part where the trigger is turned on.
Conclusion
Sources are changed as necessary and compiled. The original sources are preserved, you can restore the original objects from them without contacting the administrator of the DBMS with a request to deploy the backup.
Why was it so easy for me to perform the substitution of one function for another? Because this ERP is out of the box with a 20-year history, the code is monitored, the code is designed in the same style, if it was an ERP on my knee, it’s not a fact that I would be able to perform the substitution of the function name so easily.
Comrades, do not be your enemies, love yourself - watch your code, let it be written in the same style according to the same standards!
Amen.
Links
Source: https://habr.com/ru/post/280648/
All Articles