Mike’s PL/SQL Notes 2007-09-28 13:40:00

Uncategorized
| Sep 28, 2007

Visualizing rollups


Post 1 of a 3 post article.


Many code examples, found on the web, focus on a single feature of the programming language. There is a good reason for this which is that examples incorporating many features are less generic and therefore less likely to be of use to a large portion of the audience. The down side of this 'keep it simple' approach is that the audience is less often exposed to ways to combine features. The ability to combine the features of SQL is what makes it an amazingly powerful language.

Before you begin, you should (more...)

Mike’s PL/SQL Notes 2007-09-25 10:46:00

Uncategorized
| Sep 25, 2007

Using a Function Based Index to enforce complex unique constraints


Sometimes there is a need to enforce different rules for uniqueness depending on
the type of record. Using a Function Based Index is one way to accomplish this.
 
EXAMPLE:

Lets start with a preview of our table



  PRIM_KEY A REC_CLASS           DLR_FK DLR_SER_FK WHERE_CLAUSE         FIELD_FK
---------- - --------------- ---------- ---------- ------------------ ----------
        70 Y PRODUCT                          2202 number-Floors=2          1111
        80 Y PRODUCT                          2202 number_floors = 1        1111
        90 N DEALER_SERVICE                   2202                          3333
       100 Y DEALER_SERVICE                   2202                          3333
       110 Y DEALER                1000                                     3333
(more...)

Mike’s PL/SQL Notes 2007-09-24 09:08:00

Uncategorized
| Sep 24, 2007

Basics of PL/SQL Package Structures

 

      This article covers some of the basics of PL/SQL package structure.
There is much that is left out including package cursors and exception
structures. Beginners should learn a lot from following the code logic.
Intermediate programmers may pick up a couple of things. Advanced
programmers will probably already know everything presented here.
If you attempt to follow the code logic, don’t expect the code to do
anything meaningful. The point of the code is to illustrate some
structural components.

CREATE
OR REPLACE PACKAGE Demo AS

-- This is a package specification (not a (more...)

Mike’s PL/SQL Notes 2007-09-22 17:04:00

Uncategorized
| Sep 22, 2007

Using Model for generating CSV by Partition

This example assumes that  "thedate"can not be NULL.
CUSTTAB
      CUST    THEDATE
---------- ----------
         5        234
         5        333
         5        336
         6        111
         6        222
         6        333
         5        100

col csv format a30

SELECT cust,SUBSTR(csv,2) csv FROM custtab
MODEL
 
RETURN updated ROWS
 
PARTITION BY (cust)
 
DIMENSION BY (row_number() over (PARTITION BY cust ORDER BY thedate) AS therow)
 
MEASURES ( thedate,cast (NULL AS VARCHAR2(400)) AS csv (more...)

Using Dynamic Sql to work around PL/SQL bugs

Uncategorized
| Sep 22, 2007

A coworker encountered this problem. We search the Internet for a solution, but nothing was found. I am hoping that by posting this, others having the same problem will quickly find a solution. Furthermore, this example illustrates a more general principle which is that sometimes things work in SQL which do not work in PL/SQL and a simple solution is to use dynamic sql.

This problem is encountered when trying to access an NVARCHAR2 using a TABLE function in PL/SQL.

ORA-12714: invalid national character set specified

Originally written as:

FOR c IN ( SELECT * FROM TABLE (p_question_tab_i) ) LOOP

(more...)

Oracle 11g New Features DBMS_ADDM for RAC

Virag Sharma virag123@gmail.com

In OCP Oracle Database 10g Exam Guidechapter 3 “Automatic Database Management” there is question


5. To retrieve the ADDM reports using SQL, what do you need to do?

A. Run the addmrpt.sql SQL script
B. Use the DBA_ADDM view
C. Use the DBA_ADVISOR view
D. Use the DBMS_ADDM package

Answer “A” is correct and D is wrong because there is no PL/SQL package named DBMS_ADDM.
But in 11g , it is not true , i.e. Package DBMS_ADDM is there in 11g. In case of (more...)

Redwood Shores




I was in Redwood last week and got a chance to meet some of the best minds in Server Technologies including a few ex-colleagues and a couple of guys on the revered
Oak Table

And although a significant part of the week was spent in the wine and lounge bars of San Francisco it was quite an enriching experience..

Query to determine the clustering factor before you create the index

I found the following query useful to determine the clustering factor for the indexes that has not been created yet.

select count(1) clustering_factor
from
(
select dbms_rowid.rowid_block_number(rowid) block_no,
list of the indexed columns,
count(1) num_rows,
LAG(dbms_rowid.rowid_block_number(rowid))
over (order by list of the indexed columns) prev_block
from (more...)

Oracle 11g Documentation

Since reading the documentation is always a good idea when a new Oracle release is out, I decided to read Oracle® Database Upgrade Guide and the Oracle® Database New Features Guide before getting into the 11g installation guide.

It is funny how I haven’t yet downloaded the new Oracle 11g database and I am already using one of its new features!

Oracle 11g now enables users to send out comments, making it faster and easier to everyone to enhance the documentation.
This new feature have also been discussed here:
IT-eye Weblog
OracleAppsLab
OTN TechBlog

While reading through the 2 guides, (more...)

Oracle 11g Database New Features: Data Guard Enhancements


Why We need Data Guard ?? for

  • Data protection
  • Data Availability

11g Increase ROI from standby systems and enhance manageability, New feature like Active Data Guard , Snapshot standby make better ROI.

Here is some data guard category and there enhancement

1) Data Protection
  • Advanced Compression
  • Lost-write protection
  • Fast-Start Failover
2) Increase ROI
  • Active Data Guard
  • Snapshot Standby
3) High Availability
  • Faster Redo Apply
  • Faster failover & switchover
  • Automatic Failover using ASYNC
4) Manageability
  • Mixed Windows/Linux

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option
(more...)

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

I have rated 5 *(star) for some sites , which I like Most

( these links collected from google alert )

  1. Oracle Database Online Documentation 11g Release 1 (11.1) *****
  2. ORACLE-BASE - Articles on Oracle 11g new features *****
  3. Oracle Database 11g on OTN
  4. Oracle Database 11g: The Top Features for DBAs and Developers
  5. Pythian Group Blog » Tuning Pack 11g : Real-Time SQL Monitoring
  6. PSOUG - New in 11gR1*****
  7. Robert G. Freeman’s Blog: 11g Security New Feature… (more...)

Nulls: Nulls and Aggregate Functions (New SQL Snippets Tutorial)

Uncategorized
| Sep 8, 2007
A new tutorial has been added to SQL Snippets which demonstrates how aggregate functions deal with null values. Techniques for generating results that ignore nulls and results that include nulls are highlighted.
...

Oracle 11g New Features SQL plan management (SPM)

You might have noticed that execution plan changed in CBO , specially in following case

  • Database Upgrade
  • Database / Schema Stats collection
  • Change in environment ( LinkUnix to Linux Migration )
  • Change in data














Case
:
You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when (more...)

Disabling cursor trace

Sometime last year I had blogged about unshared cursors and an event to trace the same here
http://el-caro.blogspot.com/search?q=cursor+trace


Well after you set the trace on and have got the required information you would obviously want to turn it off.

The command to do the same is

alter system set events 'immediate trace name cursortrace level 2147483648, addr 1';

However this does not work as I realized today when I was diagnosing multiple versions created for pl/sql procedure calls with ref cursors as arguments and the trace almost filled up my udump. New sessions spawned have entries such (more...)