SQL and PL/SQL Articles

Kevin Meade's picture

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document. So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT.

Kevin Meade's picture

A Simple Example of Oracle Analytics: Running Totals

Analytics are magic. But as with most software products; I seem to use only one percent of the features, ninety nine percent of the time. Indeed, having built warehouses and reporting systems for the last eighteen months, I look back and see that I got a lot done with only three Analytics, SUM, LAG, and LEAD. Knowing how intimidating Analytics can look to those who have not used them, I figured I’d show the uninitiated, how to get in through the back door, with a reduced look at the capability. You can do a lot with very little. We are going to discuss just one Analytic, SUM used to create running totals.

Kevin Meade's picture

Example of Data Pivots in SQL (rows to columns and columns to rows)

My current client has the crew here doing a good deal of data pivoting in migrating data between several Oracle Systems. I figured to turn some heads in a code review by providing a solution for data pivots which is not based on "locally favored" traditional 3GL coding practices but instead employs what is to me the more natural way of doing it for an Oracle database. I was right, heads did turn. Since I now have to document it, I figured why not post it on OraFAQ once and then tell them its online, come here and read it. So this is an example of pivoting in SQL.

Kevin Meade's picture

An Introduction to ANYDATA

My newest project needed to create a record keeping component that would keep track of balancing results over time. Sounded good, meant my customers could look back over time to see how things went, and it provided persistent evidence of good results. I figured on adding two routines (save/get) to a logging package already in existence to save balancing data via an autonomous transaction and retrieve it when needed. But in writing these routines it dawned on me that they would destroy the reusable nature of the logging package. Finally, a real life use for ANYDATA.


SQL Tuning made easy in Oracle 10g

“Find out how to use DBMS_SQLTUNE package to tune SQL Statements in Oracle 10g”

Have you ever been frustrated with SQL Tuning?
Did you get lost in the maze of /+ HINTS +/ and analysis paralysis?
Pre-10g you have to be either a SQL tuning expert or should have a license to expensive tuning tools available in the market to effectively tune SQL Statements. Not the case anymore.

In 10g you can use DBMS_SQLTUNE package to get tuning recommendations for SQL Statements.


Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:


Dynamic creation of procedures in PL/SQL

Every PL/SQl devloper must have tried their hands with dynamic SQl. Its an indelible part of oracle.But its generally used when we dont know at compile time about the object on which we are to perform the task. Suppose we want to insert data into a table but we get the name of the table during the execution of the procedure, in those cases we would use dynamic SQl with the table name passed as bind variable. Dynamic Sql is even used to execute DDl commands inside a PL/SQl block.

Kevin Meade's picture

Point in Time (Nearest Neighbor Algorithms for SQL)

Been doing a lot with Historical Perspective lately. This has caused me to think a bit about the different variations of lookup that can be called a Point In Time query. My customers have found this discussion useful in understanding the details of how their report programs find specific rows in time. It is helpful to them because it provides an understanding as to why a specific row shows up on a report, and thus allows them to create more exacting definitions of what they want. Plus I find it is a good primer for newbies on staff to read so they don't make the same mistakes we made when we first started doing PIT queries. Maybe you can use it too.

Kevin Meade's picture

Difference between two rows

One of my recent clients had a real data twisting process to validate. It didn't help much that their source had been corrected by many "ONE-SHOTS" over the years to deal with bugs, and accounting methods mandated by Act of Congress. What we needed was a way to see changes in a stream of related rows. In the end I created for them, a solution that allowed developers to pick two rows from a table at random and compare them in SQL with a result set returned showing only differences. Its a simple thing, but kind of neat, and very useful. So I'd like to share it with you.


Tuning "BETWEEN" Queries

A common sight in databases is a table that contains the start and end values of a range - usually dates. One or both of the dates are typically part of the primary key; sometimes they are the entire key. Some examples:

  • History tables History tables record the changes made to a row over time. Instead of updating a row and losing the prior values of each non-key attribute, a new row in inserted. Each row is differentiated by two columns that record the date the row was created (START_DATE) and the date it was superceded (END_DATE).

bhupinderbs's picture

A PL/SQL program to convert numbers into words

Here is a Function that convert NUMBERS into WORDS:

[code]CREATE OR REPLACE FUNCTION f_words (p_amount IN Number) RETURN Varchar2 IS
--Author : Bhupinder Singh
--Creation Date : 05/03/2007
--Purpose : This Function returns amount in words.
--Parameters :
--1) p_amount : Only positive and negative values are allowed.
Precision can be entered upto 10 digits and only 2 scales
are allowed e.g 9999999999.99



Subscribe to RSS - SQL & PL/SQL