Defying Referential Integrity

Updating a Primary Key value without dropping Foreign key constraints in Oracle 8i and later versions

By: Dhaval Somaiya/Kunal Mehta

Manier times there are cases wherein a primary key value needs an update, But  updating  them in Oracle would be a tricky thing if you do have foreign key.

Trying to update a primary key will throw an error “Child record found”.

Similarly, trying to update a foreign key will throw an error “Parent record found”. 

Following steps guides you to overcome these hurdles while making sure to have referential integrity intact.

Step 1:

Assume a Primary key and a foreign key relationship.

ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPARTMENT_ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMP FOREIGN KEY (DEPARTMENT _ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) ;

It is essential to create the constraint as DEFERRABLE right away when creating it, if you want to be able to differ the constraint check to the end of the transaction.

DEFERRABLE constraint is IMMEDIATE by default.

Step 2:

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMP FOREIGN KEY (DEPARTMENT _ID) REFERENCES DEPARTMENT (DEPARTMENT_ID) DEFERRABLE;

You can check the same using the following query.

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME=’ EMPLOYEE’

Step 3: ALTER SESSION SET CONSTRAINTS=DEFERRED;

Step 4: ALTER TABLE DEPARTMENT SET DEPARTMENT_ID=DEPARTMENT_ID+1;

Step 5: ALTER TABLE EMPLOYEE SET DEPARTMENT_ID=DEPARTMENT_ID+1;

Step 6: ALTER SESSION SET CONSTRAINTS=IMMEDIATE

Step 7: EXECUTE COMMIT;

Oracle 11g New Features

Oracle 11g Launched

By Sandip Davda

On 11^th July 17, 2007 Oracle today introduced Oracle(r) Database 11g, the latest release of the world’s most popular database. With more than 400 features, 15 million test hours, and 36,000 person-months of development, Oracle Database 11g is the most innovative and highest quality software product Oracle have ever announced. 

However actual product will be released in August 2007 on Linux Platform. As of now the release date for Windows and other platform is not decided. So you need to wait before you get your hands on Oracle 11g  -  the most eagerly awaited product…

Oracle Database 11g can help organizations take control of their enterprise information, gain better business insight, and quickly and confidently adapt to an increasingly changing competitive environment. To do this, the new release extends Oracle’s unique database clustering, data center automation, and workload management capabilities. With secure, highly available and scalable grids of low-cost servers and storage, Oracle customers can tackle the most demanding transaction processing, data warehousing, and content management applications.

Real Application Testing Helps Reduce Time, Risk and Cost of Change 

Oracle Database 11g features advanced self-management and automation features to help organizations meet service level agreements. For example, with organizations facing regular database and operating system software upgrades, and hardware and system changes, Oracle Database 11g introduces Oracle Real Application Testing, making it the first database to help customers test and manage changes to their IT environment quickly, in a controlled, cost effective manner.

Increase Return On Investment for Disaster Recovery Solutions 

In Oracle Database 11g, Oracle Data Guard enables customers to use their standby database to improve performance in their production environments as well as provide protection from system failures and site-wide disasters. Oracle Data Guard uniquely enables simultaneous read and recovery of a single standby database making it available for reporting, backup, testing and ‘rolling’ upgrades to production databases. By offloading workloads from production to a standby system, Oracle Data Guard helps enhance the performance of production systems and provides a more cost-effective disaster recovery solution.  

Enhanced Information Lifecycle Management and Storage Management 

Oracle Database 11g has significant new data partitioning and compression capabilities, for more cost-effective Information Lifecycle Management and storage management. Oracle Database 11g automates many manual data partitioning operations and extends existing range, hash and list partitioning to include interval, reference and virtual column partitioning. In addition, Oracle Database 11g provides a complete set of composite partitioning options, allowing storage management that is driven by business rules.  

Advanced Data Compression 

Building on its long-standing data compression capabilities, Oracle Database 11g offers advanced data compression for both structured and unstructured (LOB) data managed in transaction processing, data warehousing, and content management environments. Compression ratios of 2x to 3x or more for all data can be achieved with the new advanced compression capabilities in Oracle Database 11g.  

Total Recall of Data Changes 

The new release also features “Oracle Total Recall,” enabling administrators to query data in designated tables “as of” earlier times in the past. This offers an easy, practical way to add a time dimension to data for change tracking, auditing, and compliance.  

Maximum Availability of Information 

Oracle has consistently led the industry in protecting database applications from planned and unplanned downtime. Oracle Database 11g continues this lead by making it easier for administrators to meet their users’ availability expectations. New availability features include Oracle Flashback Transaction which makes it easy to back out a transaction made in error, as well as any dependent transactions; Parallel Backup and Restore which helps improve the backup and restore performance of very large databases; and ‘hot patching,’ which improves system availability by allowing database patches to be applied without the need to shut databases down. In addition, a new advisor - Data Recovery Advisor - helps administrators significantly reduce recovery downtime by automating problem investigation, intelligently determining recovery plan and handling multiple failure situations.  

Oracle Fast Files 

The next-generation capability for storing large objects (LOBs) such as images, large text objects, or advanced data types including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly. 

Faster XML

Oracle Database 11g includes significant performance enhancements to XML DB, a feature of Oracle database that enables customers to natively store, and manipulate XML data. Support for binary XML has been added offering customers a choice of XML storage options to match their specific application and performance requirements. XML DB also enables manipulation of XML data using industry standard interfaces with support for XQuery, Java Specification Requests (JSR)-170 and SQL/XML standards.

Transparent Encryption

Oracle Database 11g continues to build on its unmatched security capabilities through the addition of significant enhancements. The new release features improved Oracle Transparent Data Encryption capabilities beyond column level encryption. Oracle Database 11g offers tablespace encryption that can be utilized to encrypt entire tables, indexes, and other data storage. Encryption is also provided for LOBs stored in the database.

Embedded OLAP Cubes 

Oracle Database 11g also provides data warehousing innovations. OLAP cubes are enhanced to behave as materialized views in the database. This allows developers to use industry standard SQL for data query, but still benefit from the high performance delivered by an OLAP cube. New Continuous Query Notification features allow applications to be immediately notified when important changes are made to database data without burdening the database with constant polling.

Connection Pooling and Query Result Caches 

The performance and scalability features in Oracle Database 11g are designed to help organizations maintain a highly performant, scalable infrastructure to provide users’ with the best quality of service. Oracle Database 11g further enhances Oracle’s position as the industry’s performance and scalability leader with new features such as Query Result Caches which improves application performance and scalability by caching and reusing the results of often called database queries and functions in database and application tiers, and Database Resident Connection Pooling which improves the scalability of web-based systems by providing connection pooling for non-multi-threaded applications.

Enhanced Application Development 

Oracle Database 11g offers developers a choice of development tools, and a streamlined application development process that takes full advantage of key Oracle Database 11g features. These include new features such as Client Side Caching, Binary XML for faster application performance, XML processing, and the storing and retrieving of files. In addition, Oracle Database 11g also includes a new Java just-in-time Compiler to execute database Java procedures faster without the need for a third party compiler; native integration with Visual Studio 2005 for developing .NET applications on Oracle; Access migration tools with Oracle Application Express; and SQL Developer easy query building feature for fast coding of SQL and PL/SQL routines.

Enhanced Self-Management and Automation 

The manageability features in Oracle Database 11g are designed to help organizations easily manage enterprise grids and deliver on their users’ service level expectations. Oracle Database 11g introduces more self-management and automation that will help customers reduce their system management costs, while increasing performance, scalability, availability and security of their database applications. New manageability capabilities in Oracle Database 11g include Automatic SQL and memory tuning, a new Partitioning Advisor which automatically advises administrators on how to partition tables and indexes in order to improve performance, and enhanced performance diagnostics for database clusters. In addition, Oracle Database 11g includes a new Support Workbench which provides an easy-to-use interface that presents database health-related incidents to administrators along with information on how to quickly manage the resolution of incidents.  

Summary 

Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making the management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g’s unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.

Oracle Database 10g Express Edition – Free(Can’t Believe? Read on….)

 

By Sandip Davda

This is really a very good news for all Oracle fans who want to learn/use oracle but cant aford it….

Oracle Database 10g Express Edition is Free to develop, deploy, and distribute. Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that’s free to develop, deploy, and distribute; fast to download; and simple to administer.

Oracle Database XE is a great starter database for:

• Developers working on PHP, Java, .NET, XML, and Open Source applications.
• DBAs who need a free, starter database for training and deployment.
• Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge.
• Educational institutions and students who need a free database for their curriculum With

Oracle Database XE, you can now develop and deploy applications with a powerful, proven, industry-leading infrastructure, and then upgrade when necessary without costly and complex migrations.

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine. Download Oracle Database 10g Express Edition Oracle Database XE includes Oracle HTML DB 2.1, a declarative, graphical development environment for creating database-centric Web applications. In addition to HTML DB 2.1, you can use popular Oracle and third-party languages and tools to develop your Oracle Database XE applications. Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base. Adrian Bridgwater samples a cup of this fresh new brew.

