Home arrow Oracle arrow Page 6 - Working With Oracle on Windows: Part 2
ORACLE

Working With Oracle on Windows: Part 2


Now we have installed Oracle we can now begin to familiarise ourselves with use of SQL and how to use basic Oracle expressions.

Author Info:
By: Ben Shepherd
Rating: 4 stars4 stars4 stars4 stars4 stars / 17
March 04, 2003
TABLE OF CONTENTS:
  1. · Working With Oracle on Windows: Part 2
  2. · Databases
  3. · Relationships
  4. · Structuring your database
  5. · Tables in Oracle
  6. · User Defined Schema Objects
  7. · Conclusion

print this article
SEARCH DEVARTICLES

Working With Oracle on Windows: Part 2 - User Defined Schema Objects
(Page 6 of 7 )

The DDL (i.e. Data Definition Library) contains the set of commands that allow us to create, drop tables and establish new schema objects. To create an object we can type the following,

CREATE OR REPLACE TYPE firstType AS OBJECT
(
 v_id               NUMBER,
 v_firstName     VARCHAR2(25),
 v_surname      VARCHAR2(30)
)
/

We have now created a new schema object in the DDL. Now to create a table we can type,

CREATE TABLE firstTable of firstType
/


This will create the same table the only difference is that the DDL doesn’t create an object this time. The reason why you would create a schema object is so that you can create a number table that has the same attributes. Another reason why you’d use schema objects is the simplicity of inputting data into a table.

Let’s input data into our firstTable table using DML (i.e. Data Manipulation Language). We could simply insert data like this

INSERT INTO firstTable VALUES (1,’Kim’,’Tyson’);

How could we use schema objects for inputting data into a table? The answer is again very simple. We create a schema object and embedded the user defined schema object into the table.

CREATE OR REPLACE TYPE t_address AS OBJECT
(
 v_streetNumber     NUMBER,
 v_streetName       VARCHAR2(30),
 v_citySuburb        VARCHAR2(30),
 v_state               VARCHAR2(4),
 v_postCode          NUMBER  
)

CREATE TABLE secondTable
(
 v_customerId         NUMBER,
 v_homeAddress      t_address,
 v_workAddress       t_address
)

Now we may insert data using the schema objects as follows,

INSERT INTO secondTable VALUES
( 0001,
t_address(1, ‘Jackson Street’, ‘Sydney’, ‘NSW’, 2000),
t_address(3, ‘Jackson Street’, ‘Sydney’, ‘NSW’, 2000)
)

Did you notice something apart from the fact that this poor guy lives next door from where he works? Yes, you can insert schema object into a field.


blog comments powered by Disqus
ORACLE ARTICLES

- Oracle Purchases Taleo to Extend Cloud Compu...
- Partitioning in Oracle. What? Why? When? Who...
- Datatypes Used in Oracle 9i
- Creating a Database in Oracle 9i
- Oracle with Doctor Janusz Getta
- Working With Oracle on Windows: Part 3
- Working With Oracle on Windows: Part 2

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials