Snippets and Thoughts of One Oracle Developer

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

R Recently I have added a new functionality to the OPY project (you can read more about that in the Database versioning article). And that is the CSV data files conversion to MERGE statements. I store some of the tables in the Git. Typically it is about LOV tables, list of users, roles, navigation... Basically all tables with data I need to run the application. I don't store any sensitive data this way. I run this statement everytime I know I changed something or once a week: python OPY/export.py -n PROJECT -r 0 -csv -v And when I check my patch files, I can see for example the MERGE statement for the ROLES table: -- -- database/data/roles.csv -- MERGE INTO roles t USING ( SELECT 770 AS app_id, 'IS_ADMINISTRATOR' AS role_id, 'Administrator' AS role_name, '' AS role_group, '' AS description_, 'Y' AS is_active, 1 AS order# FROM DUAL UNION ALL SELECT 780 AS app_id, 'IS_TESTER' AS role_id, '' ...

E Especially when you come to a new environment, you might want to check existing apps. What apps are mapped to what schema, how big they are, do they use authorization schemes? Which pages are the main, which pages are not used? Which pages has forms or grids? Which pages has dynamic actions or hardcoded JavaScript? Etc. Following scripts can help you to figure out the structure of your applications/pages. Get list of applications with some details: SELECT w.workspace, --w.workspace_id a.owner, a.application_group AS app_group, a.application_id AS app_id, a.alias AS app_alias, a.application_name AS app_name, a.pages, a.application_items AS items, a.application_processes AS processes, a.application_computations AS computations, a.application_settings AS settings, a.lists, a.lists_of_values AS lovs, a.web_ser...

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

F Fifteen years ago I wrote my first script to extract (Oracle) database objects into files in folders, so I can track changes in Subversion. Since that I had rewritten it multiple times using different approaches and languages. Today I will share with you my latest creation available for free under the MIT licence on my GitHub, OPY . What it does export database objects into files in folders you can export just objects compiled in past X days you can export just specific object types some objects are not supported (like dimensions, mview logs, types) export tables data into CSV files designed to keep track of LOV/setup tables so far just the export to CSV files LOBs and some data types not supported export APEX application as a whole and into folders update: now with also partial exports (since day) update: now also exporting embedded code (hardcoded queries) cleanup files to make them more readable ...

I I have dedicated June to finally clear some business analyst certifications I wanted to do for a year. Why BA? Well, being a good business analyst will help me (and you) to become a better developer and also product owner, since both professions heavily rely on BA skills. And yet not many developers nor product owners pursue them. I searched for the top BA certifications and I found IIBA . This Canadian non profit company is on the very top and I like their offerings. I don't want to spend time and money on studying something dubious from someone nobody knows or acknowledge. You can't make a mistake when choosing IIBA. I think these 3 paths are great for both developers and product owners: Agile Analysis Certification (IIBA®-AAC)   Certificate in Product Ownership Analysis (IIBA®-CPOA) Certification in Business Data Analytics (IIBA®- CBDA) I have passed both AAC and CPOA exams and they were not easy. I will do CBDA certification later and I will probably add the ECBA to th...

I I was giving a presentation to a manager deciding the path for his projects. For me Oracle database and APEX is the obvious choice. I really can't recommend anything else. You can read a lot about both on the official pages, specially on features , new features  and why to chose APEX . But coming from other databases and programming languages I have to highlight following points. Focus on solving business issues First of all, APEX is a true low code platform. Less code means less time , which means less complexity (and less bugs , less technical debt ), which translates to less cost . It is secure and responsible out of the box. You can build apps very fast, not just the prototypes, but the full apps. Not in months, but in weeks if not days. I always suggest to my clients to use the native APEX components as they are if they can. This way, you safe even more time and the outputs across applications will be consistent. The more time your developers save on mundane jobs, th...

