
svenweller – Notes about Oracle SQL, PLSQL and APEX
270 FOLLOWERS
I am a professional software developer working with Oracle Databases for more than 15 years. I am strongly passionate about performance tuning and about rapid application development with Apex. My real strength is in depth analysis of problems. That helps a little when finding new ways to tweak and twist SQL and PLSQL. More recently focused on APEX and BI integrations.
svenweller – Notes about Oracle SQL, PLSQL and APEX
4M ago
The current APEX version has some improvements made to working copies. For more information check out https://apex.oracle.com/en/platform/features/whats-new-241/#improvements_to_working_copies I still encounter several issues with the general working copy feature. One issue is that when we export a working copy (full application export) and import it again, then the connection to the main application is lost. Here ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
During an ODA X7-2S upgrade from 19.9 to 19.13 we encountered the following issue. The prepatch report mentioned that the check “Validate kernel log level” failed with the message
OS kernel log level is set to debug, this may result in a failure when patching Clusterware
If kernel OS log level is more than KERN_ERR(3) then GI patching may fail
This problem also seems to exist in versions 19.10+ . It is a problem that can not be ignored. Trying to update the server anyways will lead to an error.
Here is an example how such a prepatch report might look like
Patch pre-check report ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
Problem / Justification
The installation and configuration of ORDS has been changed quite a bit in version 22. Your hitherto existing installation and upgrade process needs to be carefully examined and probably reworked.
Here is an issue I encountered.
Imagine a database with two PDBs. One of them is the normal APEX database (test environment), the other is a regular pdb clone from a different environment, for example from development.
The goal is to run and access a different version of ORDS (and potentially APEX) in each PDB.
This post considers only ORDS, not APEX.
For simplicity we want t ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
Motivation
In recent times I speculate where and when to use scalable sequences. Especially if it makes sense to use them in general for sequence driven ID columns of larger tables. I know this sounds a bit like an early sign of CTD (compulsive tuning disorder), but at least I’m aware of it.
Scalable sequences offer a performance benefit in certain special situations (hot block, index contention). This post is not about those benefits.
I assume the reader has a basic understanding what a scalable sequence is. If not, check out this older post of mine: All about sequences .
A scalable sequence ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
To show (not to edit) pretty code inside an APEX application in the past I had used the libraries that were deployed along with APEX, like CodeMirror (see https://svenweller.wordpress.com/2015/12/07/apex-5-syntax-highlighting/) and CkEditor. In APEX 21 CkEditor got a new version and CodeMirror is not supplied anymore since several APEX versions now. But there is a new very lightweight alternative, which is prism.
In my use case I need this to quickly present currently running edition based plsql code.
Implementation Step 1) Load code into a hidden page item
I use a before region process to loa ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
Vector image by VectorStock / Anastasia8
This is something I read about and forgot until Chris Saxon mentioned and showcased it during todays AskTOM Office Hour session.
In Oracle 12.2 the create table command was enhanced to avoid the error
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
during an exchange partition operation. We can now do create table ... for exchange.
The basic idea is that the for exchange syntax enhancement considers things like invisible columns that are usually not created and by that it avoids complications during an exchange partition at a ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
2y ago
tl;dr
-- enable sql*plus to track the name of any currently running script as module name in application_info
set appinfo on
-- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME
column script_name new_value scriptname
column logfile_name new_value logfilename
-- fetch name of the currently running SQL*plus script
select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual;
-- change suffix from .sql to .log and use as name for the log
select replace('&scriptname.','.sql$','.log') as logfile_name from dual;
-- start the log
spool &log ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
3y ago
Here is a quick overview about commands in SQL*plus that help to track and measure time.
set time on/off
This displays a prompt in front of each statement with the current time. Be aware that it is not the time, when the statement was executed, but the time when the line in sql*plus was created. This difference is usually not relevant when running scripts, just something to be aware of when manually typing and executing statements in sql*plus.
SQL> set time on;
10:56:02 SQL>
10:56:10 SQL> execute dbms_session.sleep(3);
PL/SQL procedure successfully completed.
10:56:23 SQL>
The ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
3y ago
A colleque made me aware of the following misbehaviour of Oracles optimizer. Shoutout to Christine S. who discovered that problem.
demo
create table a
as select 1 x from dual;
create table b
as select 1 x, 1 y from dual union all
select 1 x ,2 y from dual;
select a.x, c.y
from a,
lateral (select b.x, max (b.y) y from b where b.x = a.x) c
where a.x = c.x;
result
X Y
---------- ----------
1 2
At first glance this looks like what we intended to see. However a closer inspection of the code reveals that select b.x, max (b.y) y from b is not val ..read more
svenweller – Notes about Oracle SQL, PLSQL and APEX
3y ago
Introduction
To gather statistics for a schema or a table there is the dbms_stats package. Either we call it manually or the automatic statistic gathering (scheduled) job is used.
We can provide many settings for the statistic gathering job as a parameter during the gather call. For parameters that we do not explicitly set, preferences are used. Either on a global or on individual table level.
Since there are many preferences this article has some SQL statements that help to check how the current dbms_stats preferences are.
Table preferences overrule the global preferences. And preferences set ..read more