Oracle Single Row Numeric Functions

Exam Topic: Using Single-Row Functions to Customize Output - Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has … Continue reading Oracle Single Row Numeric Functions

Change Oracle’s SQLPLUS EDitor

Not everyone wants to use sqlplus' built in editor. It is a bit of a pain and not at all WYSIWYG. Thankfully, changing sqlplus to use another editor is an easy process where you change the _editor session variable to another value. For a more permanent change, you can alternatively change the setting in the … Continue reading Change Oracle’s SQLPLUS EDitor

Ampersand Substitution and Session Variables

Exam Topic: Restricting and Sorting Data - Use ampersand substitution to restrict and sort output at runtime The Ampersand (&) and Double-Ampersand (&&) are used in oracle as prompts for input. They are called "Substitution Variables." They can be used to input a value or dynamically build or modify a statement at runtime. This allows you … Continue reading Ampersand Substitution and Session Variables

Sorting Data – the ORDER BY Clause

Exam Topic: Restricting and Sorting Data - Sort the rows that are retrieved by a query As data is inserted and updated and indexes keep track of rows, the order in which the rows are stored can change. There is no guarantee that the way Oracle stores the data in your system is in any kind of … Continue reading Sorting Data – the ORDER BY Clause

Handling Nulls

Null is an unknown value. Null is Schrodinger's Cat's state before opening the box. There are ways to deal with null that are unlike anything else in the database, both in concept and in function. Equalities/Inequalities Nothing can equal or not equal Null. Not even another Null. It is an unknown value. Only something that is … Continue reading Handling Nulls

Using Expressions in a SELECT FROM DUAL

Exam Topic: Retrieving Data Using the SQL SELECT Statement - Execute a basic SELECT statement In The Capabilities of a SELECT Statement, we talked about returning columns from a table using the SELECT list and a few of the alternatives. In fact, any expression you can think of can be in the SELECT list. An expression is "a combination of one … Continue reading Using Expressions in a SELECT FROM DUAL

Restricting Data – Selection in the WHERE Clause

Exam Topic: Restricting and Sorting Data - Limit the rows that are retrieved by a query Returning the right data by restricting rows to answer questions is at the heart of SQL Development. As shown in The Capabilities of a SQL SELECT Statement, the WHERE clause is used to specify conditions for which rows should be returned. If the … Continue reading Restricting Data – Selection in the WHERE Clause

Gotchas with the Oracle DATE Datatype

DATE is a common datatype. Most transactions in a database have a date associated with them for tracking purposes. When was the order made? When is the order expected to be delivered? When was it actually delivered? And so on. Querying dates, however, takes some attention to detail as there are many details to consider … Continue reading Gotchas with the Oracle DATE Datatype

Null Parameters in WHERE Conditions

When building a procedure, I want to be able to pass a parameter or pass a NULL if I don't want to search by a parameter. Some people will tell you to accomplish this that you should write a dynamic SQL statement so you can add or remove parameters as needed. I avoid Dynamic SQL … Continue reading Null Parameters in WHERE Conditions