PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.
Unfortunately do to a timing issue, (more...)
I thought I'd chime in after some recent experiences were helped along by the use of Twitter, a tool it seems many are underutilising.
Exhibit AI had an afternoon of generating JSON with SQL using LISTAGG and I kept facing this issue of being limited by 4000 characters. Knowing I occasionally (more...)
Until we get our shiny new 12c database running on its shiny new box (and all the data shifted to it), we are living with a mix of databases. To begin with, the data we managed was mostly AU/NZ and Europeans stuff, and the character set (more...)
I had a registration form in Apex which asks the applicant to enter their Date of Birth in a date item; I then needed to calculate how old they would be at the start of the event, which determines a number of rules, such as whether we need to obtain their parent’s permission.
In my first release I implemented this with a Dynamic Action which ran SQL something like this:
select round(months_between(start_date ,to_date(:P1_DATE_OF_BIRTH,'DD-MON-YYYY')) / 12,1) from (more...)
- in 10g führt die Statistikerfassung bereits bei weniger als 255 Spalten - nämlich bei etwa 165 Spalten - dazu, dass mehrere FTS erforderlich werden, um die Daten einzulesen.
- das (more...)
I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a
marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.
You can query a sequence with xquery as follows:
DECLARE @x xml; SET @x = N''; SELECT @x.query( (more...)
Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:
- An XML Schema Collection type.
- A table that uses an XML Schema Collection as a column’s data type.
- An example on how you can transfer the contents of a table into the XML Schema Collection.
This post assumes you (more...)
I have to kick off this post with a huge apology to my dedicated readers for not posting recently. As I am sure you are all aware, the Northeast has been experiencing some crazy snow storms, and Boston has turned completely white. The almost constant snowfall has made my life incredibly hectic because school keeps getting canceled and chasing my 3 little ones (under the age of 6) it makes it hard to stay on top (more...)
each tables covers 24m rows.
Same structure ...
Prior to 22.214.171.124, DBMS_STATS.PURGE_STATS does a slow delete of stats before the parameterised input timestamp.
Why might you be purging? Here’s one such illustration:
This delete can be slow if these tables are large and there are a number of reasons why they might be so, notably if MMON cannot complete the purge within its permitted timeframe.
But note that if you’re happy to purge all history, there (more...)
I was inspired by Peter Raganitsch to use the Survey Builder packaged application to create a survey that will help with my "Evidence Based APEX" presentation.
I would appreciate 5-10 minutes of your time if you could fill out my survey:
You'll find a small set of questions over five major topics.
- Instrumentation (debugging)
Here’s another six SQL brainteasers. This time the SQL is a clue to the power(s) of a superhero. Who is each superhero?
insert into body (skeleton) values ('adamantium'); update person set strength = 'superhuman' where emotion = 'anger'; delete from powers where location = 'near Kryptonite'; update body set exoskeleton = 'ferrous material'; select * from women where equipment = 'noosed rope of veracity'; begin insert into boy (more...)
My students asked if you could embed an
OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in (more...)
There’s a problem most people who’ve had to do production datafixes have encountered at some point: you need to update a dataset. Unfortunately there’s a trigger on the target table. The trigger either prevents the update outright or just has some unwanted side effects (firing off a business process, etc.). The trigger is necessary for the application to function correctly, so you can’t just drop it.
How do you apply the changes?
You can (more...)
- New Version Of XPLAN_ASH Utility - In-Memory Support: Informationen zur neuen Version, die insbesondere die CPU-Nutzung von In-Memory-Operationen ausweist.
- Video Tutorial: XPLAN_ASH Active Session History - Introduction: das Tutorial (18 min) stellt die zentralen Funktionen des Tools vor.
- Video Tutorial: XPLAN_ASH Active Session History - Part 2: (more...)