## SQLDeveloper and Userdefined datatypes in tables

B2B works with advanced queueing with the queue-table ip_qtab based on the IP_MESSAGE_TYPE Oracle Type wich is defined like:

create or replace type IP_MESSAGE_TYPE as OBJECT (In the queuetable you (more...)

MSG_ID VARCHAR2(128),

INREPLYTO_MSG_ID VARCHAR2(128),

FROM_PARTY VARCHAR2(512),

TO_PARTY VARCHAR2(512),

ACTION_NAME VARCHAR2(512),

DOCTYPE_NAME VARCHAR2(512),

DOCTYPE_REVISION VARCHAR2(512),

MSG_TYPE INT,

PAYLOAD CLOB,

ATTACHMENT BLOB

);

## The Fundamental theorem of arithmetic – SQL version

Every positive integer (except the number 1) can be represented in exactly one way apart from rearrangement as a product of one or more primes, see for example Wolfram MathWorld or Wikipedia.

Here is the SQL-Version, we compute this for all integers up to 100

with bound as ( select 100 as bound from dual ), n_until_bound as ( select level+1 n from dual connect by level <= (select bound.bound from bound) ), primes_under_bound (more...)

## A Greedy Algorithm using Recursive subquery factoring

Today is friday and I like the twitter-hashtag #FibonacciFriday,

so I tweeted

```
```Zeckendorfs theorem every positive integer is uniquely the sum of distinct nonconsecutive Fibonaccis http://en.wikipedia.org/wiki/Zeckendorf%27s_theorem#FibonacciFriday

— Matthias Rogel (@MatthiasRogel) 22. Mai 2015

Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a (more...)

## Fourth International NoCOUG SQL Challenge

The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave.

Entries should be attached as a comment to this announcement. (more...)

## LoggerUtil: Create a Custom Template

Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.

If you haven't read that blog about my pet project, here's the synopsis:

I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)

## Strategies for Minimising SQL Execution Plan Instability

## Execution Plan Instability – What is the problem?

The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.

### RBO

In the beginning, the Optimizer was rule-based.

The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.

This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.

This means that (more...)

## Viewing Models Details for Decision Trees using SQL

Developing your Decision Tree models using the ODMr allows you to explore the decision tree produced, to drill in on each of the nodes of the tree and to see all the statistics etc that relate to each node and branch of the (more...)

## Little quiz: Ordering/Grouping – Quess the output

How many times have you guessed the right answer?

select * from dual order by -1; select * from dual order by 0;

select * from dual order by -(0.1+0/1) desc; select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by -(0.1+0/1) desc;

select 1 n,0 n,2 n,0 n,1 n from dual group by grouping sets(1,2,3,2,1,0) order by 0; select 1 n,0 n,2 (more...)

## The Hitchhiker’s Guide to the EXPLAIN PLAN Part 37: POISED: A problem-solving method

## Overview of all time changes this year via SQL

inspired by Laurent Schneider’s remark concerning special time changes in Lord Howe Island

with dates as ( select trunc(sysdate, 'year') + level - 1 as day from dual connect by extract(year from trunc(sysdate, 'year') + level - 1) = extract(year from trunc(sysdate, 'year')) ), timezones as ( select vtn.TZNAME, listagg(vtn.TZABBREV, ', ') within group(order by vtn.tzabbrev) tzabbrevs from v$timezone_names vtn group by vtn.TZNAME ), offsets as ( select v.*, d. (more...)

## What’s this ‘WHERE 1=1′?

Since some time I have been adding `WHERE 1=1`

to all my queries.

I get queries like this:

SELECT * FROM emp e WHERE 1=1 AND e.ename LIKE 'A%' AND e.deptno = 20

Lots of people ask me what’s the use of this `WHERE 1=1`

.

You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)

## Join Tables on Date Ranges

## Visualizing Statspack Performance Data in SQL Developer

## Oracle APEX_WEB_SERVICE REST API call

*' and hasn't been extensively tested.*

**Hackday**My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.

The idea was to build an (more...)

## The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns

## Comparing NULLable Values

## Real time sql monitoring – bulk bound statement

Moving through some observations of an ongoing investigation tackling the easiest ones first.

Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.

Statement was sql id 9v9n97qj8z1dg:

delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum);

From the real time sql (more...)

## COUNT STOPKEY – NVL

Yesterday I mentioned issues with a recursive delete operation on statistics history.

This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.

COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.

For example:

create table t1 as select * from dba_objects; alter session set statistics_level = all; (more...)