Is it really free?
Oracle Database XE really is for free development. There are no database license costs associated with developing applications for the product and it is offered using the complete integrated set of Oracle Database programming interfaces. This includes SQL, PL/SQL, Java, C and PHP, Windows .Net, Oracle Application Express, C++, ODBC and OLE DB. Developers can then freely distribute Oracle Database XE as a standalone database or as part of a third-party application or product, provided the defined criteria are adhered to.

“Oracle Database XE allows developers, DBAs, ISVs and students the opportunity to learn, develop and deploy on the world’s leading database software, at no cost,” said Thomas Kyte, vice president, Oracle. “No one has to make do anymore; everyone can now start with the best.” “Developers, DBAs, students and ISVs often look to no cost DBMSs to get started in developing and learning to develop database applications,” said Carl Olofson, research director for information management and data integration software research at analyst firm IDC. “When such a DBMS is supported by a wealth of literature, online technical material and professional experts, it allows developers to easily grow and support their applications and data needs over time without needing to convert their database later on, the result is a low risk path that offers a high potential return not only immediately, but in the long run.”

When such a DBMS is so well supported… it allows developers to easily grow and support their applications and data needs over time without needing to convert their database later on.

How do I manage Oracle Database XE?
Oracle Database XE comes complete with its own management browser interface to monitor database activity and manage database users, storage and memory. The Oracle Database 10g product family offers many different options and features across different editions.For a complete comparison, see oracle.com/technology/products/database/oracle10g/pdf/twp_general_10gdb_product_family.pdf
Registered users of Oracle Database XE get access to a free online community forum hosted and monitored by Oracle experts at oracle.com/technology/xe and the company urges all users to register and take advantage of this free resource. Users are encouraged to discuss challenges and successes in the online community forum so that the Oracle Database XE development team can respond.

So how will ISVs benefit from Oracle Database XE? With Oracle Database XE, ISVs have one of the industry’s leading database technology to power their applications. Distributing it in their applications or products without additional cost offers even greater value to their customers, this is what Oracle says – but it’s hard to argue with the fact that even ISVs who are unfamiliar with Oracle Database have an opportunity to experience the technology, validate it with their applications and freely distribute it-with zero risk and investment.

Migration situation
If your usage exceeds Oracle Database XE criteria then the company offers a choice of database editions to upgrade to. Depending on scale of application requirements, you can easily upgrade to Standard Edition One, Standard Edition, or Enterprise Edition.

Migration is pretty straightforward. Any application developed for Oracle Database XE will run completely unchanged with Oracle Database 10g Standard Edition One, Standard Edition, or Enterprise Edition; application development investment is guaranteed.

Oracle Database 10g Express Edition Features

• Available on 32-bit Linux and Windows
• Installs using native installers
• English (single byte character set) and International (Unicode) versions available with support for 10 major languages
• Supports up to 4GB of user data
• Executes on one processor in any size server
• Uses up to 1GB RAM of available memory in any size server
• Fully upgradeable to other Oracle Database 10g editions
• Oracle Text for efficient text-based searches
• Oracle Data Provider for .NET

Oracle Database XE Development Environments

• Oracle Call Interface for C and C++
• Oracle Application Express
• ODBC
• OLE DB
• JDBC
• PHP

The Limitations of Oracle 10gXE

• Supports upto 4GB of user data (in addition to Oracle system data)
• Single instance only of Oracle DatabaseXE on any server
• May be installed on a multiple CPU server, but only executes on one processor in any server
• May be installed on a server with any amount of memory, but will only use upto 1GB RAM of available memory

UTL_FILE A Great Utility For PL/SQL

UTL_FILE  A Great Utility For PL/SQL 

 By Anju Ravindran  

UTL_FILE is a package that has been welcomed warmly by PL/SQL developers. It allows PL/SQL programs to both read from and write to any operating system files that are accessible from the server on which your database instance is running.

Getting Started with UTL_FILE

The UTL_FILE package is created when the Oracle database is installed. The utlfile.sql script contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym UTL_FILE for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

UTL_FILE programs

Following table shows the UTL_FILE program names and descriptions.

UTL_FILE Programs
Name	Description	Use in SQL
FCLOSE	Closes the specified files	No
FCLOSE_ALL	Closes all open files	No
FFLUSH	Flushes all the data from the UTL_FILE buffer	No
FOPEN	Opens the specified file	No
GET_LINE	Gets the next line from the file	No
IS_OPEN	Returns TRUE if the file is already open	No
NEW_LINE	Inserts a newline mark in the file at the end of the current line	No
PUT	Puts text into the buffer	No
PUT_LINE	Puts a line of text into the file	No
PUTF	Puts formatted text into the buffer	No

Before writing programs

·          You need to modify the INIT.ORA parameter initialization file of your instance.
·         You will have to contact your database administrator and have him or her make the changes (if willing) and then “bounce” the database.
·         You need to get the format of the parameter entries correct. Here’s the format of the parameter for file access in the INIT.ORA file:  utl_file_dir = <directory>
·         You need to add exception sections to your programs.

 UTL_FILE nonprogram elements

When you open a file, PL/SQL returns a handle to that file for use within your program. This handle has a datatype of UTL_FILE.FILE_TYPE currently defined as the following:

TYPE UTL_FILE.FILE_TYPE IS RECORD (id BINARY_INTEGER);

As you can see, UTL_FILE.FILE_TYPE is actually a PL/SQL record whose fields contain all the information about the file needed by UTL_FILE. However, this information is for use only by the UTL_FILE package. You will reference the handle, but not any of the individual fields of the handle. (The fields of this record may expand over time as UTL_FILE becomes more sophisticated.)Here is an example of how to declare a local file handle based on this type:

DECLARE

   file_handle UTL_FILE.FILE_TYPE;

BEGIN

   ...

The UTL_FILE process flow

The following sections describe each of the UTL_FILE programs, following the process flow for working with files. That flow is described for both writing and reading files. In order to write to a file you will (in most cases) perform the following steps:

1.      Declare a file handle. This handle serves as a pointer to the file for subsequent calls to programs in the UTL_FILE package to manipulate the contents of this file.
2.      Open the file with a call to FOPEN, which returns a file handle to the file. You can open a file to read, replace, or append text.
3.      Write data to the file using the PUT, PUTF, or PUT_LINE procedures.
4.      Close the file with a call to FCLOSE. This releases resources associated with the file.

More links       

   http://www.unix.org.ua/orelly/oracle/bipack/ch06_02.htm     

   http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm

SQL*Loader - Qucikly Transfer your legacy data to Oracle

SQL*Loader

By Sandip Davda

What is SQL*Loader?

SQL*Loader has lots and lots of options which can be used to handle various types of data and levels of complexity. SQL*Loader is fully described in the Oracle Server Utilities User’s Guide. This document is just about getting started. SQL*Loader runs on Unix, mainframes, and PC’s. This document is just about running it from a Windows PC.

Why Use SQL*Loader From Your PC?

If you need to transfer quite a lot of data from your machine to an Oracle database table, you might want to use SQL*Loader. If you already have the data in some other format, it may be worthwhile to use SQL*Loader. If you need to transfer local data to a remote database on some recurring basis, it may be preferable to use SQL*Loader rather than something like FTP.

Getting Started, an Example
Here’s what you do:

1) Create your data file. This is easy. Save your Excel spreadsheet data AS a Comma-Separated-Variable (*.csv) file. This will automatically put commas between each of the four data elements. In addition, if any of the data elements already contain a comma, the Save AS *.csv step will optionally and automatically enclose that data in double quotes. So, after your Save AS command, you might have a file named C:\dept.csv that contains data like this:

21,Production,Bangalore
22,HR,Chennai
23,FMG,Bangalore24,Administration

2) Create your control file. Using any text editor, create a file (say, C:\dept.ctl) containing these lines:

LOAD DATA
INFILE ‘C:\dept.csv’
APPEND
INTO TABLE scott.dept
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(deptno integer external, dname, loc )

The append keyword says, “add new rows to existing rows .” There’s also an INSERT [into empty table] and replace [existing rows] option.Because the last two items are of character datatype, it was not necessary to further describe them – character is the default. The first column is numeric data . The INTEGER EXTERNAL describes the datatype in the C:\dept.csv input file. Notice there is some missing data in the data file –Administration has no location defined. The TRAILING NULLCOLS statement handles the missing data; it tells SQL*Loader to load any missing data as NULL values. There are, as i said earlier, lots of available options described in the Utilities User’s Guide.

3) Run SQL*Loader. At an MS-DOS prompt (or the Start, Run menu) , execute SQL*Loader as follows:

sqlldr scott/tiger@UWTEST control=C:\dept.ctl

When the load completes, look in the file C:\dept.log. This log file will contain information about how many rows were loaded, how many rows — if any — were NOT loaded, and other information that may be useful to reassure or debug.