Friday, February 12, 2016

Tips on Tools Usage - SqlPlus

Editing a previously executed sql statement:

Type ed in the sqlplus. Then sqlplus will open the previous command in an editor (e.g: in vi). Then you can edit the sql command and save it using Esc key + :wq as you normally does in "vi" editor.
Then type / to run the edited command.

Executing the previous sql statement:

Type / or r in the sqlplus terminal to run the previously executed sql statement again.

Show the previously executed sql statement without executing it:

Type "list" in the sqlplus terminal.

Formatting of the sql output:

Change the width of a column in the sql output, for a character field:

column <filed_name> format a<width_number_of_characters>

E.g:
column user_name format a100

Change the width of a column in the sql output, for a numeric field:

column <field_name> format <number_format>

E.g:
column salary format 9999.99

Set the line size of the sql output:

set linesize <line_size>

E.g:
set linesize 300

More Details:

http://www.comp.nus.edu.sg/~ooibc/courses/sql/sqlplus.htm
http://ugweb.cs.ualberta.ca/~c391/manual/chapt4.html
http://www.orafaq.com/wiki/SQL*Plus


~~~


No comments: