Snippets and Thoughts of One Oracle Developer

E End of the month is a great opportunity to switch jobs. Here are some questions I asked on interview for Python beginners. I would ask you to evaluate yourself first on scale form 1 to 10. The main objective is not to embarrass you but to quickly evaluate what you know, how much experience you have and mainly how do you react when you don't know the answer. What module is used to connect to Oracle database? Describe differences between Lists, Tuples, Dicts and Sets. What are Dict and List comprehensions? How do you count specific values in List? How do you sort Dict by value? How do you extract e-mail from text? Describe syntax for ternary operator. How do you copy object? Arguments are passed by ________ Describe object constructor. What does *args and **kwargs do? What are decorators in Python? How do you implement parallel processing? Good luck in finding your dream job.

F First of all this exam/certification was introduced somewhere around autumn 2020. It has old badge and it doesn't have a description on Credly, but it is the latest and the only available certification for PL/SQL developers. Strange thing is that according to Credly ( sorted by popularity ) it is not very popular. There is a new APEX certification around for 2 months ( 1Z0-760 ) and if you check Credly, then more people have it then this at least 8 months old PL/SQL certification. If you want this certification, it is pretty much the same as (retired) 1Z0-144 exam, tiny bit harder but also with higher reward (OCP instead of OCA). So if you are prepared for 1Z0-144 exam, you should pass this too. Same topics, same style of questions. Oracle Database Program with PL/SQL (exam 1Z0-149) Oracle PL/SQL Developer Certified Professional Source: Credly

I If you are on this page, you probably lust Oracle Certified Professional Oracle Database Security Expert certification ( exam 1Z0-116 , 90 questions, 59% needed to pass). Main topics: Manage Database Users Manage and Secure passwords Configure and Use Contexts Manage Authorization Configure Fine Grained Access Control Configure and Manage Database Vault Configure and Use Auditing Configure Network Security Configure and Implement Encryption Implement Data Masking and Data Redaction Invoke the Database Security Assessment Tool Patch Databases Manage Database Security in the Cloud This exam looks wast and hard. Not many free/cheap resources available. Demystifying Oracle Database Security: On-Prem & Oracle Cloud  course on Udemy Oracle Database 12c Security book from Oracle Press Oracle Database Application Security from Apress Here are resources available thru Oracle University, specifically in Oracle Database Learning Subscription: Introduction to Oracle Databa

I I have noticed that there are more certifications prepared for this year. Here is a sneak peak. Update at the end. Oracle Database SQL Specialist Update (September 27th):  Good news. You can book these exams via Cerview (Pearson Vue) without the need to attend the overpriced courses. Unfortunately it is not possible to do them online. This exam consists of 57 questions very similar to 1z0-071 exam, 63% is needed to pass in 90 minutes . Only few questions are tricky. I suspect that PL/SQL exam is very similar to 1z0-149 and DBA exam to 1z0-083 (even the study material is basically the same). An Oracle Database SQL Specialist demonstrates understanding of fundamental SQL concepts needed to undertake any database project. Candidates have illustrated a depth of knowledge of SQL and its use when working with the Oracle Database server, and a working knowledge of queries, insert, update and delete SQL statements as well as some Data Definition language and Data Control Language,

F First machine learning certification from Oracle will be released soon. Are you excited? The official link is not ready yet but you can see brief description and badge on Credly . If you are interested in machine learning you should also check Oracle Cloud Platform Enterprise Analytics 2021 Specialist Certification with 1z0-1041-21 exam which contains ML topics. The Oracle Machine Learning using Autonomous Database 2021 Certified Specialist has foundational knowledge of Oracle Machine Learning and its components is familiar with machine learning techniques and algortihms, and has either performed the tasks covered in the exam or practiced them in labs or an environment. This candidate can create projects, workspaces, job schedules, templates & notebooks in Oracle Machine Learning and can run SQL and PLSQL scripts. Source: Credly

