Snippets and Thoughts of One Oracle Developer

2 2021 is over, it is time for a recap. I was not sure if I should share this or not, but here is one for openness, honesty and transparency. It is mostly personal so feel free to skip this. Some achievements I got few Oracle certifications, I am very proud of several of them I got few agile certifications, I value the product ownership the most and highly recommend it to everyone, it improved my approach to many things I got two interesting job offers from Oracle, but I didn't follow through, I should have I got a lovely recommendation from Steven Feuerstein after he did a review of my code, which itself was an achievement I got promoted to a Lead developer (a bit overdue and without a pay raise) I continued to share my knowledge through this blog and got to the top 100 Oracle blogs, I have a big plans and many articles forming up my 1st daughter health is slowly improving my 2nd daughter was born almost 3 months ago and she is healthy I started trading on Binance and didn't l...

E Every app needs some sort of settings. If you are looking for a pure APEX solution, you should check APEX_UTIL.SET_PREFERENCE procedure. You might have guessed it, my solution is based on a custom (home made with love and passion) table Settings from project CORE . Let's check the table definition: CREATE TABLE settings ( app_id NUMBER(4) CONSTRAINT nn_settings_app_id NOT NULL, setting_name VARCHAR2(30) CONSTRAINT nn_settings_id NOT NULL, -- setting_value VARCHAR2(256), setting_context VARCHAR2(64), setting_group VARCHAR2(64), -- is_numeric CHAR(1), is_date CHAR(1), -- description_ VARCHAR2(1000), -- CONSTRAINT uq_settings UNIQUE (app_id, setting_name, setting_context), -- CONSTRAINT ch_settings_is_active CHECK ((is_numeric = 'Y' AND is_date IS NULL) OR is_numeric IS NULL), -- CONSTRAINT ch_settings_...

