DBMS Change Notification

This is about DBMS Change Notification, enhanced feature introduced from version 10g.

DBMS Change Notification can only be used for tables those are having length (including schema name) less than 30 characters. For example, Oracle Project module PA.PA_STD_BILL_RATE_SCHEDULES_ALL table length is more than 30, so this table can not be used for DBMS Change Notification.

Also DBMS Change Notification treats particular change as bulk if numbers of changed records are more than 80. In this case ROWID will be empty. So we won’t know the affected records.

Java Stored Procedure in Oracle

Though we can write business logic, data validation, exception handling in pl/sql, still we need to use languages like C, Pro*C, Java for complex and system programming in Oracle.

Recently we had a requirement to convert the Images and store it into Oracle. As we know pl/sql won’t support graphics I used Java program for image conversion and used it in my plsql.

Java program should have public method with required parameters and return statement if needed. For example, let as take addition of two numbers. Two numbers should be input parameters and the method should return sum of these two.

Compile the java file and keep the class file in folder/directory registered in Oracle Database. We can create new directory if needed using following syntax.

CREATE DIRECTORY Bfile_dir AS '/usr/temp';

Store the class file into database using following syntax.

CREATE OR REPLACE JAVA CLASS USING BFILE (Bfile_dir, 'Addition.class');

loadjava executable can also be used instead of the above statement. Now java class is available to use. Create function/package to use in pl/sql.

CREATE OR REPLACE FUNCTION add_numbers(no1 NUMBER, no2 NUMBER) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Addition.get(int,int) return int';

I used Oracle 10g Release 2. Refer Oracle Database Java Developer’s Guide for more details.

Validate & Get Values from XML using PL/SQL

There are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using DBMS_XMLDOM API. This sample will read employee name and print it in the console.

DECLARE
lc_return_msg VARCHAR2(1000);
lc_xml_buf VARCHAR2(2000);
lc_xml_value VARCHAR2(2000);
lc_resp_xml XMLType;
lc_xml_doc DBMS_XMLDOM.DOMDocument;
lc_xml_ndoc DBMS_XMLDOM.DOMNode;
lc_xml_docelem DBMS_XMLDOM.DOMElement;
lc_xml_node DBMS_XMLDOM.DOMNode;
lc_xml_childnode DBMS_XMLDOM.DOMNode;
lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;
BEGIN
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Code>001</Code>
<Name>Sales</Name>
<Employees>
<Employee>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<DoB>01/01/1975</DoB>
</Employee>
<Employee>
<FirstName>Adam</FirstName>
<LastName>Ford</LastName>
<DoB>12/03/1983</DoB>
</Employee>
</Employees>
</Department>';

-- Validate XML
lc_resp_xml := XMLType(lc_return_msg);

-- Create DOMDocument Handle
lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);
lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);

DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);

-- Get all elements
lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);

-- Get Result Elemement
lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');

-- Get Second Employee Name
lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);
lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);
lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);

-- Print second employee name
DBMS_OUTPUT.put_line(lc_xml_value);
END;

Steps to activate User Preferred Time Zone Support

From release 11.5.10 CU2, EBS introduced new feature called User Preferred Time Zone Support. Ie each user can specify their time zone preference, and the system will honor this preference for display and entry of date with time fields. Before 11.5.10 CU2, end users had to interact with the system in time zone set at database level.

To activate this feature following steps to be true or setup properly.

