SQL

I wrote about UUIDs in the Oracle database two months back in this post , but it turns out that UUID v4 is so… 2005 . You probably need UUID v7 Head over to How UUIDv7 makes your (database) life easier to understand why you want v7. As mentioned in the article there is no native support for v7 in Oracle, neither a function to generate it or a proper datatype for UUIDs like PostgreSQL has. But the article shows an implementation of v7 generation in a PL/SQL function called generate_uuid_v7 that I used in some testing. ...

Update: I wrote another post about UUID v7 with some alternative solutions. UUIDs are useful, especially when you expose data in REST APIs, but there are cases where you may want to stick to the good old sequence-based primary key. UUIDs in Oracle Database #JoelKallmanDay UUID, short for universally unique identifier, are also known as globally unique identifier (GUID). You can use the function sys_guid() in the Oracle database to generate it: ...

Foursquare open sourced over 104 million points of interest (POIs) collected over several years. This is a large dataset perfect for testing and learning more about Spatial in the database. Oracle APEX has what you need to explore and display these places in your own browser. Meet Foursquare OS Places Foursquare used to be very popular. People where checking in everywhere with their mobile phones. In November last year Foursquare made these POIs available for download. You can read more about the dataset here, and you can explore the places with Foursquare Studio where the graphics above is taken from. ...

Oracle Flashback Time Travel aka Flashback Data Archive ensures you can track changes to the data and the structure of a table. Flashback Time Travel #JoelKallmanDay Flashback Time Travel previously called Total Recall lets you track all changes to the data in a table and even structural changes like added or removed columns. This means you can use it in various scenarios from detecting illegal state-transitions caused by bugs in application to compliance where full control of sensitive data is required. ...

ORA-01835 day of week conflicts with Julian date - This means your weekday in input is wrong. ORA-01835 This error message is also a bit confusing, especially if you were not using a Julian date at all; the error messages probably reflects how Oracle calculates dates. You are likely to encounter this error when you are do testing or some how construct a string to be parsed by to_date or to_timestamp. It just means that you specified an incorrect weekday for a date. Like today, April 17, 2024 is a Wednesday. ...

The error message ORA-01950 should be easy to solve, but perhaps you forgot the indexes? ORA-01950 ORA-01950 no privileges on tablespace … usually has a couple obvious solutions. If the tablespace in the error message is wrong like SYSTEM, the default tablespace must be changed for the user. Change it with something like this: alter user luser default tablespace users quota 1g on users; If the tablespace is correct, the user may be lacking a quota on the tablespace. The fix is almost the same: ...

Large Language Models (LLM) are everywhere now, now you can even get help with your SQL. A few months back, Oracle introduced a feature in the Autonomous Database where you can ask normal questions in order to query your data. First Journey with SelectAI Brendan Tierney at Oralytics has written two good articles, SelectAI – the beginning of a journey and SelectAI – Doing something useful on a cool feature in the Oracle Autonomous database; SelectAI. He shows how you in a few steps can query your data using natural language. I followed the steps in his posts, and in a few minutes got it to work in my own Autonomous Database that I am using in the Oracle Free Tier. ...

Be careful with identity columns. Primary Key as Generated Identity From Oracle 12c you can have Oracle generate the primary key for you. As an example: create table customer ( id number generated always as identity, name varchar2(50), constraint customer_pk primary key (id)); Later you can remove this identity with an alter table command, but you can’t add it back! SQL> alter table customer modify (id drop identity); Table CUSTOMER altered. SQL> alter table customer modify (id number generated always as identity); Error starting at line : 1 in command - alter table customer modify (id number generated always as identity) Error report - ORA-30673: column to be modified is not an identity column 30673.0000 - "column to be modified is not an identity column" *Cause: An attempt was made to modify the identity properties of that is not an identity column. *Action: Modify the identity properties of an identity column. You can check the documentation for 21c , there is no other syntax to solve this issue. ...

Epoch time is common in some situations because it is easier to do date calculations than with normal dates. The most popular is UNIX Epoch time which is the number of seconds since the start of January 1, 1970. If you have your data in Oracle you don’t have to worry about date arithmetic, since Oracle handles that for you. So in case you have such a table with epoch time and want to query it with functions that expect the DATE datatype, you can add a virtual column that returns the epoch time converted to DATE like this: ...

Oracle Developer Community Most of my work in the Oracle world has been DBA-oriented. But for the last years, I have rediscovered the joy of development and data analysis. When I haven’t been writing the code myself, I have often helped other developers when they connect to the Oracle database, and their program does not perform as expected. Developers coding in Java, Python, or most other programming languages spend time on figuring out how to get the work done. They learn algorithms to discover the fastest way to do it, or the lazy coders just punch out code without wondering too much about efficiency. If their test data is small, performance problems usually get detected after release to production with ensuing frustrations. ...