Snippets and Thoughts of One Oracle Developer

S Sometimes, when I'm working on a new package, it's difficult to keep the specification updated to reflect changes in arguments. Adding new procedures becomes tedious, and when I change the formatting, it's time-consuming to update the specification accordingly. When I reformat a legacy package, I usually copy the whole body into the specification and keep only the declarations. But that also takes some effort. So I created this nifty script, which generates a specification from your package body. It keeps everything that exists in your current package spec before the first procedure or function (like constants, exceptions, comments...). Everything else is replaced. It does not support forward declarations, and your private procedures will get exposed (which is a good thing, because you should be using the ACCESSIBLE BY clause instead). Here is the script: DECLARE in_package_name CONSTANT VARCHAR2(30) := '&PACKAGE_NAME.'; -- v_head...

S Sometimes, you have to modify all triggers, if you were for example using SYSTIMESTAMP and now SYSDATE is enough. To modify all triggers by hand might be a lot of work (it is not just about modifing, you also have to compile all of them in the database). So let's write a script for that (replace LOCALTIMESTAMP with SYSDATE in all triggers), but you can easily modify it to whatever you need to change: DECLARE v_query VARCHAR2(32767); v_modified BOOLEAN; BEGIN FOR c IN ( SELECT t.name, ROWNUM AS r#, d.referenced_name FROM user_source t JOIN user_dependencies d ON d.name = t.name AND d.name LIKE '%' AND d.type = t.type AND d.type = 'TRIGGER' AND d.referenced_type = 'TABLE' WHERE t.line = 1 ORDER BY 1 ) LOOP v_query :=...

A Another issue I faced on each project is data type inconsistency. In this older article I mentioned how to fix data types mismatches on keys . Today, I show you a script, which fix it for you (based on a column name match). Let's explore this issue on the audit columns (who created or updated the row and when). To check the audit columns for user: SELECT c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc, COUNT(*) AS count_tables FROM user_tab_cols c JOIN user_tables t ON t.table_name = c.table_name WHERE c.table_name LIKE '%' AND c.column_name LIKE 'CREATED_BY' GROUP BY c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc ORDER BY 1, 2, 3, 4; Let's check the audit column for date. This gets more trickier, because we might not have consistent names. To keep the changes low, I am not changing the column names, just ...

I In this article, I will show you a script that deletes all rows from all tables for a specific tenant. You don't need to be familiar with your schema or manually piece together queries to perform the deletion. You can also easily modify this script to delete any entity, not just a tenant. There are several ways to achieve this, and I will show you two of them: To find (even compound) references to a specific table, you can use this query: SET DEFINE ON SELECT r.table_name, c.column_name, r.constraint_name, k.column_name AS primary_column FROM user_constraints r JOIN user_constraints p ON p.constraint_name = r.r_constraint_name AND p.constraint_type IN ('P', 'U') JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name AND c.column_name NOT IN ('TENANT_ID') -- we should match on position JOIN user_constraints s ON s.table_name = r...

T The space on free OCI can sometimes be a bit limiting, especially if you're using table partitions. Even an empty partition consumes 8 MB, and if you have partitions per tenant in every table — and over 100 tables — it can quickly grow with each new tenant. So here is the script which helps you to remove empty partitions and also rename the partitions so you can easily identify specific tenants: DECLARE in_table_like CONSTANT VARCHAR2(128) := '%'; in_partition_prefix CONSTANT VARCHAR2(128) := '__P'; -- v_found PLS_INTEGER; v_high_value VARCHAR2(4000); v_new_name VARCHAR2(128); BEGIN FOR c IN ( SELECT t.table_name, t.partition_name, t.high_value FROM user_tab_partitions t WHERE t.table_name LIKE in_table_like ORDER BY 1, 2 ) LOOP v_found := NULL; v_high_value := c.high_value; -- LONG convers...