T This is my solution how to manage users and roles in APEX apps in a very effective and clear way. All code is part of the CORE project . Authentication - Users You have multiple options how to manage users in APEX. Just explore Shared Components - Authentication Schemes. These methods are available: APEX Accounts Custom Database Accounts HTTP Header Variable LDAP Directory No Authentication Open Door Credentials Social Sign-in For my business projects I usually go for SSO or LDAP for legacy or small projects. For my personal projects it is usually APEX od DB accounts. Either way I have my own Users table , so I can reference users in other tables (with foreign keys). CREATE TABLE users ( user_id VARCHAR2(30) CONSTRAINT nn_users_user_id NOT NULL, user_login VARCHAR2(128) CONSTRAINT nn_users_login NOT NULL, user_name VARCHAR2(64), -- lang_id VARCHAR2(5), is_active CHAR(1...

I I am not a fan of default APEX navigation with hardcoded lists. It is hard and slow to maintain, sometimes you have to do things twice and it is not easy to transfer this to different environment. Fortunatelly, there is an option to create a navigation list based on your SQL query. How it works normally You create a new page and during this you specify page position and visibility in navigation menu When you change the page name, icon or page auth scheme you also have to manually alter navigation menu - this is the source of the pain Many times developers forgot to sync page changes with navigation menu which leads to users being confused How it works with my solution You add page however you like and ignore navigation options You add your page to a page group (or create a new group if needed) You open the service page (910 Navigation, on the screen below) and adjust position in navigation and page visibility When you change the page name, icon or...

T There are some interesting views in APEX dictionary which can help you to track users/developers activity. SELECT * FROM apex_workspace_access_log ORDER BY access_date DESC; SELECT * FROM apex_workspace_activity_log ORDER BY view_timestamp DESC; SELECT * FROM apex_debug_messages ORDER BY message_timestamp DESC; For this purpose I not interested in login attempts, so I can skip the access log. Developers activity To track activity of developers in you app you can use following query. It includes both activity in the current app itself and in the APEX Builder for the same workspace. It also transform developers account name into user name. You can distinguish APEX Builder from your app via the application_id or by empty application_name. Request_type "R" means rendering of the page, "P" means processing (page submit). You can use same query without the LEFT JOIN to track common app users. SELECT NVL(d.user_id, LOWER(l.apex_user))...

8 80 articles in 9 months and my blog was today listed in Top 100 Oracle blogs on 89th place, just above Oracle blogs. I am very honoured and grateful and I will certainly continue to deliver valuable content. Thank you. I am aiming for short and practical articles, straight to the point, easy to grasp with links to the documentation (if applicable) and added value (hopefully) interesting content for intermediate Oracle developers not covered elsewhere

W When your app is ready for users, you want to mask (trap/catch, translate, log) error messages which slipped throught your standard PL/SQL error handler and show user something less revealing. You might also want to store these errors in your own error log table and not in the APEX log. To achieve this you need to define an Error Handling Function in Shared Components, Application Definition Attributes, Error Handling. Use your own name, mine is "apex_error_handling". To create this function you can get inspired like me by the nice example in Oracle documentation, in APEX_ERROR package, section Example of an Error Handling Function . I modified this function a bit to serve my needs: show whole error message with backtrace to APEX developers show just a error type and log_id to common users show constraint names on integrity errors (you can easily convert them to relevant table and columns) log everything (specially the component causing the error and the er...

I I often use views with APEX items, so I can filter data ASAP, not after they are all loaded to the app. It is also a security layer like using contexts for VPD or views shadow schema. To test these views in SQL Developer is tough. Unless you create an APEX session first and set values to items you want to use. Creating session To access page/app items from APEX in PL/SQL you have to create APEX session first: DECLARE in_user_id CONSTANT apex_workspace_apex_users.user_name%TYPE := '&USER_ID'; in_app_id CONSTANT apex_applications.application_id%TYPE := &APP_ID; in_page_id CONSTANT apex_application_pages.page_id%TYPE := 0; -- not important now -- v_workspace_id apex_applications.workspace%TYPE; BEGIN -- find and setup workspace SELECT a.workspace INTO v_workspace_id FROM apex_applications a WHERE a.application_id = in_app_id; -- APEX_UTIL.SET_WORKSPACE ( p_works...

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

D Did you ever wanted to pass dynamic query to interactive report or grid? I did. I have a uploader page where you can upload multiple files to multiple tables. Lets say there is a 60 tables. All these tables have their clones for DBMS_ERRLOG handling so I can catch errors on upload (including wrong data). Now how do you display these tables to the user? I dont want to create 60 reports for 60 error tables, I want just one. Here is how to achieve that. 1) Create form on APEX_COLLECTION S table and hide it, we need just the items to hold real column names. 2) Create interactive report or grid to display the data from your query. SELECT seq_id AS row#, c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050 FROM a...

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

X XML is not cool anymore, but if you are still using it and want to have it in readable form (indented), there is a "AS CLOB INDENT SIZE" trick. There is a little side effect and that is that the empty tags (departure_at) will be shortened. SELECT '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:app=""> <soap:Header> <a:Action soap:mustUnderstand="1"></a:Action> </soap:Header> <soap:Body> <app:message><![CDATA['|| XMLSERIALIZE(DOCUMENT XMLELEMENT("messages", XMLAGG(XMLELEMENT("message", XMLATTRIBUTES(r.message_id AS "id"), XMLELEMENT("location", XMLELEMENT("country_id", r.country_id), XMLELEMENT("source_id", r.source_id) ), XMLELEMENT(...

Y You might be frustrated when you want to add a column to a table and you can't choose a specific position. You can't easily change column order either. Well, this is the best trick. Just change the position in dictionary without moving data, cloning or recreating a table. Or without using DBMS_REDEFINITION . Move specific column to the end of the table: DECLARE in_table_name CONSTANT user_tab_cols.table_name%TYPE := '&TABLE_NAME'; in_column_name CONSTANT user_tab_cols.column_name%TYPE := '&COLUMN_NAME'; BEGIN EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' INVISIBLE'; -- EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' VISIBLE'; END; / To reorder all columns in desired order you need to provide a full list of colums. Here is how to get the current list: SELECT 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 Welcome to the last part of the unit testing serial, the gamification part. If you are still reading this then congratulations, you have the most boring parts behind you. How to keep writing tests fun? Let's think about it. As a leader/manager you (should) want your code covered by meaningful tests which won't costs you a lot of money. You don't want useless tests. As a developer you don't want to create too many tests but you want to cover at least the most risky/fragile parts of your code. Gamification is the answer. I created some packages for a tower defence game. Then I want to make sure I wont break my code in the future or at least I would be notified when I do before it reaches users. My procedures are usually short but there are some exceptions. Also they usually do more than one thing so they require more than one test. I wrote some tests and realised it is like being half blind. That led me to the code coverage feature ( DBMS_PLSQL_CODE_COVERAGE ) intro...