Snippets and Thoughts of One Oracle Developer

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

L Last week I have bought this book: Pro Oracle SQL Development: Best Practices for Writing Advanced Queries by Jon Heller. So far I did read all chapters from part III - Write Elegant SQL with Patterns and Styles. I recently published two articles about similar topic as APEX Blueprint , so I wanted to see how my world view align with the author. Here are some excellent quotes from the book, specifically chapters 11. Stop Coding and Start Writing and 13. Write Beautiful SQL Statements. We must spend extra time upfront to make our code easier for developers later. Making our code readable encourages other people to use our code and will even help our future selves. It’s good to program with a sense of idealism and beauty, but we should try to keep the dogmatism out of it. After a while we should all evolve a sense of when code “just looks right.” Eventually we’ll know that when our code looks right, it will run right. But sometimes we need to ignore our feelings and stop ins...

T There is an article (more like a reply from grid master John Snyders) about how to enable cell copy from interactive grid . It has some limitations, at least for my use case. Some grids don't have static_id, it ignore some cells (with HTML code) and I want all grids behave the same without putting anything into the JavaScript initialization block. I have spent too much time researching how to do that properly and then I wrote this code. Just put it to onload event in your app, no need to adjust individual pages or grids. document.addEventListener('copy', (event) => { event.clipboardData.setData('text/plain', $(document.activeElement)[0].innerText || window.getSelection()); event.preventDefault(); }); When you select a cell in a grid and copy it to clipboard, the text value from selected cell will be copied no matter the cell type (read only, display only, link...). You can still do partial text selections on input fields. And you can/will copy a...

I I had to switch to using materialized view for my custom navigation because quering dictionaries on free cloud tends to be too slow. So now I have a button Publish changes to refresh this MVW and it takes some time. I don't want to keep user waiting while this is done, so I refresh it via background job. Which brings a problem. How to inform user about the result of the background job? Websockets for poor. I created application process AJAX_PING and call it in Javascript each 6 seconds. This lightweight process just checks content of user_messages table which is filled through app_actions.send_message procedure . So user click on the Publish button, which triggers DA, which starts background job for MVW refresh, disable the button and show user a message confirming start of the operation. User can wait on the page or freely move through the application. 20 seconds later procrastinating on a different page a result arrive: I think this is a very user friendly way...

A A friend of mine told me he use DeepL translation service for his translations. So naturally I implemented it into my app. Here is how I did it. You have to create account on DeepL , free account is enough, at least for me. Then you need to get your auth key and use it in code below. FUNCTION get_live_translation ( in_text VARCHAR2, in_lang VARCHAR2 ) RETURN VARCHAR2 AS l_secret CONSTANT VARCHAR2(64) := 'YOUR_AUTH_KEY'; -- r VARCHAR2(4000); BEGIN r := APEX_WEB_SERVICE.MAKE_REST_REQUEST ( p_url => 'https://api-free.deepl.com/v2/translate?auth_key=' || l_secret || '&' || 'text=' || UTL_URL.ESCAPE(in_text) || '&' || 'target_lang=' || in_lang || '&' || 'source_lang=' || 'EN', ...

Y You probably know the official way how to translate APEX apps . You most likely also know ho to translate APEX itself or how to add unsupported languages . For me, translating my apps the official way is too slow and complicated. I often have requirements to translate just the most important things in the app, so people without english knowledge can get around. There is no need to translate every bit. Which is ideal for utilizing APEX items. Alternative Lets explore this alternative. The main idea is to use APEX items instead of texts and translate these items based on user preference (or browser language when preference is missing). The main issue with this approach is the time needed to create and maintain these items. And also the item clutter madness and minimal item reusability. Who likes to have tens to hundreds items on page? Check the source for this translated page, you should notice that I don't use item prefixes (for translation items to make code r...

I It is terrible what is currently happening in Ukraine. I would never say I will live on the edge of the war. Our nation was occupied by germans in 1938, 1939. The land was taken, lifes were lost. Than it was occupied by soviets from 1968 to 1991. We feel that devastation to this day. Specially the devastation of human character. Constant lies and propaganda. We remember. We feel the aggression from Russia as real threat. I wish EU would intervene swiftly and harshly, empty words will not help. Where is NATO? Where is ammunition for Ukraine? I decided to help Ukraine also professionally. If you are from Ukraine, affected by the war with Russia and you would like to start a career as an Oracle Developer or Oracle APEX Developer , then please reach out to me. I will try my best to help you build your career for free. My help is available in english or czech although. Fight back Ukraine.

O One of the most overlooked features of Oracle since Oracle 10g? DML error logging. When you insert/update/merge/delete data in a table, you might get an error if you are trying to insert string into a number column, if the string is too long, if the key is not valid... You will get an error message but you don't exactly know which column and value caused this problem. That's where you can leverage this feature. All you need to do is to: add 1 line at the end of your DML statement create error tables If you already know how this works, scroll down to My problem section, there is the twist. Magic line The one line you have to add looks like: LOG ERRORS INTO table_name_erros (your_tag) So your INSERT became like: INSERT INTO roles (role_id, role_name) VALUES ( 1, 'DEVELOPER' ) LOG ERRORS INTO err$_roles (123456); Same goes for other DML operations. Simple change in your code and huge difference on error. Error tables To creat...