Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 9i Tables and Constraints

By | on February 2, 2005 |
Oracle 9i

In week 6 of our Oracle 9i training course we will be learning about Data types with the differences between different Oracle versions, creating tables, altering tables, dropping tables, Integrity constraints supported by Oracle, Inserting records, Modifying the records and deleteting the records. If you can’t see the screen shots, please save them on to your hard disk.

Table: Tables are basic units of data storage. A table is defined as intersection of rows and columns. Data is stored in rows and columns.

Oracle Data Types:


CHAR: Char data type stores fixed-length character strings.


Max Size: Oracle 7 255 bytes Default and minimum size is 1 byte.
Max Size: Oracle 8 2000 bytes Default and minimum size is 1 byte.
Max Size: Oracle 9 2000 bytes Default and minimum size is 1 byte.


VARCHAR or VARCHAR2: This data type stores variable-length character data. Varchar is a deprecated data type and is a synonym for varchar2.


Max Size: Oracle 7 2000 bytes minimum is 1
Max Size: Oracle 8 4000 bytes minimum is 1
Max Size: Oracle 9 4000 bytes minimum is 1

NUMBER: The NUMBER data type stores fixed and floating point numbers. We can specify Precision

(Total number of digits) and scale (number of digits to the right of decimal point).


Max Size: Oracle 7 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 8 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 9 The precision p can range from 1 to 38.The scales can range from -84 to 127.


DATE: The DATE data type stores dates and time in the table.


Max Size:Oracle 7 from January 1, 4712 BC to December 31, 4712 AD.
Max Size:Oracle 8 from January 1, 4712 BC to December 31, 9999 AD.
Max Size:Oracle 9 from January 1, 4712 BC to December 31, 9999 AD.


LONG: The LONG data type stores variable-length character data (bigger than VARCHAR2) up to two gigabytes length. You can use LONG columns to store long text strings.


Max Size:Oracle 7 2 Gigabytes
Max Size:Oracle 8 2 Gigabytes
Max Size:Oracle 9 2 Gigabytes

RAW: This data type stores binary data. We must specify the size for RAW value.


Max Size:Oracle 7 Maximum size is 255 bytes.
Max Size:Oracle 8 Maximum size is 2000 bytes
Max Size:Oracle 9 Maximum size is 2000 bytes

LONG RAW: Raw binary data of variable length.


Max Size:Oracle 7 2 Gigabytes.
Max Size:Oracle 8 2 Gigabytes.
Max Size:Oracle 9 2 Gigabytes.

LARGE OBJECTS (LOBs): The above data types are called traditional oracle data types. LOBs are added from Oracle 8.0 version. From there Oracle 8.0 is considered as ORDBMS (Object relational database management system.). The traditional oracle database is extended to include object-oriented concepts and structures such as abstract data types, nested tables, varying arrays, object views and references.


LOB datatypes are capable of storing large volumes of data. The LOB datatypes available are BLOB, CLOB, NCLOB, and BFILE.


CLOB ( Character Large Object): This data type is used to store character data.


Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes


BLOB ( Binary Large Object): This data type is used for binary data.


Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes

NLOB (National Character Large Object): This datatype is used to store character data containing Unicode characters.( ASCII character is of 1 byte and UNICODE character is of 2 bytes )


Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes

 

BFILE (Binary File): It is a pointer to external file. The files referenced by BFILE exist in the file system. The database only maintains a pointer to the file. The size of the external file is limited only by the operating system. ORACLE does not maintain concurrency and integrity of the data.

 

Note: A table can use more than one LOB. Earlier to LOBs it was possible to have only one LONG datatype column in a table.

 

CREATING TABLES: A table name can not exceed 30 characters and necessarily should start with a character. Blank spaces and other special characters except an “under score” ( _ ) are not allowed in table names.

 

Each column in the table will be given a unique name. Every column is assigned a data type corresponding to the data to be entered into the column. Column names are also restricted to 30 characters and follow the same rules as table names.

Syntax for creating a Table:

CREATE TABLE

( ,

,

_ _ _

) ;

Example:

 

 

 

DROPPING A TABLE: To drop a table it must already exist in the database. The syntax for DROP TABLE statement is

 

DROP TABLE ;

 

 

