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.
]: Failed opening '../../../comments.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in