How to maintain the history of code changes of pl/sql?

Answers were Sorted based on User's Feedback



How to maintain the history of code changes of pl/sql?..

Answer / arup ratan banerjee

U CAN REFER ALL_SOURCE TABLE...
SELECT * FROM ALL_SOURCE WHERE OWNER='IHIS11'
AND TYPE = 'PROCEDURE';


U will get procedure body from this table

Is This Answer Correct ?    4 Yes 0 No

How to maintain the history of code changes of pl/sql?..

Answer / guru

--

CREATE TABLE SOURCE_HIST -- Create
history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist --
Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA --
Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE')
then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
--

Is This Answer Correct ?    5 Yes 2 No

Post New Answer

More SQL PLSQL Interview Questions

what is sql profiler? : Sql dba

0 Answers  


Is not equal in sql?

0 Answers  


What are sql injection vulnerabilities?

0 Answers  


Can a select statement fire a trigger?

0 Answers  


What is a natural join?

0 Answers  






what is the use of HASH, LIST partitions?

1 Answers   ITC Infotech,


How do I run a query in pl sql developer?

0 Answers  


What is an index? What are the types of indexes? How many clustered indexes can be created on a table?

0 Answers  


Can we use pl sql in sql server?

0 Answers  


What is a variable in sql?

0 Answers  


Define select, insert, create, delete, update, drop keywords

0 Answers  


Differentiate between sga and pga.

0 Answers  






Categories