Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7 8 9

(Continued from previous part...)

How To Run SQL Commands in SQL*Plus?

If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:

SQL> SELECT 'Welcome!' FROM DUAL;

'WELCOME
--------
Welcome!

SQL> SELECT 'Welcome to FYIcenter.com tutorials!'
  2  FROM DUAL
  3  ;

'WELCOMETOFYICENTER.COMTUTORIALS!'
-----------------------------------
Welcome to FYIcenter.com tutorials!

How To Run PL/SQL Statements in SQL*Plus?

If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to FYIcenter!')
Welcome to FYIcenter!

PL/SQL procedure successfully completed.

How To Change SQL*Plus System Settings?

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
SET HEADING OFF - Stops displaying the header line of the query output.
SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
SET WRAP OFF - Turns off the wrapping feature when displaying query output.

How To Look at the Current SQL*Plus System Settings?

If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:

SQL> SHOW AUTOCOMMIT
autocommit OFF

SQL> SHOW HEADING
heading ON

SQL> SHOW LINESIZE
linesize 80

SQL> SHOW PAGESIZE
pagesize 14

SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows

SQL> SHOW TIMING
timing OFF

SQL> SHOW NULL
null ""

SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
...

(Continued on next part...)

Part: 1 2 3 4 5 6 7 8 9

More...