Read an Excerpt
Chapter 1: Introducing Oracle and V13
Why would we want to combine Oracle and VB? Oracle is the relational database of preference in the corporate world. It is deployed in more businesses than any other RDB. Oracle works on every computing platform available to us - from the PC to the Mainframe and across every shade of Unix. This means that Oracle scales better. Scaling, as you know, may be done in two ways: horizontally, deploying more and more servers of the same kind, or vertically, deploying a bigger and bigger server. When you can deploy many of the biggest you obviously scale best. Oracle is also a forerunner of the in-memory databases and has been memory centered since its beginning. The popularity of Oracle as a purveyor of relational database engines is constantly growing. In these days of e-commerce, businesses must have highly reliable and highly scalable databases and they are turning to Oracle.VB is the rapid application tool of choice and like Oracle enjoys a plurality of preference. This means that there will be more magazine articles, books and thirdparty tools available, making it easier to master the technology and solve problems. By now the number of VB programmers has surpassed the number of COBOL programmers. Ever since the advent of VB 3.0 and ODBC in 1993 it became easier and easier to deploy data-centric applications using VB as the front end. Lately, because we can create (somewhat) multithreaded. COM objects using VB, we are starting to see VB as a viable server-side programming tool. The advent of ASP in 1996 and the ability program it with VB Script gives a great boost to VB's popularity. It is this capability for producing flexiblesophisticated software that makes VB so ideal as a front end to Oracle.
We're going to learn how to use these two technologies together. Each chapter contains real examples. You can repeat them on your computer, or you can take my word for it. I hope you opt for the more active approach. In fact, when you finish reading this chapter the active way, you will have already produced a simple yet useful VB and Oracle program.
I've just finished an unscientific survey of the New York City job market. One popular online job site has posted 1575 request for jobs involving Oracle. During the very same week they have also posted 1126 jobs for Sybase, 835 jobs for SQL Server, 470 for DB2, and significantly lesser numbers to the rest of the field. The respective number of requests also involving VB (in other words, Oracle and VB, Sybase and VB, etc.) was 175, 93, 198, and 32. There is a higher demand for VB and SQL Server than for VB and Oracle, but the gap is narrowing. Again, why VB and Oracle? That's where the jobs are!
Previous Visual Basic support for Oracle has been less than ideal. However, this situation has been continuously improving as new versions of VB are released.
- Until Visual Basic 3 there was no support for remote databases. With this version, access to Oracle was provided via DAO. This didn't (and still doesn't) support many Oracle-specific features.
- With version 4, RDO was introduced, which was the first ODBC method to support some of the necessary features, but things were still not perfect.
- Now we have VB 6 and ADO, which provides greater support for Oraclespecific syntax than any other product except for 0040 (Oracle Corporation's own data-access method, based on DAO).
- In addition to supporting ODBC data sources, VB6 also comes with a native OLE DB provider for Oracle; and database design tools that work well against an Oracle database.
With these advances and the continuing improvements in 0040, the outlook has never been brighter for VB and Oracle!
The History of Oracle
Larry Ellison and others formed Oracle Corporation in 1977 as a small start-up company called Relational Software Incorporated (RSI) to develop the world's first relational database - Oracle.
Oracle originally came out with a prototype, Oracle 1.0, with the first actual release being version 2. Version 3 saw the company's name change to Oracle Corporation. Surprisingly, it was not until version 5 that client/server architecture was supported. With Oracle8 there have been further architectural developments with the introduction of the Network Computing Architecture (NCA) concept, discussed later in the chapter.
So that we provide useful information on developing Visual Basic and Oracle solutions we are going to only talk about examples from Oracle 7.3 to the latest version Oracle 8. We have chosen 7.3 because that was when integration of Oracle and Visual Basic 4.0 started.
Following are some of the enhancements that came with version 73:
- Major performance gains in the processing of SQL operations, such as "antijoins" including the NOT IN condition, and the evaluation of the OR condition.
- Enhanced the use of views in applications development by allowing unambiguous INSERT, UPDATE, and DELETE operations on views involving multi-table joins, providing additional flexibility.
- Index rebuilds can now execute faster, because the existing index is used, rather than the base table itself. Since the index is much smaller then the base table, executing time is substantially reduced.
- Support for partition views. A partition view allows a table, usually a core application table, to be broken into multiple partitions ("sub-tables") and then presented to the application as one table by using the UNION ALL view. Since the partitions are independent of each other, application developers can query just the data they want.
The Oracle Database Architecture
Oracle is a DataBase Management System (DBMS) that supports the largest number of databases found in today's development arena. Where most DBMS meet their limitations, Oracle does just fine maintaining potentially hundreds of gigabytes of information. In addition to supporting large amounts of data Oracle can support large numbers of concurrent users.
Oracle has both physical and logical structures within its architecture. These structures follow the relational model; hence Oracle is a Relational DataBase Management System (RDBMS). A relational database is made of tables of data, in which individual rows can quickly be identified and accessed. Tables may be related to each other - hence the term relational - by having one or more columns that they share. Here is a disturbing example of such a relationship. The IRS and the Census bureau both use the Social Security Number (SSN) to identify you. The two databases share this column, and could thus be connected. Luckily this is illegal, yet this illegality does not lessen my worries.
Physical and Logical Structures
The physical structure of the database constitutes the operational system files that store the information about the database and its data. Within Oracle the physical structure consists of three types of system files: one or more data files, two or more redo log files; and one or more control files...