Skip navigation.

SQL & PL/SQL

SQL and PL/SQL Articles

Info on PIVOT

Database tables are structured in columns and rows. However, some data lends itself to switching row data as column data for expository purposes. The pivot operation in SQL allows the developer to arrange row data as column fields. For example, if there are two customers who have both visited a store exactly four times, and you want to compare the amount of money spent by each customer on each visit, you can implement the pivot operation.

Oracle SQL Model - An Alternative to UNION for Creating A list of Static Values

Here’s an alternative to the union statement for creating a collection of values using the Oracle SQL Model construct in 10g.

[code]--Using SQL Model clause to return a list of items.

SELECT col_1 Product, col_2 Price, col_3 Description
FROM (SELECT -1 col_id,
RPAD ('X', 30) col_1,
TO_NUMBER(RPAD ('1', 3)) col_2,
RPAD ('X', 40) col_3
--Change RPAD number to reflect length of column value.
FROM DUAL)
WHERE col_id <> -1
MODEL
DI

Need of Framework in PL/SQL Coding

Over a period of time, I have seen many database (backend) centric projects using huge amount of processing, I found most of them lacking Development Framework, To make it more clear I would say that Framework is not only limited to GUI like JAVA DOT.NET. It is certain sets of standards used before starting development projects, This presentation will support approach towards achieving this objective.

Undocumented OVERLAPS Function, Don't Use it Yet

Kevin Meade's picture

Recently I have noticed there has been considerable talk on the WEB about date logic; in particular talk about overlapping date ranges and how to detect them. I can't say why there is such an interest, but some people have turned to an undocumented feature (a function called OVERLAPS) to solve their problems. This article will describe date overlap, and show equivelant SQL that will allow you to avoid using this undocumented feature which as always is a good idea, because undocumented features in Oracle have propensity to change and/or disappear unexpectedly leaving those who foolishly realied upon them in unanticipated trouble after an upgrade.

ORACLE OOP SQL APPROACH TO COMPLEX DATA TRANSFORMATION: OVERCOME PL/SQL STRIING FUNCTION INEFFICIENCIES

This Blog is to be serve as a compilation of my research in preparation for a paper on
The extreme performance issues with PL/SQL String Functions and how to circumvent their usage using Straight SQL. The Object Oriented approach allows SQL to be virtually executed against "Anything" not just tables - data fields, variables, even a single character!

Hierarchical queries

dwarak.k's picture

A relational database does not store data in a hierarchical way. Then how do I get the data in a hierarchical manner? Here we get to know about how to use the hierarchical querying feature which Oracle has given. This article talks about how you can interpret the hierarchical query conceptually and build hierarchical queries catering your needs.

Using hierarchical queries, you can retrieve records from a table by their natural relationship.

Creating a Calendar in a single SQL statement

Ahysanali M. Kadiwala's picture

A few days ago, somebody asked if it is possible to write a Calendar in a single SQL statement.

Here you go...

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",

Disabling constraints before the import operation

Some times you need to disable user constraints before doing the import for some user,
for this reason i created a procedure that can be used to disable the constraints before the import,and than use it again to enable the constraints after doing the import,and also this procedure can be used to drop user constraints when needed.
I named it MANAGE_USER_FK_PK_UK,note the order in the name FK_PK_UK,because when you want to disable constraints you should disable the foreign keys after doing that for the primary keys,for the unique keys it can be done at first or not,i do it at last.
The proced

Easy XML - a Programming Oriented Approach

Kevin Meade's picture

In a previous article I talked about using Natural Oracle Features to get XML data out of an Oracle database, and put data into an Oracle database, without actually having to learn a lot about XML related technologies. Still, there are many who continue to resist using OBJECTS, COLLECTIONS, and INSTEAD-OF-TRIGGERS in their daily work with the Oracle RDBMS. Since the biggest hurdle with XML seems to be parsing it into its component data elements, here is a more programming oriented approach to parsing XML data inside Oracle that should make this crowd happy.

Tuning High-Volume SQL

Mr and Mrs Oracle at the Supermarket

I was at the supermarket the other day waiting my turn at the checkout behind another guy. The checkout-chick (I'm sure there is a more PC term, I just don't know what it is) just finished scanning his groceries and he asked her to wait until his wife returned with a few last-minute items. I've done it before, so steam didn't start coming out of my ears - yet. Fifteen seconds later she ran up - sorry, sorry - and checked out the last few items, so I wasn't really inconvenienced.