Snippets and Thoughts of One Oracle Developer

S Sometimes, when I'm working on a new package, it's difficult to keep the specification updated to reflect changes in arguments. Adding new procedures becomes tedious, and when I change the formatting, it's time-consuming to update the specification accordingly. When I reformat a legacy package, I usually copy the whole body into the specification and keep only the declarations. But that also takes some effort. So I created this nifty script, which generates a specification from your package body. It keeps everything that exists in your current package spec before the first procedure or function (like constants, exceptions, comments...). Everything else is replaced. It does not support forward declarations, and your private procedures will get exposed (which is a good thing, because you should be using the ACCESSIBLE BY clause instead). Here is the script: DECLARE in_package_name CONSTANT VARCHAR2(30) := '&PACKAGE_NAME.'; -- v_head...

S Sometimes, you have to modify all triggers, if you were for example using SYSTIMESTAMP and now SYSDATE is enough. To modify all triggers by hand might be a lot of work (it is not just about modifing, you also have to compile all of them in the database). So let's write a script for that (replace LOCALTIMESTAMP with SYSDATE in all triggers), but you can easily modify it to whatever you need to change: DECLARE v_query VARCHAR2(32767); v_modified BOOLEAN; BEGIN FOR c IN ( SELECT t.name, ROWNUM AS r#, d.referenced_name FROM user_source t JOIN user_dependencies d ON d.name = t.name AND d.name LIKE '%' AND d.type = t.type AND d.type = 'TRIGGER' AND d.referenced_type = 'TABLE' WHERE t.line = 1 ORDER BY 1 ) LOOP v_query :=...

A Another issue I faced on each project is data type inconsistency. In this older article I mentioned how to fix data types mismatches on keys . Today, I show you a script, which fix it for you (based on a column name match). Let's explore this issue on the audit columns (who created or updated the row and when). To check the audit columns for user: SELECT c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc, COUNT(*) AS count_tables FROM user_tab_cols c JOIN user_tables t ON t.table_name = c.table_name WHERE c.table_name LIKE '%' AND c.column_name LIKE 'CREATED_BY' GROUP BY c.column_name, c.data_type, c.char_used, c.data_length, c.nullable, c.data_default_vc ORDER BY 1, 2, 3, 4; Let's check the audit column for date. This gets more trickier, because we might not have consistent names. To keep the changes low, I am not changing the column names, just ...

I In this article, I will show you a script that deletes all rows from all tables for a specific tenant. You don't need to be familiar with your schema or manually piece together queries to perform the deletion. You can also easily modify this script to delete any entity, not just a tenant. There are several ways to achieve this, and I will show you two of them: To find (even compound) references to a specific table, you can use this query: SET DEFINE ON SELECT r.table_name, c.column_name, r.constraint_name, k.column_name AS primary_column FROM user_constraints r JOIN user_constraints p ON p.constraint_name = r.r_constraint_name AND p.constraint_type IN ('P', 'U') JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name AND c.column_name NOT IN ('TENANT_ID') -- we should match on position JOIN user_constraints s ON s.table_name = r...

T The space on free OCI can sometimes be a bit limiting, especially if you're using table partitions. Even an empty partition consumes 8 MB, and if you have partitions per tenant in every table — and over 100 tables — it can quickly grow with each new tenant. So here is the script which helps you to remove empty partitions and also rename the partitions so you can easily identify specific tenants: DECLARE in_table_like CONSTANT VARCHAR2(128) := '%'; in_partition_prefix CONSTANT VARCHAR2(128) := '__P'; -- v_found PLS_INTEGER; v_high_value VARCHAR2(4000); v_new_name VARCHAR2(128); BEGIN FOR c IN ( SELECT t.table_name, t.partition_name, t.high_value FROM user_tab_partitions t WHERE t.table_name LIKE in_table_like ORDER BY 1, 2 ) LOOP v_found := NULL; v_high_value := c.high_value; -- LONG convers...

T There is another new exam available at Oracle, the Oracle APEX Cloud Developer Professional (1Z0-771) with a free course to get you there at Mylearn . Till March 15th you can get the course and the certification for free! "An Oracle APEX Cloud Developer Professional certification signifies practical expertise and in-depth knowledge in designing, developing, and deploying Oracle APEX applications. Professionals with this certification are skilled in creating modern, secure, scalable, AI-powered enterprise applications equipped with world-class features, deployable both in the cloud and on-premises. This certification assesses a candidate’s comprehensive understanding of low-code application development using Oracle APEX. It also validates their ability to utilize APEX’s advanced capabilities, including Generative AI, to deliver innovative solutions tailored to diverse business requirements. Earning this certification demonstrates proficiency in building robust, user-focused...

T There is a new exam available at Oracle, the Oracle AI Vector Search Professional (1Z0-184-25) . And since the certification is not listed under the Cloud certifications, it wont expire. Correction, it does expire in 2 years. "The Oracle AI Vector Search Professional Certification is designed for Oracle DBAs, AI engineers, and cloud developers to unlock the potential of Oracle Database 23ai to build AI-driven applications. The target candidate for this certification should have basic familiarity in Python and AI/ML concepts. This certification bridges the gap between traditional database management and cutting-edge AI technologies by focusing on leveraging Oracle Database 23ai capabilities for handling vector data and enabling semantic and similarity searches. Through in-depth training, candidates will master techniques like vector data storage, indexing, and generating and storing embeddings, alongside advanced applications such as building Retrieval-Augmented Generation (...

A A year ago I have shared a tip how to use the same mechanism for closing modal dialogs . Today, I would like to share few more tips about levaraging the Global page. When you have a button on page and the action behind is a bit slow or if you have users who are constantly double clicking on everything, you might want to disable clicked button. You can easily fix this with a simple dynamic action. But instead of repeating this DA on each button, you can define it on the Global page. The trick is to use the jQuery selector and CSS class on each of these buttons. So go to your Global page. Create DA called SUBMIT_AND_DISABLE_BUTTON on Click event. Set Selection Type to jQuery Selector Set value to "button.SUBMIT_DISABLE" Switch the default true action to Execute JavaScript code Define the action code: var button_id = this.triggeringElement.getAttribute('id'); console.log('SUBMIT_AND_DISABLE_BUTTON:', button_id, this); docum...

T Today, I will show you how to easily check if your authorization schemes set on pages match the schemes set in the navigation. In my custom navigation (including the multicolumn navigation ), I don't have to set it in two places (which is never a good idea). My navigation gets the schemes from the pages. But the default APEX navigation is not like that. That causes a lot of confusion to users when they see a page in navigation, but can't access it. Or when they have some hidden pages, and they are missing the navigation links. Fortunately, if you combine few APEX views, you can find out: SELECT p.application_id, e.list_name, p.page_id, p.page_name, --p.page_alias, --p.page_group, --p.page_mode, p.authorization_scheme AS auth_scheme_page, e.authorization_scheme AS auth_scheme_nav, e.entry_text, e.entry_target -- FROM apex_application_pages p LEFT JOIN apex_application_lists l ON l.application_id = p.application_...

I I spent the past few days at the HrOUG conference in Rovinj, on the Red Island. I was there for the first time. The location was superb, but difficult to get to, at least from Prague. No direct flights, no trains, 9 hours by car... The hotel was ok, I expected it would be a bit outdated and it was not. But I was not expecting a noise till 2am. If you are not an old fart who goes to bed at 10pm like me, you probably won't bother. The food was excellent. I don't think this can be improved. The sessions. With the 5 tracks and one dedicated to Oracle APEX and the other one to Oracle AI , there was always something interesting to listen. Just check the agenda link at the top. These conferences are really a great opportunity how to stay ahead of the people who don't go there. As a speaker, you can pass your knowledge to others. Very different experience than online events. The people. Probably the best part of the whole conference. You meet a lot of cool people. A ...

I I was going to write about the brand new and hot Oracle AI feature, the prebuilt OCI Generative AI agent with RAG , which I saw last week at local Oracle event at Prague. You basically get a pretrained AI agent, throw at it your files and it will be answering your questions with knowledge gained from your files. The plan was to spend some time during the weekend and play with it, but both my cloud accounts are at Frankfurt. Despite the claims of the Oracle support, you have to be in Chicago region. It is not available in other regions yet. I don't want to create another free OCI account and on free it is not possible to subscribe to another region. Today is day #5 since I am unsuccessfully upgrading my OCI to paid version (and despite having a ton of credits from the Oracle ACE program). As all of you certainly know, the Oracle support sucks. You don't have to be sad, because Adrian is the expert on this field and he wrote about this amazing feature today and with mu...

T Typically you have some constants in packages. And sooner or later you need to use these constants in SQL statements outside of the package, like in a view. Sadly even the latest Oracle 23ai won't help you with this. The solution is to create a function for each constant, which can be very tedious if you have many constants. I have created a generator to automate this , but here is a different approach. We have can utilize the USER_SOURCE view to get the constant value and the USER_IDENTIFIERS view to localize constants in the package. With this approach you can also expose constants from the BODY. Here is the function: CREATE OR REPLACE FUNCTION get_constant ( in_package VARCHAR2, in_name VARCHAR2, in_prefix VARCHAR2 := NULL, in_private CHAR := NULL ) RETURN VARCHAR2 RESULT_CACHE AS out_value VARCHAR2(4000); BEGIN SELECT NULLIF( REGEXP_REPLACE( ...

C Common sense says you need more than a paycheck to be happy, but happy work won't more likely pay your bills. Ikigai is about finding a balance between what you love, what you are good at, what the world needs, and what you can be paid for. You might enjoy the rat race more with more money, but at the end of the day, you will still feel empty. If you don't enjoy what you do, you are missing a lot. If you are happy in your job and you think you can do this for the rest of your life (and not just till retirement, that is the key difference), congrats! You might have found your Ikigai . If not, you need to find a purpose. Image from https://hyperisland.com/en/blog/thought-leadership/feeling-drained-at-work I have a project where I have a free hand. I am very lucky. The client trusts me and I have a week to build something they want, but in a way I think it will best fulfill the goal. I presented my solutions to the client and so far he always loved it. I can sell my p...

