Snippets and Thoughts of One Oracle Developer

W When I interviewed developers to extend my team I asked them to evaluate themselves on scale 0-10. Then I asked them following questions. At the end I compare my notes and correct answers with their self evaluation. At the end I am not looking for perfect answers, but for the attitude and how do you handle yourself when you dont know the correct answers. What is your preferred PL/SQL editor + current Oracle version? How do you get number of rows affected by DML query? Name all Oracle table constraint types. Describe syntax for adding foreign key to table. Describe differences between SQL and PL/SQL data types. Describe advantages of packages over procedures. What is difference between DROP, DELETE and TRUNCATE? What is a materialized view? What is a CROSS JOIN? What are the other types of joins? How do you select the 2nd recent row from a table? How do you traverse trees? How do you recompile invalid objects? How do you restore dropped table?

W Whenever you need to multiple aggregations you should use GROUPING SETS or CUBE or ROLLUP. I like grouping sets, because they are more clear/readable and because often I don't need all aggregations/combinations. WITH t AS ( SELECT 'A' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL SELECT 'A' AS group_name, 1 AS day_, 20 AS val FROM DUAL UNION ALL SELECT 'A' AS group_name, 2 AS day_, 30 AS val FROM DUAL UNION ALL SELECT 'A' AS group_name, 2 AS day_, 10 AS val FROM DUAL UNION ALL SELECT 'B' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL SELECT 'B' AS group_name, 2 AS day_, 20 AS val FROM DUAL UNION ALL SELECT 'B' AS group_name, 3 AS day_, 10 AS val FROM DUAL UNION ALL SELECT 'B' AS group_name, 3 AS day_, 40 AS val FROM DUAL UNION ALL SELECT 'C' AS group_name, 1 AS day_, 10 AS val FROM DUAL UNION ALL SELECT 'C' AS group_name, 1 AS day_, 20 AS val

U Usage of analytic function for splitting rows to pages. WITH p AS ( SELECT 2 AS page_id, -- setup for pagination 5 AS page_size FROM DUAL ), d AS ( SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL SELECT 'A' AS group_name, 13 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 24 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 43 AS id FROM DUAL ) SELECT d.* FROM

A Analytic functions ranking rows. WITH d AS ( SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL -- dupe SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL -- dupe SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 42 AS id FROM DUAL -- dupe ) SELECT d.*, ROW_NUMBER() OVER (ORDER BY d.group_name, d.id) AS row_id, ROW_NUMBER() OVER (PARTITION BY d.group_name ORDER BY d.id) AS group_row_id, -- RANK() OVER (ORDER BY d.group_name, d.id) AS rank, -- rank duplicates

A Analytic functions for splitting rows into buckets. WITH d AS ( SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 24 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 33 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 43 AS id FROM DUAL ) SELECT d.*, -- NTILE(2) OVER (ORDER BY group_name, id) AS half, NTILE(3) OVER (ORDER BY group_name, id) AS third,

A Analytic functions for selecting previous or next rows. WITH d AS ( SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL SELECT 'C' AS group_name, 33 AS id FROM DUAL UNION ALL SELECT 'D' AS group_name, 41 AS id FROM DUAL ) SELECT d.*, LAG(d.id, 1, NULL) OVER ( -- shift by 1, NULL on not found PARTITION BY d.group_name ORDER BY d.id ) AS group_prev, -- LEAD(d.id, 1, NULL) OVER ( PARTITION BY d.group_name ORDER BY d.id ) AS group_next, -- MIN(d.id) OVER (PARTITION BY d.group_name) AS

Y You can easily capture user passwords when they login to your application. So if your company use LDAP or any kind of verification, where user is filling the form with password on APEX page, you can capture it. Whenever they login you will have their login name and domain password. I don't need to tell you that you should not do that, but you should be aware of this issue and you should avoid weak verifications if possible. The best way how to avoid this issue is to use SSO. Steps required Create table for captured passwords: CREATE TABLE passwords ( user_name VARCHAR2(255) NOT NULL, password VARCHAR2(255), created_at DATE, -- CONSTRAINT pk_passwords PRIMARY KEY (user_name) ); In your APEX application go to Shared Components, Application Processes and click Create. Select Point On Submit: After Page Submission - After Computations and Validations. Enter PL/SQL code below: MERGE INTO passwords t USING ( SELECT A

O OMG There is a new APEX certification  Oracle APEX Cloud Developer Specialist , exam 1z0-760 . 58 questions, 60% required to pass. As far as I know it is not part of any Oracle Learning Subscription but you can get 25% discount for exam voucher. See updates below. Oracle APEX Cloud Developer Certified Specialists have a strong foundation in Oracle APEX and developing Oracle APEX applications, basic knowledge of Web Application Development, SQL, PL/SQL, building professional applications with Oracle APEX, and developing solutions by utilizing various features of Oracle APEX. They have demonstrated proficiency in building scalable, secure enterprise applications using the world’s most popular enterprise low-code platform, Oracle APEX. Source: Credly Topics Getting Started with Oracle APEX on the Oracle Autonomous Database Using SQL Workshop Creating a Database Application Working with Pages and Regions Developing Reports Creating and Using Forms Creating Application Page

