sqlplus

Basic stuff

  • Invoking sqlplus

  • Exiting sqlplus

  • Basic sqlplus usage

  • Changing your password

    Advanced stuff

  • SQL in files

  • Saving SQL queries to file

  • Logging output to a file

  • Escaping

  • Editing and executing from the sqlplus buffer

  • Customizing the sqlplus output

  • Accessing an Oracle Database located on another host

  • commit/rollback


    Basic stuff

  • Invoking sqlplus

    Sqlplus is the program supplied by Oracle to give you a command line interface to the Oracle database. It is very simple and has no history like for instance the bash Shell. The sqlplus program is located in ~oracle/bin/sqlplus on my basic Oracle installation under Linux. All future references to sqlplus will just be 'sqlplus'.
    Before starting to use sqlplus you will need to set up the enviroment with the Oracle specific enviroment variables: ORACLE_SID, ORACLE_HOME, ORACLE_BASE, LD_LIBRARY_PATH. In my basic install they were: ORACLE_SID=ORCL, ORACLE_HOME=/home/oracle, ORACLE_BASE=/home/oracle, LD_LIBRARY_PATH=/home/oracle/lib. These enviroment variables would connect you to the ORCL Oracle instance on the local machine.
    Invoking 'sqlplus' without any kind of arguments will try to log you onto the Oracle instance defined in the enviroment variable ORACLE_SID and prompt for user-name and password. If an argument is supplied this will be taken as the user-name and sqlplus will prompt you for your password.
    The full amount of options to my sqlplus are (sqlplus --help):
    Usage: SQLPLUS [<option>] [<user>[/<password>] [@<host>]]
                   [@<startfile> [<parm1>] [<parm2>] ...]
    where <option> ::= { -s | -? }
    -s for silent mode and -? to obtain version number
    

    So summarizing on this we can connect to the Oracle database with '~oracle/bin/sqlplus username/password'.
    When you are correctly logged into the Oracle database you will see the prompt looking like 'SQL>'.

  • Exiting sqlplus

    To exit your sqlplus session you can simply type 'exit', 'quit' or simply use your shells EOF escape sequence, on my system that is CTRL-D (^D).

  • Basic sqlplus usage

    If you're having problems with a specific command or sql statement in your sqlplus session you can always use the built-in help. If you're looking for help on SELECT you would write 'help SELECT' in your sqlplus session.
    All sql-statements (using either SELECT, DELETE, UPDATE, INSERT, CREATE or DROP) that you want executed have to end with a ';', which is the End Of SQL identifier for the sqlplus tool.
    To show all the subjects that sqlplus have help for you would type 'help TOPIC'.
    One way of listing all the tables you have in your current database would be by executing the following statement in your sqlplus session: 'SELECT table_name FROM user_tables;'.
    When you have found all your table names and you want to see what the table is defined as you can use the desc function. Desc is short for 'describe' (which also can be used).
    SQL> describe dual
     Name                            Null?    Type
     ------------------------------- -------- ----
     DUMMY                                    VARCHAR2(1)
    
  • Changing your password

    It is important that you change your personal passwords on your shell account and it is likewise important to know how to change your password on the Oracle database. Use the following method for changing your password.
    SQL> grant connect to <username> identified by <password>;
    

    Advanced stuff

  • SQL in files

    Sometimes it can be unhandy handling large sql-statements in sqlplus and very annoying having to deal with the very limited command interface of sqlplus. That is why it is possible to use <insert-your-favorite-editor-here> to edit your sql-statements. There is infact more than one method for doing this and it will be descriped later in the Editing and executing from the sqlplus buffer section.
    I usually place my sql scripts in .sql files, which is a very good standard to follow. When you're done editing, save and then log on to Oracle via sqlplus.
    For sample purposes I made a test.sql file containing:
    desc dual
    
    To execute the file as sql you have two options, which basically does the same. Either you use 'start test.sql' or '@test.sql'. I prefer the last one, since it is shorter to type. If you omit the .sql part of the filename Oracle will though look for such a file.
    SQL> @test.sql
     Name                            Null?    Type
     ------------------------------- -------- ----
     DUMMY                                    VARCHAR2(1)
    
    If you need to edit the file without having to exit sqlplus you can type 'edit test.sql' and sqlplus will launch your enviroment-choosen $EDITOR or just 'ed' if $EDITOR is undefined.

  • Saving SQL queries to a file

    If you have just executed a SQL statement and you want to save it for future use you can use the sqlplus built-in function called 'save'.
    SQL> desc dual
     Name                            Null?    Type
     ------------------------------- -------- ----
     DUMMY                                    VARCHAR2(1)
    
    SQL> save dual.sql
    Created file dual.sql
    
    If you want to append the last executed SQL statement to a file you use 'save <file.sql> app'.

  • Logging output to a file

    When you need the data your SQL-statements have shown you in a file it can sometimes be error-prone to use the 'cut & paste' method, especially if you have a large amount of output. Sqlplus offers a function to put the result set directly into a file and on the display at the same time. This function is called 'spool'.
    SQL> spool test.out
    SQL> desc dual
     Name                            Null?    Type
     ------------------------------- -------- ----
     DUMMY                                    VARCHAR2(1)
    
    SQL> spool off
    
    This example would put the output of everything after 'spool test.out' until and including 'spool off' into the file test.out.

  • Escaping

    There are a few characters to be carefull about when working with SQL-statements in sqlplus. One of these characters is '&'. The '&' charachter is usually used for replacing a string with something the user types in on a prompt.
    SQL> select &name from dual;
    Enter value for name: *
    old   1: select &name from dual
    new   1: select * from dual
    
    D
    -
    X
    
    This seems like a very nice feature for scripts, but what if you want to insert or select on something that contains a '&' it gets problematic. The solution is to turn escaping on.
    SQL> set escape \
    SQL> select * from test where name like '%\&%';
    
    [Returning rows]
    
    SQL> set escape off
    
    This would achieve the wanted effect and turn off escaping again.

  • Editing and executing from the sqlplus buffer

    If you like me have been annoyed with the fact that you can't do real command line editing like the bash Shell's emacs mode, you'll be happy about sqlplus even though it doesn't look like you have these possibilties first off. What you need to do is export the enviroment variable EDITOR with your favorite editor. Since I use emacs and have bash as my shell I do 'export EDITOR=emacs', vi users would similary do 'export EDITOR=vi'. When entering sqlplus after exporting this enviroment variable you can edit the SQL-statement in your current SQL buffer by writing 'ed' on your SQL> command line. This will launch your favorite editor with the content of the current SQL buffer of sqlplus. The SQL buffer contains the last executed SQL-statement or if you just launched sqlplus you will get an error like 'Nothing to save.'. The 'ed' command can also be used to edit SQL-scripts from a file if you supply the filename. To execute the SQL-statement in the current buffer you simply type 'r' for run.

  • Customizing the sqlplus output

    The level of which you can customize your output in sqlplus is very big. I will only try to describe a few and you are encouraged to explore all the possibilities of set; 'help set'.
    If you do not like the column names to show up when you execute your SQL statement you have to use the command 'set heading off'.
    SQL> set heading off
    SQL> select * from dual;
    
    X
    
    SQL> set heading on 
    SQL> select * from dual;
    
    D
    -
    X
    
    If you want to know how many rows were selected, inserted, updated, or deleted you would use the 'set feedback on'. Likewise if you want to know how long the query took you 'set timing on'.
    SQL> set feedback on
    SQL> select * from dual;
    
    D
    -
    X
    
    1 row selected.
    
    In tables where there are big fields you sometimes only have little data and then only want sqlplus to show a certain amount of space for the non-excisting rest of the data you can experiment with the 'set linesize <number>' command.

  • Accessing an Oracle Database located on another host

    To be able to access an Oracle Database on another host you need to change your ~oracle/network/admin/tnsnames.ora and add an entry to the file like the following.
    local_SID =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
        (CONNECT_DATA = (SID = remote_SID))
      )
    
    It's defined like the local SID you want to use when starting sqlplus and the remote SID which the database has on the remote host hostname.network. You connect to the database like 'sqlplus user/pass@local_SID'.

  • commit/rollback

    Everybody makes mistakes sometimes and with the limited amount editing you can do on the sqlplus command line you will run into problems. You can accidently drop a table or delete rows. This is where commit and rollback comes in.
    SQL> rollback
    
    Will discard all changes since last commit or since you logged in or since your last ddl-command (grant/create/drop).
    SQL> commit
    
    Will save all changes to the database since your last commit.


  • Warning: include(../../../comments.php) [function.include]: failed to open stream: No such file or directory in /var/www/thomas.eibner.dk/oracle/sqlplus/index.php on line 203

    Warning: include() [function.include]: Failed opening '../../../comments.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /var/www/thomas.eibner.dk/oracle/sqlplus/index.php on line 203