Oricle Call Intergace.

Why OCI?

Applications form the most important infrastructural elements of an enterprise. These applications help your customers to enter orders, your employees to service these orders, and your business to be successful. From small tasks to large mission critical functions, enterprises depend on applications connected to the Oracle Database to transact business quickly and efficiently.

For application developers looking to develop the highest performance applications for the Oracle Database, the Oracle Call Interface (OCI) is the only choice. For the capability of accessing the most platforms and the most database features, application developers choose OCI for its stability, features, and outstanding support. OCI provides these developers the flexibility and degree of control unparalleled in any other programming interface for the Oracle Database. Already used in all levels of database connectivity, from the database access layers of tandem application servers, to full-fledged applications, OCI is the recurring choice for working with Oracle. Some reasons include:

Cross-platform functional support – All platforms supported by the Oracle Database have the same functional API support. There is neither source code porting nor loss of functionality when moving to different platforms. Just compile and run your applications on the platform of choice.

Stability – Programs written today will work well into the future, ensuring the functional longevity of your applications. Thus, there is no constant code rewriting to achieve the same functionality.

Greatest control – OCI offers the greatest degree of control in transaction handling and SQL statement processing for the more sophisticated developers

Transaction Management

Applications use OCI especially for the transaction management capabilities. From the simplest queries to complex scheduled transactions and information movement, OCI allows applications to perform under the most intense workload requirements.

• Simple Local Transactions – simple queries and inserts are optimized by OCI to execute on the server quickly while using very few resources.

• Global Distributed Transactions over dblinks – more complex transactions involving multiple databases are multiplexed through dblinks to provide unparalleled performance when dealing with large numbers of transactions that span multiple databases.

• XA transaction management – in heterogeneous environments, Oracle can act as the focal point of transaction coordination between Oracle and many different vendors’ databases and applications, so that your applications are not locked in by any limitations.

Connection Management

Many applications need to manage connections at different granularities, but should not need to reimplement the functionality. OCI provides many features that allow the sharing of many virtual connections to the database without the management hassle:

• Connection Pooling – multiplex many concurrent virtual network connections over a fewer number of physical connections, since not all connections are being used simultaneously in real world environments. This feature limits the number of network connections to your real simultaneous load.

• Session Pooling – multiplex many short transactions over a few sessions, which is good for most web service applications with many servers doing the same types of transactions. This saves time both server resources and network resources, leading to greater scalability.

• Statement Caching – cache commonly used statements for quick results. Useful for most applications that do simple queries, inserts, and updates. These types of applications include high performance information servers such as stock quote services, rental listing services, and standardized information gathering services.

Object Interaction

Oracle Objects allows your applications to be extensible, manageable, and scalable. OCI provides many hooks for your application to access these powerful capabilities.

• Object Type Translator – The Object Type Translator creates the appropriate C, C++, and Java code for converting Oracle Objects in the

database to usable types in native application code. Thus, little maintenance is required for creating the connective layer that interacts with Oracle Objects.

• Navigational Object Interface – Ability to access objects through links in other objects. Your application can interact with objects in the database in the same manner as your own locally created application objects.

• Client-side Object Cache – When accessing objects through the object hierarchy, OCI can cache these objects for speedier access, while maintaining coherency with the database backend. This prefetching capability includes both simple objects and connected graphs of objects.

New Features

OCI is actively developed, with useful new features always added. Here are some of the newest features:

• Instant Client – Now your applications can run independent of an Oracle Home environment, as the power of the OCI library is repackaged in a convenient form

• Native Float and Double Support – Applications can now seamlessly use the standard IEEE754 float and double types native to C without conversion, when interacting with the Oracle Database and the appropriate column types.

• Database Startup and Shutdown – Administrative applications can now startup and shutdown the database through new API calls.

BENEFITS

Developers use OCI not only for the features, but also for the benefits it provides them. Some of the major benefits include:

• Lower Maintenance Costs – your applications can take advantage of commonly required functionalities. Session pooling, statement caching, and scrollable cursors are all features already implemented by OCI for your application’s benefit.

• API Stability – your applications are expected to work as long as you do, so why repeat work when you do not have to? OCI has a very stable API that helps your applications through the changes in your IT infrastructure. Your applications can expand their capabilities as needed, while maintaining the core sets of functionality from release to release without any change.

• Unparalleled Performance – OCI is simply the backbone for the Oracle Database. Since most of the Oracle Database internal code already interacts with OCI, it is a very well tested interface that lives up to the constant scrutiny, just as your applications do. Performance is constantly tweaked in OCI, while maintaining the API stability. Thus, your applications benefit

INTRODUCTION TO SQL

Introduction

SQL is Structured Query Language. It is a language like C or COBOL but with a difference. C and COBOL are procedural languages where as SQL is non-procedural.

A procedural language allows programming constructs like if-then-else and looping logic such with ‘while’ or ‘for’ loops. To retrieve data in procedural language you must navigate, or find a path to the data record you want. Procedural languages are complex, therefore programmers not for end-users.

SQL is an English- like language. Users with little of no experience in data processing can learn SQL’s basic features very quickly. Yet it also provides programmers and data processing professionals, both for communicating application requirements and implementing solutions.

To store and retrieve data, you need commands to :

Controlling database access and other security requirements.

Create or drop table and other data structures – Data Definition Language (DDL) commands.

Query, Update, Insert or Delete from the tables – Data Manipulation Language (DML) commands.

Create user and grant privileges for security purposes – Data Control Language (DCL) commands.

SQL lets you forget about “how to do it” and focus instead on “what is to be done”. For example, with a single command you can update multiple rows in your database, without worrying about their locations, storage formats and access path.

As in other database management systems, you can use SQL statements in other Oracle tools. For example, in Oracle Forms (Oracle’s screen design and application generation tool) you can use SQL statement to manipulate data. So

Understanding SQL will let you use any other Oracle tool without learning additional syntax.

CLIENT/SERVER COMPUTING MODEL

2.7 Introductions

The problems of the LAN have led to the genesis of client/server computing model. Client/server computing (also called distributed application processing and cooperative application processing) delivers the benefits of networking computing model along with shared data access and high performance characteristics of the host based computing model.

A client/server system has three distinct components, each focusing on a specific job a database server, a client application and a network. A server focuses on efficiently managing a resource such as database of information. The server’s primary job is to manage its resource optimally along multiple clients that concurrently request the server for the same resource.

Database servers concentrate on task such as :

Managing a single database of information among many concurrent users.

Controlling database access and other security requirements.

Protecting database information with backup and recovery features.

Centrally enforcing global data integrity rules across all client applications.

A client application is the part of the system that users employ to Interact with data. The client applications in a client/server database system focus on jobs such as

Presenting an interface a user can interact with no accomplish work.

Managing presentation logic such as popup list on a data entry form or bar graphs in a graphical data presentation tool.

Performing application logic, such as calculating fields in a data entry form.

Validating data entry.

Requesting and receiving information from a database server.

Oracle and clientserver :

Oracle is one of many database servers that we can plug into a client server equation. Oracle works to efficiently manage its resource, a database of information, among the multiple clients requesting and sending data in the network.

Features :

Oracle supports all major Operating Systems for both clients and servers, including MS-DOS, NetWare, UnixWare, OS/2 and most of the Unix flavors.

Oracle has client/server features developers can use to minimize network I/O between clients and servers.

Oracle has features that make it easy to administer complicated client/server systems.

WINDOWS 2000

What is an R.D.B.M.S?

Selecting the computing environment today for commercial applications is not a simple act of selecting a single vendor who would provide the hardware, the operating system and a choice of one or two different database management systems. In the selection of a computing environment today, the user's view is increasingly getting a higher value, as wide sections of the organization are getting exposed to the computerized system. The end-user today, besides operating the application developed, is also capable of small-scale customizations to suit the local needs. In other words, in a typical multi-user commercial application scenario, a majority of the users would be using some of the popular features of DBMS. This would mean that selecting a proper DBMS for an organization is indeed a very crucial decision. Subsequently, other features of the DBMS could be identified and selected.

A Relational Database Management System (RDBMS) is required to handle the data processing requirements of the units. Features to look for while selecting a proper RDBMS are categorized under the following heads:

• Basic architecture and general facilities
• Database administration features
• Interoperability
• User interface
• Productivity tools
• Programming facility
• Platform and hardware resource requirement
• Manpower & training
• Pricing policy

Basic Architecture and facilities

The RDBMS should support client server architecture, both on a single host environment and in LAN environment. It is preferred that a single multithreaded server process caters to a user's need, communicating with his / her client process.

The database should provide a mechanism to define transactions & should also provide an efficient transaction-processing environment.

It should support mechanisms by which integrity rules like referential and null integrity on the database can be defined. Manipulation of data in the database should always be in line with the rules defined for the database.

It should support concurrence control & deadlock handling. The lock management should work in the defined granularity & should have the capability to escalate automatically to higher resource levels to ensure to ensure better performance.

It should have an intelligent query optimizer.

It should have a high user base & established product support facilities in the country.

Sufficient & lucid documentation should be available both on-line & in printed media.

It should provide standard data types including 'binary' & user defined data types.

It should support database triggers, stored procedures, remote procedure calls & database mirroring.

Database Administration Features

The RDBMS should:

Support on-line backup & restore facilities

Support automatic recovery ensuring data integrity & consistency till the last completed transaction.

Support proper two phase commit & commit with deferred write in the actual database.

Support check pointing & facility to roll forward to the last completed transaction.

Have consistent error reporting at every stage, & a mechanism for logging such errors

Have on-line performance monitors, the results from which could be used to tune performance by setting / configuring installation parameters

Have comprehensive audit training facility

Support authority allotment mechanism to different users (The levels to which authority can be given should also have data viewing capability. Authority level fixing to the user for executing stored procedure should be available.)

Have capability to provide effective & integrated data dictionary of the database.

Be able to spread database support across disks & file systems

Support products, which can create, shadow copies of databases or database segments (The copies should automatically synchronize with the master database at predefined intervals.)

It should provide a mechanism for exchanging data between different locations

It should provide a mechanism for building a distributed database with dissimilar RDBMS.

It should support independent third party 4GLs or tool sets either in the same host, or in a client server environment.

It should support embedded static, dynamic & extended dynamic SQL conforming to ANSI X3: 135, 1992.

It should be available in diverse operating systems & hardware combinations. It should provide source level portability from one operating environment to another as diverse as DOS based PCs & proprietary OS based mainframes.

It should support data exchange.

User interface

In the client server mode, the tool set in the client environment enhances the usability of a database. The strength of the database management system should not only be based on its own client tool set, but also on the basis of third party tools available with it.

The RDBMS should support various standard windowing software and graphic user interface connectivity in a client server environment.

It should provide assistance & context sensitive help to guide a new user to make adhoc queries or reports.

It should support tools like spreadsheet, business graphics etc.

Productive tools

The RDBMS should possess the following features:

Support of a versatile report writer, which can generate various kinds of flexible report formats.

Ability to generate reports through step by step menu driven assistance

Ability to generate executable code from the report writer

Support of extensive text formatting functions

Support of stored procedures developed using 4GL or high level language interfaces, either from SQL, other 4GLs or report writers

Provision of source level porting of report writer programs from one environment to another.

Support of at least one versatile 4GL which, in addition to common non-procedural constructs, support some of the control handles like loop, exit, etc., & can accommodate stored procedures.

Support of a versatile screen painter with multiple tables, default screens & help window options

Support of integrated CASE tools with application documentation facility is an added advantage

Automatic integrity & creation capability with the design incorporated in CASE tools

Support of multiple kinds of triggers, which can be a stored procedure & fired on the occurrence of an event.