JAVA- PLSQL STORED PROCEDURES

How can one see if somebody modified any code?

Code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view.

Example: SELECT OBJECT_NAME, TO_CHAR (CREATED, 'DD-Mon-RR HH24: MI’) CREATE_TIME, TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME, STATUS FROM USER_OBJECTS WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';

How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.

SELECT TYPE, NAME, LINE FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%&KEYWORD%';

How can one keep a history of PL/SQL code changes?

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes.

Look at this example:

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