• Database should be Oracle RDBMS 9i or higher
• Environment variable ORA_TZFILE must be configured to use the time zone file timezlrg.dat rather than timezone.dat (Note: database needs to be restarted if this environment is changed).
• The database must be started in the standard corporate time zone
• Profile Server Timezone (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database
• Profile Client Timezone (CLIENT_TIMEZONE_ID) must be set at the user level
• Profile Enable Timezone Conversions (ENABLE_TIMEZONE_CONVERSIONS) must be set to Yes at the Site level
• Profile Concurrent: Multiple Time Zones (CONC_MULTI_TZ) should be set to No at the Site level
• Environment variable FORMS60_APPSLIBS must be set in the Forms tier with APPS standards libraries like APPCORE FNDSQF APPDAYPK...

For more details, refer User Preferred Time Zone Support Documents and metalink notes 330075.1 & 340512.1.

Oracle 11g Database New Features for Developers

• Table can be altered to Read Only or Read Write.

• A new XMLIndex_clause lets you create an XMLIndex index for XML data.

• In CREATE TABLE, A new virtual_column_definition create a virtual column.

• SELECT has new PIVOT syntax that rotates rows into columns. A new UNPIVOT operation to query data to rotate columns into rows.

• TRUNCATE statement was presented as a single statement with separate syntactic branches for TABLE and CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements.

• Enhancements to Regular Expression (REGEXP_INSTR, REGEXP_COUNT and REGEXP_SUBSTR) Built-in SQL Functions.

• The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration (in contrast with the EXIT statement, which exits a loop and transfers control to the end of the loop).

• Allow Sequences in PL/SQL Expressions. PL/SQL now recognizes the SQL pseudo columns CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM.

• Both native dynamic SQL and the DBMS_SQL package have been enhanced and support dynamic SQL statement larger than 32KB by allowing it to be a CLOB.

• Named and Mixed Notation in PL/SQL Subprogram Invocations are allowed.

• PL/SQL provides a function result cache. To use it, use the RESULT_CACHE clause in each PL/SQL function whose results you want cached. Because the function result cache is stored in a shared global area (SGA), it is available to any session that runs your application.

• PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code.

• Subprogram Inlining replaces a subprogram call (to a subprogram in the same PL/SQL unit) with a copy of the called subprogram.

• Oracle Application Express provides an application development tool that is built into the database and is installed in the database by default.

• Create Rules and Actions for DML Events With Rules Manager. In 10g only INSERT is supported.

• Rules Manager now includes a PL/SQL API to store reusable and shareable rule conditions in SQL WHERE clause format.

• Unicode 5.0 Support

• New Compound Trigger Type: Compound trigger has a section for each of the BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT timing points. All of these sections can access a common PL/SQL state.

• Client-Side Query Cache feature enables caching of query result sets in client memory. The cached result set data is transparently kept consistent with any changes done on the server side.

• Enable the XDB HTTP Server for SOA feature allows Oracle Database to be treated as simply another service provider in a service-oriented architecture (SOA) environment.

• Automatic SQL Tuning with Self-Learning Capabilities: Automatically detects high-load SQL statements and then tunes them automatically as needed in the maintenance window by creating appropriate SQL Profiles. It also issues proactive advice to create new access structures such as indexes that will significantly improve the performance of the high-load SQL statements.

• Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases.

• Database Resident Connection Pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented.

• Online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc. expect triggers), provided the redefinition does not logically affect them.

• Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

Refer Oracle 11g Database Documents to learn more and to find examles

Oracle 10g Database New Features for Developers

• This version of the Oracle Database has been designed to meet two key goals: Reducing the cost of manageability and Delivering increased performance for all key workloads.

• Major new high-availability features are also provided, including new Flashback capabilities (including new Flashback Database and Flashback Table) that reduce the downtime caused by human errors. In addition, support for rolling upgrades has been provided to reduce the downtime associated with database and application upgrades.

• Application development improvements include a new built-in application development environment, support for a high-performance and highly integrated XML capability across the entire technology stack, and a framework provided with the database that enables desktop and middle-tier applications to retrieve and extract data from the database using standard Web Services mechanisms.

• Automatic Storage Management (ASM) automates and simplifies the optimal layout of datafiles, control files, and log files. Database files are automatically distributed across all available disks, and database storage is rebalanced whenever the storage configuration changes.

• Automatic Workload Repository (AWR) is the latest evolution of statspack which provides the central information store for all Oracle 10g self-tuning functionality.

• Rename already existing tablespace. No longer have to create a new tablespace, copy the contents from the old tablespace, and drop the old tablespace.

• Automatic Shared Memory Tuning automates the configuration of System Global Area (SGA) memory-related parameters (buffer cache, shared pool) through self-tuning algorithms.

