During my work lately I found out some things about SQL (or is it Oracle SQL) that I didn’t know about and have really made my life easier. Well, maybe not easier, but at least it enriched my knowledge 😉
Read moreCategory Archives: SQL
Using Table Functions
Overview of table functions Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The difference with a physical database […]
Read moreODTUG Kscope 2011
Last week I attended my very first ODTUG KScope conference in Long Beach, California. I have attended conferences by ODTUG before, but they were never this broad. Sunday On Sunday I attended the APEX symposium. It was nice to see that APEX is being developed by a rather small, but geographically dispersed, group of people. […]
Read moreKScope 11
About 4 weeks from now KScope11 will be well underway. I am really looking forward to attending this conference. I think it is more ‘developer-centric’ than for instance Oracle Open World is. I still have to create my schedule for the days, but I know of at least one session where I will be present.
Read moreAnalytic vs Hierarchical
While playing around with Analytic functions with a Hierarchical function in my query I stubled upon some strange behaviour I cannot explain (yet). The documentation provided by Oracle states the following: Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. Right, that would mean I […]
Read moreXOR in SQL
If you have ever tried to implement a requirement that implies that either one field is filled, or the other one but not both and not both not then you might have thought about using an XOR function. Unfortunately this is not implemented in Oracle SQL.
Read more[Book Review] SQL Pocket Guide By Jonathan Gennick
I started reading this book by Jonathan Gennick about a week ago. After a couple of chapters I realized this book is more a reference than a book you should read from A through Z. I am really an Oracle guy. I think I know a lot about the PL/SQL and I think I know […]
Read morePipelined Table Functions
I have had trouble with a certain view I have to create at the customer site I am currently working at. The view involves 3 SQL queries combined by using a UNION ALL, since the separate queries are mutually exclusive. Using the UNION ALL makes it a bit faster since Oracle doesn’t have to do […]
Read moreHashing rows…
During my work at the current customer site I was asked to create files with the changes to the data in the database. Normally you would use triggers on the tables to create logs of the records changed. Or use a Flashback Data Archive, which is only available starting Oracle 11G and I am working […]
Read moreReport in APEX
The other day someone at a customer had a question about displaying a report. The different options are in a table so he could easily use a report to display the options. But he wanted to display three columns of options. That is, if there are 10 options, he would want to display 3 rows […]
Read more