Compiling views: when the FORCE fails you

Darth-Vader-selfieThe order in which your deployment scripts create views is important. This is a fact that I was reminded of when I had to fix a minor issue in the deployment of version #2 of my application recently.

Normally, you can just generate a create or replace force view script for all your views and just run it in each environment, then recompile your schema after they’re finished – and everything’s fine. However, if views depend (more...)

OUG Ireland 2017 Presentation

Here are the slides from my presentation at OUG Ireland 2017. All about running R using SQL.

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

(more...)

12cR2 tightens up ORA-01841 for zero year ANSI dates, but not for Oracle SQL syntax

In moving some more code from an 11gR2 database to a 12cR2 database, I found another change where a piece of code that works in 11gR2 doesn’t compile in 12cR2.

In this instance a view was being created with a projected date column which used the ANSI DATE syntax. Here is a simplified test script:

CREATE OR REPLACE VIEW test1 AS
SELECT date '0000-01-01' date_col
FROM dual
/
DROP VIEW test
/

CREATE OR REPLACE  (more...)

Using VS Code for PL/SQL development

I've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to Visual Studio Code (VS Code).



Some good reasons to use VS Code:
  • Multi-platform (Windows, OS X, Linux)
  • Free, open source
  • Lightweight, fast
  • Large ecosystem of extensions
  • Built-in Git support
  • Can be adapted to PL/SQL coding via a plsql language extension (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation (more...)

ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:

ORA-54002: only pure functions can be specified in a virtual column expression

The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.

Here is a simplified (more...)

Presentations from OUGN17

Here are the presentations I gave at OUG Norway last week. These are also available on SlideShare

The Difference Between WHERE and HAVING Clause

What is the difference between WHERE and HAVING clause in SQL? Learn what they are and the differences in this article. Difference Between WHERE and HAVING Clause Both the WHERE and the HAVING clause are very similar and are used to restrict the rows that are returned in a SELECT query. The difference between WHERE […]

ORA-01031 insufficient privileges Solution

Are you getting the “ORA-01031 insufficient privileges” error? Learn what causes this error and how to resolve it in this article. ORA-01031 Cause The cause of the ORA-01031 error is that you’re trying to run a program or function and you don’t have the privileges to run it. This could happen in many situations, such […]

A nice Descending Index Range Scan

I’ve been aware of some of the ways that Oracle database optimises index accesses for queries, but I’m also aware that you have to test each critical query to ensure that the expected optimisations are taking effect.

I had this simple query, the requirement of which is to get the “previous status” for a record from a journal table. Since the journal table records all inserts, updates and deletes, and this query is called immediately (more...)

ORA-00937: not a single-group group function Solution

Have you received an ORA-00937: not a single-group group function error? Learn what it is and how to fix it in this article. ORA-00937 Cause The cause of the ORA-00937 error is that a SELECT statement is trying to be executed, and the SELECT statement has an aggregate function (e.g. COUNT, MIN, MAX, SUM, or […]

60 SQL Interview Questions and Answers

Are you going for a job where you need to know SQL, such as a Database Developer or Database Administrator? Brush up on your interview questions with this extensive list of SQL interview questions. This collection of interview questions on SQL has been collated from my experience with SQL and from various websites. It contains […]

Bad, Bad data




One should feel really sorry about anyone who will rely on filtering and making a decision based on bad, bad data. It is going to be a bad decision.


This is serious stuff. I read the other day a recent study by IBM which shows that "Bad Data" costs US $3.1 trillion per year!


OK, let's say you don't mind the money and have money to burn, how about the implications of using the (more...)

ORA-00907: missing right parenthesis Solution

Did you get an ORA-00907: missing right parenthesis error? Learn what caused it and how to resolve it in this article. ORA-00907 Cause When working with Oracle SQL, all left parenthesis (the “(” character) must be paired with a right parenthesis character (the “)” character). If there are more left parentheses than right parentheses, then […]

Reset Oracle Password

This blog entry shows you how to reset the system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the student user is the sudoer user.

After you sign on to the student user account, you open a Terminal session and you should see the following:

[student@localhost python]$ 

The oracle user account should be configured to prevent a login. So, you should use the su (more...)

web scale patterns in the bol.com back office – Mixed SQL – NoSQL

web scale patterns in the bol.com back office – Mixed SQL – NoSQL

In the previous weeks, we started a series of blog posts that show you how we use “web scale” patterns to achieve scalability and flexibility in our back office software. The previous patterns discussed were Event Sourcing and CQRS. This week we will dive into mixed SQL – NoSQL. Showing you how this doesn’t just solve a technical problem, they help (more...)

Truncating LISTAGG prior to 12.2

I've been following the LISTAGG problem for a while because I find the function useful.

The problem being this error:
ORA-01489: result of string concatenation is too long

It relates to when the result set is over 4000 characters, eg:
with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(str,', ') within group (order by null)
from data
Ten rows of 400 characters equals 4000, add the concatenation operator and you get (more...)

Formatting results from ORE script in a SELECT statement

This blog post looks at how to format the output or the returned returns from an Oracle R Enterprise (ORE), user defined R function, that is run using a SELECT statement in SQL.

Sometimes this can be a bit of a challenge to work out, but it can be relatively easy once you have figured out how to do it. The following examples works through some scenarios of different results sets from a user defined (more...)

Script: Inactive Parallel QC Holding Parallel Processes

Working on a data warehouse system can be quite challenging, as I mentioned in the post from yesterday. One of the things we need to take care of is the amount of parallel processes that are in used at all times. Yesterday I wrote about how to locate downgraded sessions. Today we will look at another aspect – who “steals” parallel processes and what can we do to solve it.

One of the biggest thieves (more...)

Script: Finding Session With Downgraded Parallel Execution

I was working with the data warehouse team at a customer site and at some point we realized that some parallel executions are not getting enough resources (downgraded).

Not getting enough parallel processes in such a complex environment is really bad. That means that since everybody is hogging the CPU, some sessions will not be able to complete inside the night ETL time frame. If that happens – some ETLs will go on into the (more...)