During the last couple of days I have been playing around with the new, hosted version of APEX. I know I haven’t been using all the features available in APEX and I have mostly been using my mod_plsql skills, because I don’t know how to do certain things in APEX (yet).
Read moreCategory Archives: SQL
Useless joins…
Sometimes when you’re building queries you end up joining tables which you don’t really need (anymore). They are just there to join some other table from which you do need some data. The join is just there to satisfy some foreign key references. It can however greatly impact the speed of your query. If the […]
Read moreBulk Operations in PL/SQL
When I was in Chicago for the OPP2008 and APEXposed event I talked to someone who seems to have trouble understanding bulk operations in PL/SQL. I helped him out by providing a test/demo script, that shows how it could be done. First of all, one of the most important rules of thumb that I got […]
Read more(Dutch) Woensdag 8 oktober: KC PL/SQL Developer onder de loep (met spreker Theo Tol)
In het midden van de 90-er jaren waren er heel weinig en vrij beperkte ontwikkeltools beschikbaar voor het ontwikkelen van PL/SQL. Tegenwoordig is dat heel anders en zijn er verschillende leveranciers van IDE’s voor het ontwikkelen van en beheren van databaseobjecten en databasecode. Toad for Oracle van Quest en SQL Developer van Oracle zelf zijn […]
Read moreUnion vs Union All
Do you know the difference between the UNION and the UNION ALL part of a statement? In short, UNION does a sort-unique on both collections. UNION ALL just retrieves all rows, regardless of any double values.
Read moreTriggers…
I ran into an issue at a customer site where certain triggers were disabled in the database where they should be enabled. It appeared that an update script, that is run every night, first disables all triggers on a couple of tables. Then does what it needs to do, without the overhead of the trigger […]
Read moreBlogging block…
I seem to be suffering from a blogging block (like a writers block, but for a blogger). Maybe it’s my new job at AMIS or the new application at the KPN I am working on. It’s a lot of figuring out what is going on. Reading through other peoples code and try to understand their […]
Read moreWITH Clause
During my work for AMIS I came across a (to me at least) new feature of the SQL engine. Instead of building an inline view in the from clause of a statement you can build this ‘inline view’ before the SQL statement and use the results in the from clause. The syntax is pretty straightforward: […]
Read moreOracle 11G – Compound Trigger
In Oracle 11G it is now possible to create a compound trigger. One piece of trigger code that holds all the code for the different triggers you would want. Why do we want to have this? Consider a solution for a mutating table problem. Normally you would use about at least three different triggers and […]
Read moreOracle 11G – Virtual Columns (Dates)
A couple of days ago there was a question (in Dutch) on the utPLSQL forum about filling up a table with a lot of dates and some info about those dates, like the weeknumber, the dayname etc. I created code using persistent columns at first, so the code would run on a pre-11G installation, but I was […]
Read more