Skip to main content

Posts

New APEX certification, Oracle APEX Cloud Developer Professional

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
Recent posts

Bulk components discovery (for auth purposes)

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',

APEX interactive grids health check

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

APEX interactive grids & Invoke API with zero arguments

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

One authorization scheme for APEX interactive grids

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

One authorization scheme to rule them all

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

APEX Alpe Adria 2023

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.

Oracle 23c Free

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.

APEX page item computations and processes under control

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

The magic of dynamic views in APEX

E Everyone should know the power of WITH clause these days. For me this is a great way how to have more readable views and with some magic it also allows me to have dynamic views (well, the views returning different rows based on page items). You are asked to show a monthly based calendar on a page (and let's skip the Calendar region for demo purposes) based on a page item. Here is how you can utilize WITH clause on a regular query (returning a calendar data for a whole year): WITH days AS ( SELECT TRUNC(SYSDATE, 'Y') + LEVEL - 1 AS day, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'IYIW') AS week, TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month FROM DUAL CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y') ) SELECT d.month, d.week, MAX(DECODE(TO_CHAR(d.day, 'DY'), 'MON', TO_NUMBER(T