Snippets and Thoughts of One Oracle Developer

T This week I got my Oracle ACE Associate award. I feel very excited, honoured and proud to receive this recognition. Not everyone can get it. It is certainly the best work related event for me in years. There are currently 392 ACEs world wide, 196 in Database focus area, 99 in EMEA region and just me in Czech Republic. My interested at Oracle started when I was a kid and I bought a book about Oracle 7i. I didn't understand it and it was very hard to even install the database then. I started developing web applications as a freelancer when I was 18 and could legally set up my first company. PHP cluttered with MySQL calls was my first choice. But I was lured by Oracle's ability to store and execute the business logic inside of the database and to even run the webserver there. Magic. So I found a job at StringData where I had the opportunity to learn Oracle on commercial projects. I grew up on the Oracle Base blog from Tim Hall , on orange books from Steven Feuerstein ,

R Recently I have encountered a strange data model. Imagine you have an app with many tables for a specific group of users. Then you are asked to add a second group of users to the app. These groups have same tables but must not see records from the other group. Author of this solution decided to copy all objects (not just tables) and use a different prefix for them. Then he made some small adjustments here and there. For tables, views, mviews, triggers and packages. He skipped sequences for some reason. If you wonder why is this strange, then think about how would you proceed if you have been asked to add a third, fourth or fifth group of users... The issue I have was how can I quickly compare these tables to analyze how much they overlap? They are in the same scheme and they have different prefixes. I don't want to compare them one by one manually. Here is the script I wrote for this, just change the prefixes: WITH x AS ( SELECT /*+ MATERIALIZE */ 'P1_&

I I was tired of searching the app for places where is the authorization scheme referenced. So I wrote this script to get a nice overview of components and relevant pages. To get list of the autorization schemes/roles: SELECT a.authorization_scheme_name AS scheme_name, a.authorization_scheme_id AS scheme_id FROM apex_application_authorization a WHERE a.application_id = &APP_ID ORDER BY 1; To get list of components and relevant pages: SET SERVEROUTPUT ON DECLARE in_app_id CONSTANT NUMBER := &APP_ID; -- v_records NUMBER; v_pages VARCHAR2(4000); BEGIN FOR c IN ( WITH x AS ( SELECT a.application_id, a.authorization_scheme_name AS scheme_name, a.authorization_scheme_id AS scheme_id FROM apex_application_authorization a WHERE a.application_id = in_app_id ), p AS ( SELECT d.apex_view_n

I I have been asked multiple times about CSV files processing recenly. I guess CSV files are not going away any time soon. Excel (.xlsx) files, CSV, XML and JSON files can be uploaded and processed by awesome APEX_DATA_PARSER package. You can check my more sophisticated uploader I wrote few years ago, but this short tutorial is a great way how to start. First you have to upload your file, you can use File page item for that. Then you will see list of uploaded files (for your active session) and file profile, columns and data. All of this is achieved by simple queries: Uploaded files: SELECT f.id, f.filename AS file_name, f.name, f.mime_type, f.created_on FROM apex_application_temp_files f WHERE f.application_id = :APP_ID; File profile: SELECT f.name, f.mime_type, REGEXP_SUBSTR(f.name, '([^/]*)$') AS file_name, -- JSON_VALUE(f.profile_json, '$."file-encoding"' RETURNING VARCHAR2) AS file_