Sometimes my students find new errors that I’ve never seen. One student did that this week by including an
ORDER BY clause in a subquery that feeds an
INSERT statement. It raises an
ORA-00907 exception, like:
ORA-00907: missing right parenthesis
You can’t include a subquery with an
ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick (more...)
SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.
Before I demonstrate that query - which is already found in many good libraries
- I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.
We can do this using date format masks
with placing as (select rownum rn from dual connect by level < (more...)
SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on
option in SQL*Plus.
I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.
|No sub-totals please|
I could use jQuery to hide the rows instead of modifying them
, but as (more...)
On the Toad World site, I’m publishing a whole series of short blog posts on the subject of EXPLAIN PLAN. I’m actually using EXPLAIN PLAN as a central motif to teach not just SQL tuning but relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]
who never needed to export data to excel from sqlplus ? everybody had one day needed to do that. Sqlplus support the HTML markup which provides an excellent result once opened in excel. With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table [...]
The post Export data to excel appeared first on Oracle DBA Scripts and Articles (Montreal).
Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table. Here is a diagram which illustrate the situation: In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that [...]
The post Unindexed foreign keys appeared first on Oracle DBA Scripts and Articles (Montreal).
I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,
I have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That (more...)
This script can be used to show the top 10 SQL activity for the last hour. It uses the v$active_session_history view to search top SQL by resource consumption. SQL Activity [crayon-5377b1876b186473467355/] Here is the result you can obtain: and the active sessions history graph for the same period:
The post SQL Activity for the last hour appeared first on Oracle DBA Scripts and Articles (Montreal).
There are already a lot of blogposts and presentations done about Hybrid Columnar Compression and i am adding one more blogpost to that list. Recently i was doing some small tests one HCC and noticed that that inserts on a HCC row didn’t got compressed and yes i was using direct path loads:
DBA@TEST1> create table hcc_me (text1 varchar2(4000)) compress for archive high;
KJJ@TEST1> insert /*+ append */ into hcc_me select dbms_random.string('x',100) (more...)
In my current project I need to query an MS SqlServer database.
Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.
To get the system date, you can do:
(SELECT dt=GETDATE()) a
It's maybe (more...)
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
The regular install script for Logger looks something like the following (parts removed and table names are changed):
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here
It describes how to create an xml document as an XMLType with an Oracle SQL Query.
The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data (more...)
Here is an example of surprising behaviour from a remote DB from an OTN forum thread
Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)
create table t1
create or replace view v1
select count(*) c1 from t1@l1;
Then alternate variations on this sequence of events:
1. On local DB execute SELECT:
SELECT * FROM v1;
2. On remote (more...)
For a project we are currently working on, we needed to generate, and send a Word 2010 document to the client. The document was generated by a great PL/SQL document generation tool called Doxxy, and was send to the client using the wpg_docload package. This is a standard Oracle pl/sql package that can be used to download files, BLOBs and BFILEs.
Before the download, we set the Content-type in the http header as follows :
firstname.lastname@example.org > select banner from v$version;
Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
CORE 18.104.22.168.0 Production
TNS for Linux: Version 22.214.171.124.0 - Production
NLSRTL Version 126.96.36.199.0 - Production
email@example.com > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
firstname.lastname@example.org > ! (more...)
Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.
This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing (more...)
There continues to be a disproportionate amount of hype around 'NoSQL' data stores. By disproportionate I mean 'completely and utterly out of scale with the actual problems of the vast majority of companies'. I wrote before about 'how NoSQL became more SQL'. The point I made there is now more apparent the more I work with companies on Big Data challenges.
There are three worlds of data
This year, from June 22 - June 26, the best convention in the world, ODTUG’s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world.
Some of the presentations I am looking forward to:
In the Developer’s Toolkit track:
So I wrote about why your Hadoop project will fail so I think its only right that I should follow up with some things that you can do to actually make the Big Data project you take on succeed. The first thing you need to do is stop trying to make 'Big Data' succeed and instead start focusing on how you educate the business on the value of information and then work out how to deliver new (more...)