1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL*Plus Introduction

Discussion in 'General' started by tyro, May 2, 2009.

  1. tyro

    tyro Forum Genius

    Likes Received:
    Trophy Points:
    SQL Plus is the de facto tool to manipulate sql code for the Oracle Database. SQL Plus was initially known as UFI for User Friendly Interface which shipped with Oracle Database upto Oracle Version 4. The next version was Advanced UFI which finally gave way to SQL*Plus as we know today.

    iSQLPlus (iSQL*Plus) is a similar utility to the SQL*Plus command line utility for executing SQL and PL/SQL commands but it is browser/web-based.

    Starting SQL*Plus

    On Windows:

    Start>Oracle>Application Development>SQL*Plus for the SQL Plus GUI. Type the user name and password and host connection string if not connecting to the default database.

    For command line version on windows, start the command prompt and type sqlplus

    On Linux:

    Type sqlplus from a terminal.

    SQL Plus executable is located in <Oracle Home>/Bin directory. To start SQL*Plus and connect to a database other than the default, Open a UNIX or a Windows terminal and enter the SQL*Plus command:
    Code (Text):
    sqlplus username/password@connect_identifier
    To hide your password, enter the SQL*Plus command in the form:
    Code (Text):
    sqlplus username@connect_identifier
    You will be prompted to enter your password.

    Any SQL command can be typed in SQL Plus and it has to be ended with a semicolon ";".For example
    Code (Text):

    SQL>select * from emp;
    SQL>select emp_name, emp_address
      2 from emp;
    PL/SQL Blocks are executed as usual
    Code (Text):
      2    dbms_output.put_line('Hello World!');
      3  END;
      4  /
    SQL Plus Commands

    The following commands can be issued to SQL*Plus for various settings and functionalities.
    Code (Text):

       [B]@ pathname[/B]  Run an SQL Script from a file
                   E.g @Script.sql parameter1 parameter2 parameter3
                   In the SQL-Script, refer to the parameters as &1, &2, and &3.

       [B]@@ pathname[/B] Run a nested SQL Script.

       [B]/[/B]           Execute (or re-execute) commands in the SQL*Plus buffer
                   does not list commands before running

       [B]ACCEPT[/B]      User input
                   ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FORMAT format]
                      [DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

       [B]APPEND[/B]      Add text to the end of the current line in the buffer.
                   A[PPEND] text_to_add

       [B]BREAK       [/B]Specify where and how formatting will change.
                   BREAK ON {column|expr|ROW|REPORT} action

       [B]BTITLE      [/B]Place and format a title at the bottom of each page.
                   BTITLE printspec [text|variable]
                   BTITLE [OFF|ON]

       [B]CHANGE      [/B]Change text on the current line.
                   C /oldval/newval

       [B]CLEAR       [/B]Clear the SQL*Plus screen and the screen buffer.

       [B]COLUMN      [/B]Change display width of a column.

       [B]COMPUTE     [/B]Calculate and display totals.

       [B]CONNECT     [/B]Connect to a database as a specified user.
                   connect username/password@SID

       [B]COPY        [/B]Copy data from a query into a table (local or remote)

       [B]DEFINE      [/B]User variables:
                      DEFINE varName = String

                   Display a user variable
                      DEFINE varName

                   Display all variables

       [B]DEFINE_EDITOR [/B]= sql*plus editor (e.g. DEFINE_EDITOR=vim.exe)

       [B]DEL         [/B]Delete the current line in the SQL buffer

       [B]DESC[RIBE]  [/B]Describe a table, column, view, synonym, function
                   procedure, package or package contents.

       [B]DISCONNECT  [/B]Logoff (but don't exit)

       [B]EDIT        [/B]Load the SQL*Plus buffer into an editor.
                   By default, saves the file to AFIEDT.BUF

       [B]EXECUTE     [/B]Run a single PLSQL statement
                   EXEC :answer := EMP_PAY.BONUS('SMITH')

       [B]EXIT[/B] [n]    Commit, logoff and exit (n = error code)
                   EXIT SQL.SQLCODE

       [B]GET [/B]file    Retrieve a previously stored command file

       [B]HELP [/B]topic  Topic is an SQL PLUS command or HELP COMMANDS

       [B]HOST        [/B]Execute a host operating system command
                   HOST CD scripts

       [B]INPUT       [/B]Edit sql buffer - add line(s) to the buffer

       [B]LIST n m [/B]    Edit sql buffer - display buffer lines n to m
                   For all lines - specify m as LAST

       [B]PAUSE message[/B]   Wait for the user to hit RETURN
       [B]PRINT variable[/B]  List the value of bind variable or REF Cursor

       [B]PROMPT message[/B]  Echo a message to the screen  

       [B]REMARK      [/B]REMARK comment or --comment--  or /* comment */

       [B]RUN         [/B]Execute (or re-execute) commands in the SQL*Plus buffer
                   Lists the commands before running

       [B]RUNFORM     [/B]Run a SQL*Forms application

       [B]SAVE file[/B]   Save the contents of the SQL*Plus buffer in a command file
                   SAVE file [CRE[ATE] | REP[LACE] | APP[END]]

       [B]SET         [/B]Display or change SQL*Plus settings

       [B]SHOW        [/B]List the value of a system variable (see PRINT)


       [B]SPOOL file[/B]  Store query results in file

       [B]SPOOL OFF[/B]   Turn off spooling
                   SPOOL OUT sends file to printer

       [B]STA[RT][/B]     Run an SQL Script (see @)


       [B]TIMING      [/B]Record timing data TIMING {START | SHOW | STOP}
                   see CLEAR TIMING

       [B]TTITLE      [/B]Define a page title

       [B]UNDEFINE    [/B]Delete a user variable or passed parameter (see DEFINE)

       [B]VARIABLE    [/B]Define a bind variable
                   (Can be used in both SQLPlus and PLSQL)
                   VAR[IABLE] [variable {NUMBER|CHAR|CHAR (n)}]
                   VARIABLE on its own will display the definitions made.

       [B]WHENEVER OSERROR[/B]   Exit if an OS error occurs

       [B]WHENEVER SQLERROR[/B]  Exit if an SQL or PLSQL error occurs
    Edit SQL Commands in a file: AFIEDT.BUF
    Code (Text):
    Either of the above invokes the default editor(notepad on windows, VI on linux) to edit the sql commands in a text file called AFIEDT.BUF. To change the name of the default file:
    Code (Text):
    SQL>SET EDITFILE "myfile.txt"
    Problems with Editor

    If your editor is not getting invoked when you type ed or edit at the sql prompt, then set the editor as below:
    Code (Text):

    SQL>DEFINE _EDITOR=vi --For unix/linux
    SQL>DEFINE _EDITOR=notepad --For Windows
    Alternately if you want to use some other file editor, you need to specify the location of that editor's executable in the PATH variable, and set SQL Plus to utilize that editor by
    Code (Text):
    SQL>DEFINE _EDITOR=editplus
    How to access Help

    To access help on any command just type
    Code (Text):
    SQL>help command
    Code (Text):
    SQL>help SET
  2. orafan

    orafan Active Member

    Likes Received:
    Trophy Points:
    hey thankzzzz for this
  3. ShaheerBadar

    ShaheerBadar Active Member

    Likes Received:
    Trophy Points: