Snippets and Thoughts of One Oracle Developer

R Recently I have added a new functionality to the OPY project (you can read more about that in the Database versioning article). And that is the CSV data files conversion to MERGE statements. I store some of the tables in the Git. Typically it is about LOV tables, list of users, roles, navigation... Basically all tables with data I need to run the application. I don't store any sensitive data this way. I run this statement everytime I know I changed something or once a week: python OPY/export.py -n PROJECT -r 0 -csv -v And when I check my patch files, I can see for example the MERGE statement for the ROLES table: -- -- database/data/roles.csv -- MERGE INTO roles t USING ( SELECT 770 AS app_id, 'IS_ADMINISTRATOR' AS role_id, 'Administrator' AS role_name, '' AS role_group, '' AS description_, 'Y' AS is_active, 1 AS order# FROM DUAL UNION ALL SELECT 780 AS app_id, 'IS_TESTER' AS role_id, '' ...

E Especially when you come to a new environment, you might want to check existing apps. What apps are mapped to what schema, how big they are, do they use authorization schemes? Which pages are the main, which pages are not used? Which pages has forms or grids? Which pages has dynamic actions or hardcoded JavaScript? Etc. Following scripts can help you to figure out the structure of your applications/pages. Get list of applications with some details: SELECT w.workspace, --w.workspace_id a.owner, a.application_group AS app_group, a.application_id AS app_id, a.alias AS app_alias, a.application_name AS app_name, a.pages, a.application_items AS items, a.application_processes AS processes, a.application_computations AS computations, a.application_settings AS settings, a.lists, a.lists_of_values AS lovs, a.web_ser...

I If you have a constants in a package you might want to expose them to your SQL queries. You wont able to access them from your SQL statements (for example from views or adhoc queries) unless you create a function for each of them and call that function instead of the constant. With the generator your functions will be consistent, typo free and with correct datatypes. Here is a generator for that: SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE in_package_name CONSTANT VARCHAR2(30) := 'CONSTANTS'; -- source package with constants in_constant_prefix CONSTANT VARCHAR2(30) := ''; -- process just constants starting with this prefix in_fn_prefix CONSTANT VARCHAR2(30) := 'get_'; -- add optional prefix to functions in_generate_body CONSTANT BOOLEAN := TRUE; -- switch between spec and body generator BEGIN FOR c IN ( SELECT LOWER(s.name) AS packag...

F Fifteen years ago I wrote my first script to extract (Oracle) database objects into files in folders, so I can track changes in Subversion. Since that I had rewritten it multiple times using different approaches and languages. Today I will share with you my latest creation available for free under the MIT licence on my GitHub, OPY . What it does export database objects into files in folders you can export just objects compiled in past X days you can export just specific object types some objects are not supported (like dimensions, mview logs, types) export tables data into CSV files designed to keep track of LOV/setup tables so far just the export to CSV files LOBs and some data types not supported export APEX application as a whole and into folders update: now with also partial exports (since day) update: now also exporting embedded code (hardcoded queries) cleanup files to make them more readable ...