RDBMS Insight

Subscribe to RDBMS Insight feed
natalka's oracular insights
Updated: 18 hours 15 min ago

Blogs to read in the Oracle DBA/Developer world

Sat, 2016-10-29 16:23

:Earlier this month, I conducted a totally unscientific survey on Twitter, asking where people got their Oracle news from. Twitter and the NoCOUG Journal were two popular sources, along with a wide range of blogs. Here are some of the blogs that the Oracle DBA & Dev superstars in my Twitter like to read:

BTW, I notice that David Fitzjarrell’s blog was named one of the top 50 SQL blogs in 2016 by Ben’s DatabaseStar blog, itself a good blog with a strong focus on Oracle SQL for developers. I recommend checking out Ben’s list for more SQL blogs!

I also learned about a couple of terrific Oracle blog aggregators:

I’ve got a couple of my own to add to the list.

I also follow the articles on orafaq.com:
http://www.orafaq.com/articles

And there are some official Oracle blogs I like to keep my eye on:

And of course, there’s Ask Tom, which has an RSS feed for newest questions.

I’m experimenting with Feedly as my new RSS reader. Do you have a favorite Oracle blog, magazine or RSS reader? Let me know in the comments!

Categories: DBA Blogs

HOWTO solve any problem recursively, PL/SQL edition…

Mon, 2016-06-20 17:47
PROCEDURE solve (my_problem IN varchar2) IS
BEGIN
  my_idea := have_great_idea (my_problem) ;
  my_code := start_coding (my_idea) ;
  IF i_hit_complications (my_idea)
  THEN 
    new_problem := the_complications (my_idea);
    solve (new_problem);
  ELSE
    NULL; --we will never get here
  END IF;
END solve;

This abuse of recursion was inspired by @ThePracticalDev !

Categories: DBA Blogs

What’s in a name? – “Brittany” edition

Mon, 2016-06-20 07:46

In my last post, I loaded US SSA names data into my dev instance to play with. In this post, I’ll play around with it a bit and take a look at the name “Brittany” and all its variant spellings.

I found nearly 100 different spellings of “Brittany” in the US SSA data thanks to a handy regexp:

SELECT name nm, SUM(freq) FROM names 
 WHERE regexp_like(UPPER(name),'^BR(I|E|O|U|Y)[T]+[AEIOUY]*N[AEIOUY]+$' )
 AND sex='F'
GROUP BY name
ORDER BY SUM(freq) DESC;
NM				SUM(FREQ)
------------------------------ ----------
Brittany			   357159
Brittney			    81648
Britney 			    34182
Brittani			    11703
Britany 			     6291
Brittni 			     5985
Brittanie			     4725
Britni				     4315
Brittny 			     3584
Brittaney			     3280
...
Bryttnee			       10
Britttany				7
Brytanie				7
Brittanae				6
Bryttnii				6
...
Brittanii				5
Brittiana				5
 
91 rows selected.

The regexp isn’t perfect. It returns a few uncommon names which aren’t pronounced “Brittany”: “Brittiana”, “Brittiani”, “Britane”, “Brittina”, “Britanya”, “Brittine” – and one I’m not sure about, “Brittnae”. But on the other hand, it did let me discover that 7 “Britttany”s applied for SSNs in 1990. Yes, that’s “Britttany” with 3 “T”s.

Fortunately, all the “non-Brittanys” the regexp returns are quite uncommon and not even in the top 20. So the regexp will do for a graph of the top spellings. Let’s get the data by year and look at the percentage of girls in each year named Brittany/Brittney/Britney/Brittani:

WITH n AS (SELECT name nm, YEAR yr, sex, freq FROM names 
 WHERE regexp_like(UPPER(name),'^BR(I|E|O|U|Y)[T]+[AEIOUY]*N[AEIOUY]+$' )
 AND sex='F'),
