Skip to main content

Posts

Interview questions - APEX

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
Post a Comment
Read more
Recent posts

Borderless & dynamic background colors in Interactive Grid

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
Post a Comment
Read more

Database versioning for feature branches

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
Post a Comment
Read more

Converting CSV data to MERGE statements

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, ''
Post a Comment
Read more

Investigating APEX apps and pages

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
Post a Comment
Read more

Generate functions for package constants

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
Post a Comment
Read more

Database versioning with data, APEX apps and migrations

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
Post a Comment
Read more

Be a better developer (or PO) with BA certifications

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
Post a Comment
Read more

APEX acknowledgement

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, the mor
Post a Comment
Read more

Free certification - Oracle Cloud Data Management (1z0-1105-22)

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
Post a Comment
Read more