If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Pavi has produced an article for Oracle DBAs interested in finding ways of partitioning the tables as well showing a new form of partitioning using Oracle 9i.Business applications are growing at a faster rate than salaries, and so is the data supporting them, especially e-business applications where the data growth has been around 20-30 percent or more annually. Oracle came up with the idea of Partitioning the Tables. Yes ! The name sounds right “Partitioning”, the literal meaning of Partitioning is “The Act of dividing something into parts” and in our case that ‘something’ is nothing but…? 

You guessed it right! Data.

Partitioning in Oracle

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a “partition”. From an “Application Development” perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

This type of partitioning creates partitions based on the ” Range of Column” values. Each partition is defined by a ” Partition Bound” (non inclusive ) that basically limits the scope of partition. Most commonly used values for ” Range Partition” is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter. So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

The above example creates four hash partitions based on the zip codes from where the orders were placed.

3. List Partitioning ( Only with 9i)

The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.

5. Composite Range-List Partitioning ( Only with 9i)

This is no different than the ordinary index created on a non-partitioned table. The index structure is not partitioned.

Conclusion

Partitioning greatly enhances the performance, manageability and availability of most databases. Partitioning can be applied to any databases and software shops using such a great option, have greatly improved the user satisfaction for there business applications.

I hope this overview of partitioning will provide some answers to What? When? Who? Where ? Why and How? of partitioning in Oracle. 

For detailed syntax and documentation refer to Oracle documentation. This article does not claim to provide a full understanding or the partitioning feature in oracle. Oracle has been modifying this feature with new releases. So lot more to come. Watch out.

—

by Pavi Agrawal 

• 0 Comments

For those who use databases but are not familar to the Oracle 9i database, perhaps you should read about the datatypes used in Oracle 9i and how they can be used to support object orientation.It is assumed that you have read my previous Oracle article based on creating an Oracle database in the latest Oracle database 9i. Before we can create a table, one should sit down and take the time to get to know the datatypes available for Oracle.

Also, upon reading articles relating to the Oracle database, you should have come across the term Abstract datatypes. This article will discuss abstract datatypes in depth so that creating a table, which is designed to include abstract datatypes, will inevitably become more understandable. These abstract datatypes, which I like to call user-defined types, are datatypes that behave like objects.

Oracle Datatypes

These Oracle datatypes are as follows:

Character Strings

• CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).

• NCHAR (size) – A fixed-sized field of characters, where the character set is determined by its definition. So, the maximum size is 2000 bytes per row or 2000 characters. This handles multibyte character sets.

• VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters.

• NVARCHAR2 (size) – A variable-sized field of characters, where the character set is determined by its definition. The maximum size is 4000 bytes per row or 4000 characters. This handles multibyte character sets.

Note: The VARCHAR2 datatype is the successor of VARCHAR. So it is recommended that you use VARCHAR2 as a variable-sized array of characters.

• LONG – A variable-sized field of characters. The maximum size of this field is 2GB.

Number

• NUMBER (precision, scale) – A variable-sized number, where the precision is between 1 and 38 and size is between -84 and 127. A NUMBER datatype with only one parameter is NUMBER (precision), where the parameter specifies the precision of the number. A NUMBER datatype with no parameters is set to its maximum size.

Date and Time

• DATE – A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT.

• TIMESTAMP (precision) – A variable-sized value ranging from 7 to 11 bytes, that is used to represent a date/time value. It includes both date and time. The precision parameter determines how many numbers are in the fractional part of SECOND field. The precision of the SECOND field within the TIMESTAMP value may have a value ranging from 0 to 9 with a default precision of 6.

• TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’.

• TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.

Intervals

• INTERVAL DAY (day_precision) TO SECOND (second_precision) – A fixed-sized 11 byte value that represents a period of time. It includes days, hours, minutes and seconds.

• INTERVAL YEAR (year_precision) TO MONTH - A fixed-sized 5 byte value that represents a period of time. It includes years and months.

Binaries

• RAW (size) – A variable-sized field of raw binary data. The maximum size for this datatype is 2000 bytes.

• LONG RAW - A variable-sized field of raw binary data. The maximum size for this datatype is 2 GB.

• BLOB – The Binary Large Object is a field that holds unstructured binary data. The maximum size for this datatype is 4 GB.

• CLOB – The Character Large Object is a field that holds single byte character data. The maximum size for this datatype is 4 GB.

• NCLOB – The National Character Large Object is a field that holds either single byte of multibyte character data dependent on the national character set. The maximum size for this datatype is 4 GB.

• BFILE – An external binary file. The maximum size for this file is 4 GB. The size is also limited by the operating system.

Rows

• ROWID – A datatype that contains binary data that is used to identify a row.

Each ROWID is:

• • 6 bytes for normal indexes on non-partitioned tables, local indexes on partitioned tables and row pointers for chained/migrated rows.
  • 10 bytes for global indexes on partitioned tables.

• UROWID – The Universal ROWID is the datatype used to store both logical and physical ROWID values as well as foreign tables accessed through a gateway.

Alternatives for ANSI Standard Datatypes

Instead of using ANSI standard datatypes, you can use Oracle defined datatypes. View the table below to see the Oracle datatype alternative for ANSI standard datatypes.

Abstract Datatypes

In Oracle, one may create there own datatypes. Abstract datatypes allow Oracle to hold a range of datatypes. So, an abstract datatypes can have many parts to it. To do this one needs to create the datatype as an object. This object is made up of one or more datatypes.

Example of an Abstract Datatype

Let’s say that we want a datatype to split up a person’s address. The abstract datatype may be,

CREATE OR REPLACE TYPE persons_address AS OBJECT
(
v_streetNumber                      NUMBER,
v_streetName                         VARCHAR2(30),
v_citySuburb                          VARCHAR2(30),
v_state                                  VARCHAR2(4),
v_postCode                            NUMBER
);

When we create a table that references this abstract datatype the values must be inserted as

pe

rsons_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345)

You should now know what datatypes exist in Oracle 9i. You should also understand how one could use this datatypes to create abstract datatypes and hence support object orientation.

Now you understand how datatypes work in Oracle, you should be ready to create Oracle tables.

—

by Ben Shepherd 

• 0 Comments

Every wondered how the Oracle database is structured? To learn more about the structure and how one would create an Oracle 9i database using the latest features read this article.When creating a database, the main concept is to know how the database is structured in Oracle 9i. You should be aware that ever since the release of Oracle 8i, the Oracle database support object orientated structures. This means that Oracle supports abstract data types and methods.

If you are new to Oracle, then perhaps you are unclear on how data is stored and what files are included internally and externally. With this article you will be clear about how to create a database using Oracle 9i’s latest features.

How Data is Stored 

Data in Oracle is stored in tables and accessed data via a relational model. This means that one may use the tables of data items inside the database in a manner such that the tables relate to one another with the use of primary and foreign keys.

Tables that contain a foreign key are often translated as being a lookup table. Each table should have a primary key, so that each row can be uniquely identified. The primary key is used to connect with the foreign key in another table to form a relationship.

Oracle also supports object-orientated structures. This allows the database to include abstract data types and methods. Due to this object orientation property, objects may related to other objects and that object may exist inside other objects.

Files are used to store data in Oracle. Oracle 9i removes the risk of having orphan file. That is, like the name suggests the file doesn’t have a parent, which means that the datafile was not removed when it’s tablespace is removed. To prevent this from ever happening to your database again, Oracle has introduced “Oracle Managed Files”.

Oracle Managed files, abbreviated as OMF, uses a brand new system of storing files. OMF uses file system directories as opposed to filenames for files that are associated with the tablespace.

This means that when a tablespace is created, the files are given a unique system-generated name, using the locations that have been assigned to them along with two new initialization parameters. So when the tablespace is removed, the files will be removed with it.

Another new feature in Oracle 9i is the System Managed Undo, abbreviated as SMO. The SMO was created to make the creation of the database simpler for the DBA and to reduce the problem relating to misused rollback segments. The DBA now creates a tablespace as an UNDO type to allow the Oracle database to dynamically and automatically manage the number of undoes, along with the size of the undo segments within the tablespace.

A database is partitioned using tablespaces. The tablespace named SYSTEM is the databases default tablespace. These tablespaces allows for the grouping of elements within the system that transact with the database.

