, , ,

Temporary tables

Global temporary tables
Global temporary tables are distinct within SQL sessions.
The basic syntax is:
    CREATE GLOBAL TEMPORARY TABLE table_name ( …);
For example:
    CREATE GLOBAL TEMPORARY TABLE supplier
    (     supplier_id     numeric(10)     not null,
        supplier_name     varchar2(50)     not null,
        contact_name     varchar2(50)    
    )            
This would create a global temporary table called supplier .
Local Temporary tables
Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.
The basic syntax is:
    DECLARE LOCAL TEMPORARY TABLE table_name ( …);
/by
, , ,

CREATE OR REPLACE VIEW

Views are queries stored in Oracle that dynamically assemble data into a virtual table. To the person using the view, manipulating the data from the view is just like manipulating the data from a table. In some cases, it is even possible for the user to change data in a view as though the view were a table. Let’s now explore the topic of creating, using, and managing views in more detail.

Creating a VIEW
The syntax for creating a VIEW is:
    CREATE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;A view will not be created if the base table you specify does not exist. However, you can overcome this restriction by using the force keyword in the create view command. This keyword forces Oracle to create the view anyway. However, the view will be invalid because no underlying table data is available to draw from.
For example:
Create VIEW XX_PO_DETAILS_v AS
Select a.PO_ID, a.PO_NUMBER, b.ITEM_NAME, b.NEED_DATE
From XX_PO_HEADERS_ALL a, XX_PO_LINES_ALL b
Where a.PO_ID=b.PO_ID
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
    SELECT *
    FROM  XX_PO_DETAILS_vs;

Creating Views That Enforce Constraints
Tables that underlie views often have constraints that limit the data that can be added to those tables. As I said earlier, views cannot add data to the underlying table that would violate the table’s constraints. However, you can also define a view to restrict the user’s ability to change underlying table data even further, effectively placing a special constraint for data manipulation through the view. This additional constraint says that insert or update statements issued against the view are cannot create rows that the view cannot subsequently select. In other words, if after the change is made, the view will not be able to select the row you changed, the view will not let you make the change. This viewability constraint is configured when the view is defined by adding the with check option to the create view statement. Let’s look at an example to clarify my point:
 create or replace view emp_view as
 (select empno, ename, job, deptno
   from emp
   where deptno = 10)
  with check option constraint emp_view_constraint;
Updating a VIEW
You can update a VIEW without dropping it by using the following syntax:
    CREATE OR REPLACE VIEW view_name AS
    SELECT columns
    FROM table
    WHERE predicates;For example:
    CREATE or REPLACE VIEW sup_orders AS
    SELECT suppliers.supplier_id, orders.quantity, orders.price
    FROM suppliers, orders
    WHERE suppliers.supplier_id = orders.supplier_id
    and suppliers.supplier_name = ‘Microsoft’;
Dropping a VIEW
The syntax for dropping a VIEW is:
    DROP VIEW view_name;
For example:
    DROP VIEW sup_orders;
Creating Simple Views That Can’t Change Underlying Table Data
In some cases, you may find that you want to create views that don’t let your users change data in the underlying table. In this case, you can use the with read only clause. This clause will prevent any user of the view from making changes to the base table. Let’s say that after reprimanding SCOTT severely for calling him a fool, KING wants to prevent all employees from ever changing data in EMP via the EMP_VIEW again. The following shows how he would do it:
create or replace view emp_view
 as (select * from emp)
 with read only;
Frequently Asked Questions
Question:  Can you update the data in a view?
Answer:  A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view. So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.
Question: Does the view exist if the table is dropped from the database?
Answer: Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.If you recreate the table (that you had dropped), the view will again be fine.
/by
, , ,

DROP TABLE

The DROP TABLE statement allows you to remove a table from the database.
The basic syntax for the DROP TABLE statement is:
    DROP TABLE table_name;
For example:  DROP TABLE XX_supplier;
This would drop table called XX_supplier.
Sometimes objects are associated with a table that exists in a database along with the table. These objects may include indexes, constraints, and triggers. If the table is dropped, Oracle automatically drops any index, trigger, or constraint associated with the table as well. Here are two other factors to be aware of with respect to dropping tables:

  1. You cannot roll back a drop table command.
  2. To drop a table, the table must be part of your own schema, or you must have the drop any table privilege granted to you.

Truncating Tables
Let’s move on to discuss how you can remove all data from a table quickly using a special option available in Oracle. In this situation, the DBA or developer may use the truncate table statement. This statement is a part of the data definition language (DDL) of Oracle, much like the create table statement and completely unlike the delete statement. Truncating a table removes all row data from a table quickly, while leaving the definition of the table intact, including the definition of constraints and any associated database objects such as indexes, constraints, and triggers on the table. The truncate statement is a high-speed data-deletion statement that bypasses the transaction controls available in Oracle for recoverability in data changes. Truncating a table is almost always faster than executing the delete statement without a where clause, but once this operation has been completed, the data cannot be recovered unless you have a backed-up copy of the data. Here’s an example:
SQL> truncate table tester;
Table truncated.

/by
, , ,

Alter Table

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.
Renaming a table
The basic syntax for renaming a table is:
    ALTER TABLE table_name
     RENAME TO new_table_name;
For example:
    ALTER TABLE suppliers
     RENAME TO vendors;
This will rename the suppliers table to vendors.
Adding column(s) to a table
Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     ADD column_name column-definition;
For example:
    ALTER TABLE supplier
     ADD supplier_name  varchar2(50);
This will add a column called supplier_name to the supplier table.
Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    ADD (column_1     column-definition,
          column_2     column-definition,
          …    
          column_n     column_definition );
Drop column(s) in a table
Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     DROP COLUMN column_name;For example:
    ALTER TABLE supplier
     DROP COLUMN supplier_name;

Modifying column(s)(datatypes) in a table
Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     MODIFY column_name column_type;

For example:
    ALTER TABLE supplier
     MODIFY supplier_name   varchar2(100)     not null;
This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.
Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
    MODIFY (     column_1     column_type,
          column_2     column_type,
          …    
          column_n     column_type );
Rename column(s) in a table
(NEW in Oracle 9i Release 2)
Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
    ALTER TABLE table_name
     RENAME COLUMN old_name to new_name;
/by
, , ,

Create TABLE

The CREATE TABLE statement allows you to create and define a table.
The basic syntax for a CREATE TABLE statement is:
    CREATE TABLE table_name
    ( column1 datatype null/not null,
      column2 datatype PRIMARY KEY,      column3 datatype PRIMARY KEY,
      …
    );
Each column must have a datatype. The column should either be defined as “null” or “not null” and if this value is left blank, the database assumes “null” as the default.
Example 1:
CREATE TABLE XX_PO_HEADERS_ALL
(
PO_ID         NUMBER(12) PRIMARY KEY,
PO_NUMBER     NUMBER(12),
SUPPLIER_NAME VARCHAR2(12) NOT NULL,
SUPPLIER_SITE VARCHAR2(12),
SHIP_TO       VARCHAR2(12),
BILL_TO       VARCHAR2(12)
)
Example 2:
CREATE TABLE XX_PO_LINES_ALL
(
PO_ID         NUMBER(12),
LINE_NUMBER   NUMBER(12),
ITEM_NAME     VARCHAR2(12),
QUANTITY      NUMBER(12),
ITEM_PRICE    NUMBER(12),
ITEM_TAX      NUMBER(12),
LINE_PRICE    NUMBER(12),
ORDER_DATE    DATE,
NEED_DATE     VARCHAR2(12),
BILL_TO       VARCHAR2(12)
)

Column Default Values
You can define tables to populate columns with default values as well using the default clause in a create table command. This clause is included as part of the column definition to tell Oracle what the default value for that column should be. When a row is added to the table and no value is defined for the column in the row being added, Oracle populates the column value for that row using the default value for the column. The following code block illustrates this point:
SQL> create table display
  2  (col1 varchar2(10),
  3   col2 number default 0);
Table created.
CREATE a table from another table
You can also create a table from an existing table by copying the existing table’s columns.
The basic syntax is:
CREATE TABLE table_name
  AS (SELECT * FROM old_table);
Example1:
CREATE TABLE XX_PO_HEADERS_ALL_COPY
AS (Select * From XX_PO_HEADERS_ALL)
The above statement ‘ll create a new table that is just an exact copy of XX_PO_HEADERS_ALL.
Example 2:  Copying selected columns from another table
The basic syntax is:
    CREATE TABLE new_table
      AS (SELECT column_1, column2, … column_n FROM old_table);
Example 3: Copying selected columns from multiple tables
The basic syntax is:
    CREATE TABLE new_table
      AS (SELECT column_1, column2, … column_n
              FROM old_table_1, old_table_2, … old_table_n);
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement). If you want to create a blank table then use a condition which is always false in the where clause of the select statement.
Creating Temporary Tables
Most of the time, when you create a table in Oracle, the records that eventually populate that table will live inside your database forever (or at least until someone removes them). However, there might be situations where you want records in a table to live inside the database only for a short while. In this case, you can create temporary tables in Oracle, where the data placed into the tables persists for only the duration of the user session, or for the length of your current transaction.
A temporary table is created using the create global temporary table command. Why does a temporary table have to be global? So that the temporary table’s definition can be made available to every user on the system. However, the contents of a temporary table are visible only to the user session that added information to the temporary table, even though everyone can see the definition. Temporary tables are a relatively new feature in Oracle, and Oracle hasn’t had enough time yet to implement “local” temporary tables (that is, temporary tables that are only available to the user who owns them). Look for this functionality in later database releases. The appropriate create global temporary table command is shown in the following code block:

Create global temporary table XXX_PO_HEADERS_ALL as
Select *
From PO_HEADERS_ALL
Where 10=11
The purpose of writing the where clause is to make the temporary table blank. If we dont put the where clause the temporary table would contain all the rows of XXX_PO_HEADERS_ALL
/by