This started as a fun discussion in an syntegris internal chat group. But it got me thinking:
Is there a way to make APEX applications more energy efficient?
I came up with a set of ideas and options that have the potential to save energy. Not all options are useful and not all options have a measureable effect. Some are very controversial. The ideas presented here are far from complete.
I added my thoughts to a kialo discussion. Feel free to enter your own arguments there. This is my first kialo discussion – I want to try it out. So please be kind. 😉
I will try to add any interesting considerations that are in the kialo discussion also to this post.
From a very high level view point, we have three areas where we can influence the amount of energy that is used: server – network – client.
server side considerations
Using less CPU means using less energy. APEX applications should avoid wasting CPU.
serve APEX files from the Oracle CDN
A content delivery network provides common files from a server that is geographically close to the client. This also means that the company webserver will not provide the file but the CDN server will. It is possible to configure APEX to get its static files (like jquery, core apex css, etc.) from the CDN.
PRO: The company web/app server will need less energy when the static files are delivered by the CDN.
CON: For local clients the time to get the files from the CDN will be longer. Which also means that energy is wasted somewhere. Probably on the network. Maybe on the firewall.
PRO: Ressources (images, css, js files) that are identical for multiple applications even from different host urls, will be cached only once on the clients browser if they are served from the same CDN.
ORDS should run on the same machine as the database.
PRO: One less server is one less server.
CON: Servers nowadays are mostly virtualized containers running on the same hardware.
ORDS standalone is better than ORDS on Tomcat
A similar consideration could be made for Tomcat vs. Glashfish or Tomcat vs. WebFly (formerly known as JBoss)
PRO: ORDS standalone runs with the Jetty Webserver. Jetty is considered to be very lightweight. Which means it uses less RAM and probably less CPU.
Using more disk space does not correlate to energy consumption the same way as CPU does. Discs are cheap and they don’t need more energy to store more data.
CON: More data that needs to be transfered and backuped will inevitably need more energy.
PRO: Powered down disks keep the data regardless of how much they store. 4 GB or 5 GB is not a difference to them.
Avoid pagination schemes that include Z (x of y from z)
PRO: A pagination scheme with Z means the query is rewritten to include the analytical row_number() over () function. This also means, that more data needs to be read from disk into memory, just to know how many potential rows there are.
CON: For smaller tables a multi-block-read count will load the whole table into memory. Counting or numbering the rows will then have no noticable impact on energy consumption.
client side considerations
In general a tablet uses less energy than a laptop which uses less energy than a desktop PC. So should APEX applications be build for tablets first and foremost?
CON: It does not make sense to exclude users that prefere a certain screen size.
CON: We should make the whole screen available to the end user. More information on a single page will mean less need to navigate to other pages. Which potentially saves energy.
PRO: APEX applications should be carefully tested and optimized for usage on a tablet. If they work really well on a smaller screen, then users will not feel forced to switch to another device when using the app.
APEX 19 has a dark mode. Developers should use dark mode and dark themes to save energy.
The idea is that a monitor uses less energy to show “blackness” than to show “whiteness”.
Brightness affects power usage, and battery life, in a mostly linear fashion.
A dark theme can reduce battery usage, even with max brightness, by up to 63% on AMOLED displays.
Pixel color value can also affect power usage quite a bit, with white being the most power-hungry color.
Classic Reports use less energy than Interactive Reports (or Interactive Grids)
PRO: A classic report will be created on the webserver and send as static html to the browser. An interactive report will add a lot of javascript functionality to the data. Which means all those JS files need to be loaded and some are executed during page load.
CON: Interactive Grids have this lazy loading pagination scheme. Data will only be fetched when the end user requests to see more data by scrolling down.
PRO: With APEX 18.2 we also get the option to create a column toggle report. This is slightly more flexible than a classic report without all the interactions made available by an IR.
network traffic
The computers that run the network are there anyway. However we can speculate that by sending less information over the internet, there would be less powerhungry computers that are the internet nodes.
Decrease image size. Provide smaller images depending on screen resolution.
PRO: Smaller images allow faster loading times which positivly effects network traffic and also client rending times. This is especially noticable on low bandwith machines like mobile phones.
CON: There is a huge starting effort to create and provide images for all sizes. It is highly doubtful that this original energy spent will ever be overtaken by the energy savings from smaller network traffic.
How do web applications build with APEX compare to other web applications?
APEX applications have a tendency to consume data in a very efficient way.
The overhead for communication with the database is less then for other types of web applications, like PHP.
CON: Not all applications are data centric.
PRO: There is no additional network between the application layer and the database layer.
CON: With APEX 18 and even more so with APEX 19, reports can be built on top of a web service. The web service provider would be the same no matter what kind of framework was used to build the application. In many cases a pure javascript framework (like Oracle JET) might be more efficient to consume those REST based web services, than an APEX application.
PRO: Because the APEX repository already resides in the database all the optimizions for database logic are used while preparing a page for rendering.
Result?
What started out as a “Schnapsidee” quickly got me thinking. There are actually quite a few ways to influence the energy consumption of an APEX application. And this is true during developement but also for running the application.
By Unknown – 1911 Ottoman calendar, Public Domain, Link
Calendar and date arithmetic is not easy. And not because of the database, but because the stuff exists in the real word. The database does an excellent job to reflect many or most of the strange attributes that calendar systems have.
Here is a collection of surprising, quirky or interesting things around usage of date and time values in an Oracle database. A lot of those things have nothing to do with the database, but instead with the complexity of the world.
By examining those edge cases we might learn something about the general workings of dates, calendars and the Oracle database.
5 Fun Facts
1) We live in the year 2561
alter session set nls_calendar = 'THAI BUDDHA';
select to_char(sysdate,'YYYY') as current_year from dual;
CURRENT_YEAR
2561
q.e.d.
Reason of cause is that there are other calendars out there in the world, not only the default Gregorian one. And according to the Thai Buddha calendar we are now in 2561.
2) persons existed that were born on the 30th of february
Why is 1753 the earliest date for datetime?
Good question. It is for historical reasons. In what we sometimes refer to as the western world, we have had two calendars in modern time: the Julian and the Gregorian calendars. These calendars were a number of days apart (depending on which century you look at), so when a culture that used the Julian calendar moved to the Gregorian calendar, they dropped from 10 to 13 days. Great Britain made this shift in 1752 (1752-09-02 were followed by 1752-09-14). An educated guess why Sybase selected 1753 as earliest date is that if you were to store an earlier date than 1753, you would also have to know which country and also handle this 10-13 day jump. So they decided to not allow dates earlier than 1753. Note, however that other countries did the shift later than 1752. Turkey, for instance, did it as late as 1927.
Being Swedish, I find it a bit amusing that Sweden had the weirdest implementation. They decided to skip the leap day over a period of 40 years (from 1700 to 1740), and Sweden would be in sync with the Gregorian calendar after 1740 (but meanwhile not in sync with anyone). However, in 1704 and 1708 the leap day wasn’t skipped for some reason, so in 1712 which was a leap year, they inserted yet an extra day (imagine being born in Feb 30!) and then did the shift over a day like everyone else, in 1753.
Does the database know about this? Lets find out:
alter session set nls_date_language='SWEDISH';
alter session set nls_territory='SWEDEN';
select to_date('30-02-1712','dd-mm-YYYY') from dual;
ORA-01839: date not valid for month specified
Using a date literal instead of TO_DATE doesn’t help either.
select to_char(date '1712-02-30') from dual;
ORA-01847: day of month must be between 1 and last day of month
So this doesn’t work. Why? Because the 30th of february only existed in the Julian Calendar System. The Oracle Database doesn’t support a Julian Calendar – we can not choose calendar systems, that are not in use anymore.
Btw. the 29th of februar 1712 does exist. Even in Sweden.
select to_char(date '1712-02-29','fmddth Month YYYY') from dual;
29th Februari 1712
The article also briefly mentioned that Turkey was the last country to switch to the Gregorian Calendar. Before that, they had the Rumi Calendar, which is a variation of Julian. It differs mostly about the starting year.
Turkey did the switch as late as 1927. After the 15th February 1332 AH (Rumi) they skipped 13 days, so that the next day was the 1st March 1927 AD (Gregorian). And they switched the year too.
Again, if we ask the database then it does not know about this gap.
set pagesize 100
alter session set nls_date_language='TURKISH';
alter session set nls_territory='TURKEY';
-- 28 days later...
with twentyeight as (select level lv from dual connect by level <=28)
select to_char(date '1927-02-10' + lv,'fmddth Month YYYY')
from twentyeight;
11th Şubat 1927
12th Şubat 1927
13th Şubat 1927
14th Şubat 1927
15th Şubat 1927
16th Şubat 1927
17th Şubat 1927
18th Şubat 1927
19th Şubat 1927
20th Şubat 1927
21st Şubat 1927
22nd Şubat 1927
23rd Şubat 1927
24th Şubat 1927
25th Şubat 1927
26th Şubat 1927
27th Şubat 1927
28th Şubat 1927
1st Mart 1927
2nd Mart 1927
3rd Mart 1927
4th Mart 1927
5th Mart 1927
6th Mart 1927
7th Mart 1927
Since the leaped days existed in the Gregorian Calendar, there is no reason to leave them out.
Interestingly in Turkey there should still be living persons that were born in or before 1332!
(older than 92 years)
3) There are no days between 4 Oct 1582 and 15 Oct 1582
select to_char(date '1582-10-4','ddth Mon YYYY') from dual;
04th Oct 1582
+1 day
select to_char(date '1582-10-4' + 1,'ddth Mon YYYY') from dual;
15th Oct 1582
04th Oct 1582 + 1 = 15th Oct 1582 ?
After the previous chapter the reason should be obvious.
At this time the Gregorian calendar leapt 10 days in order to catch up with the astronomical year.
year (a or y or yr) [2]
a traditional unit of time usually equal to 365 or 366 days. We need a whole number of days for the calendar year used in ordinary life. Ancient astronomers knew that the year [1] is approximately 365 days long, and we now know the correct figure is approximately 365.242 days. If we use 365 as the number of days in every calendar year, the extra 0.242 day adds up quickly and causes large errors in predicting the seasons. To solve this problem, the Roman emperor Julius Caesar decreed in 46 BC that the calendar year should have 365 days generally, but that every fourth year should have an extra, or 366th, day. The longer year is called a leap year. In this Julian calendar, four years equal exactly 1461 days, so the average Julian year is exactly 365.25 days.
This was a big step toward accuracy in the calendar, but the Julian year is too long by 0.008 day, or a little over 11 minutes. By the time of the Renaissance, these 11-minute errors had accumulated to a total error of about 10 days (since the Council of Nicaea in 325 AD, which set the rules for deciding when Easter should be celebrated). The spring equinox was occurring near March 11 instead of March 21. In 1582, Pope Gregory XIII decreed that 10 days should be dropped from the calendar: the day after 1582 October 4 was October 15. To reduce future errors, the pope further decreed that years divisible by 100 are not leap years unless they are also divisible by 400. Thus 2000 and 2400 are leap years, but 2100, 2200, and 2300 are not. It took many years, but the Gregorian calendar has now been accepted as the civil calendar in all countries of the world.
With the Gregorian adjustment, there are exactly 146 097 days in every 400 years, and the average Gregorian year is exactly 365.2425 days. The Gregorian year is still too long, but by less than half a minute. It will take thousands of years for this error to accumulate to 1 day, so the calendar year and the tropical year are in good enough agreement to last us a long time.
And this time, the database with its default Gregorian calendar knows about it!
Database considers days that seem to be inside this gap, as if they were after the leaped days.
select to_char(date '1582-10-5','ddth Mon YYYY') from dual;
15th Oct 1582
4) The north pole and the south pole are on different time zones
In the poles, the time is UTC in the North Pole and UTC+12 in the South Pole.
(and no this is not the “South Pole, Illinois, USA” nor the “North Pole, New South Wales, Australia”)
So in case you happen to live or work at the south pole, set your session time zone to UTC+12.
Let me simulate that for you:
col sessiontimezone format A20
col dbtimezone format A20
-- check current settings
select sessiontimezone, tz_offset(sessiontimezone),
dbtimezone, tz_offset(dbtimezone)
from dual;
Currently I’m in Frankfurt which is the same time zone as Berlin, so 2 hours before UTC.
Since I have to do some quick work at the south pole, let’s change my session settings to reflect that.
-- change to UTC+12
alter session set time_zone ='+12:00';
Session altered.
-- how late is it here currently?
select sysdate, current_date from dual;
It is good to be back! Timezone travelling is exhausting.
5) The word calendar comes from the Latin word Kalendae which meant the first day of the month
A calendar is essentially a list of first month days.
Getting the first day of a month is easy.
trunc(sysdate,'MM') => 1st of the current month.
But what about the first day of a week? And which week are we in?
This is complex for two major reasons. There is quite some confusion about when does a week start and what is the first week in the year. And with confusion I mean, different countries have different rules for that.
The good thing is, the Oracle database has all this knowledge. A developer just has to understand it. As usual NLS settings will influence the behaviour.
Different date formats refer to those NLS settings.
‘D’ returns the number of the day inside the week.
‘WW’ returns the number of the week inside the year. Week 1 starts on the first day of the year and continues to the seventh day of the year.
‘IW’ returns the number of the ISO-week.
‘YYYY’ refers to the year
‘IYYY’ refers the ISO-year.
some examples
What weekday is the first day of the week?
NLS_TERRITORY influences what the first day of a week is.
alter session set NLS_DATE_LANGUAGE = 'AMERICAN';
alter session set NLS_TERRITORY = 'AMERICA';
select to_char(trunc(sysdate,'D'),'Day') as "First Day in America" from dual;
alter session set NLS_TERRITORY = 'GERMANY';
select to_char(trunc(sysdate,'D'),'Day') as "First Day in Germany" from dual;
alter session set NLS_TERRITORY = 'IRAQ';
select to_char(trunc(sysdate,'D'),'Day') as "First Day in Iraq" from dual;
select to_char(trunc(sysdate,'IW'),'Day') as "ISO First Day" from dual;
result
Session altered.
Session altered.
First Day in America
---------
Sunday
Session altered.
First Day in Germany
---------
Monday
Session altered.
First Day in Iraq
---------
Saturday
ISO First Day
---------
Monday
NLS_TERRITORY does not directly influence what the first week in the year is! To get the week we always use formatter options (IW or WW) that directly decide, which week logic needs to be applied.
However NLS_TERRITORY sets an ISO-Week flag. Unfortunately this is not well documented. I believe this flag is partially responsible for one of the behaviours mentioned in the “Stranger Things” section.
5 Stranger Things – bug or feature?
1) sysdate returns a date, but systimestamp does not return a timestamp
systimestamp returns a timestamp with timezone. Localtimestamp would be the function that returns a timestamp. In many cases systimestamp still is the best option to use!
These are the DATATYPES : pseudocolumns dealing with points in time.
DATE : sysdate, current_date
TIMESTAMP : localtimestamp
TIMESTAMP WITH TIME ZONE : systimestamp, current_timestamp
TIMESTAMP WITH LOCAL TIME ZONE
example
First modify the session so that session timezone and db timezone differs.
ALTER SESSION SET TIME_ZONE='+10:00';
show nls
DB_TIMEZONE +01:00
SESSION_TIMEZONE +10:00
SESSION_TIMEZONE_OFFSET +10:00
Check the pseudocolumns and convert them to timestamp if needed (using cast). The same would happen when the value is stored in a timestamp column. The database implicitly does the conversion.
select to_char(sysdate,'DD-MM-RR HH24:MI:SS') as "sysdate",
to_char(localtimestamp,'DD-MM-RR HH24:MI:SS') as "localTS",
to_char(current_timestamp,'DD-MM-RR HH24:MI:SS TZH') as "currentTS",
to_char(systimestamp,'DD-MM-RR HH24:MI:SS TZH') as "sysTS",
to_char(cast(current_timestamp as timestamp),'DD-MM-RR HH24:MI:SS') as "currentTS_converted",
to_char(cast(systimestamp as timestamp),'DD-MM-RR HH24:MI:SS') as "sysTS_converted"
from dual;
Note that localtimestamp and current_timestamp returns (and stores) a different hour than sysdate. Systimestamp returns the same hour after conversion and therefore matches sysdate.
Better would be to have a column that stores the timezone information too. Either timestamp with time zone or timestamp with local time zone. Since TZ info is stored, it does not matter anymore which datatype has the source.
select to_char(sysdate,'DD-MM-RR HH24:MI:SS') as "sysdate",
to_char(localtimestamp,'DD-MM-RR HH24:MI:SS') as "localTS",
to_char(current_timestamp,'DD-MM-RR HH24:MI:SS TZH') as "currentTS",
to_char(systimestamp,'DD-MM-RR HH24:MI:SS TZH') as "sysTS",
to_char(cast(localtimestamp as timestamp with local time zone),'DD-MM-RR HH24:MI:SS [TZR]') as "localTS_converted",
to_char(cast(current_timestamp as timestamp with local time zone),'DD-MM-RR HH24:MI:SS [TZR]') as "currentTS_converted",
to_char(cast(systimestamp as timestamp with local time zone),'DD-MM-RR HH24:MI:SS [TZR]') as "sysTS_converted"
from dual;
As we can see the result is always the same point In Time.
2) Micro Intervals
And of cause there are also datatypes that deal with durations.
NUMBER
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Why are there two interval types? Because those are independend measurement systems for a duration of time. And there is no correct conversion possible from one into the other.
Consider this: 29 days (interval day to second) could be slightly less, slightly more or sometimes even exactly one 1 month (interval year to month). We can only do this conversion if we additionally have some reference point, like the starting day. Without this reference point in time, we can’t correctly convert from one system of measurement into the other.
We can convert from number to interval using the functions numtoDSinterval or numtoYMinterval.
Let’s do some workday calculations.
My typical day of work is from (ok I might understate a bit) is from 9:30 to 18:30.
select to_date('18:30','HH24:MI') - to_date('9:30','HH24:MI') from dual;
0.375
A date minus a date returns a number. This is the result in days. So I worked for 0.375 days. Lets see that in interval format.
select numtoDSinterval(0.375, 'day') from dual;
+00 09:00:00.000000
Looks about right!
Can we also see that in a Month interval ? The MONTHS_BETWEEN functions gives us the result in number (= months) for the difference between two dates.
select months_between(to_date('19:30','HH24:MI')
,to_date('10:30','HH24:MI'))
from dual;
0
Hm… 9 hours equal 0 months…
Let’s assume somebody has a large shift requiring to work for more than 24 hours.
-- how many months did he work?
select months_between(to_date('2018-07-02 19:30','YYYY-MM-DD HH24:MI')
,to_date('2018-07-01 07:15','YYYY-MM-DD HH24:MI'))
from dual;
0.0487231182795698924731182795698924731183
And as an interval…
select numtoYMinterval(0.0487, 'MONTH') from dual;
+00-01
Hmm… 0.0488 Months => 0 Years 1 Month ?
Is it always rounded up?
So what would a larger result be:
select numtoYMinterval(1.0487, 'MONTH') from dual;
For very small values (0.01-0.49) numtoYMinterval rounds up, instead of rounding down. For larger values, the rounding is consistent. Those values are rounded down, as expected.
This feature was implemented somewhere between Oracle DB version 10 and 11. It took me a while, but now it is accepted as a unpublished bug (26244914).
Recommendation: Avoid numtoYMinterval. There are several strange edge cases with that function.
3) date format “J” – Julian
We can use the J format mask to spell out numbers (Jsp).
J is called Julian day; the number of days since January 1, 4712 BC.
Let’s play around with it a little.
select to_char(sysdate,'J') from dual;
2458317
Of cause when you execute it, you will get a different (larger) number. Time passed.
We use this number and substract it from sysdate, then add the number of days we are interested in. This time using “sp” = spelling in the format parameter.
select to_char((sysdate-(2458317-5)),'Jsp') from dual
Five
Cool! This way we can spell out any number. Unfortunatly this only works in English, not in other languages.
Important side note: The calculation must be like this
sysdate-(2458317-5)
Although mathematically the same, this would have resulted in an error
(sysdate-2458317)+5
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Problem is that the resulting date value is invalid (before the beginning of time).
To avoid hardcoding 2458317, we can calculate it from sysdate.
select to_char(sysdate-(to_number(to_char(sysdate,'J'))-123),'Jsp') spell_number from dual;
One Hundred Twenty-Three
Ok let’s try a large number…
select to_char(sysdate-(to_number(to_char(sysdate,'J'))-1721116),'Jsp') spell_number from dual
One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-One
And one more…
select to_char(sysdate-(to_number(to_char(sysdate,'J'))-1721117),'Jsp') spell_number from dual
Wat!? So spelling out One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-One is ok, but One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-Two not…
Hold on a minute. Even the first number was not spelled correctly!
1721116 != One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-One
As so often there is a reason for that. The difference between both numbers is exactly 365. Which is our first clue. We need to understand which dates are we talking about.
The following rows depend heavily on the database version it is running.
This is from a 12.2.0.1 DB. For readability, I removed several hundred rows from the output.
with testdata as (select level lv, 1721340-300+level days# from dual connect by level <= 400)
select lv, days#,
to_char(sysdate-(to_number(to_char(sysdate,'J'))-days#),'YYYY-MM-DD A.D.') dy,
to_char(sysdate-(to_number(to_char(sysdate,'J'))-days#),'J') Julian,
to_char(sysdate-(to_number(to_char(sysdate,'J'))-days#),'Jsp') spelled_number
from testdata;
LV DAYS# DY JULIAN SPELLED_NUMBER
1 1721041 0001-12-15 B.C. 1721041 One Million Seven Hundred Twenty-One Thousand Forty-One
2 1721042 0001-12-16 B.C. 1721042 One Million Seven Hundred Twenty-One Thousand Forty-Two
3 1721043 0001-12-17 B.C. 1721043 One Million Seven Hundred Twenty-One Thousand Forty-Three
4 1721044 0001-12-18 B.C. 1721044 One Million Seven Hundred Twenty-One Thousand Forty-Four
5 1721045 0001-12-19 B.C. 1721045 One Million Seven Hundred Twenty-One Thousand Forty-Five
6 1721046 0001-12-20 B.C. 1721046 One Million Seven Hundred Twenty-One Thousand Forty-Six
7 1721047 0001-12-21 B.C. 1721047 One Million Seven Hundred Twenty-One Thousand Forty-Seven
8 1721048 0001-12-22 B.C. 1721048 One Million Seven Hundred Twenty-One Thousand Forty-Eight
9 1721049 0001-12-23 B.C. 1721049 One Million Seven Hundred Twenty-One Thousand Forty-Nine
10 1721050 0001-12-24 B.C. 1721050 One Million Seven Hundred Twenty-One Thousand Fifty
11 1721051 0001-12-25 B.C. 1721051 One Million Seven Hundred Twenty-One Thousand Fifty-One
12 1721052 0001-12-26 B.C. 1721052 One Million Seven Hundred Twenty-One Thousand Fifty-Two
13 1721053 0001-12-27 B.C. 1721053 One Million Seven Hundred Twenty-One Thousand Fifty-Three
14 1721054 0001-12-28 B.C. 1721054 One Million Seven Hundred Twenty-One Thousand Fifty-Four
15 1721055 0001-12-29 B.C. 1721055 One Million Seven Hundred Twenty-One Thousand Fifty-Five
16 1721056 0001-12-30 B.C. 1721056 One Million Seven Hundred Twenty-One Thousand Fifty-Six
17 1721057 0001-12-31 B.C. 1721057 One Million Seven Hundred Twenty-One Thousand Fifty-Seven
18 1721058 0001-01-01 B.C. 1720693 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Three
19 1721059 0001-01-02 B.C. 1720694 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Four
20 1721060 0001-01-03 B.C. 1720695 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Five
21 1721061 0001-01-04 B.C. 1720696 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Six
22 1721062 0001-01-05 B.C. 1720697 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Seven
23 1721063 0001-01-06 B.C. 1720698 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Eight
24 1721064 0001-01-07 B.C. 1720699 One Million Seven Hundred Twenty Thousand Six Hundred Ninety-Nine
25 1721065 0001-01-08 B.C. 1720700 One Million Seven Hundred Twenty Thousand Seven Hundred
... snip 1721066-1721112 no major change there...
73 1721113 0001-02-25 B.C. 1720748 One Million Seven Hundred Twenty Thousand Seven Hundred Forty-Eight
74 1721114 0001-02-26 B.C. 1720749 One Million Seven Hundred Twenty Thousand Seven Hundred Forty-Nine
75 1721115 0001-02-27 B.C. 1720750 One Million Seven Hundred Twenty Thousand Seven Hundred Fifty
76 1721116 0001-02-28 B.C. 1720751 One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-One
77 1721117 0000-00-00 0000 0000000 000000000000000000000000000000000000000000000000000000000000000000000000000000
78 1721118 0001-03-01 B.C. 1720752 One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-Two
79 1721119 0001-03-02 B.C. 1720753 One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-Three
80 1721120 0001-03-03 B.C. 1720754 One Million Seven Hundred Twenty Thousand Seven Hundred Fifty-Four
... snip 1721120-1721420 no major change there...
381 1721421 0001-12-29 B.C. 1721055 One Million Seven Hundred Twenty-One Thousand Fifty-Five
382 1721422 0001-12-30 B.C. 1721056 One Million Seven Hundred Twenty-One Thousand Fifty-Six
383 1721423 0001-12-31 B.C. 1721057 One Million Seven Hundred Twenty-One Thousand Fifty-Seven
384 1721424 0001-01-01 A.D. 1721424 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Four
385 1721425 0001-01-02 A.D. 1721425 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Five
386 1721426 0001-01-03 A.D. 1721426 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Six
387 1721427 0001-01-04 A.D. 1721427 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Seven
388 1721428 0001-01-05 A.D. 1721428 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Eight
389 1721429 0001-01-06 A.D. 1721429 One Million Seven Hundred Twenty-One Thousand Four Hundred Twenty-Nine
390 1721430 0001-01-07 A.D. 1721430 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty
391 1721431 0001-01-08 A.D. 1721431 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-One
392 1721432 0001-01-09 A.D. 1721432 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Two
393 1721433 0001-01-10 A.D. 1721433 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Three
394 1721434 0001-01-11 A.D. 1721434 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Four
395 1721435 0001-01-12 A.D. 1721435 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Five
396 1721436 0001-01-13 A.D. 1721436 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Six
397 1721437 0001-01-14 A.D. 1721437 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Seven
398 1721438 0001-01-15 A.D. 1721438 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Eight
399 1721439 0001-01-16 A.D. 1721439 One Million Seven Hundred Twenty-One Thousand Four Hundred Thirty-Nine
400 1721440 0001-01-17 A.D. 1721440 One Million Seven Hundred Twenty-One Thousand Four Hundred Forty
Two strange things to observe. If we look closely we can see that the year 1 BC repeats itself two times. For example line 17 and line 383 both show the 31st of December 1 BC. I now say 1 BC[1] and 1 BC[2] to distinguish the entries for those years. Second observation is that the 29th of February 1 BC[2] exists and is kind of undefined (0000-00-00 0000 0000000). The 29th of februar in year 1 BC[1] does not exist in the data. This is not shown in the sample set, but you can easily modify the query to see for yourself.
In older DB versions the second 1 BC occurence would be year 0. For which some of the date functions also return the error message
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I believe that there was some half baked bug fix at oracle. Year 0 was aliased as Year 1 BC. This also explains, why there seems to be a leap day. Year 0 would have a leap day according to the logical rules (as defined by Pope Gregory XIII). In older database versions any day from the year 0 resulted in 0000-00-00 0000. Which also created problems. Fortunatly the issue only seems to appear when playing around with the J(ulian) format. And even then only for some very large values. The chance to get bugs in real world systems is extremly slim.
some more examples Date based comparison
select case when 1721057 = 1721423
then 'equal' else 'unequal' end as "check"
from dual;
unequal
As expected those two numbers are not equal.
Compare the same numbers, but added to the same date.
select case when to_date('01-01-4712 BC','DD-MM-YYYY AD') + 1721057
= to_date('01-01-4712 BC','DD-MM-YYYY AD') + 1721423
then 'equal' else 'unequal' end as "check"
from dual;
equal
Now they are equal.
consecutive day check
Negative years within a date literal are BC years. Here follows a shot list of small date calculations around the problematic years and days.
select to_char(date '-0001-12-31','DD-Mon-YYYY AD') from dual;
31-Dec-0001 BC
select to_char(date '-0001-12-31' + 1,'DD-Mon-YYYY AD') from dual;
01-Jan-0001 AD
31-Dec-0001 BC + 1 = 01-Jan-0001 AD
select to_char(date '-0001-01-01' - 1,'DD-Mon-YYYY AD') from dual;
31-Dec-0002 BC
01-Jan-0001 BC – 1 = 31-Dec-0002 BC
select to_char(date '-0002-12-31' + 1,'DD-Mon-YYYY AD') from dual;
01-Jan-0001 BC
31-Dec-0002 BC + 1 = 01-Jan-0001 BC
select to_char(date '-0001-02-28' + 1,'DD-Mon-YYYY AD') from dual;
01-Mar-0001 BC
28-Feb-0001 BC = 01-Mar-0001 BC
Seems about right.
At least here Oracle adhers to ISO 8601 regulation “every date must be consecutive”.
And some more edge cases
select to_char(date '-4712-1-1','YYYY A.D.') from dual;
4712 B.C.
select to_char(date '-1000000','YYYY A.D.') from dual;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
So there is no 1 Million Years B.C. in the Oracle DB.
4) The first ISO-year day for 1st Januar 2021 is in 2019
select to_char(trunc(date '2021-01-01','IYYY'),'YYYY-MM-DD') from dual;
2019-12-30
Now this looks strange! It happens in all supported DB versions.
According to the docs IYYY is a supported format model for TRUNC. It is specified as “Year containing the calendar week, as defined by the ISO 8601 standard“.
Note that the doc mentions the calendar week. The 1st Januar 2021 is in calendar week (IW) 53. That week still is part of year 2020. And the first day (Monday) of the first ISO week of 2020 is 30th of December 2019.
So it seems as if
TRUNC(:date,'IYYY')
really does:
TRUNC(
TRUNC(
TRUNC(:date,'IW') -- first day of the iso-week
,'YEAR') -- first day of the calendar YEAR regardless of iso
,'IW') -- first day of iso-week
I think this is illogical behaviour and as such should be considered a bug. However it is correct in terms of the ISO 8601 – which calls it the “ISO week-numbering year”. So most likely it will not be fixed.
Remember: Always truncate useing ‘YEAR’ or ‘YYYY’, never use ‘IYYY’ it is just to confusing.
5) lateral SQL injection
Consider the following code. Is it safe?
create or replace procedure date_proc(p_date in date) is
stmt varchar2(200);
res varchar2(100);
begin
stmt:='select object_name from all_objects where created = ''' || p_date ||'''';
dbms_output.enable;
dbms_output.put_line(stmt);
execute immediate stmt into res;
dbms_output.put_line('result:'||res);
exception
when no_data_found then
dbms_output.put_line('result: no objects found');
end;
/
set serveroutput on
execute date_proc(sysdate);
Dynamic sql is always at risk for sql injection. However since that code makes sure the value is a correct date it doesn’t seem possible to tamper with the code.
This is just some demo code. It has other issues as well (like removing time from the input).
Afaik David Litchfield was the first to describe the potential security issue with this. He called it the Lateral SQL injection. I prefer the name SQL injection by NLS.
Consider the following NLS setting. Any client can modify its NLS environment.
alter session set nls_date_format = 'DD/MM/YYYY"'' or 1=1"--';
Then run the statement again!
set serveroutput on
execute date_proc(sysdate);
select object_name from all_objects where created = '16/07/2018 ' or 1=1--'
ORA-01422: exact fetch returns more than requested number of rows
Ok this happens, because the code doesn’t expect multiple rows to be returned. We can modify that.
alter session set nls_date_format = 'DD/MM/YYYY"'' or 1=1 and rownum=1"--';
Fortunatly the possibilites via NLS format are limited. For example the length of the injected code can only be very small. When trying more complex modifications we get
ORA-01801: date format is too long for internal buffer
However sometimes this can potentially be used as a first step for more serious hacking.
A similar exploit is possible using the NUMBER datatype (via nls_numeric_characters).
The more interesting question is, how to correct code like this.
One option is to use bind variables.
create or replace procedure date_proc(p_date in date) is
stmt varchar2(200);
res varchar2(100);
begin
stmt:='select object_name from all_objects where created = :dat';
dbms_output.enable;
dbms_output.put_line(stmt);
execute immediate stmt into res using p_date ;
dbms_output.put_line('result:'||res);
exception
when no_data_found then
dbms_output.put_line('result: no objects found');
end;
/
Other options include validating any concatenated input for example by using dbms_assert.
Conclusion
Calendars are difficult. Oracle implemented calendar functionality very thoroughly.
It is the duty of a developer/DBA to understand the complexities around calendars, time zones, time conversions and Time Machines.
This is a “just for fun” experiment. But I demonstrate some model clause effects on the way.
Task: Sort a pipe separated list using SQL.
For example this string ’29|1|3004|3|2|24′ has 6 elements separated by |. The elements should be sorted in numerical order so that the resulting string looks like this ‘1|2|3|24|29|3004’.
Standard way
The “normal” way would be to tokenize the input string, put the tokens into rows, sort the rows and aggregate the sorted result to reassemble the string.
Here is one possible solution.
with inputdata(src)
as (select '29|1|3004|3|2|24' from dual)
,num_rows (part, remains)
as (select to_number(substr(i.src,1,coalesce(nullif(instr(i.src,'|'),0)-1,length(i.src)))) as part
,substr(src,instr(i.src,'|')+1) as remains
from inputdata i
UNION ALL
select to_number(substr(r.remains,1,coalesce(nullif(instr(r.remains,'|'),0)-1,length(r.remains)))) as part
,substr(remains, nullif(instr(r.remains,'|'),0)+1) as remains
from num_rows r
where r.remains is not null
)
, combine (resultdata)
as (select listagg(to_char(part,'FM9999999999'),'|') within group (order by part) from num_rows)
select * from combine;
The num_rows subquery in this example is a recursive with clause that splits the input string into rows, one row for each token. The logic to split the string into tokens is based around substr . Essentially it just cuts of an element from the beginning of the string and keeps the remainder until there are no elements left.
The combine subquery then uses listagg to sort and convert the rows into a string again.
Standard SQL technologies that each decent developer should know about.
There are multiple ways how to tokenize, how to create the rows and how to aggregate the rows again. But the general principle is still the same.
Here is a second example solution. This time using connect by to create rows and some regular expressions to count and split.
with inputdata(src) as (select '29|1|3004|3|2|24' from dual )
select listagg(token,'|') within group (order by to_number(token)) as res
from (
select src, regexp_substr(src,'[^|]+', 1, level) token
from inputdata
connect by level <= regexp_count(src,'[|*]') + 1
)
group by src;
Exotic way
I was wondering if we can use a totally different approach.
Instead of letting the database do the hard work and implement a sort mechanism, we can do it ourselves. Here comes bubbles sort. It is one of the most inefficient sort mechanisms you can think of (unless the list is already sorted), but it is fairly easy to implement.
Since bubble sort is an iterative approach with a simple set of rules, the model clause immediately jumped to my mind.
So here is a solution based around a slightly optimised bubble sort mechanism (gnome sort). We will take it apart afterwards.
The solution here creates several rows, but this is just to see and check each step of the iteration. It is possible to do this using one row (dim=0) only, although it is much harder to develop and to understand.
with inputdata(src) as (select '29|1|3004|3|2|24' from dual)
select *
from inputdata
model dimension by (0 as dim)
measures ( src
, 1 ele_pos1
, 2 ele_pos2
, cast (regexp_substr(src,'[^\|]+',1,1) as varchar2(100)) token1
, cast (regexp_substr(src,'[^\|]+',1,2) as varchar2(100)) token2
, regexp_count(src,'\|')+1 max_element
, 2 hwm
, cast (src as varchar2(500)) as res
)
rules iterate (500)
until (-- we can stop if the high water mark is at the last position and no switch needed
hwm[iteration_number] >= max_element[0]
and (ele_pos1[iteration_number] = 1 or to_number(token1[iteration_number]) right token
res[iteration_number+1]
= case when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
-- do the switch
regexp_replace(
regexp_replace(res[iteration_number],'[^\|]+',token2[iteration_number],1,ele_pos1[iteration_number])
,'[^\|]+',token1[iteration_number],1,ele_pos2[iteration_number])
else res[iteration_number] -- no switch needed
end ,
-- calculate next position for token1
ele_pos1[iteration_number+1]
= case
when ele_pos1[iteration_number] = 1 then
-- we reached first position, so go back to hwm
hwm[iteration_number]
when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
-- after a switch, move one position the the left and check there
ele_pos1[iteration_number] - 1
when ele_pos1[iteration_number] + 1 to_number(token2[iteration_number]) then
ele_pos2[iteration_number] - 1
when ele_pos2[iteration_number] + 1 <= max_element[0] then
hwm[iteration_number] + 1
else
ele_pos2[iteration_number]
end,
-- calculate high water mark
hwm[iteration_number+1]
= greatest(hwm[iteration_number],ele_pos2[iteration_number+1]),
-- get token1 for new calculated position1
token1[iteration_number+1]
= regexp_substr(res[iteration_number+1],'[^\|]+',1,ele_pos1[iteration_number+1]),
-- get token2 for new calculated position2
token2[iteration_number+1]
= regexp_substr(res[iteration_number+1],'[^\|]+',1,ele_pos2[iteration_number+1])
);
Here is the result, but I show only the important column. Feel free to run the statement yourself (works on all supported database versions) and see the other helper columns.
Result (only Res column)
29|1|3004|3|2|24
1|29|3004|3|2|24
1|29|3004|3|2|24
1|29|3|3004|2|24
1|3|29|3004|2|24
1|3|29|3004|2|24
1|3|29|2|3004|24
1|3|2|29|3004|24
1|2|3|29|3004|24
1|2|3|29|3004|24
1|2|3|29|24|3004
1|2|3|24|29|3004
1|2|3|24|29|3004
Fairly easy SQL isn’t it?
65 rows instead of 8. So if your aim is to write obfuscated SQL then this certainly is the way to go.
If you want to understand it, skip to the “algorithm explained” chapter.
Rant
Now what is wrong with the model clause!? A simple algorithm like bubble sort looks this complex?
In the recent months I have written a number of model clause solutions for different problems. However none of them is easy. Apart from very specific cases all model solutions are terrible to look at and terrible complex to build. In comparison it is much easier to do the same in excel.
I attribute this to several things.
The way to reference a cell is verbose. And when we look at the code then the brackets break the eye scanning mechanism of a trained developer eye. If more than one dimension is involved it is even worse.
Here are some examples:
measure1[cv(),3] or resultdata[iteration_number+1]
It is hard to see this as one cell reference.
In Excel this would simply be: B$3 or C2.
The way to reference a cell from the previous row is complex. It requires a working calculation for the cell address. We can do this by applying the row_number() analytic function or using iteration_number (for iterative models). But this requires some extra logic.
There is no simple way to set several different measures at once. A rule can only be applied for one measure. This means that sometimes we need to repeat the same logic for a different measure.
In the bubble sort example this happened for the calculation for the position of token 1 and for position of token 2. In this case it can be simplified a lot because the position of token 2 is always exactly +1 from position of token 1. In other cases it is not that simple. Additional measures can serve as a kind of variables to capture rules that then need to applied to several other columns.
Excel Comparison
The model clause resembles Excel very much.
Dimensions are rows.
Measures are columns.
Rules are formulas
But why does the model clause feels so more complex that some simple excel cells.
Excel has a similar way to address cells. It is called the R1C1-style reference. It uses number coordinates to find a cell. But most of times we use the A1-Style reference. This is a little more intuitive and much shorter.
But more importantly the process to build formulas (=rules) is different. While building formulas we click on a source cell and its coordinates are used automatically. Excel then calculates the difference from the current cell to the referenced cell.
The main difference to Excel is that is has a separate formula for each cell, and we use just a simple way to fill out the formulas to the other cells. We don’t need that copy mechanism with the model clause, because the rules automatically are applied for all relevant cells. But it is hard with the model clause to give the direct reference for a different row.
Also the different styles and the different ways to use absolute and relative cell references in Excel (e.g. B$2) are more convenient than doing the same in the model clause.
Pro-Tipp: A good way to develop model rules is to build the formulas in excel first, then copy the logic to SQL.
The model clause also has several advantages over excel. Among others are
Excel only has 2 dimensions and no decent concept of partitions.
Excel columns can not be named (although single cells and cell ranges can).
Model allows to return only updated rows.
Model has a easy way to work with invalid cell references (IGNORE NAV)
Algorithm explained
Before we look at the statement, let’s look at the bubble sort logic first.
bubble sort logic
Start: 29|1|3004|3|2|24
I start with the first two tokens. 29 and 1. Token1 is always the left and token2 is always the right token.
Rule 1: If token1 is bigger than token2 we need to switch them.
Rule 2: If we are at position 1, we can go one step to the right.
Rule 3: If we don’t need to switch, then we can also go one step to the right.
Rule 4: If we did switch then go one step to the left.
Now lets see how the rules can be applied to the string.
Step 1: 1|29|3004|3|2|24 – Rule 1
Step 2: 1|29|3004|3|2|24 – Rule 2
Step 3: 1|29|3004|3|2|24 – Rule 3
Step 4: 1|29|3|3004|2|24 – Rule 1
Step 5: 1|3|29|3004|2|24 – Rules 4+1
Step 6: 1|3|29|3004|2|24 – Rule 3 (here I used an additional optimisation, by storing a high water mark and jumping as far right as the HWM allows)
Step 7: 1|3|29|2|3004|24 – Rule 1
…
Stop: We can finish the iteration if the HWM is to the far right and if no switch is needed anymore.
model SQL explained in detail
So how is this logic implemented in the model clause?
A token is found using a regular expression with the position of the token.
regexp_substr(src,'[^\|]+',1,4)
This finds the fourth token.
We start with a way to number our rows. The initial row is defined as 0.
dimension by (0 as dim)
0 is only used because later we address our rows using an iteration_number. If we would start with 1, then we can potentially overwrite our data. Often 0 is a good starting row.
We then define several columns. And the initial value for those columns.
measures ( src
, 1 ele_pos1
, 2 ele_pos2
, cast (regexp_substr(src,'[^\|]+',1,1) as varchar2(100)) token1
, cast (regexp_substr(src,'[^\|]+',1,2) as varchar2(100)) token2
, regexp_count(src,'\|')+1 max_element
, 2 hwm
, cast (src as varchar2(500)) as res
)
The datatype and size of the columns is automatically deducted from the initial value. This is why cast is sometimes needed, if the column size should be bigger than what the initial value indicates.
Now we define that an iterative model is to be used.
rules iterate (500) until (stop criteria)
Iterative models are good for implementing procedural logic. Or for tasks when it is unknown beforehand what the area for the calculation should be.
It gives us a variable called iteration_number which can be used as a cell address.
Now lets look at a few rules.
-- switch tokens if left token > right token
res[iteration_number+1]
= case when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
-- do the switch
regexp_replace(
regexp_replace(res[iteration_number],'[^\|]+',token2[iteration_number],1,ele_pos1[iteration_number])
,'[^\|]+',token1[iteration_number],1,ele_pos2[iteration_number])
else res[iteration_number] -- no switch needed
end ,
The left hand side of the rule “res[iteration_number+1]” creates a new row for the res column. The right hand side of the rule references data from the previous row “token1[iteration_number]” to do the switch logic.
If data from the same row is needed, it is possible to use “token1[cv()]”. cv() stands for cell value of the current dimension. It is possible to calculate with those values. So instead of token1[iteration_number] we could also write “token1[cv()-1]” to fetch the value from the previous row. For iterative models I find it more convenient to stay consistent and use iteration_number instead.
This rule implements rule 1 (switch tokens) from the bubble sort logic. However it is not a 1:1 matching of rules. We can see this when looking at the next rule.
Instead the logic needs to be implemented for each column. To calculate the next position a similar case construct is needed.
-- calculate next position (for token1)
ele_pos1[iteration_number+1]
= case
when ele_pos1[iteration_number] = 1 then
-- we reached first position, so go back to hwm
hwm[iteration_number]
when to_number(token1[iteration_number]) > to_number(token2[iteration_number]) then
-- after a switch, move one position the the left and check there
ele_pos1[iteration_number] - 1
when ele_pos1[iteration_number] + 1 <= max_element[0]-1 then -- no more switch, so go to hwm and look for next element hwm[iteration_number]
else
-- just in case, don't do anything
ele_pos1[iteration_number]
end
Bubble sort rules 2-4 are implemented by a case construct.
As we can see in this example, the rules from the model clause do not match our business rules. Sometimes they do, but more often they do not.
conclusion
The Model clause can open up new ways to do solve SQL puzzles. Bubble sort is a good candidate for an iterative model.
For real world cases the Model clause is hardly maintainable. If we find a solid way to match business rules to model clause rules, then we have a good way to react to future changes of those business rules.
Sometimes model clause can provide a performance advantage, because of they way how the data is handled.
Model clause is a tool in our toolbox, although an extremely sophisticated and complex one. We need to train using this tool on a regular basis.
btw: Some of the constructs from the model clause can be rediscovered in other statements. For example the new analytic views in 12.2 also have a “dimension by” and a “measures” part. But the rules are missing (one can argue that attribute dimensions and hierarchies resemble the rules).
Many people enjoyed reading my last blog post “10 Oracle SQL features you probably didn’t know”. So I decided to spice it up a little more and do something similar for plsql.
I hope you like that one too.
With our further ado, let’s get started with the list.
10. The first Oracle version to feature plsql was Oracle DB version 6 (1988)
And no. Steven Feuerstein did NOT invent it.
At that time PLSQL did not have stored procedures nor did it have proper exception handling. But it already had embedded SQL.
I learned that from the great Lewis Cunningham. One of the godfathers of development with SQL and PLSQL.
Stored Procedures were added in Oracle 7 (1992). 7.3 was the version when I started to work with an Oracle Database. At that point plsql was in version 2.x. However there never was a version 3. Plsql versioning jumped to 8 when Oracle DB version 8 was introduced and plsql versioning was aligned with the db versions. So there are no plsql versions 3-7. But honestly? Nobody cares anymore that plsql does have its own versioning.
9. labels do not need to match
We can use <<labels>> in plsql. Mostly to increase readability of code. This is especially useful for loop constructs, but it also works for normal begin..end blocks.
begin
<<mainloop>>
loop
<<check_some>>
begin
<<dummyloop>>
for r in (select * from dual) loop
-- do nothing
null;
end loop dummyloop;
end check_some;
exit when 1=1;
end loop mainloop;
end;
/
As we can see there are several <<labels>>. And the usage of those labels at the “end” helps to distinguish which code part we are looking at [1].
But this is only as good as the programmer is!
Unfortunatly this works too:
begin
<<mainloop>>
loop
<<check_some>>
begin
<<dummyloop>>
for r in (select * from dual) loop
-- do nothing
null;
end loop mainloop;
end check_sum;
exit when 1=1;
end loop dummyloop;
end;
/
Here I mixed up the labels from the loops. And the “end check_sum” does not match the label at the beginning of the block. In fact the “label” at the end can be anything that is not a reserved word.
It runs identical to the previous code (still doing nothing). But it is way more confusing for the “future me” that has to maintain this mess.
8. pragma SERIALLY_REUSABLE
During the lifetime of a session, the package state (package variables, open cursors, etc.) are held in the UGA (user global area). Subsequent calls in the same session to the same package profit from that by not needing to reinitialize the package state.
The pragma SERIALLY_REUSABLE is able to change this behaviour.
After the work unit (server call) of a SERIALLY_REUSABLE package completes, Oracle Database does the following:
Closes any open cursors.
Frees some nonreusable memory (for example, memory for collection and long VARCHAR2 variables)
Returns the package instantiation to the pool of reusable instantiations kept for this package.
Essentially this means, that the package state exists only during the package call. Not for the whole session.
So far I never had the need to use this pragma. But I can imagine some very very special situations, where this might become interesting.
7. You can compile a package even while another session is running it
In general this is not possible. Assuming a package is currently running. Or to say it in technical terms: We have an active session (Session A) executing a packaged procedure.
A second session (Session B) trying to do an “ALTER COMPILE PACKAGE (BODY)” would wait until session A finishes and will then afterwards try to compile the package. Upon success the first session A then will get a “package state has been discarded” error message as soon as it tries to run the same package once again. The second next try to run the package would succeed and will use the new package version. At that time the package state was discarded from the session and the new instantiation can be loaded.
This is all documented and well known behaviour.
We face three potential issues with this
Session B needs to wait
Session A might get an error
Session B might wait so long that the developer decides to kill the client (closing SQL developer) thereby making everything 10 times worse, because the database compile call still is valid on the database session level – blocking all following attempts to run or compile the package.
EBR for the rescue!
Using Edition Based Redefinition (EBR) we can circumvent those issues.
Both sessions just need to use different editions. EBR allows us to store and run different code versions of the same plsql based object in the same database.
preparation
First create a package with a long running procedure. My example uses a procedure that runs for exactly 1 minute.
create or replace package myPck is
procedure runMinute;
end myPck;
/
create or replace package body myPck is
procedure runMinute is
begin
sys.dbms_lock.sleep(60);
end runMinute;
end myPck;
/
Setup an edition DEV$ALPHA that is a child of the default edition (ORA$BASE).
create edition DEV$ALPHA;
You need an edition enabled schema to do this.
This is simple to do, but to explain EBR in more detail is beyond the scope of this blog post.
Example scenario
Lets run a few commands in two different sessions.
Session A resembles a USER/TESTER who currently executes the packaged function.
Session B resembles a DEVELOPER who wants to deploy a new version of the package.
Session A does this
set time on
alter session set edition=ORA$BASE;
execute myPck.runMinute;
Session B was started already and after the execute in Session A, run the following script in Session B.
alter session set edition=DEV$ALPHA;
— add a new procedure to the package
create or replace editionable package myPck …
/
create or replace editionable package body myPck …
/
— run the new procedure
exec myPck.run5secs;
Result is Session B finishes way before Session A does complete its 1 minute run.
See screenshot
q.e.d.
6. call a (pipelined) table function without the TABLE operator
It works only from 12.2 onwards. It is more of a SQL feature than a plsql one.
This will make a table function look indistinguishable from a parametrized view.
Example: split_string
First lets create a simple little table function. This one here just converts a delimited list into rows.
create or replace function split_string
(p_str IN VARCHAR2
,p_delimiter IN VARCHAR2 default ','
) RETURN sys.odcivarchar2list PIPELINED
IS
/** Function to split strings based upon delimiter
*
* @author Sven Weller
*
* @param p_str input string
* @param p_delimiter delimiter string, default =, Delimiter should only be 1 char.
* @return list of strings
*
*/
v_entry varchar2(4000);
v_remaining_str varchar2(4000);
BEGIN
-- input string needs to hold something to be able to split
if p_str is not null then
<<steps>>
for i in 1..regexp_count(p_str,'\'||p_delimiter)+1 loop
-- search + split
v_entry := rtrim(regexp_substr(p_str,'[^\'||p_delimiter||']*('||p_delimiter||'|$)',1,i),p_delimiter);
pipe row(v_entry);
end loop steps;
else raise no_data_found;
end if;
END split_string;
/
function created.
in 11g we call the function like this:
select * from TABLE(split_string('A:BB::CCC',':'));
in 12.2 we can now call it like that:
select * from split_string('A:BB::CCC',':');
As you can see the TABLE row source operator is gone. And it still works! The results of both statements are identical.
At the moment this is an undocumented 12.2 feature. So don’t use it for production code (yet). I quite like it. Less code is better! It might become some de-facto standard (similar to connect by level) and eventually will make it into the documentation.
5. dot notation for parameters
We can refer to parameters using the name of the module that declared them. This is useful when we need to distinguish a parameter from a column name.
example
create or replace function myFancyFunc (dummy in varchar2) return number
is
ret number := 0;
begin
begin
select 1 into ret
from dual
where dummy = myFancyFunc.dummy
and rownum = 1;
exception
when no_data_found then null;
end;
return ret;
end myFancyFunc;
/
Function MYFANCYFUNC compiled
select myFancyFunc('X') from dual;
1
select myFancyFunc('Y') from dual;
0
The function simply compares the value in the dummy column of the dual table to the value we input. If instead we would just compare dummy=dummy then we would get always 1 as a result. No matter what the input is. Even if we add an alias to the table and prefix the column with an alias, the non aliased “dummy” will still be interpreted as a column.
If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.
Interestingly we can also use labels on block level for specifying variables that are defined in this block.
set serveroutput on
<<main>>
declare
dummy varchar2(10) := 'Y';
begin
<<block1>>
declare
dummy varchar2(10) := 'X';
begin
<<block2>>
declare
dummy varchar2(10) := 'A';
begin
select dummy
into dummy
from dual
where dummy = block1.dummy;
dbms_output.put_line('MainBlock:'||main.dummy);
dbms_output.put_line('Block1:'||block1.dummy);
dbms_output.put_line('Block2:'||block2.dummy);
end block2;
end block1;
end main;
/
PL/SQL procedure successfully completed.
MainBlock:YBlock1:XBlock2:X
Without dot notation the innermost variable (block2) is used – as we can see in the INTO part. And we can reference a different variable with the same name from a “higher” declaration by using the dot notation.
4. variable names can be emojis
example
set serveroutput on
declare
"💩"exception;
pragma exception_init("💩",-20001);
"⌚" timestamp := systimestamp;
"🕑"interval day to second;
"🎲"number;
"💤"number := 2;
begin
"🎲":= round(dbms_random.value(1,6));
for "🔜"in 1.."🎲"loop
dbms_lock.sleep("💤");
end loop;
"🕑":= systimestamp - "⌚" ;
dbms_output.put_line('Slept for '|| "🕑");
exception
when "💩"then
dbms_output.put_line('Sorry something bad happend!');
raise "💩";
end;
/
PL/SQL procedure successfully completed.
Slept for +00 00:00:08.049000
The source code looks a little bit different in sql developer. But trust me. I simply copy&pasted it from there to here.
To make this work you need to use a font that supports emoijs/symbols, I used font “Segoe UI Symbol”. It is supposed to look better on windows 10[3].
If you are a hard core emoji lover then I suggest to have a look at emojicode.org
It is a emoji based programming language. Which did not make it into the esoteric programming languages list (yet). Ook? Ook!
3. variables can be made mandatory (NOT NULL)
Check out the NOT NULL keyword during the variable declaration.
declare
v_index number not null := 0;
begin
v_index := 1;
v_index := null;
end;
/
Error report – ORA-06550: line 5, column 14: PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3: PL/SQL: Statement ignored
The error message is a bit vague about what happened, but it is very exact where it happened (line 5, column 14). And what do we see there? A NULL expression.
The expression is of wrong type, because we added a NOT NULL constraint to the number type that was used. For more complex cases we can create our own sub types and use them. But if we just want to make sure that we do not need to consider null cases during further variable calls, then this is a possible way.
Currently there are no such other constraints that we can use. I could imagine with the potential arrival of SQL assertions, this might become a hot topic in plsql too.
2. you can “hack” dbms_output
Warning! This is dangerous. It might break some (poorly written) code that resides in the same schema. Do it at your own risk! It is also hilariously funny to do on april fools day to your fellow coworkers. I mean they shouldn’t use dbms_output anyway. That will teach them!
I start the example by showing the behaviour first. Then the code to produce this result.
behaviour
create or replace procedure doSomething is
v_dummy dual.dummy%type;
begin
select dummy into v_dummy from dual where 1=2;
exception
when others then
dbms_output.put_line(sqlerrm);
end doSomething;
/
Now we run the module a couple of times and want to see the output. We should expect a NO_DATA_FOUND error message.
Surprisingly instead of the error message we get something like this.
PL/SQL procedure successfully completed.
Wrong usage of DBMS_OUTPUT detected.
PL/SQL procedure successfully completed.
Make Databases Great Again!
PL/SQL procedure successfully completed.
ORA-01403: no data found
PL/SQL procedure successfully completed.
Make Databases Great Again!
PL/SQL procedure successfully completed.
So tell me what you want, what you really, really want
Omg! What is going on here?
Well here is the catch. We can “overload” dbms_output in our own schema. Then the local package is called and not the original package from sys.
source code
create or replace package dbms_output
as
procedure enable(BUFFER_SIZE number default null);
procedure put_line(A in varchar2);
procedure GET_LINE(LINE out VARCHAR2,STATUS out integer);
procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER);
procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER);
end dbms_output ;
/
create or replace package body dbms_output
as
procedure enable(BUFFER_SIZE number default null) is
begin
sys.dbms_output.enable(BUFFER_SIZE);
end;
function getRandomQuote (A in varchar2) return varchar2
is
type quotes_t is table of varchar2(4000) index by binary_integer;
v_quotes quotes_t;
v_random binary_integer;
begin
v_quotes(1) := 'You are hacked by the Chinese';
v_quotes(2) := 'Wrong usage of DBMS_OUTPUT detected.';
v_quotes(3) := 'System failure. Get away from keyboard';
v_quotes(4) := 'Close all windows! NOW!';
v_quotes(5) := 'Make Databases Great Again!';
v_quotes(6) := A; -- sometimes return the correct text
v_quotes(7) := A; -- sometimes return the correct text
v_quotes(8) := 'So tell me what you want, what you really, really want';
v_quotes(9) := 'None but ourselves can free our minds.';
v_quotes(10) := 'Let there be light!';
v_random := round(dbms_random.value(1,v_quotes.last));
return v_quotes(v_random);
end getRandomQuote;
procedure put_line(A in varchar2) is
begin
sys.dbms_output.put_line(getRandomQuote(A));
end;
procedure GET_LINE(LINE out VARCHAR2,STATUS out integer)
is
begin
sys.dbms_output.GET_LINE(LINE,STATUS);
end;
procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER)
is
begin
sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;
procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER)
is
begin
sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;
end dbms_output ;
/
How does it work?
Because how sql name resolution kicks in, the DBMS_OUTPUT package in our schema is used and not the public synonym for the DBMS_OUTPUT package from the sys schema.
The get_line functions then pushes the changed text to the normal buffer mechanism.
How can we avoid it?
Best is not to use DBMS_OUTPUT in real production code. It is a nice quick debugging tool. But not more than that.
Also if you prefix dbms_output always with the SYS schema, then it will call the original logic.
1. when others does not catch all exceptions
example
set serveroutput on
declare
e_cancelled exception;
pragma exception_init(e_cancelled, -1013);
begin
begin
raise e_cancelled;
exception
when others then
dbms_output.put_line('EXCEPTION OTHERS');
end;
end;
/
ORA-01013: user requested cancel of current operation
ORA-06512: at line 6
We still see an exception, but not the dbms buffer output!
This needs some explanation.
There is a very limited set of exceptions that will not be captured by the WHEN OTHERS handler. We need to look closely and understand the exception itself to comprehend why this is a good thing.
Here the ORA-01013 is the “user requested cancel of current operation” exception. Essentially it means somebody pressed “CTRL+C” while running the code. In almost all environments this means: Stop doing whatever you do immediately! Or in more technical terms: It is an interrupt to the os process running your command. Same as executing “kill -2” (kill -SIGINT) in a nix environment (the-3-most-important-kill-signals-on-the-linux-unix-command-line). Even if the process is allowed to ignore the command, it shouldn’t do so by default.
ORA-01013 can sometimes also be the result of a timeout. Where the client is waiting for a response and after some time sends this as a timeout signal to the database session.
We are allowed to capture this exception and write a special handler for it.
set serveroutput on
declare
e_cancelled exception;
pragma exception_init(e_cancelled, -1013);
begin
begin
raise e_cancelled;
exception
when others then
dbms_output.put_line('EXCEPTION OTHERS');
end;
exception
when e_cancelled then
dbms_output.put_line('OPERATION CANCELED');
END;
/
PL/SQL procedure successfully completed.
OPERATION CANCELED
Nothing to worry about. Just nice to know.
Please note: This example will behave differently in older outdated db versions. I think it was introduced as a fix for bug#12838063 in 11.2.0.4.
Other exceptions that are not handled include “ORA-03113: end-of-file on communication channel”.
But not “ORA-06508: PL/SQL: could not find program unit being called”. This was supposed to go through “when others” but testing on 12.2.0.1 revealed it is captured.
Footnotes
1. This is probably the only bug free code I ever wrote. It was meant to do nothing and it does that exceptionally well![2]
2. There might be room for some performance improvement. Allowing us to do nothing even faster.
I can not guarantee that you never heared of this. But the majority of developers doesn’t know or doesn’t seem to know. And honestly – most of this information is fairly useless – and mostly harmless.
10. Why we have the CONCAT function
There is a concat function that can be used instead of the concat operator ||.
Nobody uses that! But there is a reason why it exists.
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
So we do have this function, because in the past this || was not consistent over all the different platforms using different character sets. Since nobody seems to use IBM EBCDIC anymore, there is no real need to use the CONCAT function.
9. select UNIQUE
Instead of doing a SELECT DISTINCT you can do SELECT UNIQUE.
But you shouldn’t. None of them. Forget I mentioned this.
It is also possible to do SELECT COUNT(UNIQUE …) . Not sure when that one was introduced, but it seems to work now.
Sven says: “SELECT DISTINCT|UNIQUE should be considered a bug in real production code.”
I have yet to find an example where SELECT DISTINCT is needed. More likely there is a bug in the data model or missing joins in the where clause. GROUP BY or sometimes EXISTS are the better long term alternatives.
Using SELECT DISTINCT is absolutly fine for developer ad-hoc queries.
I feel this whole concept is a bit inconsistent. 12c introduced the new approximation function APPROX_COUNT_DISTINCT, but there is no APPROX_COUNT_UNIQUE!
8. there is an ANSI datatype DATE
The ANSI datatype DATE does not hold any time information (no hours, minutes, seconds). That is what the ANSI datatype TIME is for. Oracle does not officially know these two datatypes.
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:
If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMPWITHTIMEZONE, TIMESTAMPWITHLOCALTIMEZONE, or INTERVALYEARTOMONTH.
If DAY is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMPWITHTIMEZONE, TIMESTAMPWITHLOCALTIMEZONE, or INTERVALDAYTOSECOND.
If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMPWITHTIMEZONE, TIMESTAMPWITHLOCALTIMEZONE, or INTERVALDAYTOSECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMPWITHTIMEZONE or TIMESTAMPWITHLOCALTIMEZONE.
So essentially the doc says, that EXTRACT uses an ANSI DATE as an expression input.
Proof
select extract(day from sysdate) from dual;
21
select extract(second from sysdate) from dual;
ORA-30076: invalid extract field for extract source
select extract(second from systimestamp) from dual;
4.823
The error appears, because sysdate is kind of converted into a ANSI DATE and this does not hold time information.
Problem is: We just can not use this ANSI date type for normal activities.
Alternative for the ANSI date: TRUNC(datetime). In 12c as a virtual column.
Problem solved. ✔
7. group by ()
You can group by an empty parenthesis. Or let’s call it group by the FULL SET. This is in fact useful in some very specific cases.
It gives us the ability to get a NO_DATA_FOUND error if there is no data at all. Oracle guarantees that an aggregation select without a group by will always return a record. GROUP BY () can be added if you want to change this default behaviour on purpose.
Example
Let’s say we want to count how many employees are in one specific department.
select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'OPERATIONS';
EMP#
----
0
Since there are no employees in operations, this returns 0. This is correct and the information I want.
More specifically this query returns one row, but the e.empno is null and therefore is not counted. Count(*) would have returned 1!
But if we use a department name that does not even exists, then this will also return 0.
select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE';
EMP#
----
0
Not correct! In this case I prefer to get a no data found error.
Grouping on the full set gives us this option.
select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE'
group by ();no rows found.
Btw: the SQL Developer syntax (pre-)parser identifies that () as an syntax error. But it will execute it successfully.
I have to admit, that in this case it would be much better to group by the department. But maybe it would be possible to get the count for multiple departments using an IN list. Then the group on department level would not help much.
Btw: GROUP BY NULL does the same thing, and might be less confusing for some developers. I personally prefere GROUP BY () because the () also is used in GROUPING_SETS.
6. + vs. –
In some edge cases using a different operator (+|-) leads to disturbing results.
Example:Substracting a tiny number from 1 does not equal 1. But adding a tiny number from 1 does equal 1!
select * from dual
where 1 = 1-0.000000000000000000000000000000000000001;
No data found!
This is expected behaviour…
select * from dual
where 1 = 1+0.000000000000000000000000000000000000001;
DUMMY
-----
X
Wat? Why do we get a result?!
The solution is simple. We overstepped the maximum precision of the number datatype when doing the addition. Max precision is 38 digits. The addition would require a precision of 39. Instead the result is rounded (or truncated) to a precision of 38 digits, which happens to equal 1. The substraction result still is in the 38 precision range and therefore exact (very slightly less than 1).
Don’t confuse precision with maximum value! Precision is the number of different digits that we can store. This can then be moved by an exponent to the left or right for very large or very small values.
The complete monetary assets in the whole word is estimated about 80 trillion $ (broad money=coins, banknotes, savings accounts, time deposits, …). That is a 80,000,000,000,000 or an 8 with 13 zeros at the end or 8e13 in scientific exponential notation.
select 8e13 from dual;
Considering all the national and private debts is even higher. Here we reach about 200 trillion $.
And if we consider derivates (=high risk gambles) then the high estimations go as far as 1.2 quadrillion $. That is a 1 followed by 24 more digits (mostly zeros) or 1.2e24.
Since all this is expressed in dollars, we can increase the number even further by chooseing a different currency. For example the Nepalease Rupee is worth a little less than 0.01 dollar. So all the worlds derivatees could be expressed in Nepalease Rupees using a number as big as 1.2e26.
That means using a number with a precision of 38 we can easily express all the money in the world including derivates up to the very last Nepalease Rupee. And we still have only used 2/3rds of the maximum available precision. Or to say it differently. If we duplicate the planet Earth 1,000,000,000,000 (1 trillion) times – we could still give the exact amount of money on all planets in Napalease Rupees up to the very last Rupee using the number datatype.
That’s quite impressive!
Btw. The biggest number that can be expressed using the NUMBER datatype is 999…(38 9’s) x10125 . Which is way way bigger than anything we discussed so far.
5. instead of IN we can use = ANY
select 'TRUE'
from dual
where 1 = ANY (1,2,3,4,5)
TRUE
There are several similar unusual “replacement” options.
I expect that SOME people do know about these operators, since questions about them are part of the SQL expert exam. So from ALL experts who took the exam at LEAST the few who passed should know SOMEthing about ANY.
The last replacement differs how NULL values are treated. In theory there could be cases where this comes in handy. I so far never had such an encounter.
Example
with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
select 5 a, 4 b, 3 c, 2 d from dual union all
select 1 a, null b, 2 c, null d from dual union all
select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= LEAST(a,b,c,d);
A B C D
-------
1 2 3 4
5 4 3 2
with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
select 5 a, 4 b, 3 c, 2 d from dual union all
select 1 a, null b, 2 c, null d from dual union all
select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= ANY(a,b,c,d);
A B C D
-------
1 2 3 4
5 4 3 2
1 - 2 -
4. external table on multiple files
External tables can load multiple files at once! As long as they have the same structure of cause. So here is an example.
CREATE TABLE EXT_DUMMY
(
"RECORDTYPE" VARCHAR2(100 BYTE),
"COL1" VARCHAR2(100 BYTE),
"COL2" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY "IMPORT_BAD_FILE"
ACCESS PARAMETERS (
records delimited BY newline
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
( RECORDTYPE CHAR
, COL1 CHAR
, COL2 CHAR
)
)
LOCATION ( 'Testfile1.txt, Testfile2.txt' )
)
reject limit 10
;
When we then do a select on the external table, we will see the data from both files.
We can also switch the external table to a different file.
alter table EXT_DUMMY location ('Testfile3.txt' )
For the experts: We can use the ROWID to find out which file was used to load a specific record. Special thanks go to OTN-forum member Odie_63 who found this solution. More info in this old OTN forum thread.
with ext_loc as (
select position-1 as pos
, name as filename
from sys.external_location$
where obj# = ( select object_id
from user_objects
where object_name = 'EXT_DUMMY' )
)
select x.filename,
t.*
from EXT_DUMMY t
join ext_loc x
on x.pos = to_number(regexp_substr(dump(t.rowid,10,9,1),'\d+$'))
;
It’s a very clever piece of software. Essentially it extracts the filenumber from the rowid, looks up the file number in the data dictionary and combines that with our data set.
This select was done in a 10g database. In 12c we can probably use dbms_rowid to do the same, instead of regexp_substr(dump(rowid)).
3. insert default values
We can insert into a table using the “default” keyword to force default behaviour for this column.
Example
-- setup
create table swe_default_test
(col1 varchar2(10) not null,
col2 varchar2(10) default 'TEST' not null
);
Table SWE_DEFAULT_TEST created.
-- test
insert into swe_default_test (col1) values ('X');
1 row inserted.
insert into swe_default_test (col1,col2) values ('X',null);
ORA-01400: cannot insert NULL into ("MYUSER"."SWE_DEFAULT_TEST"."COL2")
insert into swe_default_test (col1,col2) values ('X',default);
1 row inserted.-- double check
select * from swe_default_test;
COL1 COL2
---------- ----------
X TEST
X TEST
-- cleanup
drop table swe_default_test;
Table SWE_DEFAULT_TEST dropped.
In 12c we now have a new and I think better option to do the same. The default column can be defined additionally with “ON NULL”. Which has the effect, that inserting a NULL value, will lead to using the default value instead. Of cause the “default” keyword still works too.
-- setup
create table swe_default_test12c
(col1 varchar2(10) not null,
col2 varchar2(10) default on null 'TEST' not null
);
Table SWE_DEFAULT_TEST12C created.
-- test
insert into swe_default_test12c (col1,col2) values ('X',null);
1 row inserted.
insert into swe_default_test12c (col1,col2) values ('X',default);
1 row inserted.-- doublecheck
select * from swe_default_test12c;
COL1 COL2
---------- ----------
X TEST
X TEST
-- cleanup
drop table swe_default_test12c;
Table SWE_DEFAULT_TEST12C dropped.
As we can see both cases now work. The one using a NULL value and also useing the DEFAULT keyword.
Although there are some special optimizations in various tools, dual is a real table residing in the sys schema.
select owner, table_name
from dba_tables
where table_name = 'DUAL';
OWNER TABLE_NAME
----------------
SYS DUAL
In older database versions it was possible to do an insert into the DUAL table.
Warning! This is extremly dangerous. It will probably break ALL applications in your database.
insert into dual values ('Y');
select mysequence.nextval into v_id from dual;
TOO_MANY_ROWS error!
And nobody captures the TOO_MANY_ROWS exception for selects from dual.
Btw: A working safety mechanism is to add rownum = 1 to your query.
select mysequence.nextval into v_id from dual where rownum = 1;
Fortunately manipulating DUAL does not work anymore in recent versions of the database. I think 12c introduced it. Not even DBAs have the privs anymore to do this.
ORA-01031: insufficient privileges *Cause: An attempt was made to perform a database operation without the necessary privileges.
0. The Oracle SQL Syntax is documented!
Bam! There you have it! You wouldn’t have thought that. Well, you are not alone. So many developers seem to have no clue that this exists or where it exists.
When googeling use “oracle 12c doc” or “oracle 12.2 SQL ref” in front of your search term. It helps to find the proper pages.
Today is OTN Apprecitation day so I decided to write a short article about my favourite Oracle feature. It is the OTN SQL and PLSQL forum! Reading and posting on this forum made me a better developer. I also frequently visit other forums like Database General, Apex and lately the Oracle JET, but not as intensively as the SQL and PLSQL forum.
My OTN forum handle is Sven W.
greetings/honorable mentions
BluShadow – for moderating the forum and creating the FAQ list
Frank Kulash – for always answering in a nice and calm way insistently leading the OP to the final solution.
Billy Verrennye – for makeing me rethink old habits (like naming conventions) and for providing excellent and well thought source code examples.
William Robertson – for always beeing spot on
Odie_63 – For answering some of my questions, for example by taking apart the internal meaning of ROWIDs for external tables.
Boneist and ApexBine – for makeing their statements in a male dominated industry
Developers sometimes can be funny and sometimes they just need to boil of some steam.
Here is a collection of thread snippets from the past of the forum. Some years ago I collected memorable posts, but I don’t do this anymore. So the collection is slighty outdated now, but I tried to add a few recent quotes as well.
“OP” is used when I cite the “Original Poster” without giving the real forum handle. Otherwise usually only the first name for some of the well known members are used. Comments to the original quote from myself are in italic. Different threads are separated by a line.
Best of Forum 2007
OP> want a procedure or a block which will give a tree like structure using loops and cursors
3360> Why? Do you have a requirement to make this as slow as possible?
Dave Hemming> select ‘don”t stop me now, I”m having such a good time’ from dual
Special thanks to Dave! This is one of my favourite Queen songs.
APC> Late breaking newsflash: users are not developers!
Damorgan> Writing “working on tables” is as informative as writing “using keyboard and mouse.” See your instructor.
OP> SO CAN YOU PLEASE ASSIST ME ON THAT .
APC> On most keyboards the CAPSLOCK key is halfway down the lefthand side. Please learn to use it.
Billy> The features and flexibility and power of Oracle is NO substitution for a solid relational design.
Sentinel>
insert into table (column) values ('I have John''s shoes');
Sentinel>Of course what I’m doing with his shoes is a completely different story.
John Spencer> Since I only have one pair, I had to go to work barefoot this morning 🙂
John Spencer> Shoeless John
Damorgan> Without a context your posting is just a waste of perfectly good electrons.
OP>Is it possible to do something like this from a running program ?
Billy>Is it possible to jump from an aircraft at 5000 feet? Yes.
Billy>Of course, this has to be questioned as when it is done without a parachute, the changes of survival are very very slim. Never mind that if you’re the pilot, you are sending that plane down.
Billy>Yes, columns can be renamed dynamically from a running program. But it makes as much sense as jumping from a perfectly capable plane without a parachute.
APC> In general computing is about precision and removing ambiguity. That’s why the industry is full of pedants. Maddeningly, there is a direct correlation between pedantry and good programming.
Best of forum 2008
Billy>Be careful about making conclusions using observation only. Simple example. Observe how the WARP_SPEED hint makes the SQL go faster:
SQL> set timing on
SQL> select count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:09.60
SQL> select /*+ WARP_SPEED */ count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:00.50
SQL>
The empirical conclusion is that the WARP_SPEED hint made the query faster by 90%.
This conclusion (based on observation) is incorrect. The real reason why the 2nd query is faster is that it made substantially less physical I/O than the 1st query. The 1st query loaded a lot of data needed into the buffer cache. The 2nd query found that data there and had no need to perform the same expensive and slow physical I/Os that the 1st query did. Nor is there a WARP_SPEED hint.
So be very careful on making assumptions and basing conclusions solely on observation.
Sven> If you provide some example data and your insert statement as everybody suggested, we could give much better solutions without guessing all around.
Hans> But then we would only average one reply per question. And we would not get to spend as much time on the forums, getting to know each other so well.
Billy>Users make incredibly poor Oracle gods. That is what the DBA role is – godlike in Oracle and should be treated with care and respect and given only to those persons responsible for actual database administration. And no, users cannot administrate an Oracle database either.
WhiteHat>Hi all,
The Powers that Be at my work have decided to cut back on the number of different systems we have by re-writing a lot of them from scratch and combining functionality in order to reduce downtime caused by ETL processes and the like. so as a result I’m trying to implement the unified theory of everything in my stored proc and I can’t get it to work. Specifically I’m having difficulties combining quantum mechanics and general relativity into a single SQL statement. I’m getting ORA-06502: numeric or value error: String theory conversion error at line 3523
Is this possible in oracle v150.2.0.5 or will I have to upgrade to 153gR2?
being friday afternoon, my brain isn’t really in gear so I’m certain I’ve overlooked something simple. I suspect my basic architecture assumptions are incorrect but not sure. any advice?
Cheers,
WH.
Dave>It’s possible you’re trying to imply a quantum function to a relativistic variable. You’ll need to explicitly CAST it first.
Of course, I definitely think you need to show us your code. 🙂
Leo>Thats not always necessary as especially the quantum function sometimes can be decrypted itself by Oracle.
But definitely we need the code from line 3517.3 until line 3527.8
Billy>If this was ancient times, and you wrote this code to run on any of my databases, I would have handed your over to the SQL Inquisition for showing you the error of your ways.
Sarma>OP already told it is just an exercise for him on PL/SQL. In short, home-work.
Billy>Oh… I see.. You mean like attending Police College and committing, as home work, crimes like armed robbery, assault with a dangerous weapon, vehicular manslaughter, arson, and so on.
Billy>Yeah, I can see how this can teach you how to enforce the law.. NOT!
Justin>It’s generally helpful to specify the actual exception you’re getting rather than just saying “raises an exception”. Oracle error numbers and error strings are exceptionally useful debugging tools.
Michael O>Isn’t this the Oracle Support Forum where all wishes are granted?
Unknown>It is a basic problem that we face too here in forums. How do we show The Good Stuff of Oracle to a SQL-Server fanboy that cannot bare to empty his cup of SQL-Server in order to taste some Oracle?
Or dealing with a Java zealot that has been bitten badly by the J2EE religion, and sees Oracle as a mere persistence layer.. and not good for anything else?
Some people are so convinced that they are so absolutely right, they cannot even entertain the idea of something alternative.. never mind the idea that they just may be horribly wrong.
WhiteHat>[clippy]
Hi! it looks like you’re trying to use Oracle!
Do you:
( ) want to INSERT data to a table
( ) want to UPDATE existing data in a table
( ) ALTER the structure of the table
( ) search the internet for other queries
[clippy]
It’s not clear what you’re trying to do:
as we understand it it seems like:
you have a newly created table and you want to make it so there’s data in it is this correct?
OP> what if i will have thousands of record.i cant write
them all.
Dave> BANGS HEAD ON DESK
Dave> Instead of the select … from dual union select … from dual… perhaps you could use YOUR OWN TABLE.
OP> yeah i told you that i got it already so i dont need to bang head on desk……thanx neways
OP> Can you just tell me which is the best oracle performance tuning tool in the market? It should be free download.
Guido> It’s name is BRAIN (Biological Resource for All Informations Needs). If you really need to download that you should opt for another career path, I guess. 😉
OP>PL DESCRIBE U’R TABLE WORD.
Billy>Use proper English and not IM SPEAK as this is a technical forum and not some SMS teenage chat room.
padders>Please note however that it is considered acceptable to refer to someone’s ‘leet SQL skillz’.
Dave>Although it’s worth first establishing a reputation that clearly indicates that you do not think “irony” means “similar to iron”.
OP>i have run the package and it will take execution time more than 1 hour, how can i redure the execution time? any one help on this issue.
Matt>Remove all the code from the package.
shoblock> I really wish people would read the responses before they complain that they
aren’t working as desired.
APC> Aw c’mon. Next you’ll be wishing people would look stuff up in the documentation instead of straightaway posting questions here.
Laurent> of course regexp could save ink when printed
OP>i’ve try to add commit; but doesn’t work.
Dave>Glad to see you picked up on the need for a more complete explanation than “doesn’t work”.
Dave>Oh wait, you didn’t.
Someoneelse>That’s a new error in 11g:
Someoneelse>ORA-00042 DOESN’T WORK
Someonelse>IF Using_SQL_Server THEN
Someonelse> EXIT Oracle_Forums;
Someonelse>END IF;
Keith>If thats not clear, I’ll join the hitting head against the brickwall gang.
Billy> Do you fix the symptoms? Or do you fix the problem?
Padders>Erm. The problem I think. Aren’t we supposed to hit the symptoms with the lead pipe?
OP>Thread with title like “urgent help in sql plz ”
Billy>STOP!!
For that you need to fill in the “It Is Truly Urgent” form via the request link on the Oracle Forum main page. In triplicate. Submit it to the moderator. Wait for an urgency verification key to be supplied by the moderator. And only then can you post your urgent posting by attaching the urgency key to it for verification purposes.
Since you did not do it, your account is being reviewed for a possible 6 month suspension. You will also be prohibited from practicing Oracle during that time as you have illustrated the lack of common sense by posting this totally uncalled for and unwarranted “urgent” posting in this forum. And not applying common sense when using Oracle can cause serious injury to your database, cause serious damage to the scalability and performance of your applications, and may just cheese off your Oracle DBA resulting in a lead pipe being taken to your knee caps.
William>A right outer join is just a normal outer join written backwards to confuse everyone
More forum fun
John Stegeman> Last time I checked (1 minute ago), there is no “PL/SQL for SIM cards”
John Stegeman> Or even a entry in the mystical magical caverns of the registry, if none of those are set.
Ed Stevens> Please! I’ll do anything! I wash your car! I’ll mow your lawn! Just don’t send me to the registry!
Someoneelse> We are under attack!
The Database General forum is being flooded with spam!
Here are some of the userids: …
What the hell, is this a new feature of Jive?
jgarry> You want Jive to pump up social media, Jive pumps up social media.
jgarry> On other places I’ve been surprised by being blocked for too much posting.
I’m really not a robot! It’s hard to tune that limit right, and some people may compose things beforehand.
But worse, spammers would consider it damage and route around it, with whatever they need to do to have numerous logons.
Like when I knocked some fuzzy balls off the umbrella next to my pool and little black widow spiders scattered everywhere.
Dude!> There is already a feature in place that does not allow people to post one message right after another without waiting for a while; 5 min. if I remember correctly.
BluShadow> It’s 30 seconds Dude!, not 5 minutes.
Dude!> Ah well, time is relative
KayK> all you need is a DeLorean
Dude!> How long will it take until everything implodes?
Billy> Everything? I assume you are limiting “everything” to our solar system?
In that case, around 5 billion years from now, our sun will run out if fuel, shed its outer layers, and implodes into a white dwarf. Unfortunately it is too small to become a black hole. Which would be a kewl thing. Size some time matters.
Everything as in the universe? Guestimate is a 100 or so trillion years – depending on the theory you deem most likely (of which there are more than a few) describes the end of the universe. Implosion is just one of the theories. Perhaps an Asimov’s The Last Question end and beginning?
Dude!> I don’t worry so much about 5 billion years from now — not even history of the past 100 years is correct.
Billy> Disk space is cheaper than the effort to rebuild tables and indexes in order to reclaim space – and to support this effort as SOP.
William> So ‘QTR’ means ‘Quarter’? What is this, Twitter?
Jonathan Lewis> I got to the end to the first line (after the Hi) and thought: “we’re going to see a match_recognize() solution from Stew Ashton here”.
He was right.
“Re: What is the difference between select count(1) from tab and select count(*) from tab;”
Well after some short ramblings about performance and table sizes the gurus discussion went on to the right track.
Dave> One press on the shift key on my keyboard
William> “count(1)” is a nonstandard variation that takes more keystrokes and requires the parser to substitute “*” in place of the “1”, while making the person who wrote the query look foolish.
If you want an approximate result for a large data set quickly, have a look at the SAMPLE clause, e.g.
select count(*) * 20 from somebigtable sample(5);
Frank> Actually, on my keyboard, ‘1’ takes fewer keystrokes (depending on how you count) than ‘*’. To type ‘1’, I just press the ‘1’ key, but to type ‘*’ I have to hold down the SHIFT key and then press the ‘8’ key.
Even though it’s that much harder to type, “COUNT (*)” is still better than “COUNT (1)”, for the reasons you mentioned.
Jonathan> You may be taking too narrow a view on the problem – although the correct view may, of course, be keyboard-dependent. You need to step back from the 1/* dichotomy and consider the effect of parentheses:on the problem.
On my keyboard (*) requires me to do: {shift} 980 {release} (a total of 4 keystrokes – or 5 finger movements)
but (1) requires me to do: {shift} 9 {release} 1 {shift} 0 {release} (a total of 5 keystrokes – or 6 finger movements)
Note also that if you are a “classical typist” your are probably going to use {left shift}, which means a large movement to the 1, unless you use a numeric keypad – in which case the 9 requires you to make a large lateral movement with your right hand (which can then stay in place until after the 8 stroke, of course).
Youngsters these days! Just don’t think things through properly! (;)
William> Perhaps the round bracket keys are not shifted on some keyboards? I don’t think I’ve ever seen that though.
rp0428>Can you provide a specific reference to ANY of your books or blogs that cover an advanced topic such as this?
Sometimes ‘youngsters’ can benefit from seeing the explanation in context with some example code, trace files and execution plans.
Ospin> Just to inform for people with spain keyboards, this keyboards has “(” in shift+8 and “)” in shift+9, so is quit bit easy type “(8)”, so less finger movements and same results
John> To really figure this out, we probably need sql_trace for brains and bodies – when is Oracle going to wake up and put a bunch of SQL coders under a functional MRI scanner and do metabolic analysis to determine the precise effort involved?
However, i’ll say this: even if select(1) was an order of magnitude easier to type than select (*) (which it’s not), the dissonance and mental stress caused by seeing select(1) is probably enough to kill a few million brain cells of my own (not to mention people who come after me and have to read my code)…
In the old times, when it was still of some use to wish for the thing one wanted, there lived a King named Joel R. whose daughters were all handsome, but the youngest was so beautiful that the sun himself, who has seen so much, wondered each time he shone over her because of her beauty. The name of the little girl was Apex.
Near the royal castle build with bricks of Forms there was a great dark wood where birds were twittering and many squirrels were running up the trees. In the bark of some trees there were mysterious inscriptions from the foreign county of Java. And in the wood under an old js-tree was a font; and when the day was hot, the King’s daughter used to go forth into the wood and sit by the brink of the cool font. The font was simply awesome and if the time seemed long, she would take out a golden chart, and throw it up and catch it again, and this was her favourite pastime.
On her 12th Birthday Apex looked into a codemirror and saw Apex 5.1 always wearing beautiful purple boots. Now it happened one day that her boots were strapped so tight that the chart, instead of falling back into the maiden’s little hand which had sent it aloft, dropped to the ground near the edge of the well and rolled in. The king’s daughter followed it with her mobile-UI as it sank, but the well was deep, so deep that the bottom could not be seen. Then she began to weep, and she wept and wept as if she could never be comforted. And in the midst of her weeping she heard a voice saying to her: “What ails thee, king’s daughter Apex? Thy tears would melt a heart of stone.” And when she looked to see where the voice came from, there was nothing but a Toad stretching his thick ugly head out of the water. – “I weep because my golden chart has fallen into the font.” – “Never mind, do not weep,” answered the Toad, “I can help you; but what will you give me if I fetch up your chart again?” – “Whatever you like, dear toad,” said she, “any of my wizards, dynamic actions and interactive reports, or even the golden cloud that I wear.” – “Thy wizards, thy dynamic actions and interactive reports, and thy golden cloud are not for me,” answered the Toad, “but if thou wouldst love me, and have me for thy companion and play-fellow, and let me sit by thee at the Universal Theme, and eat from thy tables, and drink from thy views, and sleep in thy little pages, if thou wouldst promise all this, then would I dive below the water and fetch thee thy golden chart again.” – “Oh yes,” Apex answered, “I will promise it all, whatever you want, if you will only get me my chart again.” But she thought to herself: What nonsense he talks! As if he could do anything but sit in the water and croak with the other frogs, or could possibly be any one’s companion.
But the Toad, as soon as he heard her promise, drew his head under the font and sank down out of sight, but after a while he came to the surface again with the chart in his mouth, and he threw it on a nearby Peake. The King’s daughter was overjoyed to see her pretty plaything again, and she required it up and ran off with it. “Stop, stop!” cried the Toad, “take me up too. I cannot run as fast as you!” But it was of no use, Apex had no listener for him, and made haste home, and very soon forgot all about the poor Toad.
The next day, when the King’s daughter was sitting at table with the King and all the court, and eating from her golden plate, there came something up the marble stairs, and then there came a knockout at the door, and a voice crying: “Apex, Apex, let me in!” And she got up and ran to see who it could be, but when she opened the door, there was the Toad sitting outside. Then she shut the door hastily and went back to her server, feeling very uneasy. King Joel noticed how quickly her heart was beating, and said: “My child, what are you afraid of? Is there a giant Page Designer standing at the door ready to carry you away?” – “Oh no,” answered she, “no Page Designer, but a horrid Toad.” – “And what does the Toad want?” asked the King. “O dear father,” answered she, “when I was sitting by the font yesterday, and playing with my golden chart, it fell into the water, and while I was crying for the loss of it, the Toad came and got it again for me on condition I would let him be my companion, but I never thought that he could leave the application server and come after me; but now there he is outside the door, and he wants to come in to me.” And then they all heard him hammering the second time and crying:
“Youngest King’s daughter,
Open to me!
By the fonts water
What promised you me?
Youngest King’s daughter
Now open to me!”
“That which thou hast promised must thou perform,” said King Joel, “so go now and require him in.” So she went and opened the door, and the Toad hopped in. Then he stopped and cried: “Lift me up to install beside you.” But she delayed doing so until the King ordered her. When once the Toad was on the chair, he wanted to get on the table, and there he sat and said: “Now push your page a little nearer, so that we may eat together.” And so she did, but everybody might see how unwilling she was, and the Toad feasted heartily, but every jquery seemed to stick in her throat. “I have had enough now,” said the Toad at last, “and as I am tired, you must deploy me onto your server, and make ready your image folder, and we will lie down and go to sleep.” Then the King’s daughter began to weep, and was afraid of the cold Toad, that nothing would satisfy him but he must sleep in her pretty clean workspace. Now the King grew angry with her, saying: “That which thou hast promised in thy time of necessity, must thou now perform.” So she picked up the Toad with her finger and thumb, carried him upstairs and put him in a corner, and when she had lain down to sleep, he came creeping up, saying: “I am tired and want sleep as much as you; take me up, or I will tell your father.” Then she felt beside herself with rage, and picking him up, she threw him with all her strength against the browser, crying: “Now will you be quiet, you horrid Toad!”
But as he fell, he ceased to be a Toad, and became all at once a prince with beautiful kind shapes. And he told her his name was Jet and how the wicked witch of ADF had bound him by her spells, and how no one but she alone could have released him. Apex soon forgot about her old pal AnyChart and only had an UI for the young and beautiful Jet. And they two would go together to his father’s kingdom. And there came to the door an interactive grid, and behind the grid was standing faithful John Snyders, the servant of the young prince Jet. Now, faithful John had suffered such care and pain when his master was turned into a Toad, that he had been obliged to wear three iron libraries over his heart, to keep it from breaking with trouble and anxiety. When the grid started to take prince Jet to his kingdom, and faithful John had helped them both in, he got up behind, and was full of joy at his master’s deliverance.
And when they had gone a part of the way, the prince heard a sound at the back of the interactive grid, as if something had broken, and he turned round and cried:
“John, the other real data service must be breaking!”
“The ORDS does not break,
‘Tis the library round my heart
That, to lessen its ache,
When I grieved for your sake,
I bound round my heart.”
Again, and yet once again there was the same sound, and the prince thought it must be some ORDS breaking, but it was the breaking of the other library from faithful John’s heart, because he was now so relieved and happy.
svenweller 