Oracle FROM_TZ Function with Examples

In this article, I’ll explain what the Oracle FROM_TZ function is and show you some examples. Purpose of the Oracle FROM_TZ Function The purpose of the FROM_TZ function is to convert a TIMESTAMP value and a specified TIME ZONE to a TIMESTAMP WITH TIME ZONE value. It seems like a simple data conversion function, but […]

Oracle TZ_OFFSET Function with Examples

The Oracle TZ_OFFSET function is a useful datetime function. Learn how to use this function and see some examples in this article. Purpose of the Oracle TZ_OFFSET Function The purpose of the TZ_OFFSET function is to display a number that indicates the number of hours from UTC that a specified time is. You can specify […]

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

How to Find the Row That Has the Maximum Value for a Column in Oracle

Do you need to find the data for a row which has the maximum value for a specific column in Oracle? Learn how to do it and see some alternative answers in this article. The Table Structure Let’s say you have a table called sales_volume that looks like this: CITY START_DATE SALES El Paso 27/May/15 […]

Oracle INITCAP Function with Examples

The Oracle INITCAP function is a handy string manipulation function. Learn what it does and see some examples in this article. Purpose of the Oracle INITCAP Function The Oracle INITCAP function translates a specified string into another string which has the first letter of each word capitalised, and all other letters in lower case. The […]

Oracle LOWER Function with Examples

In this article, I’ll take a look at the Oracle LOWER function, explain how it works, and see some examples. Purpose of the Oracle LOWER Function The LOWER function converts all letters within the specified string to lower case.   Syntax The syntax for the Oracle LOWER function is: LOWER ( input_string )   Parameters […]

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

The Problem with autotrace

There are few ways to see the execution plan of a SQL statement. One of these ways is the autotrace option in sqlplus. It is a very easy-to-use feature and people use it quite often. But there is a risk here. The autotrace option doesn’t always show you the correct execution plan. Lately I prepared […]

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)

Updates to Big Data Lite 4.5

There are some updates to Big Data Lite 4.5 that you will want to take advantage of: Updated Big Data SQL configuration enables predicate pushdown to engage - which can have pretty dramatic performance benefits Update to the Big Data Spatial framework (fixes a configuration issue) New... [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...)

New, Improved IN Lists!

In SQL, the "varying IN list" problem comes up constantly and there are many published solutions. My new favorite solution is inspired from the video Synthesizing rows inside Oracle by Connor McDonald (start at the 6 minute mark).

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

Use the database to do the heavy lifting

Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these (more...)

Assertions in a future Oracle release

I just found this link on OTN to vote for including assertions in a future release of the Oracle database.

A great idea – please vote for it.

One of the most important votes this month…well, I do live in England! Smile

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