Gary pointed me to a recent AskTom thread that contained a list of PL/SQL Coding Practises. The ensuing discussion added a few more, and I’d like to throw in a few of my own as well. I also think most of these tips are useful for other languages, but this is first and foremost an Oracle developer’s blog. Here they are, with the order and wording slightly revised in some cases (removed the biblical undertones [ Read More ]
I was recently asked by a former colleague “Can you insert data in an Oracle table through a View?” In some cases, yes you can, and it actually serves as a handy technique to manage security on your data. Here’s what I mean: you can restrict access to a base table, and then create a view containing only rows and columns that you wish to be accessible. That is just one of the many handy [ Read More ]
Questions: How do I limit the number of rows returned by a query? How do I write a query to get the Top-N salaries from the employee table? How can I add unique, sequential numbers to an existing table? How can I differentiate between two completely identical rows? How can I find a faster way to retrieve a queried row? How can I find the last row processed in a big batch? There is one [ Read More ]
What is a “package”? According to the PL/SQL User’s Guide and Reference, “A package is a schema object that groups logically related PL/SQL types, items and subprograms.” But I believe a package is far more than just a way of logically grouping objects together. Before I digress, let’s very briefly understand what a package is. It’s probably easiest if you take a look at Oracle’s documentation, which has a good description of packages and some [ Read More ]
Access paths are the ways in which data is retrieved from the database, and there are a number of different access paths which can be used for tables and indexes. This course explores the access paths used for tables, such as full table, rowid, and sample table scan. It also examines the access paths used for indexes, such as unique, range, full, fast full, skip, and index join index scans, as well as bitmap indexes [ Read More ]
Interviewing for a PL/SQL developer position ultimately will turn towards talking about database structures and objects. But most of us will begin to fall asleep if we had to answer some of the more basic questions about what a table is, what a column is, what an index is, etc. And I didn’t want to bore you as well having to read through that more basic questions and answers. Instead I thought it would be [ Read More ]
I was helping a colleague recently with some SQL. He had about a dozen SQL queries that all looked something like this: SELECT (some stuff), DECODE (status, ‘A’, 1, ‘I’, 0), (more stuff) FROM (wherever) I made a few observations about his DECODE statement: 1. Sometimes he used DECODE and sometimes he used CASE 2. Sometimes he would map ‘ ‘ to 0, and sometimes it would go to the default value of NULL, like [ Read More ]
Last week I got a question about changing a user’s default schema. My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead – either because he doesn’t want to log in as schema owner, or can’t. To make [ Read More ]
A colleague asked me some questions about FIRST_ROWS and ALL_ROWS, but I’m hesitant to blog about it because it’s already been done so well by others — the best example would probably be Sachin Arora. Nevertheless, it never hurts to lend another voice to the Oracle choir, so here’s everything I know on the topic. FIRST_ROWS and ALL_ROWS are values for the optimizer setting OPTIMIZER_MODE. You can set it in your init.ora file, or you [ Read More ]
Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It’s easy to connect SQL Developer to MS SQL databases, and I’ll show you how. Background For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases [ Read More ]
