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. » Continue reading “20 PL/SQL Coding Tips”
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 uses of views. » Continue reading “Updating Views”
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 thing all these questions have in common: the answer involves either ROWNUM or ROWID. » Continue reading “ROWNUM and ROWID”
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 examples: » Continue reading “Oracle Packages”
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 much more beneficial if I took from my experiences as a DBA / Developer those pieces of information I actually used and thought could help you spice up your studying for that next job interview. » Continue reading “Do You Know Your Database Objects for Your Next PLSQL Job Interview”
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) » Continue reading “DECODE/CASE vs. Mapping Tables”
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. » Continue reading “Changing User’s Default Schema”
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 can set it on a session-by-session basis (using alter session). You can also set it on a query-by-query basis by using hints (although such hints may be ignored in certain cases, like where all rows must be accessed, as in a GROUP BY). » Continue reading “FIRST_ROWS vs ALL_ROWS”
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. » Continue reading “Using Oracle SQL Developer with MS SQL”