CONSTRAINTS: A constraint may be defined as a business rule that data need to satisfy to enter the table. An integrity constraint defines a business rule for a table column. When enabled, the rule will be enforced by oracle. The two basic types of constraints are column constraints and table constraints. The difference between the two constraints is former applies to individual columns and the later one apply to group of columns.

 

Integrity Constraints Supported by Oracle

1) NOT NULL: A column with this constraint will not allow NULL values.
2) PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
3) UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
4) CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.
5) FOREIGN KEY: A foreign key is a combination of columns with values based primary.
It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.

 

 

 

Creating Table Using Constraints:

 

 

Note: In the above screen shot lines after “—-“ are comments. Different ways of defining constraints are given above.

ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.

Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE
|

The following screen shots illustrate how to add a column and how to remove a column.

 

 

ALTER TABLE can be used to add and remove constraints as shown below

Entering records into a table

  1. To enter records into a table we use INSERT command
  2. Using & we can read the values from key board in SQL .

SQL > INSERT INTO STUD (SNO, SNAME)
VALUES (3 , ‘MANOJ’);
1 row created.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
—- ————— ————— ———-
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ

Modifying existing values using UPDATE

UPDATE command is used to update the values of the fields.

Syntax :
UPDATE
SET = value WHERE = values


SQL > UPDATE STUD
2 SET COURSE = ‘J2EE’ WHERE SNO=3;

1 row updated.


SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
—– ———- ————— ————— ——– —
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ J2EE

SQL > UPDATE STUD
2 SET COURSE=’J2EE’ ;

3 rows updated.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE

Note: Without proper condition UPDATE will take effect on more than one record.

Using DELETE command

This command is used to delete rows from a table.

SQL > SELECT * FROM STUD;


SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE

SQL > DELETE FROM STUD
2 WHERE SNO=3;

1 row deleted.

SQL > SELECT * FROM STUD;

SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5

SQL > DELETE FROM STUD;

2 rows deleted.

ROLLBACK, COMMIT AND SAVEPOINT

ROLLBACK, COMMIT and SAVEPONT are called as Transaction Control Commands. A transaction is a sequence of SQL statements that Oracle treats as a single unit. Various changes we make to the table with INSERT, UPDATE, DELETE commands are temporary. To make them permanent we use COMMIT command. To undo the changes we use ROLLBACK.

« « Finding Your First Job – Adjusting to a New Job
XML Unit Testing tools Series 1 » »

Author Description

Avatar

Free Training

RSSSubscribe 391 Followers
  • Popular
  • Recent
  • Oracle 9i Exception Handling

    March 3, 2005 - 0 Comment
  • Oracle 9i PL/SQL Collections

    March 6, 2005 - 0 Comment
  • Oracle 9i Procedures and Functions

    March 13, 2005 - 0 Comment
  • Oracle 9i Database Triggers

    March 28, 2005 - 0 Comment
  • Oracle 9i Packages

    April 7, 2005 - 0 Comment
  • Oracle 9i Utilities

    April 30, 2005 - 0 Comment
  • More Oracle 9i Database Objects

    February 14, 2005 - 0 Comment
  • Introduction to Oracle 9i SQL, PLSQL, and SQL *Plus

    October 24, 2004 - 0 Comment
  • Building PL/SQL Blocks in Oracle 9i

    February 16, 2005 - 0 Comment
  • Oracle 9i Software Installation, SQL, PLSQL and SQL *Plus References

    October 25, 2004 - 0 Comment
  • Oracle 9i Utilities

    April 30, 2005 - 0 Comment
  • Oracle 9i Packages

    April 7, 2005 - 0 Comment
  • Oracle 9i Database Triggers

    March 28, 2005 - 0 Comment
  • Oracle 9i Procedures and Functions

    March 13, 2005 - 0 Comment
  • Oracle 9i PL/SQL Collections

    March 6, 2005 - 0 Comment
  • Oracle 9i Exception Handling

    March 3, 2005 - 0 Comment
  • Download example SQL Scripts used in Oracle 9i Tutorials

    March 3, 2005 - 0 Comment
  • Oracle 9i Cursors

    February 27, 2005 - 0 Comment
  • Oracle 9i PL/SQL Control Structures

    February 26, 2005 - 0 Comment
  • Building PL/SQL Blocks in Oracle 9i

    February 16, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle 9i Utilities
  • Oracle 9i Packages
  • Oracle 9i Database Triggers
  • Oracle 9i Procedures and Functions
  • Oracle 9i PL/SQL Collections

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT