Sql updating a table from the same table
You are supposed to backup and keep your *source files after wrapping them.
However it is possible for skilled hackers to unwrap your wrapped Oracle PL/SQL code.
You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. PL/SQL is a normal programming language that includes all the features of most other programming languages.
However, SQL doesn't include all the things that normal programming languages have, such as loops and IF... But, it has one thing that other programming languages don't have: the ability to easily integrate with SQL.
SCHEMA -- Change SCOTT to your schema name DECLARE BEGIN IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY') THEN -- Store old code in SOURCE_HIST table INSERT INTO SOURCE_HIST SELECT sysdate, all_source.* FROM ALL_SOURCE WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i END IF; EXCEPTION WHEN OTHERS THEN raise_application_error(-20000, SQLERRM); END; / show errors A better approach is to create an external CVS or SVN repository for the scripts that install the PL/SQL code.
The canonical version of what's in the database must match the latest CVS/SVN version or else someone would be cheating.
Example: Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated.
Just be careful, there is no "decode" command available. The syntax is: Please note: there is no legal way to unwrap a *binary file.In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.However, it is still considered a best practice to put as much of your program logic as possible into packages, rather than triggers.If not, replace &KEYWORD with the string you are searching for.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).