How to maintain the history of code changes of pl/sql?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What is scope of pl sql developer in future?
Is primary key clustered index?
How do we tune the code?
declare l1 number := null; l2 number :=null; begin if l1=l2 then message('equal'); else if l1<>l2 then message('not equal'); else message('else'); end if; end if; end; What will be the output ?
how to create a test table in your mysql server? : Sql dba
Explain how to use transactions efficiently : transact sql
How do I run a sql script?
Why triggers are used?
What is an invalid partition table?
how do you know the version of your mysql server? : Sql dba
What is a constraint?
how many no of table can be join in a sql query.