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 ?    4 Yes 1 No

Post New Answer




More SQL PLSQL Interview Questions

What is a self join ?

6 Answers   BirlaSoft,


write a procedure to find top second no from a list of numbers

1 Answers   ABC,


how to find the second highest salary from emp table?

209 Answers   Yardi, Polaris, Persistent, Patni, TCS, Zensar, IBM, EDS, Cognizant, Wipro, CIS, Cosmosoft, DAS, GreenTech, HOV Services, National Institute of Science and Technology, Infosys,


How pl/sql source code can be protected?

2 Answers  


Name the tables where characteristics of Package, procedure and functions are stored ?

3 Answers  






What is referential integrity ?

3 Answers   BirlaSoft,


How to export the table data (this table having 18 million records) to .csv file. Please tell me is there any faster way to export the data.

8 Answers  


HP Interview -2016 Unix 1) grep command in unix 2) what is set command

1 Answers   HCL,


what is sql optimization

1 Answers   Fidelity,


write a query to delete similar records in different tables with same structure

4 Answers  


How to write a procedure for displying the data in a TREE or (PARENT and CHILD ) relationship , for ex: A is the main project id, for this project B,C,D are sub tasks(sub project id's) for B the sub tasks are e,f,g and for c is h ,i ,j and for d is k,l,m now i need to display the o/p in a TREE fashion pls help me , thanks in advance surendra

2 Answers   Zensar,


Table1: Col1 col2 1 2 10 3 4 89 5 6 Table:2 Col1 col2 3 2 9 5 4 7 6 87 With the help of table1 and table2 write a query to simulate the fallowing results. Output1: Col1 col2 1 2 2 3 3 4 4 5 5 6 Output2: Col1 col2 2 3 10 4 5 89 6 7 1.Write query for single row to multiple row using sql statements. Eg:a,b,c,d,e,f Change to A B C D E F 2. Write query for multiple row to single row using sql statements. Eg2 A B C D E F Change to Eg:a,b,c,d,e,f Table1: Col1 col2 8 5 2 9 4 2 5 1.Write a query to select all the rows from a table1,if the value of A is null then corresponding B’s value should be printed in A’s value.if the value of A is null in that table then corresponding B’s value should be printed as 30. 2. write a query to find the sum of A and B .display the max among both. 3.write a query to find total number of rows from table 1. Note: if any column value is null in a row then that row should be considered as 2 rows. 4.write a query to display all the records of table1 except A containg 2 as well B containg 5. 5.rewrite the fallowing without using join and group by. Select b.title,max(bc.returneddate –bc.checkoutdate)” mostdaysout” From bookshelf_checkout bc, Book shelf B Where bc.title(+)=b.title Group by b.title. 6.rewrite fallowing query Select id_category from category_master X where exists (select 1 from sub_category Y where X.id_category=Y.id_category) Customer: Name phone1 phone2 phone3 bitwise A 23456 67890 12345 --- B 67459 89760 37689 --- Don’t_call Col1 67890 37689 1.q) update the customer table of bitwise with 1 or 0. Exists in don’t_call table menas show -1 Other wise -0. Output. Name bitwise A 010 B 010

0 Answers   Protech,






Categories