Snippets and Thoughts of One Oracle Developer

O One of my colleagues (lets name him Max) prepared a surprise for me. I had no idea where is the JavaScript code being placed on page. Turned out, there is an API for injecting JavaScript code from PL/SQL and you can do the same with CSS. That is not just an excellent hiding place, but it can be used if you need to attach these on page dynamically. Checkout the APEX_JAVASCRIPT api, especially the ADD_ONLOAD_CODE procedure. For example you can set the JavaScript variable from the PL/SQL. BEGIN APEX_JAVASCRIPT.ADD_ONLOAD_CODE ( p_code => 'variable_name = new_value;' ); END; / Better version would be to escape the value using ADD_VALUE (which is an equivalent of APEX_EXEC.ENQUOTE_LITERAL but for JavaScript) or APEX_JAVASCRIPT.ESCAPE function: BEGIN APEX_JAVASCRIPT.ADD_ONLOAD_CODE ( p_code => 'variable_name = ' || APEX_JAVASCRIPT.ADD_VALUE('new_value', p_add_comma => FALSE) || ';' ); END; / ...

T There are plenty of things you can do on global page. Not everything is a great idea. Typical usage would be: Regions used on multiple pages Page items used through multiple pages JavaScript and CSS styles Dynamic actions Footer with some extra info Regions Typically it is some sort of navigations or filtering and that is perfectly fine. You should be aware that you can also create same region with source as "Function Body returning SQL Query" or by using "Dynamic Content" region (and ofc. having content in package, not on page). For complex components you could also create your own plugin. This is useful to know specially if you want the same content on different places on page. JavaScript and CSS styles It might be ok to have scripts and styles on global page, but unless you need them just on specific pages (or you want to apply server side conditions to them), I would rather store them in application files. Or even better in worksp...

I I wanted to keep selected tabs saved for the user, but reset them on some event. Typical use case is that user open page for specific record, the page has multiple tabs and he switch to not default tab. Here starts the issue. When he switch to a different record, it is better to start on default tab, but if he submits the page, it is better to preserve selected tabs. I have figured out that it is stored in session storage and I have come up with this solution. When the page is loaded with clear flag, then I also remove all tabs on page, otherwise I don't touch it. const reset_tabs = function() { if (window.location.search.includes('&clear=')) { $('div.t-TabsRegion.js-useLocalStorage').each(function() { var region_id = $(this).attr('id'); var key = 'ORA_WWV_apex.apexTabs.' + apex.env.APP_ID + '.' + apex.env.APP_PAGE_ID + '.' + region_id + '.activeTab'; var v...

W When the mega menu came out, I was very pleased. Then I tried to use it and quickly abandon it. Now I wanted to cleanup my menus so I build my own. What I wanted was to have the several multicolumn menus, not just one. With icons, badges, different levels. I also wanted to manage where exactly it will split to the new column... And without any JavaScript. So I can create something like this: Check the article about custom navigation for some basics. How I achieved this? changed template for navigation created view to feed the template add a column value to the navigation table The template is basically just concatenating the arguments from the list, something like: #A08#<li #A10#>#A01##A02##A03##A04##A05##A06##A07#</li>#A09# The view (source for the list is returning these #A0?# fields: SELECT n.lvl, n.attribute01, -- content <li>...</li> n.attribute02, n.attribute03, n.attribute04, n.attribute0...

S Sometimes I need to login as a specific user to verify what he/she can access and see. And most of the times I would like to login as me with a single button. So I created login page like this: So I can signup with any user I like or use the Google button to sign up via Google SSO. In corporates we would typically use Microsoft 365 SSO. To make this more secure, the login field, button and process behind are visible only to developers (users signed up in APEX Builder) thanks to my core.is_developer function used as authorization scheme on these components. You should also disable the login field, button and process on production. Best approach would be to assign a Build option and disable it. Or/and you can use the SSO authentication as default there. If I am not signed in APEX Builder, I will not have the option to sign as anyone: And here is the trick how to do this: Create Google SSO authentication Enable Switch auth scheme on your open door and SSO au...

I I was very tired of filling out the forms for signing into the APEX Builder, so I decided to use the SSO there too. I wont cover how to get the credentials , but here is a great step by step article how to do that. So assuming you did setup your SSO credentials, log into INTERNAL workspace as ADMIN user. This could be the last time you have to sign in with a password, isn't that cool? At the top click on Manage Instance and Security and consider following changes: on Security tab change Allow Persistent Auth to Yes on Authentication Control tab under Development Environment Settings change Require User Account Expiration and Locking to No under Development Environment Authentication Schemes edit the SSO option To configure Google SSO, fill the fields carefully like this (use your own credentials obviously): Scope = profile,email Username = #email# Additional User Attributes = email,name For different providers parameters would var...

A All APEX developers know that you can develop prototypes really fast. Many times even the whole applications. But sometimes, the expectations from your stakeholders can be very different then what it is possible to do in APEX in reasonable time. Sometimes, the requirements you receive might be weird from your point of view. As a developer, you should think about extending your knowledge to the product world. To think about apps you build from the product/customer perspective. Don't thing too much on the features and technical side, think more about benefits for users and solving users problems. There are some excellent resources on the market and you can get most of them for free. In just few hours of your time you might change your perspective how to develop software. There is a great course and bottomless well of wisdom about building products from Pawel Huryn . Just subscribe to the free (or paid) newsletter, explore the blog or/and go for Continuous Product Discovery M...

I I had to plan a trip to Iceland. Previously I was drawing timelines on paper to make sure I can get there in time, to utilize my time on the location and to avoid any overlaps. But with this trip I knew it will be too much redo, so I thought about doing it in APEX... The Plan I need a grid to input the data and I need a Gantt chart to render it as timeline. Thats it. I had a fully functional prototype in 2 hours (I was stuck for a while on the chart). Since that I added some small features, like: daily overview so I can check (and eventually print) just specific days customizable colors, per event/stop category with event/stop level override added costs and flags so I can keep track of things I need to pay or do clickable chart (thanks to Hari), so I can click on event on chart to adjust it dynamic chart height (thanks to Hari) based on the number of categories used on trip I am planning to add maps thanks to suggestion from Jayson . Finally, this is t...

I It is the time of the year when ACEs for next season are announced. It was hard to get into this program, it is a bit easier to stay in it. I had to collect 100 points and I did managed 360, most of them through my blog. Hopefully next year I will reach to ACE Pro and get the few things I miss. So far I am contributing through my blog for over 2 years (and I have just reached the 100k views), and on GitHub a bit longer through my several open source projects. Being an ACE motivates me to write a better content, to not get satisfied with quick articles and don't go for easy topics. Basically I have to produce something I can be proud of. And that is great. ACE benefits You can explore the official list of benefits under Why join section. What I got from the ACE program? I can't say it was life changing, at least not yet, but: I can brag about being ACE I am part of the great community, I can reach out to these people more easily and I did connected ...

Y You have a lot of valuable insights in APEX logs and you most likely don't realize that. And that is because you don't have a tool to display this data in a meaningful way. Some things you can extract from the apex_workspace_activity_log table: average or maximum times for each page for several actions (rendering, processing, AJAX calls...) number of requests, so you can identify highly used pages (or not used pages) number of users and sessions as a complementary metrics you could also extract browsers, IP addresses, buttons clicked, errors... There are other things you can track. I love heat maps. So I created this mini app, which will allow you to explore the data from apex_workspace_activity_log as a heat maps. I use whole month data and the T1..T30 columns represents dates in past, T1 as yesterday, T2 as day before yesterday... You just have to set the colors and thresholds to suits your needs. What I created: simple page with IG to ...

A As part of the Race to Certification Challenge you can get OCI Foundation(s) certifications for free. Why you should bother? I think many people missed the opportunity you have here. The obvious one is that you learn a lot about Oracle Cloud in a short time. The courses are free and actually great, certifications are free and kind of easy, exams are not proctored and you have 15 free attempts per each. It is basically impossible to fail. I am interviewing a lot and I would certainly appreciate a candidate even with these type of certifications. It is still better then nothing. (free) Foundation courses: OCI Foundations , 5h course Oracle Cloud Data Management Foundations , 6h course You don't need to do the labs (although I would recommend that), so you can see the time needed to prep is very low. You can do each exam in just one day. Hidden benefit The not so obvious benefit is, that when you pass these 2 exams, you will actually get vouchers for a...

A After two years there is finally a brand new APEX certification going to be released tomorrow (on June 1st), the Oracle APEX Cloud Developer Professional , exam number 1Z0-770. Update June 1st From today, you have 3 month to get this certification for free , check the rules . I did pass the exam today, I like to pass exams when they are fresh. I have faced just few weird questions and I disagree with answers just on 2 questions. Be aware that some questions are asking for a statement (not statements) but they show answers as checkboxes, so don't be lured to select more then one. Compared to previous APEX exams this one is focusing on recent features, questions and answers are more clear. I have enjoyed the exam. Certification There is already available learning path on Oracle MyLearn as a recommended study material. It has 13 hours and 19 interesting skill checks (short quizzes) to verify your knowledge and prep you for the real test. Usually there is also a sh...

I I have touched the component discovery topic in the One authorization scheme article . I did created a table tsk_auth_components which store application components and as they are discovered (requested for the first time) they are added to this table. This might not be enough. Today I will show you how to discover all components at once. In a tsk_auth_components_list_v view I have compiled a list of APEX views containing authorization_scheme for current application (the component name is missing, but that is not the important at this time): SELECT 'APEX_APPLICATION_BC_ENTRIES', breadcrumb_entry_id, NULL, component_signature FROM apex_application_bc_entries WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_COMPUTATIONS', application_computation_id, NULL, component_signature FROM apex_application_computations WHERE application_id = &&APP_ID UNION ALL SELECT 'APEX_APPLICATION_LIST_ENTRIES', ...

