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.
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...
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.
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:
CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )
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.
Often, we want our PL/SQL code to output messages (using DBMS_OUTPUT) to debug a problem. This is mostly required during development, but may also come in handy if a program misbehaves in production. If the testing of your application is thorough, then you should never have an issue in the production environment. But, we all know that there are instances where we do want debug messages even in the production environment. Before Oracle 10g R2, this could not be done dynamically.
Indexes are used to speed up data access by SQL statements, but there is no free lunch as each additional index increases:
- The time needed to perform DML (Insert/Update/Delete) operation on the table (because additional index entries must be updated).
- The enqueue time (during DML the corresponding index entries are locked decreasing the ability of parallel updates and causing transactions, issued by another session(s) to wait.
- The generated UNDO volume.
- The disk space needed to store the index information.
I recently stumbled across a sample chapter of Expert Oracle PL/SQL where the authors (Ron Hardman and Michael McLaughlin) mentioned that OraFAQ.com is a good place to post Oracle questions.
LOBs, or Large OBjects, are Oracle's preferred way of handling and storing non-character data, such as mp3s, videos, pictures, etc., and long character data. Binary large objects, or BLOBs, and character large objects, or CLOBs, can store up to terabytes of data - much more than the paltry 4000 bytes permitted in a varchar2 column. LOBs and CLOBs offer DBAs and developers great flexibility and storage space; the tradeoff is that they're a bit clunkier to handle.
The first thing to know about LOBs is that there are two basic types: external LOBs, which are stored outside the database, and internal LOBs, which are stored in the database. External LOBs are of the BFILE datatype; essentially, the database stores a pointer to the LOB's location in the file system. As such, they can't participate in transactions, and access is read-only. This article will deal with internal LOBs.
Flashback Recovery is a new enhancement to the 10g database for the DBA's toolkit. Effectively, it's an "Oh shit!" protection mechanism for DBAs as it reduces recovery time from hours to minutes. Ask any DBA about the main cause of application outage - other than hardware failure - and the answer will be "human error". Such errors can result in logical data corruption and can bring down the complete system. Part of being human is making mistakes. Without advance planning, these errors are extremely difficult to avoid, and can be very difficult to recover from. Typical user-errors may include the accidental deletion of valuable data, deleting the wrong data, or dropping the wrong table.