Randy Sims | SQL Simantics

Generate Random Values

Randy Sims Oracle

One of the best builtin packages in Oracle for anyone who needs to create random data is DBMS_RANDOM. Not only can you use it to create random values, but you can also use it to make random selections. For instance, if you have a list of values that you want to assign randomly, you can weight […]

Nested Tables as Collection Data Type and a Table Column

Randy Sims Oracle

Sometimes, a table is designed with one column that can hold an array. An array in database design is a table. So, in Oracle, you can create a type as a table and then use this type as a column in another table. What we end up with is a nested table as a table column […]

Oracle Recycle Bin: BIN$ and RB$$ files

Randy Sims Oracle

Ever query all_source and come up with a strange object name that starts with BIN$ or RB$$? That is the recyclebin, holding on to tables and indexes you have deleted in the past until they are either released or aged out. If you are trying to make space by dropping objects, be sure to purge […]

Oracle Single Row Numeric Functions

Randy Sims SQL Fundamentals I

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 […]

Change Oracle’s SQLPLUS EDitor

Randy Sims Oracle

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 […]

Ampersand Substitution and Session Variables

Randy Sims SQL Fundamentals I

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 […]

Sorting Data – the ORDER BY Clause

Randy Sims SQL Fundamentals I

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 […]

Handling Nulls

Randy Sims Oracle

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.

Using Expressions in a SELECT FROM DUAL

Randy Sims SQL Fundamentals I

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 […]

Restricting Data – Selection in the WHERE Clause

Randy Sims SQL Fundamentals I

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 […]