L Let's face it. As an Oracle APEX developer you are a full-stack developer whenever you like it or not. And if you are one, you know what I am talking about. If not, brace yourself. As APEX developer you have to know APEX. But you also have to know SQL and PL/SQL. It is not realistic to build real/complex app without that. If you are into customizing the look and feel (UI and UX) you will have to know HTML5 and CSS (and design, color theory, typography...) and you will have to know a lot about JavaScript. You also have to know the database design (including a data modeller tool), architecture and administration basics (how database works), at least basics about security, possibly also the performance tuning. The more you know, the easier it will be for you to overcome obstacles. Then you can throw in the cloud thingy, which is also a dedicated profession. Let's add DevOps, middleware, real programming languages for things you can't do in database... On top of that y...

I I have been designing a small app where you can sync data in tables based on some web service calls. I wanted to give the users a flexible and simple option to schedule refresh of selected tables at specific time. So I have created a page where they can create, manage and test schedules. On the main page they have a list of tables and each schedule as a column with checkbox so they can easily change and see when are which tables synced. The issue is with the schedule intervals (the schedule definition). At first I have added the [?] button with a link to the documentation . But I have realized that nobody from the business will be willing to read that and not everyone will be able to actually create the repeat interval string. So I thought, wouldn't it be nice if you just describe the interval in english and don't care about the technical part at all? Screens for better imagination, prompt and response: This is probably the most simple use case for AI you ca...

I I like my certifications fresh. You can brag that you are one of the first ones who took the exam, you can help to promote and even raise the awareness that the certification exists. And no one can accuse you of cheating. Here is my mini review of the 1z0-171 exam : Most of the questions are pick 2 or 3 from 5 or 6 options. Much harder to do than on OCI foundations exams where you are picking 1 from 4. Half of the questions are easy to understand, easy to answer. One third is much harder and it will make you sweat. The rest would go to the very hard category and I would say that I have faced several questions where I strongly disagreed with the answers (it is when you are suppose to pick 2 from 6, but you know that 3 are possible or they ask for 3 and you know that only 2 are possible). But based on other legacy exams the percentage of these questions is not bad. On one question I have to guess 3 options from 6 based on non existing exhibit image. I really liked the questio...

H How to copy cell value from Interactive Grid? I wrote about this few years back in this article . Since that I have been warned by several people that it does not work on some occasions. Non editable grid, Redwood theme, read only cell with link... Many issues. Thanks to Matan from Israel and his ChatGPT friend here is a better solution: document.addEventListener('copy', (event) => { const allowed = 'a-GV-cell'; const active_el = document.activeElement; if (active_el.closest(`.${allowed.replace(/\s+/g, '.')}`)) { const selected = window.getSelection().toString() || active_el.innerText; event.clipboardData.setData('text/plain', selected); event.preventDefault(); } }); But we get the whole row (instead of the selected cell) when copy pasting to Outlook (or any rich text editor). So after more testing and tweaking, we came up with this final solution: document.addEventListener('copy', (...

I I am excited to see brand new classic (non cloud) certifications going to be released soon: Oracle Database 23ai SQL Associate: 1Z0-171 [link] , 60 questions, 65% to pass. It looks very similar to current SQL 1z0-071 exam, I wonder what will be 23ai related. Oracle Database 23ai Administration Associate: 1Z0-182 , 60 questions and 65% to pass. We can see a huge difference compared to the previous DBA 1z0-082 exam - the whole SQL section is missing, so all these 60 questions will be DBA related. If you search Oracle MyLearn for "23ai" materials, you will find this: Oracle Database 23ai: SQL Workshop , 19.5 hours Oracle Database 23ai: New Features for Developers , 6 hours Oracle Database 23ai: Administration Workshop , 12.5 hours Oracle Database 23ai: New Features for Administrators , 8.5 hours Happy learning and let me know if you pass one on these.

T There is no Oracle User Group in Czech republic. As far as I know, there are just few PR events organized by local Oracle branch few times per year. Last year there was some initiative to organize monthly meetups, but that died quickly. From the rumours I heard several people tried before and failed. I guess there is not enough interest from both sides. Other sad thing is that I don't know a single person in Czech republic who would publish anything for the community. So to be part of the Oracle community, you have to think beyond the borders. Our closest neighbours (Austria, Germany and Poland) have quite large and engaged communities with many events, but I don't know German nor Polish. It might be your case too. Don't be discouraged by that, there are other options. You can become the ODTUG member or a member of any existing Oracle User Group you like. You don't have to live in that country to be a member, but you should know the local language. Joining Spain...