SQL & PL/SQL

SQL and PL/SQL Articles

Oracle10g Regular Expression

articles: 
Kevin Meade's picture

Foreordain my Transaction to Fail

I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.

articles: 
Kevin Meade's picture

Creating and Unpacking Delimited Strings in SQL

Recently a friend asked me for this. I see it a lot on OraFaq as a question in the forums so here are the basics of working with delimited strings. I will show the various common methods for creating them and for unpacking them. Its not like I invented this stuff so I will also post some links for additional reading.

articles: 

Interchange the Values of 2 Columns

You might face a situation where you need to interchange the values of two columns in an Oracle database table. This article will explore ways to achieve this.

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.

articles: 

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.

articles: 

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.

articles: 
Kevin Meade's picture

Undocumented OVERLAPS Function, Don't Use it Yet

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.

articles: 

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!

articles: 
dwarak.k's picture

Hierarchical queries

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. Be it a family tree or a employee/manager tree or what ever.

articles: 

Pages

Subscribe to RSS - SQL & PL/SQL