Running multiple SQL Server and Oracle database platforms to support different applications is the norm for many IT organizations today. While in many cases the two different database platforms often operate as independent islands there are also times you might need to integrate the two database platforms to perform lookups, queries and other operations that […]
Read More
The ORDER BY clause is executed after the WHERE, GROUP BY, and HAVING clauses. It can employ group functions, or columns from the GROUP BY, or a combination. If it uses a group function, that function operates on the groups and then the ORDER BY sorts the results of the function in order. If the […]
Read More
Let’s take a moment to discuss the real power of a relational database. Once you’ve created a view with the count by category, and a second view displaying the count for the entire table, you can join them together to reveal information never before apparent. For instance, what percentage of the books are in each category? […]
Read More
You can create a table that exists solely for your session or whose data persists for the duration of your transaction. You can use temporary tables to support specialized rollups or specific application-processing requirements whose results will not persist beyond the session or even past a COMMIT statement. To create a temporary table, use the […]
Read More
One of Oracle’s more interesting but little used or understood facilities is its CONNECT BY clause. Put simply, this method is used to report, in order, the branches of a family tree. Such trees are encountered often—the genealogy of human families, livestock, horses; corporate management, company divisions, manufacturing; literature, ideas, evolution, scientific research, theory; and […]
Read More
Most single-value functions in Oracle are pretty straightforward. This article gives short examples of the major functions, and it shows both the results of the functions and how they correspond to columns, rows, and lists. After the examples, you’ll learn how to combine these functions. A table named MATH was created to show the calculation effects […]
Read More
One of the more powerful data analysis features introduced in Oracle Database 12c is SQL models. SQL models allow a user to create multidimensional arrays from query results. Formulas, both simple and complex, can then be applied to the arrays to generate results in which the user is interested. SQL models allow inter-row calculations to […]
Read More
(Read Part 1 of this article here.) Not everything stored in a database and schema is data. Oracle also manages source modules and supporting structures (such as sequences) that are used to populate new unique and primary key values when inserting data into the database. These objects belong to a schema and are stored in […]
Read More
In this two-part article, you take a look at the Oracle schema and storage infrastructure because these are a large part of what you, as an Oracle DBA, will be required to manage. Schemas An Oracle database can have many schemas contained in it. The schema is a logical structure that contains objects such as […]
Read More
You get a better sense of the power of the cursor in Oracle by combining it with a loop. The cursor FOR loop is the result of combining the select cursor with a FOR loop (we go into additional detail about this loop in the next section). This allows you to retrieve multiple rows from […]
Read More
How do you get data into your programs? Simple—select it from the database. This is the easiest way to use SQL in your program. Thus, inserting a line like the following will provide you with the capability to access data in the database: Let’s break down the statement and look at what it means to […]
Read More
Oracle provides us with many functions that allow us to analyze and aggregate the data, returning results that differ greatly from the result sets you’ve seen so far. A function manipulates the contents of a column in a SQL statement. You can find what the largest credit limit is in the CUSTOMERS table, and you […]
Read More