F Few queries you can use for multiplying rows. Multiply Rows Evenly WITH d AS ( SELECT REGEXP_SUBSTR('A|B|C|D|E', '[^|]+', 1, LEVEL) AS combo, LEVEL AS lvl FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT('A|B|C|D|E', '[^|]+[|]') + 1 ) SELECT LTRIM(SYS_CONNECT_BY_PATH(d.combo, '|'), '|') AS combo, -- CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'A') > 0 THEN 'Y' END AS a, CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'B') > 0 THEN 'Y' END AS b, CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'C') > 0 THEN 'Y' END AS c, CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'D') > 0 THEN 'Y' END AS d, CASE WHEN INSTR(SYS_CONNECT_BY_PATH(d.combo, '|'), 'E') > 0 THEN 'Y' END AS e FROM d CONNECT BY N

F Few colleagues of mine asked me for brief presentation of APEX . I want to reuse this later naturally, so I created list of main topics with links, put it on my Git and use it as synopsis for my presentation. Take a peek at APEX Evangelist  for the latest version. What is APEX? Low-code platform for rapid application development https://apex.oracle.com/en/ https://apex.oracle.com/en/platform/why-oracle-apex/ https://apex.oracle.com/en/platform/features/ interactive reports - filters, groups, pivots, downloads, subscriptions... interactive grids faceted search clickable charts mega menu How to try APEX? Free Environments https://www.oracle.com/cloud/free/ Free Workspace = just APEX  Free Cloud Account = APEX on ATB (with administration) Free Sample Apps https://apex.oracle.com/en/solutions/apps/ Free Labs https://apex.oracle.com/en/learn/tutorials/ https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/view-workshop?p180_id=631 https://apexapps.oracle.com/pls/apex/f?p=133💯1060629065093

S Spoiler alert, sad story ahead. No matter your skills and experience, you should start with SQL exam  1z0-071 ( Oracle Database SQL Certified Associate ). Just few years back you could do 1Z0-047 for Oracle Database SQL Expert and 1Z0-117 for Oracle Database 11g Release 2: SQL Tuning. There is no current equivalent for these exam. Then you should follow with exam  1z0-149 ( Oracle Database PL/SQL Developer Certified Professional ). For me this was hard exam. It was very similar to 1z0-144, just a 10% harder. Same style of questions, same tricky answers. Recently you could pass 1z0-144 for Oracle PL/SQL Developer Certified Associate and 1z0-148 for Oracle Database PL/SQL Developer Certified Professional. Both of them are retired now. And then there is an APEX exam 1z0-750  ( Oracle Application Express 18: Developer Certified Professional ). No more fancy Expert title. Maybe if you like data modeling you should check  1z0-006  ( Oracle Certified Foundations Associate ). It is listed

I If you are tempted to jump in any Oracle Learning Subscription or just want to buy a certification voucher, you can check  Oracle User Group Champions Program  for discounts, they are running 25% right now (March 2021). Or you can became member of UK Oracle User Group and get same deal just for small yearly fee. If you are considering buying multiple vouchers it may be more convenient to buy whole subscription (depending on exams match). If you are considering OLS, then keep in mind that it may be possible to buy it for shorter term than 12 months with adequate price reduction. So you can get same content and all certifications in 3 or 6 months just for 25% or 50% of full price. Deduct the price of all certifications you are interested in. Divide this by all courses which interests you and you may agree that it is actually reasonable priced, maybe even cheaper than buying individual exams without any courses. Let me know if you want to join me and became certified.

I In March 2020 at the beginning of world apocalypse I decided it would be nice to utilize more free time I suddenly had. So I was looking around and I found a 35% discount on Oracle Database Developer Learning Subscription. I thought about it for a while because even after this generous discount it was still a bit steep, but I bought it in May. It was the best decision of the year. Well, mainly because two months later my subscription was converted (extended) to Oracle Database Learning Subscription for free. I suddenly had access to so many courses and certifications I didn't even considered pursuing. I was aiming just for SQL, PL/SQL and APEX. I got all of them and I got whole DBA stack for free. That was an awesome deal. But I had to shift my plans to squeeze in additional courses and certifications. It is very time consuming and maybe even impossible to go thru all courses and all certifications in just 12 months. It means that you should be doing 1-2 certifications each mont

I I had this idea of publishing my code snippets and thoughts for years. Buckle up. You can expect topics related to what I do on daily basis: SQL, PL/SQL, Oracle APEX, maybe some Javascript, Python and certainly some articles about Oracle certifications. I wish you to find something useful here and thank you for your visit. You can connect with me on LinkedIn . Jan