SQL Environment Settings

 SQL Environment Settings: set - used to provide sql environment settings
      - valid only per session
 >set null novalue/space
 >set feedback off/on -- supports with all sql stmts
 >set heading off/on
 >set pause on/off  -- page by page display
 >select * from emp;
 >set linesize 200/80 -- no of character's per line
 >set pagesize 120/14/24(10g) -- no of lines                                                             per page
 >select * from emp;
 >set autocommit on/off  -- valid for DML stmts                                           only
 --------------------------------------------------------------  

spool:  Used to store the sql screen content to an
            OS file (.Lst file)
 >spool <filename>
 >spool off - stops the transfering of sql screen                    content into OS file.
 >spool <filename> append
 Adds the sql screen content to existing spool file
 ( 10g )
--------------------------------------------------------------- 

SQL Editor Commands : i - insert mode ( new line )
 a - append mode ( same line )
 ed -- opens editor ( notepad )
 / -- run the buffer content
 L -- list the buffer content
 c/old/new -- change the column/word in sql stmt
 save <filename>-- stores the buffer content
 into .SQL file
 ed <filename> -- opens file in editor ( Notepad )
 get <filename> -- displays the file
 @<filename> or start <filename> -- execute the                                                         file
--------------------------------------------------------------- 

Substitution Operator: &  [ &character ] used to accept the values from key board.
 valid for that particular stmt only.
 >select * from &tname where &condition;
 >insert into STUDENT                 values(&roll,'&sname','&course',&fee);
 >insert into stu_info values(&1,'&2','&3',&4);

 Substitution Operator: && valid for compleate session.
 >select * from &&tname where &condition;
 >insert into stu_info        values(&roll,'&sname','&&course',&fee);
---------------------------------------------------------------
 SQL Reporting Commands: - Used to arrange the select stmt output in          readable format
 - Automatically applied to any "select" stmt output
 - valid only per session
 - Non sql commands valid in sql Environment of        Oracle
 - They are not related to any Table
 5 commands
 1. Ttitle - Top Title
 2. Btitle - Bottom Title
 3. Column Headings
 4. Formats
 5. Control break reports

 ttitle 'Employ Report'
 btitle 'End of Report'
 select * from emp;
 select * from dept;
 ttitle off / on
 btitle off / on
 ttitle left 'Employ Report'  -- left / right / center   (default)
 btitle left 'End of Report'

 >ttitle left  'Student Report of Oracle9i' skip
           left  'For the month of Dec 2009'
 >select * from student where course = 'oracle9i';

   Skip - splits the title into 2 lines---------------------------------------------------------------
 Column Headings & Formats:
 column empno heading 'Employ|Number'
 column ename heading 'Emp Name'
 column sal heading 'Salary' format $9,99,999
 column comm heading 'Commision' format   $9,99,999
 column pf heading 'Provident|Fund' format    $9,9999.99

 | - pipe ( Splits column heading into 2 lines)
 >Select empno,ename,sal,comm from emp;
 >Select ename, sal , sal * .15 pf from emp;

 To clear Headings:
 column empno clear
 column ename clear
 column sal clear
 column comm clear

 >Select empno "Employ Number", ename "Emp        Name", sal  Salary, comm  Commision from emp;

 Note: Alias name is valid in that select stmt output only
 Alias name will not support Formats
 * Column headings are valid till the end of session
    and support formats.

 >select empno,ename,'Rs.'||to_char(sal) as salary
  from emp;  -- Displaying salary with " Rs. "
---------------------------------------------------------------
 Control Break Reports --------------------------
 break on deptno compute sum of sal on deptno
 select deptno,ename,sal from emp order by   deptno;

 break on job compute sum of sal on Job
 select Job,ename,sal from emp order by Job;

 break on course compute sum of fee_paid on course
 select course, sname, fee_paid from stu_info
 order by course;

 To clear Break:
 >clear breaks
---------------------------------------------------------------
 Non-SQL commands: -- supported in sql * plus environment only
 Sql Reporting commands
 Describe , set , spool , & , &&
 SQL editor commands