Y You may have noticed I like to use a lot of horizontal spacing in my code. Aligning code to multiple colums for better readability. It doesn't slow you down when you get used to it and it will save you a lot of time in a long run. You spend much more time on reading code then writing it so it is a good investment. Example of well formatted code (subjective): CREATE OR REPLACE VIEW p200_projects AS WITH t AS ( SELECT t.project_id, COUNT(*) AS tasks, SUM(CASE WHEN t.status = 'READY' THEN 1 ELSE 0 END) AS tasks_ready, SUM(CASE WHEN t.status = 'IN-PROGRESS' THEN 1 ELSE 0 END) AS tasks_in_progress, SUM(CASE WHEN t.status = 'COMPLETE' THEN 1 ELSE 0 END) AS tasks_complete, SUM(CASE WHEN t.resource_id IS NOT NULL THEN 1 ELSE 0 END) AS resources FROM tasks t LEFT JOIN sprints s ON s.sprint_id = t.sprint_id AN

O One day you will need to restrict access only for developers or some priviledged users. This is a followup for previous article Prepare users for APEX maintenance . You can manually change application availability: Interesting part is the programatic approach via APEX_UTIL.SET_APPLICATION_STATUS procedure: BEGIN -- restrict access to APEX developers APEX_UTIL.SET_APPLICATION_STATUS ( p_application_id => 700, p_application_status => 'DEVELOPERS_ONLY' ); COMMIT; END; / BEGIN -- restrict access to priviledged users APEX_UTIL.SET_APPLICATION_STATUS ( p_application_id => 700, p_application_status => 'RESTRICTED_ACCESS', p_restricted_user_list => 'USER_LOGIN,USER_LOGIN2' -- change this ); COMMIT; END; / You should explore all options and examples in documenation. AVAILABLE - Application is available with no restri

I It is quite easy to do it within app itself, but you can do it programatically too. Let's say you need to take your application offline. Ideally you want to inform user before and during this maintenance. You can use global notification feature. Default styling is non existing, it is just pure text not even enclosed in DIV/SPAN tag. You probably have to use some HTML tags and CSS to make it pop. You can set same message (even with html/css/javascript) thru APEX_UTIL.SET_GLOBAL_NOTIFICATION . DECLARE in_app_id CONSTANT apex_applications.application_id%TYPE := 700; in_message CONSTANT VARCHAR2(4000) := 'SERVER RESTART IN 5 MINUTES'; -- v_workspace_id apex_applications.workspace%TYPE; BEGIN -- setup workspace first SELECT a.workspace INTO v_workspace_id FROM apex_applications a WHERE a.application_id = in_app_id; -- APEX_UTIL.SET_WORKSPACE ( p_workspace

