Snippets and Thoughts of One Oracle Developer

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

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

T This is a followup for part 1 which was mainly about setting up the SQL Developer interface for more efficient work. In this part I would like to cover tips for developers. Shortcuts mentioned in these articles might not be the default ones. My bonus tip on shortcut is: if you can't remember shortcut, then change it to what you find intuitive. Editor First weird thing I see all the time is missing line numbers. It is so much easier to navigate through the code especially and cooperate with others when you actually can use line numbers as references. Turn that on in Code Editor - Line Gutter. Also you can quickly jump to specific line by pressing [ctrl+g] (like goto). I use text transformations a lot. Keywords to upper case, identifiers to lowercase. I try to setup auto format as close as possible to my desired look, but mainly I use it just to format legacy code to understand it better. You can download my beautifier setup at the end of this article. To quickly fix ...

H Here are some tips for my fellow developers using SQL Developer . The web version is getting a lot of attention and getting better every day, however I use the desktop version and this article is about that. For anything about SQL Developer you should visit Jeff's blog , it is a bottomless well of wisdom. Cleaning up I like simple tools. I don't like clutter and too much options (like Toad), it is distracting. So lets close the panels you don't need. Are you really using Reports panel? If not, close it, you will have more space for Objects Browser and panels you really use. On the left panel I use Connections, Find Database Objects and SQL History. You can turn them on or off in View menu. I also use Bookmarks and Code Outline at the bottom, but just when I need that. At the bottom I use Compiler Log, Bookmarks, Code Outline, but I am also closing these when they are not needed. There are more panels, you should explore options under the main menu View, you mi...

I Imagine a table with products. For simplicity lets use just product name and description. CREATE TABLE products ( product_id NUMBER(10), product_name VARCHAR2(256), product_desc VARCHAR2(4000), -- CONSTRAINT pk_products PRIMARY KEY (product_id) ); -- DELETE FROM products; INSERT INTO products SELECT 1, 'First product name, one', 'Some description' FROM DUAL UNION ALL SELECT 2, 'Second product name, two', 'Another description' FROM DUAL UNION ALL SELECT 3, 'Third product, three', 'Other description' FROM DUAL; -- COMMIT; How would you search for a product with specific words in name and description? If you want to search for products containing 'First' OR 'one' words in name, then it is quite easy (case search and sanitization of inputs not included). WITH input AS ( SELECT /*+ MATERIALIZE */ 'First:one' AS in_name, ...

U Usually I see (and do) evaluations at the start of the procedure. When you check for multiple things, it can get lengthy and the reusability of the code is very low. Does this look familiar? DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs IF in_user_id IS NULL THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MANDATORY'); END IF; -- IF LENGTH(in_user_id) < 3 THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MIN_LENGTH'); END IF; -- IF NOT REGEXP_LIKE(in_user_id, '@') THEN RAISE_APPLICATION_ERROR(-20000, 'USER_ID_FAILED'); END IF; -- continue with your code NULL; END; / Imagine this instead: DECLARE in_user_id CONSTANT VARCHAR2(30) := 'USER_NAME'; BEGIN -- check inputs assert.is_not_null('USER_ID_MANDATORY', in_user_id); assert.is_false('USER_ID_MIN_LENGTH', LENGTH(in_user_id) ...

I I have been ask to implement Stripe payments into APEX. I see two options here. You can either do it in JavaScript or in PL/SQL. You can easily call a web service from PL/SQL, you wont need any extra JS library and code would be probably more secured. So let's do that. You can find several other articles about this around. If you saw those, you can evaluate yourself on how is this different. The easiest API choice on almost every payment gateway is called "checkout" or "checkout form". The beauty about this is that there are no sensitive data processed on your side, everything is processed on the payment gateway side. No worries about storing or processing client cards. You should check the Stripe Checkout and specially Quickstart . Stripe (and others) like to show the user a list of products with quantities and prices during the checkout. Hence you need to create products on Stripe and you have to do that everytime you add (or change) a new produc...

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