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 three things do you think every developer must know about #SQL ?— Chris Saxon (@chrisrsaxon) July 5, 2016
Reply and share yours!
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...)
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...)
Weekend #SQL poll: when mapping binds/columns that could contain nulls to non-nulls do you use:— SQL Daily (@sqldaily) July 1, 2016
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.
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...)
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...)
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...)
This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:
with t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d) ,x(l,s,n) as ( select 1 l, c s, chr(97)||c||' ' from t union all select l+1, x.s||t.c, n||chr(98+l)||i||' ' from x join t on instr(s,c)=0 and not exists(select 0 from dual where L+1 - t.i = level (more...)
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!
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:
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...)
Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.
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...)
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...)
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...)
When building predictive models the data scientist can spend a large amount of time examining the models produced and how they work and perform on their hold out sample data sets. They do this to understand is the model gives a good general representation of the data and can identify/predict many different scenarios. When the "best" model has been selected then this is typically deployed is some sort of reporting environment, where a list is (more...)
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...)
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...)