T This script can be used for quick compare of two similar tables (like audit/historic or DML error tables) so you can easily check if columns and their data types matches. WITH z AS ( SELECT 'UPLOADERS' AS first_name, -- base table 'UPLOADERS' || '_U$' AS second_name -- audit/hist/error table FROM DUAL ), c AS ( SELECT c.table_name AS table_name, c.column_name AS column_name, c.column_id AS column_id, -- CASE WHEN c.data_type = 'NUMBER' THEN c.data_type || CASE WHEN c.data_precision IS NOT NULL THEN '(' || c.data_precision || DECODE(c.data_scale, 0, '', ', ' || c.data_scale) || ')' END WHEN c.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2') THEN c.data_type || '(' || DECODE(c.char_used, 

I I was learning about this many years ago as a part of preparation for SQL Expert certification. Let's create simple loan calendar using MODEL clause. Looks a bit complicated at first. And especially if you check the CONNECT BY alternative below. Can you tell me advantages of MODEL ? Is there anything only MODEL can do? Well, you can access any row you calculated on previous lines. You will also look cool and nobody will be able to fix this query after you. WITH s AS ( SELECT 100000 AS amount, 12.9 / 100 / 12 AS roi, -- 12.9% p.a. 36 AS months, 2 AS precision FROM DUAL ) SELECT n, emi, ROUND(interest, precision) AS interest, ROUND(principal, precision) AS principal, ROUND(remaining, precision) AS remaining FROM ( SELECT n + 1 AS n, months, emi, precision, DECODE(n + 1, months, interest - remaining, interest) AS interest,

D Did you ever wondered how (where) are your variables used? This script show usage of local variables, constants and input parameters in packages. You can use it for example for finding unused variables (and removing them). It is based on user_identifiers view/feature and you should check previous article first if it is new for you. WITH x (object_type, object_name) AS ( SELECT 'PACKAGE BODY', '%' FROM DUAL ), i AS ( SELECT i.* FROM x JOIN user_identifiers i ON i.object_type = x.object_type AND i.object_name LIKE x.object_name ) SELECT a.object_name, b.name AS module_name, REPLACE(a.type, 'FORMAL ') AS variable_type, a.name AS variable_name, REGEXP_REPLACE( REGEXP_SUBSTR(s.text, '(' || a.name || ')([^;:]*)', 1, 1, 'i'), '\s*(' || a.name || ')\s+(.*)', '\2', 1, 1, 'i'

S Sometimes I need to cleanup trailing spaces (RTRIM) from packages, procedures... There are better methods out there, like using proper editor. But this is an approach you can use directly from SQL Developer or console. Remember that source code of views and jobs is stored elsewhere. DECLARE out_clob CLOB; BEGIN DBMS_OUTPUT.PUT_LINE('SET DEFINE OFF'); DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE STOP'); DBMS_OUTPUT.PUT_LINE(''); -- FOR c IN ( SELECT DISTINCT s.type, s.name FROM user_source s WHERE s.type NOT IN ('TYPE') AND s.text != REGEXP_REPLACE(s.text, '\s+$', '') || CHR(10) ORDER BY 1, 2 ) LOOP DBMS_OUTPUT.PUT_LINE('-- ' || c.type || ' ' || c.name); DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE '); out_clob := 'CREATE OR REPLACE '; -- FOR d IN ( SELECT REGEXP_REPLACE(

M My personal preferrence is to match index names with constraint names. At least with primary keys, foreign keys and unique constraints. That way I know the purpose of the index just from its name resp. prefix. This script suggests changes for unique constraints (including PK): WITH i AS ( SELECT d.table_name, d.index_name, LISTAGG(d.column_name, ', ') WITHIN GROUP (ORDER BY d.column_position) AS cols FROM user_ind_columns d JOIN user_indexes i ON i.index_name = d.index_name WHERE i.uniqueness = 'UNIQUE' GROUP BY d.table_name, d.index_name ), p AS ( SELECT c.table_name, c.constraint_name, LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols FROM user_cons_columns c JOIN user_constraints n ON n.constraint_name = c.constraint_name AND n.constraint_type IN ('P', 'U') GROUP BY c.table_name, c.constraint_name

I It is a common thing to store indexes in different tablespace. In the past it was mainly for performance reasons, because you put this tablespace to faster drives. But there are more reasons and you can find them elsewhere. Overview of indexes and tablespaces. SELECT i.table_name, t.tablespace_name AS table_tablespace, i.index_name, i.index_type, i.tablespace_name AS index_tablespace, i.status FROM user_indexes i JOIN user_tables t ON t.table_name = i.table_name WHERE i.index_type != 'LOB' ORDER BY i.table_name, i.index_name; Move indexes to new tablespace: DECLARE in_target_tablespace CONSTANT VARCHAR2(30) := 'INDEXES'; BEGIN FOR c IN ( SELECT i.table_name, i.index_name FROM user_indexes i WHERE i.index_type LIKE '%NORMAL%' AND i.tablespace_name != in_target_tablespace ) LOOP DBMS_OUTPUT.PUT_LINE(RPAD(c.table_name, 30) || ' -> ' || c

Y You may get into situation when you have added or enabled foreign key and it can't be activated because you have integrity issues. Here is a solution how to find these naugty rows. First, create table for gathering results. CREATE TABLE invalid_keys_tmp ( table_name VARCHAR2(128) NOT NULL, column_name VARCHAR2(128) NOT NULL, parent_table VARCHAR2(128) NOT NULL, parent_column VARCHAR2(128 NOT NULL, row_id ROWID NOT NULL, -- CONSTRAINT pk_invalid_keys PRIMARY KEY (table_name, column_name, row_id) ); Check all tables for invalid keys, ignore NULL values. You may want to extend this for compound keys and maybe filter tables to not checking whole schema in single transaction. BEGIN DELETE FROM invalid_keys_tmp; -- FOR c IN ( SELECT fn.table_name AS foreign_table, fc.column_name AS foreign_column, pc