Oracle From Scratch-The basics

Oracle Basics as we would like to learn
By Lawrence Cumber.

Most of us would like to learn the basics of Oracle in other to get where we want or do what we do. So lets start from here

It is of great importance to know how to use the set commands and what is does when you set them to your taste. These commands have variable names and values that are allowed. The most commonly used are:

ARRAY[SIZE] {15|n} – This sets the number of rows that SQL*plus will fetch from the database at one time. It is also called a batch.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} – This controls when Oracle commits pending changes to the database.

AUTO[TRACE]—Displays a report on the execution of successful SQL DML statements. {OFF|ON|TRACE[ONLY] [EXP[LAIN]] [STAT[TISTICS]]

DEF[INE]-This sets the character used to prefix substitution variables to c {‘&’|c|OFF|ON}

ECHO {OFF|ON}-Controls whether the START command lists each command in a command file as the command is executed.

ESC[APE] {\|C|OFF|ON-This defines the character you enter as the escape character.

FEED[BACK]—This helps to display the records returned by a query, when a query selects at {6|n|OFF|ON least n records.

HEA[DING]—Very essential. Used to control printing of column headings in REPORTS. {OFF|ON}

LINESIZE {80|n}—Sets the number of character that would be displayed on one line before the beginning of another line.

PAGESIZE {24|n}—Sets the number of lines in each page.

Although the above mentioned are important, its most likely that you would want to set you environment to your own taste depending on what you are about to do. This therefore brings us to another level. If after having what you want your environment to look like, you will not want to keep on setting these command every time you login.

It is possible to save your current environment using STORE SET yourfilename.

This would be stored with a .sql extension.
Lets say we store it under the filename login.sql.
If we want to get that same environment, we would have to run this script to set up our customized environment.

Creating Global login script for your database


SET HEADING OFF PROMPT Welcome to Larosbi-System Database Sql*plus !
SELECT ‘You are Connected to ‘|| GLOBAL_NAME|| ‘as’ || USER
FROM GLOBAL_NAME;
SET TIME ON PAGESIZE 24 LINESIZE 90 HEADING ON

You call this script when sql starts. An alternative and best way to customize you environment is to search glogin.sql script.. Mine is located on my system at C:\Oracle\sqlplus\admin\glogin.sql

After saving our script as login.sql, we can now rename the original script glogin.sql to something else like glogin.sql2. This would make this script inaccessible. Now copy your script login.sql to the above location and rename it as glogin.sql
This makes our created script assessable during startup. From the above script, you would have it display as

WELCOME TO LAROSBI SYSTEMS DATABASE SQL*PLUS
SP2-0640: Not connected
08:43:42 SQL>

Its more advantageous to do it in this matter for you don’t have to run your script everytime you login and it takes care of who so ever logs in on this Database (Globally)..
By Lawrence Cumber

Producing Readable Report from your SQL*PLUS

SQL*PLUS returns most of the time results that wrap to the next line,or the formatting is improper. Using some of its commands easily rectifies this display. Its good to have a good report format. For instance if you were asked by your manger or any superior to produce a report of all employees in the order of their department and names using the employee table, we could address this issue as:

Things to consider
Report must have a title----Use TTITLE command
Set the pagesize and linesize.- Check using SHOW PAGESIZE LINES
Turn off FEEDBACK—SET FEEDBACK OFF
Format the Columns to produce meaningful headings using the COLUMN command
So now our script would look like this.

Rem Report to display the employees information
Rem Created on 15th March 2005 by Lawrence Cumber
Rem
SET PAGES 55 LINES 80 TRIMS ON FEEDBACK OFF ECHO OFF DOCUMENT OFF
/* This lines below deal with column formatting */

COLUMN ename HEADING “Employee|Name” FORMAT A8
COLUMN empno HEADING “Emp|ID” FORMAT 9999
COLUMN job HEADING “Position”
COLUMN hiredate HEADING “Date Employed”
COLUMN sal FORMAT “$9,999” HEADING “Salary”
COLUMN comm. LIKE SAL HEADING “Incentive|Bonus”

/* save the output to a file */
TTITLE CENTER “Employee Information” Skip 2
/*suppress duplicate codes */

BREAK ON deptno SKIP 2
REM
REM The Query
REM
SELECT deptno,empno,ename,job,hiredate,sal,comm.
From emp
ORDER BY deptno,ename;
SPOOL OFF

/* Now we have to clear all the formatting */
CLEAR COLUMNS
CLEAR BREAKS
SET FEEDBACK ON

Accepting Values At Runtime in Oracle.

Creating an intereactive SQL Command needs Defining variables at the sql command
This is done using the ampersand (&) followed by the variable name.
A good example is:
SQL> SELECT ENAME,HIREDATE,JOB
FROM EMP
WHERE DEPTNO=&DEPT; You can also define a Substitution variable using the DEFINE command.
For Example:

SQL> DEFINE dept=10;
If we run our first script this would prompt for the value for dept because we have now define it.

Mutiple Table Queries

Querying data from more that one table is done using joins and subqueries.
Subquery is a query inside another query.
to query data from more than one table, you need to identify a common column that relate the two tables.
A join is a query that combines rows from one or more tables or veiws.
Oracle performs a join whenever multiple tables appear in the query's FROM clause.
Example fo extracting data from two tables. using a join
We have to know what we would be querying.
so:
Issue the DESC command as such:
DESC emp
DESC dept
This tells us the columns to query from both tables.
SELECT ename,hiredate,sal, loc,dname
FROM emp,dept WHERE emp.deptno=dept.deptno;

This is an equality join Equality joins carry the = sign relating two tables. If its not an equal sign, then that join is called a non-equality join
We also have other joins like Cartesian joins,Outer joins and Self joins
You should take note that a join will not just work in a query until there is some sort of relationship between this two tables,.i.e having a foreign key in the emp table referencing the dept table.

Cartesan joins occur when data is selected from tow or more tables and they is no common relation specified in the WHERE clause. The resulting query in a cartesian join between a table having M rows and another table having N rows is always M*N rows.If we should added another table with B number of rows, the resulting query would be M*N*B rows.

Outer Joins
Outer joins are used to obtain data from one table, even if there is not corresponding row in the joining table.. The (+) sign is entered beside the column not having the corresponding rows.
For Example:
SELECT D.deptno,E.empno,E.name FROM dept D,emp E;
Note The outer join can only appear in the WHERE clause.
It cannot be used with other conditions like OR AND in.
 Self Join

Self join joins a table to itself
The table name appears in the FROM clause twice
For Example:
SELECT a.ename "Employees name",b.ename "Members" FROM emp a. emp b:

Also we can make use of SET OPERATORS to selected data from multiple tables. They basically combine the result of 2 queries into one.
All set operators have equal precedence.
In the presence of multiple set operators, they evalutate from the left to the right.
The most common set operators used are:
UNION
UNION ALL
INTERSECT
MINUS
 The UNION operator is used to return rows from either query.
If we have tables with a customer column and this tables are name present_customers and old_customers. our query would look like this

SELECT customer FROM present_customers
UNION
SELECT customer FROM old_customers;
With UNION ALL. there is no filtering of rows. ALL rows would be return
INTERSECT is used to retrun the rows returen by both queries
SELECT customer FROM present_customers
INTERSECT
SELECT customer FROM old_customers