SQL * Plus

SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user.

With SQL*Plus, you can do the following:

  • Issue a SELECT query and view the results
  • Insert, update, and delete data from database tables
  • Submit PL/SQL blocks to the Oracle server for execution
  • Issue DDL commands, such as those used to create, alter, or drop database objects such as tables,
  • indexes, and users
  • Execute SQL*Plus script files
  • Write output to a file
  • Execute procedures and functions that are stored in a database

Editing Commands

SQLPlus does not store commands in the buffer, you edit a SQLPlus command entered directly to the command prompt by using [Backspace]or by re-entering the command. You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL block currently stored in the buffer. Alternatively, you can use a host operating system editor to edit the buffer contents.

Sql * Plus File Commands

  • SAVE filename - Saves current Content of SQL buffer to the file.

  • GET filename-calls up contents of previously saved file into the buffer

  • START filename–Runs a prvoiusly saved command file

  • @ filename - Runs a prevoiusly saved command file(same as start)

  • EDIT filename - Invokes the editor and saves the buffer content to a file named afiedit.buf

  • Exit – Leaves SQL * Plus

Host operating system commands

HO[ST] [command] Executes a host operating system command without leaving SQL*Plus.

Note: With some operating systems, you can use another character instead of HOST such as $ (VMS), ! (UNIX) and $ (Windows).

See the Oracle installation and user’s manuals provided for your operating system for details.

Storing settings for SQL*PLUS (login.sql and glogin.sql)

glogin.sql

Whenever SQLPLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If such a file is found, it is read and the containing statements executed. This allows to store settings (such as linesize) accross SQLPLUS sessions. New in Oracle 10g: Oracle also reads glogin.sql and login.sql at a connect in sql*plus.

login.sql

Additionally, after reading glogin.sql, sqlplus also looks for a file named login.sql in the directory from where SQLPLUS was and in the directory that the environment variable SQLPATH points to and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql A common login.sql file

SQL> set pagesize 0
SQL> set linesize 190
SQL> define _editor=gvim

Oracle 10g

Since Oracle 10g, the login.sql is executed after a connect This allows to have a prompt that displays the username. For that, the following line must be in the login.sql:

SQL> set sqlprompt "&_user> "
⌖ sql rdbms
← Database Objects
Set Commands →

Subscribe For More Content