Snippets and Thoughts of One Oracle Developer

R Recently I have encountered a strange data model. Imagine you have an app with many tables for a specific group of users. Then you are asked to add a second group of users to the app. These groups have same tables but must not see records from the other group. Author of this solution decided to copy all objects (not just tables) and use a different prefix for them. Then he made some small adjustments here and there. For tables, views, mviews, triggers and packages. He skipped sequences for some reason. If you wonder why is this strange, then think about how would you proceed if you have been asked to add a third, fourth or fifth group of users... The issue I have was how can I quickly compare these tables to analyze how much they overlap? They are in the same scheme and they have different prefixes. I don't want to compare them one by one manually. Here is the script I wrote for this, just change the prefixes: WITH x AS ( SELECT /*+ MATERIALIZE */ 'P1_&

I I was tired of searching the app for places where is the authorization scheme referenced. So I wrote this script to get a nice overview of components and relevant pages. To get list of the autorization schemes/roles: SELECT a.authorization_scheme_name AS scheme_name, a.authorization_scheme_id AS scheme_id FROM apex_application_authorization a WHERE a.application_id = &APP_ID ORDER BY 1; To get list of components and relevant pages: SET SERVEROUTPUT ON DECLARE in_app_id CONSTANT NUMBER := &APP_ID; -- v_records NUMBER; v_pages VARCHAR2(4000); BEGIN FOR c IN ( WITH x AS ( SELECT a.application_id, a.authorization_scheme_name AS scheme_name, a.authorization_scheme_id AS scheme_id FROM apex_application_authorization a WHERE a.application_id = in_app_id ), p AS ( SELECT d.apex_view_n

I I have been asked multiple times about CSV files processing recenly. I guess CSV files are not going away any time soon. Excel (.xlsx) files, CSV, XML and JSON files can be uploaded and processed by awesome APEX_DATA_PARSER package. You can check my more sophisticated uploader I wrote few years ago, but this short tutorial is a great way how to start. First you have to upload your file, you can use File page item for that. Then you will see list of uploaded files (for your active session) and file profile, columns and data. All of this is achieved by simple queries: Uploaded files: SELECT f.id, f.filename AS file_name, f.name, f.mime_type, f.created_on FROM apex_application_temp_files f WHERE f.application_id = :APP_ID; File profile: SELECT f.name, f.mime_type, REGEXP_SUBSTR(f.name, '([^/]*)$') AS file_name, -- JSON_VALUE(f.profile_json, '$."file-encoding"' RETURNING VARCHAR2) AS file_

I I saw over 100 APEX apps written by different people over the years. But I never saw an app which would leverage the object references. I believe these apps are out there hidden and protected as top secrets. Story behind Imagine you get a brief to fix charts on a Dashboard page. You open a page and there are 12 chart regions. All of them have 100+ lines query as a source with a lot of joins and multiple levels of subqueries. Just to read one query will take o lot of time. Then you have 11 more. In the end and after few hours you will realize that the code for this charts is basically the same. And now what? Fix the query on 12 regions one by one and move to another task? I asked 12 APEX senior developers how they would proceed with the queries. 9 would copy paste the query between regions 1 would use APEX_COLLECTION 1 would PL/SQL function returning query 1 would use view (finally) Yes, you can do simple changes directly on APEX components, but in many times this w

L Like the fall and recent rise of the Oracle Ace program I wish there would be a rise of Oracle University. Here a few of my concerns. I am sorry if you will feel bad or if you will be offended by this. If you know how can I help to improve this situation, please let me know. Oracle Learning Subscription Imagine you buy the OLS for $4995 and as an unexpected bonus you will get a popup survey every few minutes. You are suppose to go through as much courses as possible in following 12 months to keep your investment worth it, so you will be spending a lot of time watching the courses and you will get and feedback survey at the end of every video and every chapter if not even more often. And the irony is that nobody will read it, nobody will listen to you. Al least it feels this way. And if you manage to get several cloud certifications in that year, all of them will expire in 18 months. I understand cloud changes quickly, but not everything changes and not this quickly. Oracle joi

W We all have to concatenate strings and varibles and you have to admit that it will get ugly very quickly. Luckily someone created a sprint function we know from other programming languages. It is called APEX_STRING.FORMAT . Lets jump to the example first: What would you rather to read and maintain, before or after version? If you like the before version, you can stop reading. And if you are still here then we can be friends :-) You can pass up to 20 arguments and you have two options how to reference them. You either use "%s" and then the values are replaced in the same order as you passed them. Or you can use numbers like "%0..19" and reuse them however you need. I usually stick with "%s" unless I need to replace same value multiple times. There is a nice side effect of this type of concatenation. It is easy to run. Just copy paste the code, replace "!" with nothing, replace "%" with "&" and you can ac

I I have been consulting on some interviews lately and I want to share some questions I ask APEX developers. First of all I think SQL and PL/SQL is a crutial stepping stone to the APEX world, so you can explore some SQL and PL/SQL questions in the older article and here are some updates to the basic concepts questions: Can FK contains NULL values? Explain the autonomous transaction. What is the advantage of compound trigger? What is view with the CHECK OPTION? How can you rollback a transaction after the DDL statement? Can you do DML operations on a view? How to solve the mutating table error? What is a deadlock? Explain difference between NLV, NVL2, NULLIF and COALESCE. How to handle duplicate rows exception during INSERT? How can you refactor query from Oracle JOIN sytax to ANSI syntax? What is a whitelisting in PL/SQL? And now to the APEX questions, they are about basic concepts, performance, security and clean code: What you have

D Did you ever wanted to show cells in grid with a background color without default borders? Borderless. And to map values to the colors in a way that they can be changed by the user? Note: by dynamic I ment easily adjustable by end users. Backend First we need to create the LOV table with tresholds and colors. Typically I create more of these tables per project/app and I name them with the real value they represents (like LOV_COLOR_BUDGET_RANGE). Lets create the table and some test data: CREATE TABLE lov_treshold_colors ( status_id VARCHAR2(64), treshold_value NUMBER, color_code VARCHAR2(8), -- CONSTRAINT pk_lov_treshold_colors PRIMARY KEY (status_id) ); -- INSERT INTO lov_treshold_colors SELECT 'A', 20, '#00cc00' FROM DUAL UNION ALL SELECT 'B', 40, '#00bb00' FROM DUAL UNION ALL SELECT 'C', 60, '#00aa00' FROM DUAL UNION ALL SELECT 'D', 80, '#009900' FROM D

I In past several weeks I have been busy with improving the OPY tool . It started as a simple Oracle Python wrapper, but I have added more and more functionality to it and now it has almost 3000 lines, nifty YAML config file (per project now) and do way more than just execute queries against Oracle database. You can read about: basic versioning of database objects and APEX applications in Git version data as CSV files and convert them to MERGE statements into patch files And now I have added greater support for feature branches (and improved many other things). What is this about? Feature branches style is typical on larger projects with larger teams. You are assigned a card (or you pick one from a queue, depending on how agile you are), you create a branch just for this card, you work on a card (change objects in database, change APEX pages and components), you test it and commit the result to this branch. Then depending on your process push/merge it to source