Miscellaneous

  Home arrow Miscellaneous arrow More on Oracle Databases and Datatypes
MISCELLANEOUS

More on Oracle Databases and Datatypes
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 2
    2010-10-20

    Table of Contents:
  • More on Oracle Databases and Datatypes
  • Creating and Maintaining Tables

  •  
     

    SEARCH CODEWALKERS

    More on Oracle Databases and Datatypes


    (Page 1 of 2 )

    In this fourth part of a seven-part series on using and administering Oracle Database XE, you'll learn about ROWID datatypes, and then we'll start discussing how to create and maintain tables. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

    ROWID Datatypes

    ROWIDs are datatypes that store either physical or logical addresses of rows in an Oracle Database XE table. ROWIDs store physical addresses, and UROWIDs store both logical and physical addresses. For the vast majority of applications, using or viewing ROWIDs is not required to achieve the best performance. However, for specialized applications, you can retrieve a table row using a ROWID value with only one I/O operation. A ROWID is guaranteed to be unique across all tables in the database.

    Every table in the database has a pseudo-column namedROWID; it is not part of the table’s structure nor does it take up any space. It merely shows you the physical address of the row’s block in the database, as in this query:

    select rowid, customer_name from customer_comment;

    --------------------------------------------

    ROWID              CUSTOMER_NAME
    ------------------ ----------------------
    AAAEN8AAEAAAADdAAA Suzie Pustina AAAEN8AAEAAAADdAAB Ann Vandross
    --------------------------------------------

    Even though you can create additional physical columns in a table of type ROWID, there is no validation to ensure that the value you place in the column is the address of a valid row in the database.

    ANSI-Supported Datatypes

    To improve compatibility with database applications that use ANSI SQL datatypes, Oracle supports ANSI datatypes such as CHARACTER, NATIONAL CHARACTER, andDECIMALand stores them internally as compatible Oracle datatypes. Table 30-6 shows the ANSI SQL datatype and the equivalent Oracle datatype.

     

     

     

    Table 30-6. ANSI to Oracle Datatype Equivalents

    ANSI SQL Datatype Oracle Datatype
    CHARACTER(n),CHAR(n) CHAR(n)
    CHARACTER VARYING(n),CHAR VARYING(n)

    VARCHAR2(n),NATIONAL CHARACTER(n), NATIONAL CHAR(n),NCHAR(n),NCHAR(n)

    NATIONAL CHARACTER VARYING(n),  NATIONAL CHAR VARYING(n),NCHAR VARYING (n) NVARCHAR2(n)
    NUMERIC(p,s),DECIMAL(p,s) NUMBER(p,s)
    INTEGER,INT,SMALLINT NUMBER(38)
    FLOAT,DOUBLE PRECISION,REAL NUMBER

     

     

     

    More Miscellaneous Articles
    More By Apress Publishing

    blog comments powered by Disqus

    MISCELLANEOUS ARTICLES

    - Attention: Forum and Site Maintenance
    - Oracle Database XE: Indexes and Sequences
    - Modifying Tables in Oracle Database XE
    - Oracle Database XE: Tables and Constraints
    - More on Oracle Databases and Datatypes
    - Oracle Database XE Datatypes: Datetime and L...
    - Oracle Database XE Datatypes: Character and ...
    - From Databases to Datatypes
    - Firefox 3.6.6 Released with Improved Plug-in...
    - Attention Bloggers: WordPress 3.0 Now Releas...
    - Reflection in PHP 5
    - Inheritance and Other Advanced OOP Features
    - Advanced OOP Features
    - Linux from Scratch V.6.6 Review
    - Linux Gaining in Strength

    Developer Shed Affiliates

     



    © 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap