Blogs

Kevin Meade's picture

Easy XML - Let the Database do the Work

articles: 

I don’t want to learn XML. I don’t want to learn XPATH. I don’t want to learn XQUERY. I don’t want to learn XSLT. I don’t want to learn XSQL. I don’t want to learn XSU. XDB has some way cool stuff. But to-date, I have been pretty successful in not learning things I don’t want to learn. So you might think that when the time came for my databases to start sharing data via a XML transport, I would be in trouble. Fortunately, God gave me at least a few smarts when he loaded me up with laziness. In this article we will discuss one way to move XML formatted data in and out of Oracle using what we already know: object views, instead of triggers, collections, and PL/SQL packages.

gojko's picture

What a strange way to write NULL

articles: 

A few days ago, while hunting for a bug in PL/SQL code, I stumbled upon the strangest way to write NULL. If the e-mail address parameter was empty, the genius who wrote this PL/SQL procedure set it to , then compared it with ten lines below, in order to log a problem. I really don't know what is it about NULL that scares people so much, but over the years I got used to occasional -1 and 0, or even 'EMPTY'. However, this is the first time I ran across Donald.

Kevin Meade's picture

The Phoenix Rises: a How-To for SQL FROM SQL

articles: 

If I had to recommend THE one most exploitable skill to have for an Oracle Developer or DBA, it would have to be HOW TO PLAY GOLF. I have observed few other talents give regular Mary and Joe, greater access to people from all levels of the corporate world. You will meet everyone from the janitor, to management elite without the fear or intimidation that we sometimes feel in these circles. For some reason, on the fairway all are equal; the only things that matter are your swing and your handicap. Alas, I never learned to play golf, so I must settle for imparting an actual job skill. And for that, my best first choice would be SQL FROM SQL.

gojko's picture

A better view

articles: 

Accessing the database from the outside world basically comes down to two options - direct querying or executing stored procedures. Procedural access is often chosen for the wrong reasons - making maintenance significantly harder.

Natalka Roshak's picture

10gR2 new feature: Case Insensitive Sorts & Compares

articles: 

One handy new feature of 10gR2 is its vastly expanded ability to do case-insensitive sorts and compares (a subset of its expanded ability with other special sorts and compares, such as special linguistic sorts).

gojko's picture

Best practice to corrupt data

articles: 

A common practice for handling errors in PL/SQL procedures is to catch all errors in the top-most database layer and convert them into error codes and human readable messages for client applications. This technique is a relict from the past and, in fact, a very bad practice from today's perspective, since it can lead to data corruption.

Kevin Meade's picture

Display REFCURSOR results through SQL*Plus

articles: 

Recently a friend asked me a simple question; "How do I display the results of a REFCURSOR through SQL*Plus?". The answer is of course is just as simple as the question, you "SELECT" it like anything else. He just had not seen the syntax before. Being me though, I wouldn't let him get away with such an easy offing, so I sent him instead a quick write-up on REFCURSORS and, the just as interesting, CURSOR EXPRESSIONS. This write-up shows various coding samples of how to use these to great effect. He liked it. He said it should be written up somewhere, and I knew just where. Hope you like it too. If you do, please add something in a reply, especially if you have a better or unique or interesting way of exploiting these features. I'd like to read about it, maybe even steal your code...

Natalka Roshak's picture

Using Oracle's recycle bin

articles: 

One of the many new features that Oracle 10g introduced is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go "into" the recyclebin, and can be restored from the recyclebin. OraFAQ has already published an article covering the basics; in this article, I'll cover some of the more subtle aspects of the recyclebin.

Creating a hierical tree structure from strings

articles: 

Recently, I have been helping a company in redesigning their schema, and one issue was that they stored hieracal trees in varchar2(4000) column, one full branch per row.

Data was if this kind:

/Oracle/8/1/5/Microsoft
/Oracle/8/1/5/VMS
/Oracle/8/1/5/0/1/VMS

Their main problem was that searching in this was really ugly, as they had to full table/index scan a lot, when searching using like '%%'

I came up with this solution to convert this into a small table that can be queried using connect by instead:

[code]
CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )

Logical vs Physical Standby database

articles: 

Logical vs. Physical Standby databases
Submitted By Rama Subramoniam

A Quick Primer

Standby databases, in a nutshell, acts as a failover for our mission critical production databases. When production database crashes, applications can quickly switchover to the stand by databases.

Oracle provides two types of standby databases:

1. Physical Standby Database

Standby database is called “physical” if the physical structure of stand by exactly matches with stand by structure. Archived redo log transferred from primary database will be directly applied to the stand by database.

Pages

Subscribe to RSS - blogs