O On my projects I usually use a lot of grids to customize the app - handle the users, roles, navigation, LOVs, settings... so I create a page, which lists all IG regions and performs some checks, to make sure grids are setup in a way I like it. If you hover on the orange warning icon it will actually show you the message describing what is wrong. You can see the list of grids, source, target (procedure handling the grid changes and table behind) and some other checks. Things I check: region source type (T) - I prefer to use the view, so I check for that and for the name, if the view contains same page id grid process/handler (T) - Invoke API is my new best friend, PL/SQL code is fine... I also check if the process name match the procedure name and if the package name contains same page id passed/submitted page items (P) - I also check if I pass every page item which I use in the view, to make sure that I have proper grid refresh authorizations - check authoriz...

I Invoke API is available since APEX 22.2, since all my projects upgraded recently I can finally start using it. For me it was one of the greatest news introduced in APEX 22.2, not much invisible, but allowing me to shrink down Embedded Code report significantly. If you are into classic passing of arguments, checkout this Invoke API article from Jon Dixon . I will be talking about a more minimalistic approach. I am a huge fan of storing logic in PL/SQL packages and views. This Invoke API feature allows me to just reference the procedure same way as I can reference view in grid, as a reference, not as a written code which need to be parsed and maintained. And this is not limited just for grids. What I did in the past created view and assigned it to the grid, preferably via object reference (region source type = table/view) created procedure to handle the changes created caller to pass arguments from grid to the handler hoped that arguments wont change - I wou...

I In previous article I have showed you a way how you can manage the whole application security through user defined roles (and I mean defined by business users not by developers). Today I will show you how you can achieve the same with interactive grids. How you can use the same IS_USER role to manage which DML actions will be allowed on the grid for specific application role. I will be using the Tasks application and demonstrating it there. The security for grids is assured on multiple levels: AUTH_PAGES - protects access to the pages and page visibility in navigation (protecting access to the whole page with grid) AUTH_COMPONENTS - protects access to the individual page components (protecting access to the grid region, grid handler, specific columns, related DAs...) AUTH_TABLES - protects against unauthorized DML actions inside of the TAPI procedures, but also on a grid through dedicated IS_USER_C|U|D autorization schemes AUTH_PROCEDURES - protects access ...

I I was working on a project, where we have multiple pages with multiple regions, multiple business roles and very complex requirements what to show and allow to do to these specific roles. So we implemented one authorization scheme per one business role and map them to regions, items, buttons, columns, processes... As the requirements grew, we had to add merged auth schemes like role1_or_role2, role1_except_role2 to cover overlaps, since APEX allows us to have just one autorization scheme asssigned. Doing this with 2-3 roles is fine, doing that with 10+ complex roles is a nightmare especially when the requirements are changing all the time. Well, I wish I had used the component based authorization from the start. And this is not a new feature, this is available since APEX 5! You basically create an authorization scheme, set the caching to Always or Per Component. When you do this and select type PL/SQL fn. returning boolean, APEX will pass extra values to your function: RETURN is...

I I love this conference. Last year was awesome, this year was awesome and I bet the next year will be awesome too. Even though Peter's minions were not present and I was hoping to meet them. Common boys, stand up to your boss and come next year. Things I remember after two weeks: Future of APEX is promising Future for APEX developers is promising And so many great things ahead... just imagine 100 talented developers enhancing APEX every day Eye opening presentation about UX and how you should care about your users by Juergen Great talks with Lino and his presentations on Security and JavaScript I was thinking about presenting there myself and I might actually do it next year... Thanks to all guys who made this conference happen, I have enjoyed it.

O Oracle 23c Free is out. I am so excited about this, because it is marking a great shift. Usually we got just few new features and improvements after several years of waiting. Now it seems like it is more focused on the developers. A huge improvements, released firstly to developers and later to customers. Exciting. So we have great and always free Oracle Cloud option and now we have Oracle 23c Free as an on premise option. How cool is that? What to love? Boolean data types in SQL (finally) Aliases in GROUP BY and HAVING clauses Conditional DDL (IF NOT EXIST) Simplified data creation End of DUAL , I will miss this little quirk SQL subtypes (domains) and huge JSON improvements You can check Tim Hall's articles on 23c. You can download prebuilt VirtualBox image from Oracle, also with SQLcl 23.1 and APEX 22.2 or Tim's Vagrant build and this one also covering Docker by Gerald.

I I was experimenting lately with page items and how to setup APEX page (preprocessing section) in more cleaner, reusable and generic way. I also wanted to move the logic from APEX to PL/SQL packages to fulfil my MVC vision (to keep as much as possible code in PL/SQL, not in APEX). So I made this up. For every page in application I have created a package, "P0" for page zero, "P100" for page 100... For 100 pages I would create 100 packages, but that is still better then have the logic scattered on individual pages. And most of the times I have to create packages anyway because I need a place where to store form/grid handlers and page related logic. Then I have created INIT_DEFAULTS procedures in each of these packages. And in this procedure I setup page items and run the pre-rendering processes. Since we are talking about packages, I can reuse other procedures and function and limit the amount of copy pasted code. I use my wrapper around these session state call...