Snippets and Thoughts of One Oracle Developer

F A few years back, I created a view generator with support for JOINs. Unfortunately, it did not work for many use cases. It was bothering me for a while, so I decided to fix it. You list several tables with your preferred aliases, and it will find foreign keys between the tables (both ways) and apply them to the previous tables. So the table order is critical. I tried to adjust the table order based on the constraints, but that proved to be a bit challenging, so at least for now you have to figure out the table order yourself. Also, if you have columns with the same name, the later ones will be commented out. DECLARE in_tables CONSTANT VARCHAR2(4000) := ' TABLE_NAME1 t1, TABLE_NAME2 t2, TABLE_NAME3 t3, '; in_ignore_cols CONSTANT VARCHAR2(4000) := 'CREATED_BY,CREATED_AT,UPDATED_BY,UPDATED_AT,'; -- v_tables VAR...

I If, for whatever reason, you decide you don't want the identity columns anymore and want to switch back to the classic sequences, this is for you. Here is the plan for how you can achieve it: get identity columns (identify tables and identity sequences) create new sequences for every table with "{TABLE_NAME}_ID" name get maximum value for each column to use it as a start value for new sequence create backup for objects source code replacements (in annotations) remove identity from all columns add new sequences as a default values fix source code (triggers, packages...); this is solved just partially To identify columns with identities: SELECT s.sequence_name, c.table_name, c.column_name, a.annotation_value AS old_seq_backup FROM user_sequences s LEFT JOIN user_tab_cols c ON c.data_default_vc LIKE '"' || USER || '"."' || s.sequence_name || '".nextval' LEFT JOIN user_annotat...

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...

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...

M Many times I create an interactive grid not just on a simple table, but on a more complex view with transformed rows/columns (pivot/unpivot) with the need to store data in multiple tables on submit. I face similar difficulties when handling complex forms. Then I have to create a custom procedure to handle the grid/form submit and then pass columns from grid (or items from form) to this procedure call. That is a lot of tedious work to write these handlers over and over again. Check this GEN package, it will generate all of this code for you and with nice formatting. You can easily customize this to fit your needs and style. Generating the procedure Imagine you have this table and you have more complex view based on this table. CREATE TABLE plan_certifications ( cert_id VARCHAR2(20) NOT NULL, cert_name VARCHAR2(100) NOT NULL, path_id VARCHAR2(30), -- exam_page_link VARCHAR2(256), credly_link VARCH...

W When creating page in APEX I need a view for an object (table/view) I would like to present on page in report/grid. This is very tiring. So I created this simple generator. You just need to pass it a list of tables/view you would like to use (and join) in your new view. This will help you to create the list if you need to use more then one table/view. Just change the order of the tables and aliases to your needs. Joins will be done in this order. WITH x AS ( SELECT '%' AS tables_like FROM DUAL UNION ALL SELECT '%' AS tables_like FROM DUAL ) SELECT LISTAGG(t.table_name || ' ' || t.table_alias, ',') WITHIN GROUP (ORDER BY t.table_name) AS tables FROM ( SELECT t.table_name, CHR(96 + ROW_NUMBER() OVER (ORDER BY t.table_name)) AS table_alias FROM x JOIN user_tables t ON t.table_name LIKE x.tables_like AND t.table_name NOT LIKE '%$' GROUP BY t.table_name ) t; Now pass l...

W When you need to generate aligned function or procedure call, you can use the following script. I would like to use data types used in source code, but that is a bit more complex. This should had been my #JoelKallmanDay post, but I could not finish this in time. WITH inputs AS ( SELECT UPPER('&PROCEDURE_NAME') || '%' AS procedure_name, UPPER('&OBJECT_NAME') || '%' AS package_name, 1 AS overload FROM DUAL ), a AS ( SELECT a.argument_name, a.object_name, a.package_name, a.position, a.in_out, a.data_type, a.data_length, a.data_precision, a.defaulted, a.pls_type, a.char_length, a.char_used FROM inputs i JOIN user_arguments a ON a.object_name LIKE i.procedure_name AND a.package_name LIKE i.package_name AND (a.overload =...

W When you need to generate aligned table column names. Now updated to use in APEX calls (from Interactive Grid or from Forms DML processing) and to verify existing page items. WITH s AS ( SELECT 'in_' AS arg_prefix, 'l_' AS var_prefix, 'rec.' AS set_prefix, -- &APEX_APP_ID AS apex_app_id, &APEX_PAGE_ID AS apex_page_id, -- 5 AS minimal_space, 4 AS tabulator_width, -- MAX(LENGTH(c.column_name)) AS name_length, MAX(c.table_name) AS table_name FROM user_tab_columns c WHERE c.table_name LIKE '&TABLE_NAME' ), t AS ( SELECT s.*, CEIL((LENGTH(s.var_prefix) + s.minimal_space + s.name_length) / s.tabulator_width) ...