• SQL Tuning Advisor is a new server tool that eliminates manual tuning of SQL statements as an input and gives advice in the form of precise SQL actions for tuning the SQL along with their expected performance benefit.
• All of the DML statements (INSERT, UPDATE, DELETE, MERGE) now have an error logging clause.

• Conditional Compilation: Enables to selectively include code depending on the values of the conditions evaluated during compilation. Also useful when you want to execute debugging procedures in a development environment, but want to turn off in other instances.

• Relaxation of Line Length and Overall Limits for the DBMS_OUTPUT PL/SQL Package

• Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database.

• Database Change Notification: Receive notification whenever a change occurs in the database on the result set of registered queries. This feature enables any cache or object holding query results to ensure that it contains the very latest data.

• Enhancements in Spatial and Multimedia data management

• New top-level SQL statements have been added to support Automatic Storage Management

• New syntax in ALTER SYSTEM that lets you flushes the buffer cache of the system global area (SGA)

• New syntax in ALTER TABLE that lets you manually compact the table segment, adjust the high water mark, and free the recuperated space

• New category of collection functions lets you manipulate nested tables and varrays like SET,CARDINALITY,POWERMULTISET

• New set of aggregate functions to support statistical analysis of data

• Added hierarchical pseudo columns CONNECT_BY_ISLEAF and CONNECT_BY_ISCYCLE for better usage

• LOB column can be used in UPDATE clause when creating an update DML trigger

• New locale-independent format elements (in particular for Number and Date Formats) have been added to the tables

• New Conditions for nested tables like IS A SET, IS ANY, IS EMPTY, IS PRESENT, MEMBER

• FORALL statement can handle associative arrays and nested tables with deleted elements

• Oracle can issue warnings when you compile subprograms that produce ambiguous results or use inefficient constructs. PLSQL_WARNINGS initialization parameter is controlling this.

• Supports Unicode 4.0

• DBMS_FILE_TRANSFER new API to make binary copies of files on the local server or to transfer files between the local server and remote servers

• MERGE statement enhancement to select rows from one or more sources for update or insertion into a table or view

• Introduces the new IEEE floating-point types BINARY_FLOAT and BINARY_DOUBLE

• UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE)

• UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.

Refer Oracle 10g Database Documents to learn more and to find examles.

Oracle 9i Database New Features for Developers

• New SQL enhancements to bring Oracle in line with the ANSI/ISO SQL

• Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.

• Declarative primary key, unique key and foreign key constraints can now be defined against views

• Multitable Inserts concept can be used single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables.

• Allows the renaming of table columns and constraints

• Insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. Also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.

• Create collections that are indexed by VARCHAR2 values

• UTL_FILE contains several new functions that useful to perform general file-management operations from PL/SQL

• PL/SQL supports the complete range of syntax for SQL statements, such as INSERT, UPDATE, DELETE, and so on. If received errors for valid SQL syntax in PL/SQL programs before, those statements should now work.

• The new data type TIMESTAMP records time values including fractional seconds. New data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE allow adjusting date and timing values to account for time zone differences.

• Perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the EXECUTE IMMEDIATE statement)

• MERGE Statement: This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.

• SELECT can have multiple groupings in the GROUP BY clause, for selective analysis across multiple dimensions.

• Enhancements in Oracle Text formerly interMedia Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database.

• Includes Oracle XML DB, which is a set of built-in high-performance storage and retrieval technologies geared toward XML

• Supports JDK 1.3

• Full support for ANSI-style CASE statements and expressions

• Custom aggregate functions can be defined for working with complex data

• Oracle Ultra Search is able both to search the contents of a database to find documents, newspaper articles, and other information stored inside a database and to search the contents of static HTML pages.

• Oracle Spatial Enhancements: Location capabilities in Oracle9i and Oracle Spatial have been greatly enhanced. Content stored in Oracle9i can now be associated with related location criteria and services. New support for online mapping, yellow pages, driving directions, traffic, and geocoding services allow online content to be merged with database content.

Refer Oracle 9i Database Documents to learn more and to find examles.