Y Yup, you can get free certification  Oracle Cloud Data Management 2022 Foundations Associate (1z0-1105-22), probably with just the brown badge (update: it is blue) and it will expire in 18 months, but it is an opportunity to learn something new and validate it for free. Study materials are available on OLS, under  Become an Oracle Cloud Data Management Foundations Associate , they are 6 hours long. This learning path gives you the insight not only to Oracle's data management strategies but a complete solution for modern application development and data transformations. Learn how Oracle's cloud data platform extends and complements its core databases. Understand how to architect data management platforms that work together to simplify and maximize productivity, while reducing cost and improving performance, reliability, and security. Exam topics: Data Management Introduction Explain Oracle's Data Management Strategy Discuss the different Oracle database offerings and depl...

W We have a new cloud exam available: Oracle Cloud Infrastructure Data Science 2022 Professional (1z0-1110-22). The 2021 ML certification is retiring in August and this is probably the replacement. Update: the official article here . These are the exam topics: Configure your tenancy for OCI Data Science Discuss OCI Data Science Overview & Concepts Discuss Accelerated Data Science (ADS) SDK Capabilities Configure your tenancy for Data Science Design and Set up OCI Data Science Workspace Create and manage Projects and Notebook sessions Create and manage Conda environments Store credentials via OCI Vault Configure and manage source code in Code Repositories (Git) Implement end-to-end Machine Learning Lifecycle Discuss ML Lifecycle Overview Access data from different sources Explore and Prepare data Visualize and Profile data Create and train models using OCI and Open source Libraries Create and Use automated ML capability from Oracle AutoML Evaluate models Obtain Global & Local ...

O On rare cases I want to process selected rows in grid even if they were not changed. And I want to process them through standard grid handler, not via customized AJAX callback. Just like you would changed the rows and pressed Save. I would like to add a button Save Selected to the grid after the Save button. But not to all grids. To achieve this you can use CSS classes on the grid. In JavaScript I will check if grid has the SAVE_SELECTED class and only then I will add the button. No need to hardcode static id. Lets create a table first and a simple trigger just for demo purposes too verify that selected rows were actually changed: CREATE TABLE settings2 ( setting_name VARCHAR2(30) CONSTRAINT nn_settings2_id NOT NULL, setting_value VARCHAR2(256), -- is_numeric CHAR(1), is_date CHAR(1), -- updated_by VARCHAR2(30), updated_at DATE, -- CONSTRAINT pk_settings2 PRIMARY KEY (sett...

I I would like to notify users visually that they have some unsaved changes in grid. When page loads user will see a grid with normal Save button. Then he make some changes and immediately Save button becomes Hot (green in my case) which signals to him that there are some unsaved changes. I do it via JavaScript mutation observer feature, I am waiting for Edit button CSS style change. When this happens, I will change Save button class to hot. Just run the following code when page is loaded. var observer = new MutationObserver(function(mutations) { mutations.forEach(function(mutation) { var $target = $(mutation.target); if ($target.hasClass('is-active')) { var $save = $target.parent().parent().find('button.a-Button.a-Toolbar-item.js-actionButton[data-action="save"]'); $save.addClass('is-active'); //observer.disconnect(); // remove observer when fired ...

I I want to have the same look for all my grids. I used to modify grids via JavaScript Initialization Code section (Attributes - Advanced). But doing the same changes on all grids was not maintainable. So I switched to a common function to modify the config object (on single place). But I was still stuck with the need to copy the same code to init sections on all grids. Fortunately you can create a common function to modify all grids at one place without the need to modify the init sections. This is how the original grid buttons looks like (Edit, Save, Add row, Reset): This is what I want to achieve (modify all grids without even touching the init section): Hide Edit button (but keep the grid editable) Make Save button hot (green) when grid is modified (I will cover this in next article ) Make Add row as "+" button Hide Reset button at the end Add pagination setup to alter default page size Add new button to store refresh grid data Add new but...

Y You already faced this issue. You need to change/remove function and you don't know where it is used. Or you need to know if the functions is even used somewhere. Oracle provides us with user_source/all_source views where you can easily search source code of procedures, functions, packages and triggers. But views are tricky, there are no such objects for views. Fortunately you can create your own from view definition stored as LONG in user_views.text column. Lets create a table which will store the view source lines: CREATE TABLE user_views_source ( owner VARCHAR2(30) CONSTRAINT nn_user_views_source_owner NOT NULL, name VARCHAR2(30) CONSTRAINT nn_user_views_source_name NOT NULL, line NUMBER(10) CONSTRAINT nn_user_views_source_line NOT NULL, text VARCHAR2(2000), -- CONSTRAINT pk_user_views_source PRIMARY KEY (owner, name, line) ); -- COMMENT ON TABLE user_views_source IS '[CORE] ...

C Current APEX certifications will be retired in 3 months. The recent (and not very popular) certification Oracle APEX Cloud Developer Specialist | 1Z0-760 retiring on Oct 30th 2022 and much older Oracle Application Express 18: Developing Web Applications | 1Z0-750 on Oct 14th 2022. It seems like rumors are true and we will indeed get 2 new certifications. Foundation level for free and probably APEX 21 Specialist for the usual price. Stay tuned.

F For years I was stuck in an endless loop. Change the JavaScript file, upload to APEX, refresh the page, hope it works, repeat. There were few unofficial solutions developed in the past like APEX Builder Extension by FOS (I think it was a work of Stefan Dobre ) or APEX Nitro utilizing Node.js. Recently I was recently notified about the Chrome extension Resource Override . To set this up you will need to do this 4 easy steps: get this extention to your Chrome or Firefox browser create a rule to override your (.js, .css or any kind of) file create local server to serve your file avoid file caching in APEX First two points are covered on the project page above, just a note that syntax is "glob like" syntax. Basicaly it is similar to LIKE in SELECT, you just use "*" instead of "%". For example my rule for Oracle cloud server look like: Tab URL: *.eu-frankfurt-1.oraclecloudapps.com* From: *global.js* To: http://localhost:8080/workspa...

I It was my first on-site APEX conference. I went from Prague by bus on Thursday at 2am to meet with Foex guys at Vienna to join their party bus. To meet at least few APEX enthusiasts before we arrive. I have to move across the city to to Foex headquarters and I used my Onewheel to get there in 90 minutes and 10% battery left. That was fun. All guys at Foex were/are very friendly and the journey to Maribor went well. We talked a lot. I met Peter, Matt, Karen, Frank, Fanel, Zsolt, one guy without LinkedIn profile and Dietmar. I arrived at my hotel, put the Onewheel to the charger and went to bed for 2 hours. Then I went to explore Maribor. Drove along the river on both sides, went to the old town and didn't crashed. Beautiful city. Then I went to the first session with organizers and Patrik Wolf. That was very relaxed, very friendly, entertaining and also enlightening. The community around APEX is really something special and it is an honor to be part of that. Here are some i...

I I have passed the PSU I exam today and I am now on of the 1994 proud owners. It was a great extension od  PSPO II certification I did last year. The main goal was to extend my knowledge to the UX area and to look at things from the design perspective. I highly recommend reading these books even if you don't want to pursue this certification: Lean UX  by Jeff Gothelf The Lean Startup  by Eric Ries UX for Lean Startups  by Laura Klein

I I have been asked by my (young) boss if I am doing my job on 100%. After realizing he was not joking I felt betrayed. I felt just like a resource being drained and not as a human. He continued with even more (stupid) questions and accusations directly claiming that there is a problem on my side (without any evidence) and making the issue worst. I was just thinking what is his motivation and why is he doing that. Does he want me to quit? What does he expect from me? After this kind of pep talk I will be doing overtimes for free again? Then I was thinking about our previous talks about work life balance. What a pile of rubbish. Anyway, it somehow settled after few more unpleasant meetings and I didn't left even though I had better job offers and I probably should have. After few weeks my peer developers are targeted. Being questioned if they really give 100% to the company. Is this real? Does anyone really expect that 100% is even possible? And that this is the goal to...