DB_LINK w/o tnsnames.ora

A question popped up, which I thought was interesting. How can you create a DB_LINK in Oracle without the DBA changing the tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.

  1. Do the following with the tnsping utility:
    tnsping mohawk

    It should return this when the server’s hostname is mohawk and domain name is techtinker. (more...)

Spreadsheet-like Totals and Subtotals

We very often make spreadsheets with subtotals for each row and for each column. Someone on the OTN forum asked how to produce data in this format. I answered using the cool CUBE function.

Creating ggplot2 graphics using SQL

Did you read the title of this blog post! Read it again.

Yes, Yes, I know what you are saying, "SQL cannot produce graphics or charts and particularly not ggplot2 graphics".

You are correct to a certain extent. SQL is rubbish a creating graphics (and I'm being polite).

But with Oracle R Enterprise you can now produce graphics on your data using the embedded R execution feature of Oracle R Enterprise using SQL. In this (more...)

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, (more...)

Outer Join with OR and Lateral View Decorrelation

Use of ANSI SQL is a personal thing.

Historically I have not been a fan apart from where it makes things easier/possible.

This reticence was mainly due to optimizer bugs and limitations in the earlier days.

Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.

You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this (more...)

COALESCE vs NVL poll

Saddened but not surprised to see COALESCE lagging behind NVL.

Why? Because I think coalesce is a good idea and the modern equivalent of NVL.

I follow @SQLDaily for useful tips. Oracle SQL evangelist Chris Saxon runs the feed.

Believe it or Not: Converting an Inner Join to an Outer Join to improve performance

The cost-based optimizer tries to merge views whenever possible but sometimes we ma y want to override this behavior; that is, we may want the optimizer to push predicates into the view instead of merging the view into the main query. If the main query performs an inner join to the view, it becomes necessary to convert the inner join to an outer join if the database version is less than 12.1.0.2.(read more)

INSERT into a View with a GROUP BY Clause

When I wrote the previous post, about updatable views, I noticed an interesting issue.

The documentation says:

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

Let’s create a view with a GROUP BY clause and a group function:

ORA$BASE> create table t  (more...)

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

How do you spell “Brittany”? The picture above has four well-known women with four different spellings of the name. It turns out there are nearly 100 different ways that Americans have spelled it. The US Social Security Administration names data lets us tease out all the spellings and find out which ones are most popular – and when.

Here’s how Americans have spelled “Brittany” each year. This is a graph of SSA applications for each (more...)

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views – Part 2

In a previous post I wrote about one misconception about views that is revealed when I talk about Editioning Views in my EBR (Edition-Based Redefinition) presentations.

This post is about another misconception.

In the part of the presentation in which I “preach” to cover every table with an Editioning View and to replace every reference to tables in the code with reference to the corresponding Editioning Views, I usually get the following question from the (more...)

Kakuro Helper using SQL Query with the PowerMultiSet Function

When solving Kakuro it is essential to know for a given integer X and a given number of elements N all the combinations of N non-repeating digits [1-9] that their sum equals to X.

For example, there is only one combination for creating the number 7 from 3 elements:

1+2+4

And there are 6 combinations for creating the number 15 from 4 elements:

1+2+3+9
1+2+4+8
1+2+5+7
1+3+4+7
1+3+5+6
2+3+4+6

Let’s generate a list of all (more...)

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

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.

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views

Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.

It happens to me almost every time I talk about Edition-Based Redefinition. I present Editioning Views, and then I get questions that reveal misunderstandings about views in general.

One such misunderstanding is regarding what is kept in the view definition.

When we create a view as “select * from table”, the * is expanded to actual (more...)

Using dynamic tooltips in your Interactive Report


Inside an Interactive Report (IR) I had a comment column. The comments in this column could become really large and the users wanted the comments to be automatically trimmed if more then 60 characters were displayed. If the user moved the mouse above a trimmed comment then a tooltip should be display including all comment text.

My first idea was to check for existing plugins which could do this job for me. So I searched (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Accessing STATUS columns efficiently

A frequently reoccuring design problem with relational databases is the issue locating unprocessed rows in a large table, so we know which rows of data are still yet to be processed.

The problem with a STATUS column is that it generally has low cardinality; there are probably only a handful of distinct values [(C)omplete, (E)rror, (U)nprocessed or something like that]. Most records will be (C)omplete. This makes STATUS a poor candidate for standard B-Tree indexation. (more...)

Joining to a pipelined table function and “left correlation”

Oracle 11.2.0.4

A pipelined table function may be called from regular SQL using the TABLE collection expression, e.g.

SELECT *
FROM   TABLE(my_pipelined_function('ABC','DEF'));

where ‘ABC’ and ‘DEF’ are the inputs to the function.

What if you want to call the function repeatedly for several sets of inputs, e.g. testing the function for a variety of values? If those inputs are stored in a table somewhere, it ought to be as easy (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Subtleties – Part 2 (Nested Tables and Varrays)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer
/

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
 (more...)

Subtleties – Part 1 (SQL and PL/SQL)

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table  (more...)