y AS (SELECT  YEAR yr, sex, SUM(freq) tot FROM names GROUP BY YEAR, sex)
SELECT y.yr, 
decode(n.nm,'Brittany','Brittany', -- like Brittany Furlan
'Brittney','Brittney', -- like Brittney Griner
'Britney','Britney', -- like Britney Spears
'Brittani','Brittani', -- like Brittani Fulfer
'Other Brits') AS thename,
nvl(100*freq/tot,0) pct  FROM n, y 
WHERE  n.sex(+)=y.sex AND n.yr(+)=y.yr AND y.yr >= 1968
ORDER BY y.yr, nvl(n.nm,' ')

I graphed this in SQL Developer:
britts
From the graph it’s clear that “Brittany” is by far the most popular spelling, followed by “Brittney”. The sum of all Brittany-spellings peaked in 1989, but “Britney” has a sharp peak in 2000 – the year that singer Britney Spears released Oops I Did It Again, “one of the best-selling albums of all time” per Wikipedia.

This makes Brittany, however you spell it, a very early-90s-baby kind of name. “Brittany” was the #3 girls’ name in 1989, behind Jessica and Ashley, and was not nearly as popular in decades before or since. In subsequent posts I’ll look some more at names we can identify with specific decades.

Categories: DBA Blogs

What’s in a name? or rather, in the SSA Names data

Thu, 2016-06-09 14:31

One of the amazing things about being a DBA/developer in 2016 is the sheer amount of freely available, downloadable data to play with. One fun publicly available data sets is the American Social Security Administration names data. It contains all names for which SSNs were issued for each year, with the number of occurrences (although names with <5 occurrences are not included to protect individual privacy).

What’s so fun about this dataset?

* It’s already normalized

* It updates only once a year, and then only by adding another year’s worth of data, so it’s easy to keep current

* Almost everyone can relate to this dataset personally – almost everyone’s name is in there!

* At about 1.8 million rows, it’s not particularly large, but it’s large enough to be interesting to play with.

The one slight annoyance is that the data is in over 100 files, one per year: too many to load one-by-one manually. So here’s a blog post on loading it into your Oracle database, with scripts.

1. Visit the URL:
https://catalog.data.gov/dataset/baby-names-from-social-security-card-applications-national-level-data

2. Download and unzip names.zip . This zip archive contains one file for each year from 1880 to 2015. The files are named yobXXXX.txt eg. yob2015.txt .

3. Create a table to hold the names data:

DROP TABLE names;
CREATE TABLE names (YEAR NUMBER(4), name varchar2(30), sex CHAR(1), freq NUMBER);

4. Load in one year to get a feeling for the data. Let’s load “yob2015.txt”, the most recent year.
Here’s a sql*loader control file “names.ctl” to load the data:

[oracle@localhost names]$ cat names.ctl
load data 
infile 'yob2015.txt' "str '\r\n'"
append
into table NAMES
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( NAME CHAR(4000),
             SEX CHAR(4000),
             FREQ CHAR(4000),
             YEAR "2015"
           )

(By the way, here’s a great tip from That Jeff Smith: Use sql developer to generate a sql*loader ctl file. )
Now let’s use the ctl file to load it:

