Home arrow Miscellaneous arrow Modifying Tables in Oracle Database XE

Modifying Tables in Oracle Database XE
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 1

    Table of Contents:
  • Modifying Tables in Oracle Database XE
  • Modifying Table Characteristics



    Modifying Tables in Oracle Database XE

    (Page 1 of 2 )

    In this sixth part of a seven-part series on using and administering Oracle Database XE, you'll learn more about creating and modifying tables. This article 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).

    Setting Column Defaults

    You can set default values for columns in your table. Many times a column may have the same value most of the time or for a given condition. This saves data entry effort and reduces errors if the value does not have to be entered for each row. For example, when you enter a customer’s order, the date of the order is usually the same as the date you enter the order. Therefore, you can set the default value for ORDER_DATE to SYSDATE; SYSDATE is the predefined system variable that contains the current date and time. In this example, you change theORDER_DATEcolumn in theCUSTOMER_ORDERtable to default to the time you enter the order:

    alter table customer_order modify (order_date default sysdate);

    We show you how to alter the other characteristics of table columns in the section “Adding, Dropping, and Renaming Table Columns.”

    Creating a Table Using a Query Against Another Table

    In some situations, you may want to create another table that is identical to an existing table. For example, you may want to archive the old orders from the CUSTOMER_ORDER table to a table with the same structure with a different name. To do this, you can use a method called Create Table as Select (CTAS). All column names and attributes are inherited from the query using one or more base tables. In this example, you want to archive all orders from the CUSTOMER_ORDER table in August 2006 to a table called CUSTOMER_ORDER_AUG_2006:

     create table customer_order_aug_2006 as 
       select * from customer_order
       where order_date between '1-aug-06' and '1-sep-06';


    Table created.


    describe customer_order_aug_2006

     Name                   Null?    Type
    --------------------- -------  -----------
     ORDER_ID                        NUMBER 
     ORDER_DATE             NOT NULL DATE
     ORDER_SHIP_DATE                 DATE
     SHIP_NOTES                      VARCHAR2(1000)

    More Miscellaneous Articles
    More By Apress Publishing

    blog comments powered by Disqus


    - 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-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap