Oracle 10g Lab Simple

DBMS Lab Oracle 10g

Introduction Database A database can be defined as a collection of coherent, meaningful data.

RDBMS A Data Base Management System or DBMS consists of a collection of interrelated data and a set of programs to access those data. A Relational Database Management System is a program that lets you create, update and administrator a relational database. The primary rule for RDBMS is that the Data should be stored in the form of tables. Most of the RDBMS’s use the Structured Query Language to access the database. Different products of RDBMS are: ORACLE SQL Server DB2 UDB MySQL Sybase Teradata

Oracle Corporation. Microsoft Corporation. IBM MySQL Sybase NCR

REVIEWED By Jerrin at 8:58:32 PM, 23/6/2013

We are using ORACLE from Oracle Corp. in this Lab.

PROMOTIONAL COPY ORACLE

In June 1970, Dr. E.F. Codd published a paper entitled A Relational Model of Data for Large Shared Data Banks. This relational model, sponsored by IBM, then came to be accepted as the definitive model for RDBMS. The language developed by IBM to manipulate the data stored within Codd’s model was originally called Structured English Query Language (SEQUEL) with word English later being dropped in favor Structured Query Language (SQL). In 1979, Oracle Corp. released the first commercially available implementation of SQL.

SQL (Structured Query Language) This is a common language through which we can interact with the database SQL is classified mainly into following categories. 1. DDL (Data Definition Language) CREATE

To create objects in the database.

ALTER

Alters the structure of the database.

DROP

Delete objects from the database.

TRUNCATE

Remove all records from a table, including all spaces allocated for the records are removed.

COMMENT

Add comments to the data dictionary.

1|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

GRANT

Gives user’s access privileges to database.

REVOKE

Withdraw access privileges given with GRANT command.

2. DML (Data Manipulation Language) INSERT

Insert data into a table.

UPDATE

Updates existing data within a table.

DELETE

Deletes all records from a table, the space for the records remain.

CALL

Call a PL/SQL or Java subprogram.

3. TCL (Transaction Control Language) or DCL (Data Control Language) COMMIT

Save work done.

SAVEPOINT

Identify a point in a transaction to which you can later roll back.

ROLLBACK

Restore database to original since the last COMMIT

SET TRANSACTION

Change transaction options like what rollback segment to use.

GRANT/REVOKE

Grant or take back permissions to or from the oracle users.

PROMOTIONAL COPY 4. DQL (Data Query Language statement) SELECT

Retrieve data from a database.

Oracle Data Types Data Type Description CHAR(size) To store character strings values of fixed length. (Max. 255 characters). VARCHAR(size)/ Store variable length alphanumeric data. It’s a more flexible form of the CHAR type. VARCHAR2(size) (Max. 4000 characters). Oracle compares VARCHAR values using non-padded comparison semantics i.e. the inserted values will not be padded with spaces. It also represents data of type String, yet stores this data in variable length format. DATE To represent date and time. The standard format is DD-MON-YY. Date Time stores date in the 24-hour format. NUMBER(P,S) To store numbers (fixed or floating point). Numbers of virtually any magnitude may be stored up to 38 digits of precision. The precision (P), determines the maximum length of the data, whereas the scale (S), determines the number of places to the right of the decimal. LONG Store variable length character strings containing up to 2 GB. Can be used to store arrays of binary data in ASCII format. Only one LONG can be used per table. RAW/ To store binary data, such as digitized picture or image. LONG RAW


DBMS Lab Oracle 10g

Constraints Constraint restricts the values that the table can store. We can declare integrity constraints at the table level or column level. There are 5 constraints: 1. Not Null If a column in a table is specified as Not Null, then it’s not possible to insert a null in such a column. It can be implemented with create and alter commands. 2. Unique Key It doesn’t allow duplicate values in a column. 3. Check Used to restrict the values before inserting into a table. 4. Primary Key The key column with which we can identify the entire Table is called as a primary key column. A PK is a combination of Unique and Not Null constraint; it will not allow null and duplicate values. A table can have only one primary key. A primary key can be declared on two or more columns as a Composite Primary Key. 5. Foreign Key

PROMOTIONAL COPY Columns defined as foreign keys refer the Primary Key of other tables. The Foreign Key ‘points’ to a primary key of another table, guaranteeing that you can’t enter data into a table unless the referenced table has the data already which enforces the REFERENTIAL INTEGRITY.

Data Definition Language Syntax CREATE CREATE TABLE ( () [], (), . (), );


DBMS Lab Oracle 10g

ALTER Adding New Columns: ALTER TABLE
ADD( (), (),…);

Dropping a column from a table: ALTER TABLE
DROP COLUMN ;

Modifying Existing Columns: ALTER TABLE
MODIFY ( ());

RENAME RENAME
TO ;

TRUNCATE TRUNCATE TABLE
;

PROMOTIONAL COPY DROP

DROP TABLE

;

Data Manipulation Language Syntax INSERT INSERT INTO

(,) VALUES(,);

DELETE Removal of all rows: DELETE FROM
;

Removal of specific row(s): DELETE FROM

WHERE ;


DBMS Lab Oracle 10g

UPDATE Updating all rows: UPDATE
SET = , = ;

Updating Records conditionally: UPDATE
SET = , = WHERE ;

Data Query Language Syntax SELECT

PROMOTIONAL COPY

Figure: Syntax of Select.


DBMS Lab Oracle 10g

Transaction Control Language Syntax COMMIT COMMIT;

SAVE POINT SAVEPOINT ;

ROLLBACK ROLLBACK TO ;

Aggregate Functions AVG

Returns an average value of ‘n’, ignoring null values in a column

MIN

Return a minimum values of expression

COUNT(exp)

Return the number of rows where expression is not null

COUNT(*)

Returns the no. of rows in the table including duplicate and those with nulls

MAX

Returns the maximum values of expression

SUM

Return the sum of the values.

PROMOTIONAL COPY Oracle Operator Precedence

Operator

Operation

+, -

Identity, Negation

*, /

Multiplication, Division

+, -, ||

Addition, Subtraction, Concatenation

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN

Comparison

NOT

Exponentiation, Logical Negation

AND

Conjunction

OR

Disjunction


DBMS Lab Oracle 10g

Sub Queries A sub query is a query within a query. The results of the sub query are used by the DBMS to determine the results of the higher-level query that contains the sub query. In the simplest forms of a sub query, the sub query appears within the WHERE or HAVING clause of another SQL statement. Sub queries provide an efficient, natural way to handle query requests that are themselves expressed in terms of the results of other queries.

Pattern Matching The comparison operator usually compared one value exactly to one another value. This precision may not always we decide or necessary. For this purpose oracle provides like predicate. It allows comparison of one string value with another string value which is not identical. This is achieved by use of wild card character. The wild card characters available are modulus (%) which allows to match any strings of any length and (“_”) which allows to match a single character. Example:

Select * from emp where ename like”a%”; Select * from emp where ename like”_a%”;

Oracle 10g Oracle Database 10g is revolutionary. It is Oracle’s largest introduction of new functionality ever, beginning with Release 1 of Oracle Database 10g in January 2004 and now Release 2 in 2005. It is Oracle’s most innovative release, leading the database industry into entirely new ground in clustering, automation, high availability, and more. And, it is having the largest impact on computing of any database software release. Oracle Database 10g fundamentally changes the way data centers look and operate

PROMOTIONAL COPY Oracle Database 10g transforms data centers from collections of separate, monolithic systems configured to handle individual applications to a smaller number of consolidated, shared pools of server and storage resources called infrastructure grids. Oracle’s unique scale-out architecture enables applications of all types to dynamically provision additional servers and storage resources as needed to meet their changing processing demands. It delivers dramatic cost savings and equally dramatic advancements in quality of service. Cost savings come from multiple sources. Oracle reduces hardware costs significantly by enabling even the largest systems to be built out of small, low-cost servers and modular storage arrays. New automation capabilities in Oracle Database 10g dramatically decrease the cost of database administration. Also, Oracle full stack solutions eliminate the need for costly 3rd party management tools and utilities. The advancements in quality of service are across the board. Particularly significant is the automation of database administration functions that raise usability to a revolutionary new level. Not only are DBAs vastly more productive, they are empowered to deliver new levels of consistently optimized performance, high availability, and more. Oracle Database 10g further increases Oracle’s lead in virtually every area of database capability. This includes high availability, performance, business intelligence, data movement, security, application development, and more.


DBMS Lab Oracle 10g

Oracle 10g Installation and Configuration Follow these steps to install and configure Oracle 10g Enterprise Edition.  Run the Installation Setup (Setup.exe).  Select Advanced Installation from the Welcome window.

PROMOTIONAL COPY  The File Locations window opens.

Change the Destination Name to: OraHome10

Change the Destination Path to c:\oracle\product\10.1.0\OraHome10  Click the Next button.


DBMS Lab Oracle 10g

 Select Personal Edition (804 MB) from the Select Installation Type windows that appears.  Click Next

PROMOTIONAL COPY  The Select Database Configuration window opens.  Select General Purpose and click Next.


DBMS Lab Oracle 10g

 The Specify Database Configuration Options window opens. Enter: Global Database Name: DB1 SID: DB1  Select Create database with samples schemas and click Next.

PROMOTIONAL COPY  The Select Database Management Option window opens; simply click Next.  The Specify Database File Storage Option window opens. Keep the default or select a different directory for your database files and click Next.

10 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a

DBMS Lab Oracle 10g

 The Specify Backup and Recovery Options opens. Keep Default Settings and click Next.  Then, the Specify Database Schema Passwords window opens. Select Use the same password for all the accounts. In the Enter Password and Confirm Password fields type your password.  Click Next.

PROMOTIONAL COPY  The Summary window opens; click Install.


DBMS Lab Oracle 10g

 The Install opens. The time to install depends upon the speed and memory of your computer and may take more than 45 minutes. Don’t be alarmed if the installer seems to be stuck at a certain percentage during the installation. You can also ignore additional windows that open during installation.

PROMOTIONAL COPY  After some time, End of Installation window will appear. Click Exit and confirm exit. You have successfully installed Oracle 10g.


DBMS Lab Oracle 10g

 Now, we’ve installed Oracle 10g. Then we can configure the database as our wish by going to the Database Home Page. We can log in with the SYSTEM account, with username ‘system’ and password that we set during installation.

 For Example, to unlock the HR user, Select Manage Users.

PROMOTIONAL COPY  Click HR user, and provide a password, select Unlocked from Account status and click Alter User. We can also set storage settings, add users, create tables etc from the Home Page.


DBMS Lab Oracle 10g

Table Design using Foreign Key and Normalization Normalization Normalization is a process that helps analysis or database designers to design table structures for an application. The focus of normalization is to attempt to reduce redundant table data to the very minimum. Through the normalization process, the collection of data in a single table is replaced, by the same data being distributed over multiple tables with a specific relationship being setup between the tables. By this process RDBMS schema designers try their best to reduce table data to the very minimum. Normalization is carried out for the following reasons:     

To structure the data between tables so that data maintenance is simplified. To allow data retrieval at optimal speed. To simplify data maintenance through updates, inserts and deletes. To reduce the need to restructure tables as new application requirements arise. To improve the quality of design for an application by rationalization of table data.

Normalization is a technique that:  Decomposes data into two-dimensional tables.  Eliminates any relationships in which table data does fully depend upon the primary key of a record.  Eliminates any relationship that contains transitive dependencies.

PROMOTIONAL COPY First Normal Form

When a table is decomposed into two-dimensional tables with all repeating groups of data eliminated, the table data is said to be in its first normal form. The repetitive portion of data belonging to the record is termed as repeating groups. Example: Table(Structure): EmpProj Field Key Type Project number -Project name -Employee number -1-n Employee name -1-n Rate category -1-n Hourly rate -1-n Hint: 1-n indicates that there are many occurrence of this field.


DBMS Lab Oracle 10g

Data help in the above table structure: Project number P001 P001 P001 P002 P002

Project name Using MySQL On Linux Using MySL On Linux Using MySQL On Linux Using Star Office On Linux Using Star Office On Linux

Employee number E001 E002 E006 E001 E007

Employee name Sharanam Shah Vaishali Shah Hansel Colaco Sharanam Shah Chhaya Bankar

Rate category A B C A B

Hourly rate 7000 6500 4500 7000 4000

In the above data there are a few problems:  The Project name in the second record is misspelled. This can be solved by removing duplicates. Do this using normalization.  Data is repeated and thus occupies more space. A table is in First Normal Form if:  There are no repeating groups.  All the key attributes are defined.  All attributes are depend on Primary Key. So far there are no keys, and there are repeating groups. So remove the repeating groups, and define the primary key. To convert to First Normal Form:

PROMOTIONAL COPY The unnormalized data i n the first table is the entire table.  A key that will uniquely identify each record should be assigned to the table. This key has to be unique because it should be capable of identifying any specific row from the table for extracting information for use. This key is called the table’s Primary Key. 

Field Project number Project name Employee number Employee name Rate category Hourly rate

Key Primary Key -Primary Key ----

This table is in First Normal Form.

Second Normal Form A table is said to be in its second normal form when each record in the table is in the first normal form and each column in the record is fully dependent on its primary key. It includes no partial dependencies (where an attribute is dependent on only a part of a primary key). Steps to convert a table to its Second Normal Form:  Find and remove fields that are related to the only part of the key.  Group the removed items in another table.  Assign the new table with the key i.e. part of a whole composite key. In the above example:  

Project name is only dependent on Project number. Employee name, Rate category and Hourly rate are dependent only on Employee number.


DBMS Lab Oracle 10g

To convert, remove and place these fields in a separate table, with the key being that part of the original key they are depend on. This leads to the following 3 tables: Table: EmpProj Field Project number Employee number

Table: Emp Key Primary Key Primary Key

Table: Proj

Field Employee number Employee name Rate category Hourly rate

Key Primary Key ----

Field Key Project number Primary Key Project name -nd Table is now in 2 normal form.

Third Normal Form Table data is said to be in third normal form when all transitive dependencies (where a nonkey attribute is dependent on another non-key attribute) are removed from this data. A general case of transitive dependencies is as follows:

PROMOTIONAL COPY A, B, C are three columns in table. If C is related to B,

If B is related to A, Then C is indirectly related to A This is when transitive dependency exists. To convert such data to its 3rd normal form, remove this transitive dependency by splitting each relation in two separate relations. This means that data in columns A, B, C must be placed in three separate tables, which are linked using a Foreign Key. In the above example:   

Employee table is the only one with more than one non-key attribute. Employee name is not dependent on either Rate category or Hourly rate. Hourly rate is dependent on Rate category.

To convert the table into the 3rd normal form, remove and place these fields in a separate table, with the attribute it was dependent on as key, as follows:


DBMS Lab Oracle 10g

This leads to the following 4 tables: Table: EmpProj Field Project number Employee number

Key Primary Key Primary Key

Table: Emp Field Employee number Employee name Rate category

Key Primary Key ---

Table: Rate Field Rate category Hourly rate

Key Primary Key --

Table: Proj Field Project number Project name

Key Primary Key --

PROMOTIONAL COPY These tables are all now in their 3rd normal form, and ready to be implemented. There are other normal forms such as Boyce-Codd Normal Form, Fourth, Fifth Normal forms, but these are rarely used for business applications. In most cases, tables are in their 3rd normal form are already conform to these type of tables formats anyway.


DBMS Lab Oracle 10g

Working with SQL Table Creation and Alteration 

Create two tables using primary key and connect them using foreign key.



Populate the tables using Insert statement and display them using Select.

PROMOTIONAL COPY


DBMS Lab Oracle 10g



Alter the table using ALTER command.

Adding new column:

Modifying a column:

Defining Integrity constraints:

PROMOTIONAL COPY The structures of the tables are:


DBMS Lab Oracle 10g

Now, we alter it to add Integrity Constraints.

Check Constraints A check constraint is a search condition, like the search condition in a WHERE clause, that produces a true or false value. When a check constraint is specified for a column, the DBMS automatically checks the value of that column each time a new row is inserted or a row is updated to ensure that the search condition is true. If not, the INSERT or UPDATE statement fails.

PROMOTIONAL COPY 

Create a table studdetail with CHECK constraints.


DBMS Lab Oracle 10g

Insertion 

Create and maintain a table ‘customers’. Illustrate insert, delete and update queries.

Insert query, standard form:

Insert query, alternate method:

PROMOTIONAL COPY Now, we insert all values we need.


DBMS Lab Oracle 10g

Data Retrieval We use the SELECT query to retrieve data from a table. 

Retrieve all rows from table ‘customers’.



Select names of customers whose state is ‘Kerala’.



Find customers whose city is ‘cochi’ and date of join is before 5th May 2012.



Find customers from ‘Tamilnadu’ and ‘Karnataka’.

PROMOTIONAL COPY


DBMS Lab Oracle 10g



Find customers from ‘cochi’,’Trivandrum’ and ‘Banglore’.



Find customers who are not from ‘Kerala’ or ‘Tamilnadu’.

Updating


Change balance of customer number ‘c01’ to ‘60000’.



Change name of customer to ‘James’ whose customer number is ‘c05’.


DBMS Lab Oracle 10g

Deletion 

Delete customers whose balance is less than ‘60000’.

Group By 

Use group by clause to the following table.


Select number of employees in each branch.

Having Clause 

Display customer number and number of accounts (both SB and CA) from following table.


DBMS Lab Oracle 10g

Aggregate Functions Table accmaster is used to demonstrate aggregate functions.



Find average balance of customers.



Find minimum balance from the table.



Find maximum balance from the table.


Find the number of accounts by counting accno.



Find the number of accounts by counting rows.



Calculate the total balance.


DBMS Lab Oracle 10g

Numeric Functions 

Absolute



Power



Round



Square Root


Greatest



Least



Modulus


DBMS Lab Oracle 10g



Truncate



Floor



Ceil

String Functions 

LOWER



INITCAP



UPPER



SUBSTR



ASCII



INSTR

PROMOTIONAL COPY


DBMS Lab Oracle 10g



LENGTH



LTRIM



RTRIM



LPAD



RPAD

PROMOTIONAL COPY Conversion Functions Table oldprice is used to demonstrate conversion functions.

 TO_NUMBER


DBMS Lab Oracle 10g

 TO_CHAR

 TO_DATE

Date Functions  Display date after 4 months from current date.

PROMOTIONAL COPY  Display the last date of current month.

 Find date of next Friday from current date.

 Find the number of months between two dates.

 ROUND Date.


DBMS Lab Oracle 10g

SET Operators UNION

INTERSECT

MINUS

UNION

Multiple queries can be put together and their output can be combined using union. The union clause merges the output of two or more queries into a single set of rows and column. Tables used: Employee

Address

Customer

PROMOTIONAL COPY INTERSECT The intersect clause outputs only rows produced by the queries intersected i.e., it will include only those rows that are retrieved common to both the queries. Table used: account


DBMS Lab Oracle 10g

MINUS It outputs the rows produced by the first query after filtering the rows retrieved by second query. Table account (listed above) is used to illustrate minus.

Sorting Table Consider the following table:

 Sort the table on the basis of BRANCH_NO.

PROMOTIONAL COPY  Sort the table on the basis of BRANCH_NO in descending order.

 Sort the table on the basis of BRANCH_NAME.


DBMS Lab Oracle 10g

Sub Query Three tables are used:  movie

 customers

PROMOTIONAL COPY  transaction

The table transaction have reference to both movie and customer tables.

After inserting values:


DBMS Lab Oracle 10g

Using Sub Queries, retrieve data connecting above tables.

 Display the transaction details of the movie ‘FAST AND FURIOUS’.

 Find the name of the customer who has the movie ‘FAST AND FURIOUS’.

 Find the language of the movie which is with ‘V!5HNU’.

PROMOTIONAL COPY  Details of movie which is with ‘R3NJ1TH’.

 Details of customers who has the movie ‘HARRY POTTER’.


DBMS Lab Oracle 10g

Joining Tables The process of forming pairs of rows by matching the contents of related columns is called joining the tables. The resulting table (containing data from both of the original tables) is called a join between the two tables. A join based on an exact match between two columns is more precisely called an equi-join.

Single Join Create two tables as follows and insert values:

After insertion:

PROMOTIONAL COPY Joining both tables:


DBMS Lab Oracle 10g

Self Join In some situations, it is necessary to join a table to itself, as though joining two separate tables. This is referred to as a self-join. In a self-join, two rows from the same table combine to form a result row.

Outer Join The SQL join operation combines information from two tables by forming pairs of related rows from the two tables where the matching columns in each of the tables have the same values. Create two tables:

PROMOTIONAL COPY

Joining:


DBMS Lab Oracle 10g

Data/Transaction Control Language Oracle Transactions A series of one or more SQL statements that are logically related or a series of operations performed on Oracle table data is termed as a Transaction. First, the changes requested are done, to make these changes permanent a COMMIT statement has to be given at the SQL prompt. A ROLLBACK statement given at the SQL prompt can be used to undo a part of or the entire transaction.

Security Management Depending on a user’s status and responsibility, appropriate rights on Oracle’s resources can be assigned to the user by the DBA. The rights that allow the use of some or all of Oracle’s resources on the Server are called Privileges. If a user wishes to access any of the objects belonging to another user, the owner of the object will have to give permissions for such access. This is called Granting of Privileges. Privileges once given can be taken back by the owner of the object. This is called Revoking of Privileges. Working with Control commands:  Connect using the SYSTEM account.

PROMOTIONAL COPY  Create a user temp and grant necessary privileges.

 Grant all privileges on table clientmaster to the user temp.

 Connect using temp account.

 We can access the table clientmaster of SYSTEM account now.


DBMS Lab Oracle 10g

 Revoke the create view privilege from the user temp (By logging in as SYSTEM).

Trying to create a view by logging in as temp:

 Revoke all privileges from the user temp (By logging in as SYSTEM).

 Trying to access the table clientmaster from user temp.

 Saving work till now.

PROMOTIONAL COPY  Creating a save point.

We have 4 records in clientmaster. We create a save point to this point.

We then insert a row to clientmaster.

 We didn’t find it good to insert such a row, or we want to undo anything that has happened after the save point, we rollback to that point.

Now, the newly created row will be deleted.


DBMS Lab Oracle 10g

Views A view is a virtual table in the database whose contents are defined by a query. To the database user, the view appears just like a real table, with a set of named columns and rows of data. But unlike a real table, a view does not exist in the database as a stored set of data values. Instead, the rows and columns of data visible through the view are the query results produced by the query that defines the view. SQL creates the illusion of the view by giving the view a name like a table name and storing the definition of the view in the database.

 Create a view which contains nomno, acctno and name from nominee table.

 Display nomview.

PROMOTIONAL COPY  Work with nomview view as with a table.

 Destroy nomview.


DBMS Lab Oracle 10g

Indexes Indexing a table is an access strategy, i.e., a way to sort and search records in the table. Indexes are essential to improve the speed with which record(s) can be located and retrieved from a table. An index is an ordered list of the contents of a column, (or a group of columns) of a table. When data is inserted in the table, the oracle engine automatically insert the data value in the index. For every data value held in the index, the oracle engine inserts a unique ROWID value; which indicates exactly where the record is stored in the table. The records in the index are stored in the ascending order of the index column. The value of ROWID cannot be set or deleted using the insert or update. The address field of an index is called ROWID. The information in the ROWID column provides the oracle engine about the location of the table and a specific record in the oracle database.

Simple Index An index created on a single column of a table is called a Simple Index.

 Create a simple index on cno of table customer.

PROMOTIONAL COPY  Display ROWID of the records in table customer.

 Create simple index on cname.


DBMS Lab Oracle 10g

Composite Index An index created on more than one column is called a Composite Index.

 Create composite index on table acc.

Reverse Index

PROMOTIONAL COPY Creating a reverse key index, reverses each byte of column being indexed while keeping the column order. Such an arrangement can avoid performance degradation in indexes where modifications to the index are concentrated on a small set of blocks. By reversing the keys of the index, the insertions become distributed all over the index.

 Create a reverse key index on accno of acc table.

Drop Index


DBMS Lab Oracle 10g

Clusters Clustering is an important concept for improving Oracle performance. The concept of a cluster is where member records are stored physically near parent records. For Oracle, clusters can be used to define common, one-to-many access paths, and the member rows can be stored on the same database block as their owner row. Clusters are used to store data from different tables in the same physical data blocks. Each cluster stores the table’s data, as well as maintains the cluster index that is used to sort table data. The Cluster key: The columns within the cluster index are called the cluster key (i.e. the set of columns that the tables in the cluster have in common). After the cluster has been created, the cluster index is created on the cluster key columns. After the cluster key index has been created, data can be entered into the tables stored in the cluster. As rows are inserted the database will store a cluster key and its associated rows in each of the cluster’s blocks. Syntax: CREATE CLUSTER ( [, ]…) [];

The cluster name follows the table naming conventions, also column and data type is the name and data type used as cluster key. The column name may be same as one of the columns of a table or it may be any other valid name.

PROMOTIONAL COPY Example:

CREATE CLUSTER “DBA_BANKSYS”.”BRANCH INFO”(“BRANCH_NO” VARCHAR2(10)); CREATE TABLE “DBA_BANKSYS”.”BRANCH_MSTR”( “BRANCH_NO” VARCHAR2(10) PRIMARY KEY,”NAME” VARCHAR2(25)) CLUSTER BRANCH_INFO(BRANCH_NO); CREATE TABLE “DBA_BANKSYS”.”ADDR_DTLS”( “ADDR_NO” NUMBER(6) PRIMARY KEY,”CODE_NO” VARCHAR2(10), “ADDR_TYPE” VARCHAR2(1),”ADDR1” VARCHAR2(50), “ADDR2” VARCHAR2(25), “CITY” VARCHAR2(25), “STATE” VARCHAR2(25), “PINCODE” VARCHAR2(6)); CLUSTER BRANCH_INFO(BRANCH_NO);

Advantages:  Disk I/O is reduced and access time improves for joins of clustered tables.  In a cluster, a cluster key value is the values of the cluster key columns for a particular row. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.  Since all rows in clustered tables use the same columns as the common primary key, the columns are stored only once for all tables, yielding some storage benefit.


DBMS Lab Oracle 10g

Sequences Oracle provides an object called a Sequence that can generate numeric values. The value generated can have a maximum of 38 digits. A sequence can be defined to:   

Generate numbers in ascending or descending order. Provide intervals between numbers. Caching of sequence numbers in memory to speed up their availability.

A sequence is an independent object and can be used with any table that requires its output.

Creating a Sequence Syntax: CREATE SEQUENCE [INCREMENT BY START WITH MAXVALUE / NOMAXVALUE MINVALUE / NOMINVALUE CYCLE / NOCYCLE

PROMOTIONAL COPY CACHE / NOCACHE ORDER / NOORDER ]



Create a sequence.



Get the next value of the sequence.


DBMS Lab Oracle 10g



Create a table addrsdetail and use sequence to generate its primary keys.

To get the current value of a sequence use ‘CurrVal’.

Altering a Sequence Syntax: ALTER SEQUENCE

PROMOTIONAL COPY [INCREMENT BY MAXVALUE / NOMAXVALUE MINVALUE / NOMINVALUE CYCLE / NOCYCLE CACHE / NOCACHE ORDER / NOORDER]



Alter addrseqe.

Dropping a Sequence


DBMS Lab Oracle 10g

Synonym A synonym is an alias or alternate name for a table, view, sequence, or other schema object. They are used mainly to make it easy for users to access database objects owned by other users. They hide the underlying object's identity and make it harder for a malicious program or user to target the underlying object. Because a synonym is just an alternate name for an object, it requires no storage other than its definition. When an application uses a synonym, the DBMS forwards the request to the synonym's underlying base object. By coding your programs to use synonyms instead of table names, you insulate yourself from any changes in the name, ownership, or table locations. If you frequently refer to a table that has a long name, you might appreciate being able to refer to it with a shorter name without having to rename the table and alter the code referring to that table. Example: We’re trying to access a table in the SYSTEM account from temp account, and we find it lengthy.

PROMOTIONAL COPY We can change the “system.clientmaster” to cmaster to make it short. For that we use a synonym.

Once you have defined a synonym, you can use it just like a table name in SQL queries.

Dropping a Synonym


DBMS Lab Oracle 10g

PL/SQL Programming PL/SQL permits the creation of structured logical blocks of code that describe processes, which have to be applied to data. A single PL/SQL code block consists of a set of SQL statements, clubbed together, and passed to the Oracle engine entirely. This block has to be logically grouped together for the engine to recognize it as a singular code block. A PL/SQL block has a definite structure, which can be divided into sections. The sections of a PL/SQL block are: 1. The Declare Section Code blocks start with a declaration section, in which, memory variables and other Oracle objects can be declared, and if required initialized. Once declared, they can be used in SQL statements for data manipulation. 2. The Begin Section It consists of a set of SQL and PL/SQL statements, which describe processes that have to be applied to table data. Actual data manipulation, retrieval, looping and branching constructs are specified in this section. 3. The Exception Section (Optional) This section deals with handling of errors that arise during execution of the data manipulation statements, which make up the PL/SQL code block. Errors can arise due to syntax, logic and/or validation rule violation.

PROMOTIONAL COPY 4. The End Section

This marks the end of a PL/SQL block.

 Displaying User Messages On The VDU Screen DBMS_OUTPUT.PUT_LINE(‘Message to be displayed’);

To display messages, the SETSERVEROUTPUT should be set to ON. SET SERVEROUTPUT [ON/OFF]

 Control Structure. Conditional Control IF THEN ELSIF THEN ELSE END IF;

Simple Loop LOOP END LOOP;

Iterative While Loop WHILE LOOP END LOOP;

For Loop FOR variable IN start..end LOOP END LOOP;


DBMS Lab Oracle 10g

Factorial up to a Limit 

Write a PL/SQL code block to print Factorial of numbers up to a given limit.

Algorithm: Step1: Start Step2: Declare the variables limit, l, fact and i as number. Step3: Initialize fact:= 1 & l:= 2 Step4: Read value for limit Step5: Repeat for i=1 to limit by 1 Repeat while l<=i f:= f*l l:=l+1 [End of inner loop] [End of outer loop] Step6: Print the result Step7: End

Program:

PROMOTIONAL COPY

Output:


DBMS Lab Oracle 10g

Generate Fibonacci Series 

Write a PL/SQL code block to print Fibonacci series up to a given limit.

Algorithm: Step1: Start Step2: Declare variables f1, f2, f3, n, i as number. Step3: Initialize f1:=0, f2:=1. Step4: Read the limit n. Step5: If n=0, then Print f1 [End of if structure] Step6: If n=1, then Print f2 [End of if structure] Step7: If n>1, then Print f1, f2 Repeat for i:= 3 to n by 1 f3:=f1+f2 Print f3

PROMOTIONAL COPY Set f1:=f2 Set f2:=f3

[End of loop]

[End of if structure] Step8: End


DBMS Lab Oracle 10g

Program:

Output:

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Quadratic Equation 

Write a PL/SQL code block to solve Quadratic Equation.

Algorithm: Step1: Start Step2: Declare the variables a, b, c, d, r1, r2, root as number. Step3: Read the input values for variables a, b & c Step4: Set d:= b*b-4*a*c. Step5: If d=0, Then (a) Set root=-b/2*a. (b) Print the root Else if d>0, then (a) Set r1=(-b+sqrt(d))/(2*a) (b) Set r2=(-b-sqrt(d))/(2*a) (c) Print the results

PROMOTIONAL COPY Else

Print 'Roots are imaginary'.

[End of If structure]

Step6: End


DBMS Lab Oracle 10g

Program:

Output:

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Check Prime or Not 

Write a PL/SQL code block for checking a number to find whether it is Prime or Not.

Algorithm: Step1: Start Step2: Declare variables n, c as number. Step3: Read the number n Step4: Set c:=0 Step5: Repeat for i=2 to n by 1 If n%i=0,Then c:= c+1 [End of if structure] [End of loop] Step6: If c=1, Then Print 'IS PRIME'. Else Print 'NOT PRIME'. [End of if structure] Step7: End.

PROMOTIONAL COPY Program:

Output:


DBMS Lab Oracle 10g

Multiplication Table 

Write a PL/SQL a code block to print Multiplication Table for given number.

Algorithm: Step1: Start Step2: Declare variables n, limit & i as number. Step3: Read the values for n and limit Step4: Set i:= 1,r:=0 Step5: Repeat for i=1 to limit by 1 Print i*n [End of loop] Step6: End

Program:

PROMOTIONAL COPY Output:


DBMS Lab Oracle 10g

Check Balance in Account Table 

Write a PL/SQL code block that accept accno and check if user's balance is less than the minimum balance only then reduce Rs. 100 from it.

Algorithm: Step1: Start Step2: Create a table account with fields acctno and curbal and populate it with records. Step3: Declare acno, temp, minbal as number. Step4: Set minbal:=1000,as constant Step5: Read the acno and assign the curbal field value of that account to variable temp Step6: If temp
PROMOTIONAL COPY [End of if structure]

Step7: End

Program:


DBMS Lab Oracle 10g

Output: Table contents before executing the code block.

Table contents after executing the code block.

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Circle Area 

Calculate the area of circle for radius varying from 2 to 6 and store them into a table (circle).

Algorithm: Step1: Start Step2: Create table circle with fields radius, area. Step3: Define pi as constant having value 3.14 Step4: Declare variables i, ar as number Step5: Repeat for i=2 to 6 by 1 ar:=pi*power(i,2) Insert these values to table circle [End of loop] Step6: End

Program:

PROMOTIONAL COPY

Output:


DBMS Lab Oracle 10g

PL/SQL Functions and Procedures A Procedure or Function is logically grouped set of SQL and PL/SQL statements that perform a specific task. A Stored Procedure or function is a named PL/SQL code block that has been compiled and stored in one of the Oracle Engine’s system tables.

Creating a Stored Procedure: CREATE OR REPLACE PROCEDURE ( {IN,OUT,IN OUT} , …) {IS, AS} declarations; declarations; BEGIN ; EXCEPTION ; END;

PROMOTIONAL COPY Creating a Function

CREATE OR REPLACE FUNCTION ( IN ,…) RETURN (IS, AS) declarations; declarations; BEGIN ; EXCEPTION ; END;


DBMS Lab Oracle 10g

Add two numbers using Function 

Create and call a PL/SQL Function to add two numbers.

Algorithm: Step1: Start Step2: (a) Create a function addno() accepting 2 parameters a and b (b) Declare a variable result inside the function definition (c) Set result:= a+b (d) Return the result Step3: Define the main code declaring 2 variables a, b as number. Step4: Read the values for a and b Step5: Call the function addno(),by passing a and b as parameters Step6: Print the result Step7: End

Program:

PROMOTIONAL COPY

Output:


DBMS Lab Oracle 10g

Factorial using Function 

Use Function to find factorial of any number by calling it.

Algorithm: Step1: Start Step2:(a) Create a function named fact() with parameter (b) Declare variables f, i, l as number (c) Set f:=1,l:=2 (d) Repeat for i=1 to a by 1 Repeat while l<=i f:=f*l l:=l+1 [End of inner loop] [End of outer loop] (e) Return f Step3: Define the main code, declaring a variable n. Step4: Read the value for n Step5: Call the function fact() passing n as parameter Step6: Print the result Step7: End

Program:

PROMOTIONAL COPY

Output:


DBMS Lab Oracle 10g

Prime or Not Function 

Create a function to check whether a number is Prime or Not.

Algorithm: Step1: Start Step2: (a) Create a function chkprime with a parameter n. (b) Declare a variable c and set c:=0 (c) Repeat for i=2 to n by 1 If n % i =0, then c:=c+1 [End of if structure] [End of loop] (d) If c=1, then return 1 Else

PROMOTIONAL COPY return 0

[End of if structure]

Step3: Define the main code, declaring a variable n Step4: Read the value for n Step5: Call the function chkprime() Step6: If return value is 1 Print ‘IS PRIME’ Else Print ‘NOT PRIME’ Step7: End


DBMS Lab Oracle 10g

Program:



DBMS Lab Oracle 10g

Fibonacci Series using Procedure 

Create a procedure to generate Fibonacci Series.

Algorithm: Step1: Start Step2:

(a) Create a procedure fib with parameter n (b) Declare variables f1, f2, f3, i as number. (c) Initialize f1:=0, f2:=1. (d) If n=0, then Print f1 [End of if structure] (e) If n=1, then Print f2 [End of if structure] (f) If n>1, then Print f1, f2

PROMOTIONAL COPY Repeat for i:= 3 to n by 1 f3:=f1+f2 Print f3

Set f1:=f2 Set f2:=f3 [End of loop] [End of if structure] [End of procedure] Step3: Define the main code declaring a variable x Step4: Read the value for x Step5: Call the procedure. Step6: End


DBMS Lab Oracle 10g

Program:



DBMS Lab Oracle 10g

Quadratic Equation using Procedure 

Create a procedure to solve a Quadratic Equation.

Algorithm: Step1: Start Step2:

(a) Create a procedure quad with parameters a, b, c (b) Declare variables d, r1, r2, root as number (c) Set d:=b*b-4*a*c. (d) If d=0, then Set root=-b/2*a. Print the root Else if d>0, then Set r1=(-b+sqrt(d))/(2*a) Set r2=(-b-sqrt(d))/(2*a) Print the results

PROMOTIONAL COPY Else

Print 'Roots are imaginary'.

[End of If structure] [End of procedure] Step3: Define the main code having variables a, b & c Step4: Read the values for a, b & c Step5: Call the procedure Step6: End


DBMS Lab Oracle 10g

Program:



DBMS Lab Oracle 10g

Employee details from Table 

Create a procedure to access emp table and display its contents. Procedure should accept accno.

Algorithm: Step1: Start Step2: Create a procedure empdetails (a) Declare variables id, name, sal, depno (b) Select the field values of emp with eno=no and assign to id, name, sal, depno. (c) Display the details. [End of Procedure]. Step3: Define the main code declaring a variable empno Step4: Read the value for empno Step5: Call the procedure empdetails with empno as parameter Step6: End

Program:

PROMOTIONAL COPY

Output:


DBMS Lab Oracle 10g

Triggers The Oracle engine allows the definition of procedures that are implicitly executed, when an insert, update or delete is issued against a table. These procedures are called Database Triggers. Trigger has 3 basic parts: A triggering event or statement, a trigger restriction, and a trigger action. Triggering Event or Statement: It is a SQL statement that causes a trigger to be fired. It can be INSERT, UPDATE or DELETE statement for a specific table. Trigger Restriction: A trigger restriction specifies a Boolean (Logical) expression that must be TRUE for the trigger to fire. Trigger Action: A trigger action is the PL/SQL code to be executed when a triggering statement is encountered and any trigger restriction evaluates to TRUE.

Types of Triggers: Row Triggers Statement Triggers Before Triggers After Triggers

A row trigger is fired each time a row in the table is affected by the triggering statement. A statement trigger is fired once on behalf of the triggering statement, independent of the no. of rows the triggering statement affects. Executes the trigger action before the triggering statement. Executes the trigger action after the triggering statement is executed.

PROMOTIONAL COPY Syntax:

CREATE OR REPLACE TRIGGER {BEFORE, AFTER}

{DELETE, INSERT, UPDATE [OF Column, ..]} ON
[REFERENCING {OLD AS old, NEW AS new}] [FOR EACH ROW [WHEN Condition]] DECLARE ; ; BEGIN ; EXCEPTION ; END;


DBMS Lab Oracle 10g



Create a trigger auditclient to set operation to manage audit1 table when an update is occurred.

We use contents of table clientmaster to copy to audit1 table.

Program:

PROMOTIONAL COPY We fire an update query to clientmaster.

Output:


DBMS Lab Oracle 10g

ODBC Connectivity To use the Oracle database with any Front End Application Programs, we use ODBC (Open Database Connection). Using ODBC, we can connect we can connect to oracle tables and can be used in Programming Languages like ASP.NET, VB, Java etc. The following steps illustrate how to set an ODBC connection with Oracle 10g and use it with Visual Basic 6.0. Environment: Database

: Oracle 10g Express Edition

Operating System

: Microsoft Windows XP (32 bit) or Microsoft Windows 7 (32 bit only).

Follow these steps to configure a Data Source Name (DSN):  Open Data Sources in Control Panel. (Control Panel  Administrative Tools  Data Sources (ODBC)). In Windows 7, go to Control Panel; change the view to ‘Large Icons’ and select Administrative Tools.

PROMOTIONAL COPY  Select the System DSN tab from the Data Sources window.

 Click Add button.


DBMS Lab Oracle 10g

 The Create New Data Source appears. Select ‘Oracle in XE’ and click Finish.

 The Oracle ODBC Driver Configuration Appears. Provide following information: Data Source Name : payroll (Anything you wish, but this is used later to connect). TNS Service Name : XE User ID : system (User ID of the account you want to connect). And then click Test Connection.

PROMOTIONAL COPY

 The connection window appears; type in the Password of your account (Usually system). And click OK. If everything goes well, you’ll get a Connection Successful message. Click OK. 


DBMS Lab Oracle 10g

 After the configuration is complete, we can find the DSN we created listed.

Now, we have configured DSN. We can now use this in any Programming Language which supports Database Programming.

Database Programming with Visual Basic (and Oracle) Follow these steps to make database application with VB as Front end and Oracle as Back end:

PROMOTIONAL COPY  Open Visual Basic 6.0, start a new Standard EXE application.  We use Microsoft ADO to work with database. So, Enable ‘Microsoft ActiveX Data Objects 2.8 Library’ Project References (Project  References).


DBMS Lab Oracle 10g

 Then, design the form with necessary controls and objects.

 Declare Command, Connection and Record Set variables.  To make the connection, type following code in Form Load() event:

con.Open "payroll", "system", "system"  Here, payroll is the Data Source Name that we’ve configured; system and system are Username and Password of the database user which has the required table.

cmd.ActiveConnection = con

PROMOTIONAL COPY rs.CursorLocation = adUseClient

cmd.CommandText = "select * from paytab"

 Here, paytab is the actual table that we are accessing.

rs.Open cmd, , adOpenStatic, adLockBatchOptimistic  After the above code will be executed, the first row (complete) in the table paytab will be available in rs variable. We can get value of each column by rs(“Column Name”).

 Further operations such as INSERT, DELETE, SEARCH etc. can be done by executing SQL queries with Command Variable through CommandText property.


DBMS Lab Oracle 10g

Payroll Information System 

Create a Payroll Information application in Visual Basic with Oracle as Back End.

Table used: payroll

First, set up a System DSN as mentioned before with DSN name as ‘pay’. Then, create a Standard EXE application in VB and design the form with following controls and necessary labels. Controls Used: Controls

Name

Properties

Text

PROMOTIONAL COPY Textboxes

Command Buttons

enotxt enametxt dojtxt bsaltxt hratxt datxt tatxt grsaltxt cmdcalc cmdnew cmdsave cmdfirst cmdprev cmdnxt cmdlast

Name

Calculate New Save << < > >>

Caption


DBMS Lab Oracle 10g

Code: Dim con As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset _________________________________________________________ Private Sub Form_Load() con.Open "pay", "system", "system" cmd.ActiveConnection = con rs.CursorLocation = adUseClient cmd.CommandText = "select * from payroll" rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub _________________________________________________________ Sub Disp() If (rs.BOF = True) Then

PROMOTIONAL COPY MsgBox ("Sorry, No Records found in Database!") rs.MoveFirst

ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If enotxt.Text = rs("eno") enametxt.Text = rs("ename") bsaltxt.Text = rs("basic_sal") hratxt.Text = rs("hra") datxt.Text = rs("da") tatxt.Text = rs("ta") grsaltxt.Text = rs("grsal") dojtxt.Text = rs("doj") End Sub _________________________________________________________


DBMS Lab Oracle 10g

Private Sub cmdnew_Click() enotxt.Text = "" enametxt.Text = "" bsaltxt.Text = "" hratxt.Text = "" datxt.Text = "" tatxt.Text = "" grsaltxt.Text = "" dojtxt.Text = "" End Sub _________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into payroll(eno,ename,basic_sal,hra,da,ta,grsal,doj) values(" & enotxt.Text & ",'" & enametxt.Text & "'," & bsaltxt.Text & "," & hratxt.Text & "," & datxt.Text & "," & tatxt.Text & "," & grsaltxt.Text & ",'" & dojtxt.Text & "')"

PROMOTIONAL COPY cmd.Execute

MsgBox ("One Row Inserted!")

cmd.CommandText = "select * from payroll" rs.Requery cmdfirst_Click End Sub

_________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub _________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious Call Disp End Sub _________________________________________________________


DBMS Lab Oracle 10g

Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub _________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub _________________________________________________________ Private Sub cmdcalc_Click() Dim h As Integer Dim d As Integer Dim basic As Integer h = hratxt.Text d = datxt.Text

PROMOTIONAL COPY basic = bsaltxt.Text

If ((h <= 100) And (d <= 100)) Then

grsaltxt.Text = basic + (basic * (h / 100)) + (basic * (d / 100)) + tatxt.Text Else MsgBox ("HRA and/or DA Exceeds the limit!") End If End Sub _________________________________________________________


DBMS Lab Oracle 10g

OUTPUT

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Student Information System 

Create a Student Information System in VB with Oracle as Back End.

DSN Table used

: stud : studtab

Controls

Textboxes

Name rnotxt nametxt dobtxt m1txt m2txt m3txt tottxt pertxt percent grdtxt

Properties

Text

PROMOTIONAL COPY Combo Box

Command Buttons

% Name

List

Name

Caption Calculate Clear Save << < > >> Search

cbo cmdcalc cmdclear cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdsrch

Code: Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim cmd2 As New ADODB.Command


DBMS Lab Oracle 10g

Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset ___________________________________________________________________ Private Sub cmdclear_Click() rnotxt.Text = "" nametxt.Text = "" dobtxt.Text = "" cbo.ListIndex = 0 'To set default value to the first list item m1txt.Text = "" m2txt.Text = "" m3txt.Text = "" tottxt.Text = "" pertxt.Text = "" grdtxt.Text = "" End Sub ___________________________________________________________________

PROMOTIONAL COPY Private Sub Form_Load()

cnn.Open "stud", "system", "system" cmd.ActiveConnection = cnn

cmd.CommandText = "select * from studtab" rs.CursorLocation = adUseServer rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, No Records found in Database!") rs.MoveFirst ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If


DBMS Lab Oracle 10g

rnotxt.Text = rs("rollno") nametxt.Text = rs("name") dobtxt.Text = rs("dob") cbo.Text = rs("course") m1txt.Text = rs("m1") m2txt.Text = rs("m2") m3txt.Text = rs("m3") tottxt.Text = rs("tot") pertxt.Text = rs("percent") grdtxt.Text = rs("grade") End Sub ___________________________________________________________________ Private Sub cmdcalc_Click() Dim m1 As Integer Dim m2 As Integer Dim m3 As Integer

PROMOTIONAL COPY Dim tot As Integer Dim p As Integer Dim g As String If (IsNumeric((m1txt.Text) And (m2txt.Text) And (m3txt.Text)) = True) And (m1txt.Text <= 100) And (m2txt.Text <= 100) And (m3txt.Text <= 100) Then m1 = m1txt.Text m2 = m2txt.Text m3 = m3txt.Text tot = m1 + m2 + m3 p = tot / 3 If (p >= 80) Then g = "A" ElseIf (p >= 70) Then g = "B" ElseIf (p >= 60) Then g = "C" ElseIf (p >= 50) Then


DBMS Lab Oracle 10g

g = "D" Else g = "E" End If tottxt.Text = tot pertxt.Text = p grdtxt.Text = g Else MsgBox ("Enter Valid Marks") End If End Sub ___________________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into studtab(rollno,name,dob,course,m1,m2,m3,tot,percent,grade) values(" & rnotxt.Text & ",'" & nametxt.Text & "','" & dobtxt.Text & "','" & cbo.Text & "'," & m1txt.Text & "," & m2txt.Text & "," & m3txt.Text & "," & tottxt.Text & "," & pertxt.Text & ",'" & grdtxt.Text & "')"

PROMOTIONAL COPY cmd.Execute

MsgBox ("One Row Inserted!")

cmd.CommandText = "select * from studtab" rs.Requery cmdfirst_Click End Sub ___________________________________________________________________ Private Sub tottxt_GotFocus() cmdcalc_Click End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub Private Sub cmdprev_Click()


DBMS Lab Oracle 10g

rs.MovePrevious Call Disp End Sub Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub ___________________________________________________________________ Private Sub cmdsrch_Click() cmd2.ActiveConnection = cnn cmd2.CommandText = "select * from studtab where rollno=" & InputBox("Enter Roll Number to search!", Search) & "" rss.Open cmd2, , adOpenStatic, adLockBatchOptimistic

PROMOTIONAL COPY If (rss.EOF = True) Then

MsgBox "No Records!"

Else rnotxt.Text = rss("rollno") nametxt.Text = rss("name") dobtxt.Text = rss("dob") cbo.Text = rss("course") m1txt.Text = rss("m1") m2txt.Text = rss("m2") m3txt.Text = rss("m3") tottxt.Text = rss("tot") pertxt.Text = rss("percent") grdtxt.Text = rss("grade") End If End Sub ___________________________________________________________________


DBMS Lab Oracle 10g

OUTPUT

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Banking Application 

Create a Banking Application to store and retrieve customer details.

DSN Table used

: bankdsn : bank

Controls Textboxes

acnotxt cnametxt addrstxt

Name

Properties

Text

(Multiline = True) (Max Length = 50)

PROMOTIONAL COPY placetxt baltxt

Option Button

Command Buttons

sbopt caopt cmddel cmdclr cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdsrch

Name

Name

Caption SB CA Caption Delete Clear Save First Previous Next Last Search

Code: Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim cmd1 As New ADODB.Command Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset


DBMS Lab Oracle 10g

___________________________________________________________________ Private Sub Form_Load() cnn.Open "bankdsn", "system", "j" cmd.ActiveConnection = cnn cmd.CommandText = "select * from bank" rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, BOF!") rs.MoveFirst ElseIf (rs.EOF = True) Then

PROMOTIONAL COPY MsgBox ("Sorry, EOF!") rs.MoveLast

End If acnotxt.Text = rs("acno") If (rs("actype") = "SB") Then sbopt.Value = True Else caopt.Value = True End If cnametxt.Text = rs("cname") addrstxt.Text = rs("addrs") placetxt.Text = rs("place") baltxt.Text = rs("balance") End Sub ___________________________________________________________________


DBMS Lab Oracle 10g

Private Sub cmdclr_Click() acnotxt.Text = "" sbopt.Value = False caopt.Value = False cnametxt.Text = "" addrstxt.Text = "" placetxt.Text = "" baltxt.Text = "" End Sub ___________________________________________________________________ Private Sub cmdsave_Click() Dim atype As String If (sbopt.Value = False) And (caopt.Value = False) Then MsgBox ("Please Select an Account Type!") Else

PROMOTIONAL COPY If (sbopt.Value = True) Then

cmd.CommandText = "insert into bank(acno,actype,cname,addrs,place,balance) values( " & acnotxt.Text & ", '" & sbopt.Caption & "' ,'" & cnametxt.Text & "','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text & ")" Else cmd.CommandText = "insert into bank(acno,actype,cname,addrs,place,balance) values( " & acnotxt.Text & ", '" & caopt.Caption & "' ,'" & cnametxt.Text & "','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text & ")" End If cmd.Execute MsgBox ("One Row Inserted!") cmd.CommandText = "select * from bank" rs.Requery cmdfirst_Click End If


DBMS Lab Oracle 10g

End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub ___________________________________________________________________ Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub ___________________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious Call Disp

PROMOTIONAL COPY End Sub

___________________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub ___________________________________________________________________ Private Sub cmdsrch_Click() cmd1.ActiveConnection = cnn cmd1.CommandText = "select * from bank where acno=" & InputBox("Enter Account Number to search!", Search) & "" rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic If (rss.EOF = True) Then MsgBox "No Records!" Else acnotxt.Text = rss("acno")


DBMS Lab Oracle 10g

If (rs("actype") = "SB") Then sbopt.Value = True Else caopt.Value = True End If cnametxt.Text = rss("cname") addrstxt.Text = rss("addrs") placetxt.Text = rss("place") baltxt.Text = rss("balance") End If rss.Close End Sub ___________________________________________________________________ Private Sub cmddel_Click() Dim d As Integer

PROMOTIONAL COPY d = InputBox("Enter Account Number to Delete") cmd1.ActiveConnection = cnn

cmd1.CommandText = "select * from bank where acno=" & d & "" rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic If rss.EOF = True Then MsgBox "No Records" Else cmd1.CommandText = "delete from bank where acno=" & d & "" cmd1.Execute MsgBox "Deleted" End If rss.Close rs.Requery End Sub ___________________________________________________________________


DBMS Lab Oracle 10g

OUTPUT

PROMOTIONAL COPY


DBMS Lab Oracle 10g

Library Information System  DSN Table

Create a Library Information application which can manage, details of books in a library, in VB with Oracle as Back End. : library : libtab Controls Textboxes

Command Buttons

bidtxt titletxt authtxt pubtxt pricetxt cmdclr cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdbidsrch cmdtitsrch

Name

Properties

Name

Text

Caption Clear Save << < > >> By Book ID By Title

PROMOTIONAL COPY Code: Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset Dim cmds As New ADODB.Command ___________________________________________________________________ Private Sub Form_Load() cnn.Open "library", "system", "j" cmd.ActiveConnection = cnn cmd.CommandText = "select * from libtab" rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockBatchOptimistic


DBMS Lab Oracle 10g

Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, BOF!") rs.MoveFirst ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If bidtxt.Text = rs("bid") titletxt.Text = rs("title") authtxt.Text = rs("author") pubtxt.Text = rs("pub")

PROMOTIONAL COPY pricetxt.Text = rs("price") End Sub

___________________________________________________________________ Private Sub cmdclr_Click() bidtxt.Text = "" titletxt.Text = "" authtxt.Text = "" pubtxt.Text = "" pricetxt.Text = "" End Sub ___________________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into libtab(bid,title,author,pub,price) values(" & bidtxt.Text & ",'" & titletxt.Text & "','" & authtxt.Text & "','" & pubtxt.Text & "'," & pricetxt.Text & ")" cmd.Execute


DBMS Lab Oracle 10g

MsgBox ("Data Saved") cmd.CommandText = "select * from libtab" rs.Requery cmdclr_Click End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub ___________________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub

PROMOTIONAL COPY ___________________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious Call Disp End Sub ___________________________________________________________________ Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub ___________________________________________________________________ Private Sub cmdbidsrch_Click() cmds.ActiveConnection = cnn cmds.CommandText = "select * from libtab where bid=" & InputBox("Enter Book ID Number to search!", Search) & "" rss.Open cmds, , adOpenStatic, adLockBatchOptimistic


DBMS Lab Oracle 10g

If (rss.EOF = True) Then MsgBox "No Records!" Else bidtxt.Text = rss("bid") titletxt.Text = rss("title") authtxt.Text = rss("author") pubtxt.Text = rss("pub") pricetxt.Text = rss("price") End If rss.Close End Sub ___________________________________________________________________ Private Sub cmdtitsrch_Click() cmds.ActiveConnection = cnn cmds.CommandText = "select * from libtab where title='" & InputBox("Enter Book Title to search!", Search) & "'"

PROMOTIONAL COPY rss.Open cmds, , adOpenStatic, adLockBatchOptimistic If (rss.EOF = True) Then MsgBox "No Records!" Else bidtxt.Text = rss("bid") titletxt.Text = rss("title") authtxt.Text = rss("author") pubtxt.Text = rss("pub") pricetxt.Text = rss("price") End If rss.Close End Sub ___________________________________________________________________


DBMS Lab Oracle 10g

OUTPUT

PROMOTIONAL COPY


Report "Oracle 10g Lab Simple"

Your name

Email

Reason

Description

Copyright © 2024 IDOC.TIPS. All rights reserved.
About Us | Privacy Policy | Terms of Service | Copyright | Contact Us | Cookie Policy

Sign In

Email

Password

Remember me Forgot password?

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close

Oracle 10g Lab Simple

Recommend Documents