A tablespace contains files. These files are called datafiles, which are used to physically store data for the database. Notice this property suggests that datafiles are internal and external structure types, since they are parented by a tablespace (internal property) and are physical storage files (external). The datafile may be resized after it has been created.

Rules about Tablespaces and Datafiles

• A tablespace is parented by the database. So a tablespace cannot be a part of another database.
• A datafile is parented by the tablespace. So a datafile cannot be part of another database.
• A datafile may not be removed from a tablespace.

Now that we know how data is stored using Oracle, there are other special physical files that are associated with data storage and will assist in data recovery. The files that are created when the database in created and they are as follows,

• Redo log files, which will monitor transactions in a chronological manner. These files are known as online redo log files. The reason why this is an important file to have associated with the database is evident when the database malfunctions.

According to the Oracle 9i DBA Handbook, a database should consist of 3 or more online redo files. To archive old online redo files before they are inevitably over written, you may set the database to run in ARCHIVELOG mode. This allows the DBA to look further back into the history of transactions with the database.

• Control files, which holds the location of all the files the database will require. Multiple copies are made and stored on different disks to back up this operation-critical file. The CONTROL_FILES parameter specifies the names of the database control files. An instance must be shut down prior to an addition of a new control file.
• Temp files, which acts as a temporary storage for a result set. If the result exceeds the memory available in the RAM’s buffer, then the result set will be stored into the temporary data file. Make sure that the size of the file is large enough to hold large sorting operations.

The other files associated with an Oracle 9i database are called parameter files. There are two types of parameter files. These are,

• Static parameter files – This is what the init.ora file is. This are common known as a PFILE and should be based on the init.ora file and renamed as init{SID}.ora, where SID is the system identifier.
• Server parameter files – This is a new feature in Oracle 9i named the SPFILE. These parameter files are written in binary and should not be altered manually. The files should be named as spfile{SID}.ora, where SID is again the system identifier. The Oracle server always maintains these files.

To create the SPFILE we do the following,

CREATE SPFILE = ‘<directory>\spfile{SID}.ora’
FROM PFILE = ‘init{SID}.ora’

There are two types of parameter that are located in these parameter files. These are,

• Implicit parameter, which are parameters with no value. Oracle therefore assumes that the value for the parameter is the Oracle default values.
• Explicit parameter, which are parameter that have a value assigned to it.

Now we know how the parameter files work. Well, to access data inside the database, Oracle uses a set of background processes that are shared between users. The DBA Handbook states that an instance is “ a set of memory structures and background processes that access a set of database files.” Every instance has a large memory structure known as the System Global Area, abbreviated as SGA. The SGA will be explained in another article.

But where do the parameter fit in?

Well the parameters are used to set the size and composition of an instance and are the stored in one of the two specific parameter files as stated previously. The parameter file is read during the startup process. So, to access a database one must start an instance, which may call either the PFILE or the SPFILE. If there exist an SPFILE, then the STARTUP command will read that parameter file. If the SPFILE doesn’t exist, the default SPFILE is read. If that doesn’t exist than the PFILE is read. If you don’t wish for the server to read the PFILE, then simply override the default SPFILE with the PFILE.

STARTUP PFILE = ‘<directory>.init{SID}.ora’

Now you have a good understanding of architecture of an Oracle database, let us create one.

Create a Database 

There are two ways that you can create a database in Oracle 9i. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 9i’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory.

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

Set ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Use SQL*Plus under / as sysdba and set the database to nomount mode.

So, if we wish to override the default spfile to start the new instance in nomount mode then type,

Startup nomount pfile = ‘<directory>\init{SID}.ora’

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command. Here is the code,

CREATE DATABASE ShepIT
Maxinstances   1
Maxloghistory  1
Maxlogfiles  10
Maxlogmembers 15
Maxdatafiles  100
Datafile ‘<directory>\datafile_01.dbf’
size 300M reuse autoextend on next 15M maxsize unlimited
character set WE8MSWIN1252
national character set AL16UFT16
logfile
group 1 (‘<directory>\redo_01.log’) size 75M
group 2 (‘<directory>\redo_02.log’) size 75M
group 3 (‘<directory>\redo_03.log’) size 75M
default temporary tablespace TEMP
tempfile ‘<directory>\temp_01.dbf’
extent management local uniform size 1M
undo tablespace UNDO_TS datafile ‘<directory>\datafile_01.dbf’
size 125M reuse autoextend on next 15M maxsize unlimited;

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@<directory>\catalog.sql
@<directory>\catproc.sql
@<directory>\catexp.sql

Ok, the database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.

—

by Ben Shepherd

• 0 Comments

At the end of Part One, we were at the point where MySQL was ready to be installed. In part two of this series, we will go through some quick setup steps, create a user with some privileges, and load data - using several methods - into a MySQL database. Once the setup of the MySQL database is complete, we will be ready to start using Migration Workbench.

Starting and Using MySQL

MySQL (the company) provides user documentation in the Docs directory of where you installed MySQL (the product). Assuming you used the default installation target (on Windows) of C:\mysql, the HTML-based documentation, using a table of contents similar to what you see when looking at Oracle’s list of books, is located at C:\mysql\Docs. The MySQL Tutorial book published by MySQL Press is a condensed version of the documentation.
After installing MySQL, create a configuration file (my.ini, located in C:\Windows) using the following parameters (or just cut and paste what is shown below):

[mysqld]

# turn on binary logging and slow query logging

log-bin

log-slow-queries
# InnoDB config

# This is the basic config as suggested in the book

innodb_data_file_path = ibdata1:10M:autoextend

# Set buffer pool size to

# 50-80% of computer’s memory

set-variable = innodb_buffer_pool_size=70M

set-variable = innodb_additional_mem_pool_size=10M

# Set the log file size to about

# 25% of the buffer pool size

set-variable = innodb_log_file_size=20M

set-variable = innodb_log_buffer_size=8M

# Set flush_log_at_trx_commit

# to 0 if you can afford losing

# some last transactions

innodb_flush_log_at_trx_commit=1

Assuming you are in c:\mysql\bin, install MySQL as a service using :

c:\mysql\bin> mysqld-max –install

Once you see that the service has been created, you can start MySQL using the Services control panel or by issuing the net start service_name (net start mysql) command at the command prompt.

To connect as “root” privileged user much like sys and system), at the command prompt enter

c:\mysql\bin> mysql -u root

mysql> set password for root@localhost=password('admin');

delete from user where user='';
delete from db where user='';
flush privileges;

create database omwb;

grant create, delete, index,
insert, select, update
on omwb.*
to tiger identified by 'scott';

select empno||’,'||ename||’,'||job||’,'||mgr||’,’
||to_char(hiredate,’YYYY-MM-DD’)||’,'||sal||’,’
||comm||’,'||deptno “MySQL data load example”
from emp;

use omwb;
insert into msdept values
(10,’ACCOUNTING’,'NEW YORK’),
(20,’RESEARCH’,'DALLAS’),
(30,’SALES’,'CHICAGO’),
(40,’OPERATIONS’,'BOSTON’);

insert into mssalgrade values (1,700,1200);
insert into mssalgrade values (2,1201,1400);
insert into mssalgrade values (3,1401,2000);
insert into mssalgrade values (4,2001,3000);
insert into mssalgrade values (5,3001,9999);

c:\mysql\bin> mysqldump -u root -padmin –opt omwb > omwb_dumpfile.sql

GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE,
ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE,
CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER,
CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE,
DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,
DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE,
UPDATE ANY TABLE TO tiger;

Let me start with a bad news/good news situation. The bad news: as far as job searches are concerned, it is becoming readily apparent that knowing only Oracle is not good enough anymore. Personally, I have taken major hits for not knowing SQL Server and have heard some static about DB2. The good news: ignorance is curable through education, and if you are somewhat adept with Oracle, learning the mechanics, architecture, and administration of other relational database systems is easy (or easier, anyway). You know there are tables, you know about constraints, and you probably have some coding experience (why do SQL Server people seem to think triggers are the end all and be all of database programming?). In addition, of course, you have dealt with the wicked witches of the East and West (backup/recovery, and performance tuning).

In this series, I will cover two of the “other guys” and go into detail about leveraging what you know about Oracle into quickly assimilating, or at the least, becoming conversant with the details of SQL Server and MySQL. Why those two systems, you may be asking. I believe the reasons for covering SQL Server are fairly obvious, but the selection of MySQL may not be readily apparent. If you are looking at this article via a databasejournal.com link, note the “php” in the URL. The PHP scripting language is frequently used with MySQL and many web sites use a PHP/MySQL combination.

MySQL compares quite favorably against Oracle and SQL Server in terms of speed, performance and scalability. One area where MySQL far exceeds Oracle - in a good way - is in pricing. If not free, depending on your use of MySQL, then you are looking at mere hundreds of dollars in licensing fees as opposed to Oracle’s many thousands. The applicability of using databases with web sites is abundantly obvious. How does all of that happen? The “how” of connecting Oracle to a web site via MySQL should be of interest to you. Being able to tie in an Oracle database to a web site via MySQL graduates you from your shrink-wrapped application to deploying your application on the web.

What tools are out there to help you migrate data to (or from) Oracle? The focus of this series is on Oracle’s Migration Workbench (OMWB). The product description of OMWB is shown below.

The Oracle Migration Workbench (Migration Workbench) is a tool that simplifies the process of migrating data and applications from non-Oracle databases to Oracle. The Migration Workbench allows you to quickly and easily migrate an entire database (data and schema, including triggers and stored procedures) in an integrated, visual environment. The Migration Workbench employs an intuitive user interface and a series of wizards to simplify the migration process. To ensure portability, all components of the Migration Workbench are written in Java.

Before getting into the details of acquiring, installing, and using OMWB, the remainder of this article will help you get acquainted with MySQL. In Part 2, I’ll provide some sample data for a MySQL database that we will use for migrating to an Oracle database. Subsequent parts of this series will cover the same approach for SQL Server.

More about MySQL

If you could look only at the text on Oracle’s education/certification pages and the counterpart pages found at MySQL’s web site and could not see the name of the product, you almost would not be able to tell the sites apart. At http://www.mysql.com/training/, you see links to training, certification, and partners.

Which web site is this from?

XXXXX regularly offers training courses around the world, in-house training courses, and offers certification for developers who want to prove their expertise in using and deploying XXXXX database products.

XXXXX training courses are delivered regularly all over the world by our expert trainers, who work closely with our team of developers to make sure their students learn everything they need to know about using XXXXX software to the fullest.

And this one?

Our training representatives will work with you to customize solutions to help your work group, department, or global enterprise achieve the results you envision.

The first two quotes are from MySQL, and the third one is from Oracle.

With respect to certification, the motivation for being certified is also similar.

Getting certified with XXXXX can bring you the credibility you deserve for your knowledge, skill, and experience on the job as [a certified user] and will provide you with a market recognized credential that can lead to success.

Certified users of computer software often find that their proven credentials help them get farther in their field, giving their personal careers a boost, and bettering their company’s chances of retaining old and getting new customers.

The top quote is from Oracle and the bottom one is from MySQL. Without getting into the merits of certification, spending the time and effort to get certified is worthwhile in and of itself for the simple reason that you are going to learn something (anywhere from a little to a lot) about a product.

Something to consider about becoming proficient in designing and managing database (even if it uses Oracle just a little bit) driven web sites is the potential to work with Oracle even more. Consider this extract from a recent posting on Monster:

…seeking an experienced MySql DBA to work on new projects. This position will involve 50% DBA work with MySql, and 50% split between development, SQL Server, software testing, etc…

And these from Dice:

Seeking strong Data Base Administrator. Will provide support for company’s MySQL & Oracle databases on an IBM/Red Hat platform.

We are currently seeking a Junior Database and Systems Developer and Administrator who will develop and maintain database applications, install, maintain and tune Oracle and MySQL databases and UNIX/Linux systems in a distributed environment.

The point of all of this is to state the case that MySQL is used by many organizations in conjunction with Oracle.

Where to get MySQL

Knowing about and using the features available at Oracle Technology Network (OTN) will give you a leg up on navigating your way through MySQL’s web site. With minimal effort, you can start downloading MySQL from http://dev.mysql.com/downloads. For Windows, the download (of version 4.0, the current general release) is just over 24MB in size. Just like Oracle, MySQL comes with free documentation. Read on before you start downloading MySQL.

If you want a good, get your feet wet type of book, MySQL Tutorial, published by MySQL Press is certainly a good start. Another book, which combines PHP and MySQL and contains an overview of MySQL database administration, is SitePoint’s Build Your Own Database Driven Website Using PHP and MySQL (get the newly released 3^rd edition).

The tutorial book from MySQL Press is very straightforward and will help you install and configure a working MySQL database in short order.

If you are going to download MySQL for use with Oracle Migration Workbench, take note that OMWB supports up to version 3.23 of MySQL. The current version is in the 4.0 series, so you will have to navigate to the archived products section. If you use this link (http://downloads.mysql.com/archives.php?p=mysql-3.23&o=-win), scroll down to the bottom of the page to get the 12.8M 15 Sep 2003 version for Windows (or whichever platform you want to install MySQL on).

Where to Get Oracle Migration Workbench

If you are thinking you already have OMWB because you installed the Enterprise Edition, you are wrong. OMWB is a separate utility you acquire separately from the RDBMS product. Like most every other Oracle product, you can get OMWB from the OTN website (look under Technologies, Utilities, and Drivers heading at http://www.oracle.com/technology/software/index.html, in the middle column about halfway down). I will cover the installation of OMWB in the next article, but if you want to get started on your own, you can take the Quick Tour that comes with this utility.

In Closing

There is a lot of material to cover in this series, but at the end of it, not only will you be familiar with two other widely used and popular database systems, but you will also know how to migrate data from these systems into an Oracle database.

• 1 Comment

In this final part of the series, we will look at some additional resources for HTML DB, which are freely available. The resources include training documentation, blogs and news feeds, a free workspace (hosted by Oracle), and a user forum. Oracle Corporation typically posts a statement of direction on the OTN Web site, and we will take a look at that as well.

Free Training Course

One of the examples used in a previous article came from the Oracle HTML DB 2 Day Developer guide (Part No. B14377-01, March 2005), available here. The “course” covers the following topics.

• How to Create a Tabular Form
• How to Create a Parameterized Report
• How to Create a Drill Down Report
• How to Control Form Layout
• How to Work with Check Boxes
• How to Implement a Web Service
• How to Create a Stacked Bar Chart
• How to Incorporate JavaScript into an Application
• How to Build and Deploy an Issue Tracking Application

The 2 Day Developer course is similar to the 10g 2 Day DBA course. If you know nothing about the Oracle RDBMS, reading the 10g course and expecting to be fully up to speed is not going to happen. With HTML DB, you are a little better off with the Web development aspects, but without knowing anything about Web design (basic HTML, CSS and JavaScript), your development ability is going to be limited. Acquiring several Web design books and bookmarking several “how to” Web sites for JavaScript will make your life much easier. In addition, you get a bonus: chances are pretty good you are going to enjoy Web page design, and it certainly is a break from resetting passwords in SQL*Plus.

JavaScript can do many neat things on a Web page, but it is not very forgiving when compared to HTML. For example, (most) HTML will let a missing “</td>” tag slide. In JavaScript, if you reference an object that does not exist, your page will fail to load or submit properly. There is a difference between an element ID (for a button) value of Submit and SUBMIT. It helps to make JavaScript “code safe” (test for existence of an object before trying to do anything with it). Finally, not all browsers treat HTML and JavaScript the same (thanks a lot, Microsoft). If a user’s browser does not support JavaScript, do not forget to add in an alert or page handling.

Shown below is what you see when trying to sign in at Hotmail when JavaScript has been disabled.

Blogs and Newsfeeds

RSS generally stands for “Really Simple Syndication,” but you may see other definitions. Blogs and newsfeeds are booming in popularity. Getting an RSS reader installed and configured is very easy. Knowing this process helps you in two ways when it comes to HTML DB. The first is that Oracle provides several feeds related to HTML DB news, tips, and features at OTN. The second is that you, in turn, can incorporate RSS into your Web applications.

Installing and adding a few feeds to RSSReader takes less than two minutes.

Open the reader, click on the Add button, paste in your Web site of interest, and you are done. Shown below is an example of the HTML DB headlines.

There are three HTML DB experts who maintain blogs at OTN. The blogs include tips, suggestions, and other useful information. Drop the RSS link into your RSS reader and read away. Interestingly enough, when trying to demo one of the blogs for this article, clicking on a few of the links resulted in the error message shown below. Is someone still using dictionary managed tablespaces? Ouch.

errm:ORA-01654: unable to extend index STUDIO.RSS_LOG_PK by 128 in tablespace FLOW_4217,
 sqlcode:-1654

The RSS links for headlines and blogs can be found near the bottom right of the page at HTML DB.

Requesting a Free Workspace

Oracle Corporation provides free workspaces. There is no guarantee this will always be so, and you are cautioned not to develop or host “real” applications using this service. Two advantages of using this free service are 1) it saves your disk space, and 2) you can send users/developers to this site to learn and practice rapid application development without them cluttering up your environment. Your choice of workspace size is 2 and 5 MB.

The process of requesting a workspace looks just like what you would see in your own installation of HTML DB.

Overall, it is a very simple process. It may take a day or so before your workspace is provisioned. The email from htmldb_us@oracle.com will contain the name of your workspace, your username (which is your email address), a password, and a link to the HTML DB Web site at http://htmldb.oracle.com/pls/otn/htmldb. After logging on, the page looks similar to what we have seen before.

HTML DB User Forums at OTN

The forum here is an excellent place to see the problems (and solutions) other users have experienced, and some of the situations are quite sophisticated. The nature of these questions is indicative of a maturing user base. In other words, if you are just starting out with HTML DB, you have some catching up to do in terms of experience and expertise. Questions are quickly answered and the quality of what is posted by Oracle employees is yet another sign of Oracle’s commitment to this product. The answers are much better than “Thanks for using MetaLink, closing thread” answer. The HTML DB forum is at http://forums.oracle.com/forums/forum.jsp?forum=137.

The Future of HTML DB

According to Oracle, expect a new version (2.0) in the second half of 2005, codenamed Columbus (just like the way Microsoft codenames everything?). The new features in Columbus include the following:

• Building queries using a graphical designer
• Browsing and creating managing database objects
• Viewing data
• Running scripts
• Running ad hoc SQL and PL/SQL
• Editing PL/SQL packages, procedures, and functions.

The notable shift or addition to functionality is being able to interact more directly with database objects and data (as opposed to using only forms). You can be one of the first to find out when Columbus is released by using an RSS feed. Other RSS feeds from Oracle include these sites.

In Closing

HTML DB is a powerful tool for rapid application development. In very little time at all, developers can prototype an application and then make changes that are immediately available to and for users. With more direct object and data access in version 2.0, DBAs and users may choose to totally skip iSQL*Plus. Being able to connect to a database (or application) via your standard Web browser is a huge advantage over performing hundreds of client installs or upgrades found in the traditional client-server architecture environment. My guess is that there will be many significant improvements in HTML DB in the years to come. It is not too late to get on board!

• 0 Comments

In part 4 of the HTML DB series, I will cover the steps of upgrading to version 1.6 and creating a simple Web service. The upgrade/installation steps come straight out of the March 2005 Oracle® HTML DB Installation Guide, Release 1.6. The steps to create a simple Web service are from the Oracle® HTML DB 2 Day Developer document also available on the OTN Web site. In the last article, I mentioned the patch set for version 1.6. The patch set is available via MetaLink, so if you do not have access to Oracle Support Services, obtaining this patch may be a problem.

As a tip, locating the patch set number or file name can be somewhat of a chore when using the search interface at Patches & Updates. If the link for the patch does not work, look at the page source (the HTML code) and see where the hyperlink is supposed to send you. It turns out that the name of the zipped patch file is

aru=7180000&patch_file=
p4173133_10103_GENERIC.zip

so at least now you know the name of the file to look for. The complete URI is:

http://updates.oracle.com/…GENERIC.zip

Pre-installation steps

The steps are few and easy to perform: ensure the shared pool is large enough, and that XML DB and Oracle Text are installed.