# Presentation: 355054

## Oracle for Software Developers

## What is a relational database?

- Data is represented as a set of two-dimensional tables. (rows and columns)
- One or more of the columns is defined as a primary key and this column or combination of columns will uniquely define the row.
- Tables are related by the shared column values.

## What is a relational database? (continued)

- Operations in a relational database are processed in groups of data.
- The output of the operation is itself a table (or *relation* or *result set*), which can be processed further by relational operations.

## Relational Tables

| vac | ioc1 |
| --- | --- |
| vac | ioc2 |
| ps | ioc2 |

| ioc1 | Test 1 | Bldg A | 128.0.0.1 |
| --- | --- | --- | --- |
| ioc2 | Test 2 | Bldg A | 128.0.0.2 |
| ioc3 | Test 3 | Bldg A | 128.0.0.3 |

## Oracle database

- Two basic concepts must be understood in order to make sense of the Oracle architecture: databases and instances.
- A database is a set of data as defined previously.
- A database instance is a set of memory structures and background processes shared by all users that access a database.
- You access the Oracle database through the instance.

## Slide 6

## SQL - Interface to the database

- SQL is a data sub-language: the purpose of SQL is to provide an interface to a relational database such as Oracle. All SQL statements are instructions to the database.

## SQL*PLUS

- Oracle utility that allows SQL execution against an Oracle database.
- Useful for database administrators, software developers and end-users.
- Available as a client tool on the desktop or accessible at the OS command line interface for both Unix an NT.

## Oracle Architecture

- USERS and SCHEMAS
- TABLE
- INDEX
- VIEW
- PACKAGE
- PACKAGE BODY
- PROCEDURE
- SEQUENCE
- SYNONYM
- TYPE

**Notes:**
Package - A database object that groups logically related PL/SQL types, objects, and  
 subprograms. Packages usually have two parts, a specification. and a body,  
 although sometimes the body is unnecessary. The specification is the interface  
 to your applications; it decalres the types, variables, constants, exceptions,  
 cursors, and subprograms available for use. The body fully defines cursors and  
 subprograms, ans so it implements the specification.  

 Example:  

   ===============================================================  
   DESCRIPTION: PL/SQL specification for package:  FND_COMPILED  
   create or replace package FND_COMPILED as  

   -- DELETE_MENU  
   procedure DELETE_MENU(appl_id in number, menu_id in number);  

   -- DELETE_MENU_TREE  
   procedure DELETE_MENU_TREE(appl_id in number, menu_id in number);  

   end FND_COMPILED;  
   ===============================================================  

   ===============================================================  
   DESCRIPTION: PL/SQL body for package:  FND_COMPILED  

   create or replace package body FND_COMPILED as  

   -- DELETE_MENU  
   -- Delete a root menu from compiled menu table.  
   --  
   procedure DELETE_MENU(appl_id in number, menu_id in number) is  
   begin  
       -- If either id is null, clear entire compiled table.  
       if ((appl_id is NULL) or (menu_id is NULL)) then  
           DELETE FROM FND_COMPILED_MENU;  
       else  
           DELETE FROM FND_COMPILED_MENU  
           WHERE APPLICATION_ID = delete_menu.appl_id  
           AND MENU_ID = delete_menu.menu_id;  
       end if;  
   when others then  
         generic_error('FND_COMPILED.DELETE_MENU', SQLCODE, SQLERRM);  
   end DELETE_MENU;  

   --  
   -- DELETE_MENU_TREE  
   --  
   -- Delete any compiled menu which references given menu as a submenu  
   -- in its menu tree.  
   --  
   procedure DELETE_MENU_TREE(appl_id in number, menu_id in number) is  
   begin  
       DELETE FROM fnd_compiled_menu  
       WHERE (menu_id, application_id) IN  
       (SELECT distinct menu_id, application_id FROM fnd_menu_entry  
            CONNECT BY PRIOR application_id = action_application_id  
                    AND PRIOR menu_id = action_id  
                    AND action_type_id = 'M'  
            START WITH application_id = delete_menu_tree.appl_id  
            AND menu_id = delete_menu_tree.menu_id);  
   exception  
   when others then  
       generic_error('FND_COMPILED.DELETE_MENU_TREE', SQLCODE, SQLERRM);  
   end DELETE_MENU_TREE;  

   end FND_COMPILED;  
   ===================================================================== 

## Users and Schemas

- User - A user account is not a physical structure in the database, but it does have important relationships to the objects in the database; users own database objects.
- Schema - The set of objects owned by a user account.

## Tables, Columns, and Datatypes

- Table – The storage mechanism for data within an Oracle database  that represents a set of records of the same structure, also called rows.
- Column - A table contains columns which are descriptive attributes and have a defined data type.

## Tables, Columns, and Datatypes (continued)

- VARCHAR2(size) Variable-length character string having maximum length size bytes. Maximum size is 4000.
- NUMBER( p,s) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
- DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.

**Notes:**
12 others
CHAR(size) Fixed-length character data of length size bytes.
Maximum size is 2000 bytes. Default and minimum
size is 1 byte.

LONG Character data of variable length up to 2 gigabytes,
or 2 31 -1 bytes.

RAW(size) Raw binary data of length size bytes. Maximum size
is 2000 bytes. You must specify size for a RAW value.

LONG RAW Raw binary data of variable length up to 2
gigabytes.

ROWID Hexadecimal string representing the unique address
of a row in its table. This datatype is primarily for
values returned by the ROWID pseudocolumn.

UROWID [(size)] Hexadecimal string representing the logical address
of a row of an index-organized table. The optional
size is the size of a column of type UROWID. The
maximum size and default is 4000 bytes.

CLOB A character large object containing single-byte
characters. Both fixed-width and variable-width
character sets are supported, both using the CHAR
database character set. Maximum size is 4 gigabytes.

BLOB A binary large object. Maximum size is 4 gigabytes.

BFILE Contains a locator to a large binary file stored
outside the database. Enables byte stream I/O
access to external LOBs residing on the database
server. Maximum size is 4 gigabytes.

NCHAR(size) Fixed-length character data of length size characters
or bytes, depending on the choice of national
character set. Maximum size is determined by the
number of bytes required to store each character,
with an upper limit of 2000 bytes. Default and
minimum size is 1 character or 1 byte, depending on
the character set.

NVARCHAR2(size) Variable-length character string having maximum
length size characters or bytes, depending on the
choice of national character set. Maximum size is
determined by the number of bytes required to store
each character, with an upper limit of 4000 bytes.
You must specify size for NVARCHAR2.

NCLOB A character large object containing multibyte
characters. Both fixed-width and variable-width
character sets are supported, both using the NCHAR
database character set. Maximum size is 4 gigabytes.
Stores national character set data.

## Constraints

- A constraint ensures that a specific condition is meet by all rows in a table.
- PRIMARY KEY - Specifies that column(s) are the table prime key and must have unique values. Index is automatically generated for column.
- NULL/NOT NULL - NOT NULL specifies that a column must have some value. NULL (default) allows NULL values in the column.

## Constraints (continued)

- UNIQUE - Specifies that column(s) must have unique values
- FOREIGN KEY - Specifies that column(s) are a table foreign key and will use referential uniqueness of parent table. Index is automatically generated for column. Foreign keys allow deletion cascades and table / business rule validation.

## Constraints (continued)

- DEFAULT - Specifies some default value if no value entered by user.
- DISABLE - You may suffix DISABLE to any other constraint to make Oracle ignore the constraint, the constraint will still be available to applications/tools and you can enable the constraint later if required.

## Indexes

- An index is a database structure used to quickly find a row in a table.
- An index entry consists of a key value and a RowId.  The key value is the value of a column or columns in a row.

## Synonyms

- Mask the underlying complexity of the path to an object.
- Can be used to provide pointers for tables, views, procedures, functions, packages, and sequences.

**Notes:**
Example:  A table called EMPLOYEE created under schema HR.  For a user to address that table he would need to know the schema name.

## Views

- A view appears to be a table containing columns and is queried in the same manner that a table is queried.  Conceptually, a view can be thought of as a mask overlaying one or more tables, such that the columns in the view are found in one or more underlying tables.

## User views

- USER_TABLES
- USER_TAB_COLUMNS
- USER_INDEXES
- USER_SYNONYMS
- USER_VIEWS
- USER_SEQUENCES

## DDL

- Create table
- Alter table
- Drop table
- Create index
- Create synonym
- Create sequence

## SQL Commands

- SELECT
- UPDATE
- INSERT
- DELETE

## Data Consistency

- Rollback
- Commit
- Set Transaction

## Connecting to the database

- Net8 – Oracle transparent protocol network layer.

## Connecting to the database (continued)

- Client configuration via tnsnames.ora file.
- iroquoisdbprod =
-   (DESCRIPTION =
-     (ADDRESS_LIST =
-         (ADDRESS =
-           (COMMUNITY = tcp.world)
-           (PROTOCOL = TCP)
-           (Host = IROQUOISDB)
-           (Port = 1521)
-         )
-     )
-     (CONNECT_DATA =
-        (SID = PROD)
-        (GLOBAL_NAME = PROD.world)
-     )
-   )

## Connecting to the database (continued)

- Future plans are to go to a LDAP model for SQL*NET name resolution.

## Embedded SQL

- C/C++ - Pro*C
- Java – JDBC SQLJ
- PERL - DBI
- FORTRAN – Pro*Fortran
- OCI
- Oracle Tools (Developer 2000,PL/SQL)
- ODBC

## Resources

- http://w3.one.net/~jhoffman/sqltut.htm
- _[http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/tutorial.html](http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/tutorial.html)_
- _[http://www.spnc.demon.co.uk/ora_sql/sqlmain.](http://www.spnc.demon.co.uk/ora_sql/sqlmain.htm)__[htm](http://www.spnc.demon.co.uk/ora_sql/sqlmain.htm)_
- http://technet.oracle.com/

## Resources (continued)

- Jeff Patton
- _[pattonjg@sns.gov](mailto:pattonjg@sns.gov)_
- (865) 241-8093