Development Environment

• SQL*Plus is a Development Environment
  • Develop and test SQL
  • Manage databases
  • Write scripts
  • Generate basic reports

Connect to SQL*Plus

• Start SQL*Plus:
  • sqlplus
  • user-name: system
  • password: manager
• Now type any SQL or SQL*Plus command
  • Not case sensitive
  • SQL commands require a semicolon terminator
  • SQL*Plus commands do not require a terminator

SQL*Plus Describe Command

• Describe a table (view the structure)

SQL*Plus Connect Command

• Connect to a different user while inside SQL*Plus

SQL*Plus Host Command

• Shell out to the host operating system
  • SQL> HOST
• Exit from the host operating system

SQL SELECT Statement

• Select data from a table

Dual: The Oracle Work Table

• Dual is a one row one column table

Editing in SQL*Plus

• Editing Options
  • Use the SQL*Plus buffer editor
  • Use any standard ASCII text editor
• SQL*Plus buffer editor
  • Holds only 1 SQL command
  • Ancient-style line editor
• Standard ASCII editor
  • Save in .SQL file and run as a script
  • The preferred solution

Listing the Buffer Contents

Editing the Buffer Contents

• -A-

Editor Commands

Editor Commands

SQL*Plus Edit Command

• Shells out to the operating system
• Opens the default editor
• Loads the default editor with the contents of the SQL*Plus buffer
• You then edit the data as needed
• Exiting the default editor places the modified data back in the SQL*Plus buffer

Related SQL*Plus Commands

• Saves the contents of the SQL*Plus buffer as a file named test.sql in the current directory
• Opens the file named test.sql in the default editor
• Retrieves the data from test.sql and places it in the SQL*Plus buffer

Editing: A Better Way

• Buffer editor is ancient and should be avoided
• Use notepad or vi instead
  • Or any other ASCII editor
• Open a separate window for ASCII editor
  • SQL*Plus session and ASCII source code editor both visible on desktop
  • You can edit and test script code simultaneously
    • Save name.sql file in edit window
    • Execute .sql file in SQL*Plus window as @name

Running SQL*Plus Scripts

• Syntax:
  • Example:
• Syntax:
  • Example:

Exit from SQL*Plus

• To exit from SQL*Plus, simply type the EXIT command at the SQL> prompt
• All pending database changes are committed

SQLPlus Workshop

• Getting started in SQLPlus

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Additional Low Cost Tutorials…

• Oracle Functions
  • Hands-on tutorial on Oracle functions
• Advanced Data Access
  • Hands-on tutorial on Join, CASE and other Oracle query capabilities
• Grouping Data
  • Hands-on introduction to GROUP BY, HAVING, ROLLUP and CUBE operations

…Additional Low Cost Tutorials…

• DML and Transaction Control
  • Hands-on tutorial on INSERT, UPDATE, DELETE, COMMIT and ROLLBACK statements
• SQL*Plus Reporting
  • Hands-on instruction on use of SQL*Plus reporting features
• Tables and Constraints
  • Hands-on introduction to creating Oracle tables and constraints

… Additional Low Cost Tutorials

• Indexes
  • Hands-on introduction to creating B-Tree indexes
• Views and Synonyms
  • Hands-on introduction to creating views and Oracle synonyms