Normalizing Data Sets

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). ...

May 27, 2017 · 7 min · Øyvind Isene

ORA-1722 and Regular Expressions

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: ...

February 19, 2017 · 1 min · Øyvind Isene

24 Basic Tips on Using SQL Developer

I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. Iremember that I back then googled “how to block Toad users from the database”. Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their job done. The following list is what I use often, and some of itresults in “How did you do that?” when they are looking over my shoulder. The shortcuts here refer to the Windows version since that is what I’m currently using at work. You can easily remap them from Preferences. ...

July 21, 2016 · 5 min · Øyvind Isene

Function to let user B see all tables of user A

In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A: create type str_set as table of varchar2(30); / create or replace function a_tables return str_set pipelined is l_str varchar2(30); begin for l_str in (select table_name from user_tables) loop pipe row(l_str.table_name); end loop; return; end; / grant execute on a_tables to B; Then user B can see the list of A’s table with: ...

September 27, 2015 · 1 min · Øyvind Isene

Corrupt database blocks III

If you don’t know what procrastination is you could start your own blog. Start a post with something like “I worked with … and decided to write three posts about it”. Procrastination is what you experience before the last post is ready; seemingly simple things take forever to get out the door. This post is important for my own record and shows how to salvage data from a large table with several corrupt blocks. I also had a learning experience I wanted to remember, explaining the redundancy of information here. Previous post was about how to map out tables and the corrupt blocks in a temporary table (that is, not a table created as a global temporary table, but a table you’ll drop when you’re finished.) ...

July 7, 2013 · 11 min · Øyvind Isene

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL. First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer. ...

December 28, 2011 · 2 min · Øyvind Isene