Snippets and Thoughts of One Oracle Developer

D Did you ever wanted to add badges to the tab names in Tabs component ? You might expect this will be simple. But it is not. You add a <span> to the name and your tabs will break (see the image below). Expected result is on the right. So what you need to do? assign TABS static id to your Tabs region assign any static id to all your tabs (subregions) create page item for each tab fill these items however you like create DA to add these badges after the tabs are created adjust CSS styles to your needs Here is the DA which will map your badge items to the proper tabs. So on page 1 it will map P1_ TAB_A _BADGE item to the TAB_A tab (subregion with TAB_A static id). setTimeout(function() { var tabs_id = 'TABS'; // static_id for the Tabs region var badges = '_BADGE'; // postfix for badge items // if (!$('#' + tabs_id)) { return; } var tabs = apex.regio...

O On one of my projects I have to create patch scripts to add columns to a table or remove them. Thing is, that they might be run multiple times or might get different result on different environments. So I created this simple script where I can define which column in which table I need to add or drop and it will skip what was already done. Keep in mind that I am not solving changed data types nor the column positions. PROMPT PROCESSING TABLE CHANGES -- SET SERVEROUTPUT ON BEGIN FOR c IN ( WITH x (action, table_name, column_name, data_type) AS ( SELECT 'ADD', 'TABLE_NAME', 'COLUMN_NAME1', 'NUMBER' FROM DUAL UNION ALL SELECT 'DROP', 'TABLE_NAME', 'COLUMN_NAME2', NULL FROM DUAL ) SELECT x.*, c.column_name AS column_exists FROM x LEFT JOIN user_tab_cols c ON c.table_name = x.table_name AND c.column_name = x.column_name ) LOOP...

I Imagine this. You know stuff. Maybe a lot of stuff. How long does this last if you don't add new skills and new experience regularly? How long before you are being replaced by someone else or worst something else? How easily will you get a new job? How can you provide high quality services to your clients? I have met a lot of APEX developers. Many of them on senior level, doing APEX for 10+ years. But many of them also stuck on APEX 5 and Oracle 11g, rarely 12c. Do you realize that 11g is like 15 years old and APEX 5 half of that? You really can't call yourself a senior if you are doing the same thing for 10 years, can you? Do you know how many of these guys know at least one new feature from latest APEX? Or a main difference between 11g and 12c? Zero. One new feature from 19c? Zero. I also see this over and over in teams: We don't have time to refactor the code. We will do that in phase two (never). We don't have time to write unit tests. ...

I If you want to create a lot of items on APEX page, you should use the Form region. Based on a table/view or query it will create items for every column and add init process to prefill these items for you when you pass a primary key. Neat. The thing is, sometimes I can't use the form processes, I just need to set items based on a query. The usual approach is to SELECT INTO (select columns into the items). For 20 items you will get 40+ lines query, for 60 items you will get 120+ lines, you get the idea. You have to be careful about the column names and items names and not to mix them. And this also leads to different item names than columns. SELECT column_name1, column_name2, column_name3, column_name4 ... INTO :P100_column_name1, :P100_column_nameee2, :P100_column_name3, :P100_col_name4 ... FROM your_table WHERE ROWNUM = 1; How about this? DECLARE l_curs SYS_REFCURSOR; BEGIN OPEN l_curs FOR SELECT * -- your columns whi...

T This week I got my Oracle ACE Associate award. I feel very excited, honoured and proud to receive this recognition. Not everyone can get it. It is certainly the best work related event for me in years. There are currently 392 ACEs world wide, 196 in Database focus area, 99 in EMEA region and just me in Czech Republic. My interested at Oracle started when I was a kid and I bought a book about Oracle 7i. I didn't understand it and it was very hard to even install the database then. I started developing web applications as a freelancer when I was 18 and could legally set up my first company. PHP cluttered with MySQL calls was my first choice. But I was lured by Oracle's ability to store and execute the business logic inside of the database and to even run the webserver there. Magic. So I found a job at StringData where I had the opportunity to learn Oracle on commercial projects. I grew up on the Oracle Base blog from Tim Hall , on orange books from Steven Feuerstein ,...

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