[oracle@localhost names]$ sqlldr CONTROL=names.ctl   skip=0  
Username:scott/********
 
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Jun 9 10:41:29 2016
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Path used:      Conventional
Commit point reached - logical record count 20
 
...
Table NAMES:
  32952 Rows successfully loaded.
 
Check the log file:
  names.log
for more information about the load.

5. Let’s take a look at the 2015 data! How about the top 10 names for each sex?

WITH n AS 
  ( SELECT name, sex, freq, 
  rank() OVER (partition BY sex ORDER BY freq DESC) AS rank_2015
  FROM names 
  WHERE YEAR=2015 )
SELECT * FROM n
WHERE rank_2015 < 11
ORDER BY sex, rank_2015;
NAME			       S       FREQ  RANK_2015
------------------------------ - ---------- ----------
Emma			       F      20355	     1
Olivia			       F      19553	     2
Sophia			       F      17327	     3
Ava			       F      16286	     4
Isabella		       F      15504	     5
Mia			       F      14820	     6
Abigail 		       F      12311	     7
Emily			       F      11727	     8
Charlotte		       F      11332	     9
Harper			       F      10241	    10
 
NAME			       S       FREQ  RANK_2015
------------------------------ - ---------- ----------
Noah			       M      19511	     1
Liam			       M      18281	     2
Mason			       M      16535	     3
Jacob			       M      15816	     4
William 		       M      15809	     5
Ethan			       M      14991	     6
James			       M      14705	     7
Alexander		       M      14460	     8
Michael 		       M      14321	     9
Benjamin		       M      13608	    10

6. Now let’s load the names data for the other 135 years.
First we’ll create a generic “names.ctl”:

$ cat names.ctl
load data 
infile 'yob%%YEAR%%.txt' "str '\r\n'"
append
into table NAMES
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( NAME CHAR(4000),
             SEX CHAR(4000),
             FREQ CHAR(4000),
             YEAR "%%YEAR%%"
           )

Now we’ll write a small shell script to substitute %%YEAR%% for each year from 1880 to 2014, and load that year’s file.

$ cat names.sh
#!/usr/bin/bash
export TWO_TASK=orcl
for i in {1880..2014}
do
  echo "generating yob$i.ctl"
  sed s/%%YEAR%%/$i/g names.ctl > yob$i.ctl
  echo "loading yob$i"
  sqlldr username/password CONTROL=yob$i.ctl
  echo "done $i"
done
 
[oracle@localhost names]$ ./names.sh
... massive screen output...
 
[oracle@localhost names]$ grep "error" *.log
yob1880.log:  0 Rows not loaded due to data errors.
yob1881.log:  0 Rows not loaded due to data errors.
yob1882.log:  0 Rows not loaded due to data errors.
yob1883.log:  0 Rows not loaded due to data errors.
...
yob2012.log:  0 Rows not loaded due to data errors.
yob2013.log:  0 Rows not loaded due to data errors.
yob2014.log:  0 Rows not loaded due to data errors.

7. Now we can play with the data a bit!

Here’s a quick look at the popularity of 2015’s top girls’ names since 1880:

WITH n2015 AS 
  ( SELECT name, sex, freq, 
  rank() OVER (partition BY sex ORDER BY freq DESC) AS rank_2015
  FROM names 
  WHERE YEAR=2015 )
, y AS (SELECT  YEAR, sex, SUM(freq) tot FROM names GROUP BY YEAR, sex)
SELECT names.year, names.name, 100*names.freq/tot AS pct_by_sex
FROM n2015, y, names
WHERE n2015.name = names.name AND n2015.sex = names.sex
AND y.year = names.year AND y.sex=names.sex
AND n2015.rank_2015 < 11
AND y.sex='F'
ORDER BY YEAR, name;

I graphed this in SQL Developer. Click to embiggen:
2015_girls_allyrs

You can see that Emma, my grandmother’s name, is having a bit of a comeback but is nowhere near the powerhouse it was in the 1880s, when 2% of all girls were named Emma. (For the record, my grandmother was not born in the 1880s!)

My next post will look at the name Brittany and its variants.

Note: You can download the names.ctl and names.sh from github here.

Categories: DBA Blogs

“What do you mean there’s line breaks in the address?” said SQLLDR

Fri, 2016-05-20 19:55

I had a large-ish CSV to load and a problem: line breaks inside some of the delimited fields.

Like these two records:

one, two, "three beans", four
five, six, "seven
beans", "eight wonderful beans"

SQL Loader simply won’t handle this, as plenty of sad forum posts attest. The file needs pre-processing and here is a little python script to do it, adapted from Jmoreland91’s solution on Stack Overflow.

import sys, csv, os
 
def hrtstrip (inputfile,outputfile,newtext):
    print("Input file " + inputfile)
    print("Output file " + outputfile)
    with open(inputfile, "r") as input:
       with open(outputfile, "w") as output:
          w = csv.writer(output, delimiter=',', quotechar='"', 
quoting=csv.QUOTE_NONNUMERIC, lineterminator='\n')
          for record in csv.reader(input):
             w.writerow(tuple(s.replace("\n", newtext) for s in record))
    print("All done")

Thanks to Jmoreland91 for this. If you use it, give him an updoot.

edit – Jason Bucata (@tech31842) tweeted me another StackOverflow with a number of scripts in assorted languages: http://stackoverflow.com/questions/33994244/how-to-remove-newlines-inside-csv-cells-using-regex-terminal-tools

Categories: DBA Blogs

Tip of the day: Always put this in your .bashrc

Mon, 2016-05-09 06:52

if you like to scp:

# If not running interactively, don't do anything
[[ $- == *i* ]] || return

Otherwise scp will fail without error – it’s a known bug.

Categories: DBA Blogs

SQL vs. Excel – Subgroup medians

Mon, 2016-05-02 19:34

Recently I ran across this post on how to do subgroup medians in Excel 2010. First you need to create a pivot table, then “do some copying and pasting and use a formula to make it happen”. In SQL you can do this with one command.

Suppose that you have the same table as the Excel article, something like this:

CREATE TABLE sampletab
(arrest_day_of_week varchar2(10), 
arrest_ts TIMESTAMP, 
fingerprint_ts TIMESTAMP, 
days_between NUMBER);

and you want to get the median value of days_between for each day of the week.

The steps in Excel apparently go like this:

  1. Create pivot table to get the means
  2. Copy and paste the column values from the pivot table
  3. For Sunday, create an IF formula to include a cell’s days_between in the median calculation only if the arrest_day_of_week for that row is Sunday
  4. Repeat for other six days

Ouch!

In SQL, it’s one simple statement:

SELECT arrest_day_of_week, median(days_between) AS median_days_between FROM sampletab
GROUP BY arrest_day_of_week;

Conclusion – if you’re into data analysis, SQL can be a big time-saver!

Categories: DBA Blogs

Got anyone who needs April Fooling?

Thu, 2016-03-31 20:15

Do you have a sql*plus user who really needs an April Fool’s joke played on them? With a little editing to their glogin.sql, every sql*plus session will exit with what appears to be a pseudo-random TNS error.

(Note: assumes a *nix environment that has sed, grep, awk installed and oerr properly working.)

[oracle@localhost ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@localhost admin]$ mv glogin.sql glogin.old
[oracle@localhost admin]$ vim glogin.sql
 
--Added by APRIL FOOL
 
set echo off
set feedback off
set verify off
set head off
col the_err new_value myerrno
col the_user new_value uname
set termout off
select 
'125' || decode( to_char(sysdate,'SS'),'01','61','06','64','07',
'66','17','69','30','70','53','71','59','82', to_char(sysdate,'SS')) 
as the_err
from dual;
select user as the_user from dual;
set termout on
clear columns
prompt ERROR:
host oerr ora &myerrno | grep -v '//' | sed 's/"//g' | awk -F, '{print "ORA-" $1 ": " $3}'
prompt SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
exit &myerrno
 
--End added by APRIL FOOL
 
:wq
 
[oracle@localhost admin]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 31 17:50:39 2016
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
ERROR:
ORA-12539:  TNS:buffer over- or under-flow
 
SP2-0751: Unable to connect to Oracle.	Exiting SQL*Plus
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
[oracle@localhost admin]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 31 17:50:52 2016
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
ERROR:
ORA-12552:  TNS:operation was interrupted
 
SP2-0751: Unable to connect to Oracle.	Exiting SQL*Plus
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
[oracle@localhost admin]$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 31 17:51:02 2016
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
ERROR:
ORA-12502:  TNS:listener received no CONNECT_DATA from client
 
SP2-0751: Unable to connect to Oracle.	Exiting SQL*Plus
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Disclaimer – use at your own risk to workplace harmony!

Categories: DBA Blogs

Review: Oracle RAC Performance Tuning

Sat, 2016-01-02 15:52

Some time ago, I received a free review copy of Brian Peasland‘s recent book, Oracle RAC Performance Tuning.

First, a note on my RAC background: I spent 7 years on Oracle’s RAC Support team. When customers had an intractable RAC performance issue, I was on the other end of the “HELP!” line until it was resolved.

I made Brian’s acquaintance through the MOS RAC Support forum, where Brian stood out as a frequent poster who consistently gave well-thought-out, correct and informative responses. So I had high expectations when I sat down with his book. And I haven’t been disappointed. This book is a terrific resource for single-instance DBAs looking to come up to speed on RAC. It’ll also be useful to more experienced RAC DBAs who want to deepen their knowledge or who just have a thorny performance problem to solve.

Many RAC books start out with an overview of RAC-specific physical architecture: the interconnect and the shared storage. Not this one. Brian leaps straight into what I consider the “hard” stuff: chapter 2 covers Cache Fusion and understanding RAC-specific wait events. I’ve spoken with many RAC DBAs who’d have a hard time telling me the difference between “gc cr block 2-way” and “gc current grant 3-way”. You really need to understand Oracle’s implementation of Cache Fusion to understand many of the RAC wait events, and Chapter 2 does a good job of explaining, using session tracing to step you through the waits. It might seem odd to start out with detailed explanations of wait events that many RAC DBAs will never see in the Top 10. But, a good understanding of Cache Fusion and the related wait events is really necessary to understand RAC-specific slowdowns. Subsequent chapters depend implicitly on this understanding: you can’t really understand interconnect tuning, for instance, unless you understand how the interconnect is used by Cache Fusion.

The book covers a full toolkit of testing utilities and tools as needed: Orion is introduced in the chapter on storage, and then a full chapter is devoted to the RAC Support Tools, another to AWR/ADDM/ASH, and another to benchmark utilities. There are also dozens of SQL scripts.

Another chapter to highlight is Chapter 14, a two-page summary at the end of the book that lists what Brian considers the central points. This is a mix of broad principles and RAC-specific “gotchas” that every RAC DBA should be aware of. I’d say that if you can read through Chapter 14 and say “I knew that” to each point, then you’ve got a good grasp of the essentials of RAC tuning.

Like others I’ve read in Burleson’s Oracle In-Focus series, this book would’ve benefited from a stronger copy editor. I was chagrined to see typos right on the back cover. But that’s a small quibble that doesn’t detract from an excellent book. If you’re a RAC DBA, this book deserves a place on your bookshelf.

Categories: DBA Blogs

Roman numerals to decimal in SQL

Sat, 2015-11-07 06:38

Earlier this week I got tangled up doing a Roman Numeral conversion in my head. So of course my second thought, right after “Doh!”, was “I bet I can write a SQL statement to do this for me next time.”

The algorithm to convert roman numerals to decimal numbers is straightforward.

    For each character, starting from the RIGHT (lowest value Roman numeral):

  • Convert the character into the value it represents
  • If the character’s value is greater than or equal to the previous one, add the value to the running total
  • If character’s value is less than the previous one, subtract the value from the running total

Let’s look at a couple of examples:

    IX
    Starting from the right:

  • X = 10; Running total = 10
  • I = 1; 1 < 10; Running total = 9
  • Final result: 9
    MCMLXXVII
    Starting from the right:

  • I = 1; Running total = 1
  • I = 1; Running total = 2
  • V = 5; Running total = 7
  • X = 10; Running total = 17
  • X = 10; Running total = 27
  • L = 50; Running total = 77
  • M = 1000; Running total = 1077
  • C = 100; 100 < 1000; Running total = 977
  • M = 1000; Running total = 1977

Here’s an online converter

This is a lovely place to use Recursive With in SQL. We’ll use a CTE, aka recursive subquery, to chomp the string one character at a time from the right, keeping a running total as we go; first decode the character, then add or subtract as appropriate to the running total until we’re out of characters.

See my previous post on Recursion with Recursive With. From that post, here’s the recursive WITH basic syntax:

WITH Tablename (col1, col2, col3...) AS
(SELECT A, B, C... FROM dual                         --anchor member
UNION ALL
SELECT newA, newB, newC... FROM Tablename WHERE...   --recursive member
)
SELECT ... FROM Tablename WHERE ...

Let’s start with just identifying the characters and substrings we want to work on, using substr and length.
Substr: http://docs.oracle.com/database/121/SQLRF/functions196.htm#SQLRF06114
Length: http://docs.oracle.com/database/121/SQLRF/functions100.htm#SQLRF00658

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
RomToDec (ThisOne, Remaining, Pos) AS
(SELECT CAST(NULL AS varchar2(4000)) AS ThisOne, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT  
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos > 0
)
SELECT ThisOne, Remaining, Pos FROM RomToDec
;
VAL THISONE  REMAINING     POS
0     -       MDCCVIII     8
0     I       MDCCVII      7
0     I       MDCCVI       6
0     I       MDCCV        5
0     V       MDCC         4
0     C       MDC          3
0     C       MD           2
0     D       M            1
0     M       -            0
0     -       -            -

Now we use CASE to turn the Roman Numeral character into a number.
Case: http://docs.oracle.com/database/121/SQLRF/expressions004.htm#SQLRF20037

WITH Roman (ThisOne) AS
(SELECT 'M' AS ThisOne FROM dual)
SELECT 
CASE ThisOne 
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec
FROM Roman;

I’m only going to deal with values of less than 5000 so M will be the largest we need. I’m going to patch that in to my query-in-progress, and also add a “Val” column to hold the running total:

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
RomToDec (Val, ThisOne, ThisDec, Remaining, Pos) AS
(SELECT 0 AS Val, CAST(NULL AS varchar2(4000)) AS ThisOne, 0 AS ThisDec, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
RomToDec.Val, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos > 0
)
SELECT Val, ThisOne, ThisDec, Remaining, Pos FROM RomToDec
;
VAL    THISONE  THISDEC     REMAINING    POS
0      -        0           MDCCVIII     8
0      I        1           MDCCVII      7
0      I        1           MDCCVI       6
0      I        1           MDCCV        5
0      V        5           MDCC         4
0      C      100           MDC          3
0      C      100           MD           2
0      D      500           M            1
0      M     1000           -            0

An alternative to using CASE here would be to add another inline table in the WITH clause and join with it:

WITH Roman (Numeral) AS
(SELECT 'MDCCVIII' AS Numeral FROM dual),
CharValues (Numeral, VALUE) AS (
SELECT 'M', 1000 FROM dual UNION ALL
SELECT 'D', 500  FROM dual UNION ALL
SELECT 'C', 100  FROM dual UNION ALL
SELECT 'L', 50   FROM dual UNION ALL
SELECT 'X', 10   FROM dual UNION ALL
SELECT 'V', 5    FROM dual UNION ALL
SELECT 'I', 1    FROM dual ),
RomToDec (Val, ThisOne, ThisDec, Remaining, Pos) AS
(SELECT 0 AS Val, CAST(NULL AS varchar2(4000)) AS ThisOne, 0 AS ThisDec, Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
RomToDec.Val, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
nvl(CharValues.Value,0) ThisDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec LEFT OUTER JOIN CharValues ON ( substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1) = CharValues.Numeral )                      
WHERE RomToDec.Pos > 0 
)
SELECT Val, ThisOne, ThisDec, Remaining, Pos FROM RomToDec
;

Now finally we can add or subtract ThisDec from the running total using the rule:

    For each character starting from the left,

  • If character’s value is >= previous character’s value, add it to the running total
  • If character’s value is < previous character's value, subtract it from the running total

To implement this, I added another column to the recursive member, LastDec, which just holds the previous value of the ThisDec column. I also could have used the LAST analytic function here, but since we’re using recursive subquery it’s trivially easy to just populate the LastDec column with RomToDec.ThisDec .

WITH Roman (Numeral) AS
(SELECT 'MCMLXXXVII' AS Numeral FROM dual),
RomToDec (ThisVal, ThisOne, ThisDec, LastDec, Remaining, Pos) AS
(SELECT 0 AS ThisVal, CAST(NULL AS varchar2(4000)) AS ThisOne, 
0 AS ThisDec, 0 AS LastDec,
Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
CASE 
  WHEN RomToDec.ThisDec >= RomToDec.LastDec THEN RomToDec.ThisVal + ThisDec 
  ELSE RomToDec.ThisVal-ThisDec
END AS ThisVal, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
RomToDec.ThisDec AS LastDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos >= 0
)
SELECT * FROM RomToDec ;

And the final result:

WITH Roman (Numeral) AS
(SELECT 'MCMLXXXVII' AS Numeral FROM dual),
RomToDec (ThisVal, ThisOne, ThisDec, LastDec, Remaining, Pos) AS
(SELECT 0 AS ThisVal, CAST(NULL AS varchar2(4000)) AS ThisOne, 
0 AS ThisDec, 0 AS LastDec,
Roman.Numeral AS Remaining , LENGTH(Roman.Numeral) AS Pos 
FROM Roman
UNION ALL
SELECT 
CASE 
  WHEN RomToDec.ThisDec >= RomToDec.LastDec THEN RomToDec.ThisVal + ThisDec 
  ELSE RomToDec.ThisVal-ThisDec
END AS ThisVal, 
substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  AS ThisOne, 
CASE substr(RomToDec.Remaining,LENGTH(RomToDec.Remaining),1)  
  WHEN 'M' THEN 1000
  WHEN 'D' THEN 500
  WHEN 'C' THEN 100
  WHEN 'L' THEN 50
  WHEN 'X' THEN 10
  WHEN 'V' THEN 5
  WHEN 'I' THEN 1
ELSE 0 END AS ThisDec,
RomToDec.ThisDec AS LastDec,
substr(RomToDec.Remaining,1,LENGTH(RomToDec.Remaining)-1) AS Remaining,
LENGTH(RomToDec.Remaining)-1 AS Pos
FROM RomToDec                           
WHERE Pos >= 0
)
SELECT thisVal
FROM RomToDec 
WHERE pos IS NULL
;
THISVAL
1987

By the way, you can run these queries right in LiveSQL rather than starting up a test DB.

LiveSQL

Here’s a direct link to the final query in LiveSQL, ready to run!

Note that this SQL doesn’t validate the Roman numeral. For example, IXVIII is an invalid Roman numeral, but this SQL statement will return 17 instead of giving an error.

Today’s image is a photograph by Staci Bigelow

Categories: DBA Blogs

Search plugins: Search Oracle docs from your browser search bar

Mon, 2015-08-31 13:42

Tired of navigating to the SQL documentation every time you need to look up syntax? I created a search plugin so that you can search the SQL documentation directly from your browser’s search bar:

search

If you’re going to be doing a lot of looking-up, you can make this your default search engine. Click on “Change search settings” in the search bar dropdown, or go to Preferences > Search and select it:

change default search

I also created a search plugin for the 12c database documentation as a whole.

To install either or both plugins in Firefox, go to this page and click on the “Install Search Plugin” button.

Tested in Firefox. OpenSearch plugins reportedly work in IE too. Use IE? Try it and let me know if it works for you.

UPDATE: I added these plugins to mycroftproject.com as well. Thanks, Uwe, for pointing me to it! Also, check out Uwe’s OERR search plugin in the comments below.

Categories: DBA Blogs