Posts

A colleague said that he had some tables that showed up in one tool, but not in another, I think it was Enterprise Manager/Cloud Control. This is easy to forget if you don’t work with non-relational tables regularly. What happens is that only relational tables are listed in DBA_TABLES or USER_TABLES, but all tables can be found if you search DBA_OBJECTS / USER_OBJECTS for objects with OBJECT_TYPE=TABLE. The documentation actually says: USER_TABLES describes the relational tables owned by the current user. You can try this code to demonstrate it: ...

Have you been in a presentation with the feeling that the speaker is talking over your head? As if he is speaking to a select group, maybe trying to impress them? The selection of words and expressions, subject for the talk may sometimes give a feeling of cleverness, yet you can’t relate to it. Instead you walk away thinking you are not remotely smart enough to give a presentation. ...

Here the other day I was helping my son with his homework. His task was to create a simple data model, create it in MySQL, and make a simple web page to access the data. I thought that after creating a few INSERT statements on his own I could help him with demo data. Since he had decided to create a movie database I googled for some movie data sets and decided to use the IMDB 5000. You can download it from Kaggle.com (free registration probably required). ...

I just got started. With a new blogging platform. I was frustrated with the way things went and wanted to start over with something else, something simple and not pretentious at all. Something that let me spend time on writing the post, and not on formatting the code when I had some SQL to show. Just plain and simple. My previous blog used to be mostly about Oracle, but I spend more and more time looking into Hadoop, Spark and related software. I wanted a new place where I can blog exactly about what I am working on without worrying if it fits in. If I solve a problem with Spark on my mini-cluster I now have a place to share it. ...

If you like the simplicity of Docker, but would like to continue with Virtualbox, then Vagrant is for you. After the conference in Dublin, OUG Ireland, I got this challenge: @OyvindIsene agreed to be my guinea pig for my @vagrantup files. That’s what you get when going to a conference! ;) https://t.co/pJDisr4Mhp — Gerald Venzl (@GeraldVenzl) March 24, 2017 Not much of a challenge, of course, since he has done all the work, it is just “Download and Go”. Just follow the instructions in the README part on the page linked to in his Tweet. I did this on my Mac at the airport (I found a downloaded copy of 12.2 after all). The only comment I could make is that the git command creates a directory (oracle-12.2-vagrant) and you copy the downloaded zip file (linuxx64_12201_database.zip) into that directory. ...

Oracle Database 12.2 became available for download last week. This weekend I’ve been playing with Docker and created a container with it. The whole process is so easy, thanks to the work by Gerald Venzl at Oracle. You’ll find all the information you need in his blog post . Though the post is for version 12.1.0.2, the dockerfiles have already been updated for 12.2.0.1, and you can download it from Github using the link he provides in the post. ...

Every 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short0, I figured I should make a reminder here. Of course, you’ll find this on Stackoverflow as well. There is this function in APEX, which is usually1 available for you in the database, even if you are not using APEX. Here is a short demo: ...

Yesterday I was importing some data I downloaded. When creating a new table I tried to convert two columns with latitude and longitude, stored as VARCHAR2, to numbers (in order to use them in the SDO_GEOMETRY constructor). The create table as select (CTAS) statement failed with ORA-01722. Problem is it does not tell you which line or what value is the offender. You may turn on some tracing with event 1722, but using regular expression is much easier: ...

If you need to delete all rows in a table that has parent keys for other tables’ foreign keys, and the foreign keys constraints have not been defined with “on delete cascade”, you can do a recursive delete with the following simple procedure. This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it: ...

There are many ways to display spatial data, but when exploring a new data set in SQL Developer I think the built in Map View is practical. For this demo I used a list of nuclear power stations in the world, found via http://freegisdata.rtwilson.com/ . The Google Fusion Table can be exported to CSV format, and it includes the longitude and latitude which makes it easy to convert to SDO_GEOMETRY. After importing the file with SQL Developer into a table called NUCLEAR, I did this to add native Spatial data: ...