Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


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 ?    5 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

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

Answer / kuldeep

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



CREATE OR REPLACE TRIGGER trig_change_hist
AFTER CREATE ON SCHEMA
BEGIN
INSERT INTO SOURCE_HIST -- History table
SELECT SYSDATE, user_source.*
FROM USER_SOURCE
WHERE NAME = ORA_DICT_OBJ_NAME; --
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL PLSQL Interview Questions

How pl/sql source code can be protected?

3 Answers  


Difference between table function and pipelined function?

0 Answers  


Why does %isopen return false for an implicit cursor?

0 Answers  


What is integrity in sql?

0 Answers  


Is pl sql still used?

0 Answers  






i need department wise top 2 employees salary.which logic i will use

17 Answers  


How to get second highest salary from a table

3 Answers  


I have 2 Databases. How can create a table in particular database? How can i know the list of tables presented each database?( in oracle 10g)

5 Answers   Relq,


Does a primary key have to be a number?

0 Answers  


Is it possible to read/write files to-and-from PL/SQL?

0 Answers  


Describe types of sql statements?

0 Answers  


What is primary key and unique key?

0 Answers  


Categories