- What is SQL*Plus?
Answer: SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ship with the Oracle Database Server.It can be used interactively or driven from scripts.
Although SQL*Plus's predecessor was called UFI (User Friendly Interface), its interface is quite primitive.
- What commands can be executed from SQL*Plus?
Answer: You can enter three kinds of commands from the SQL*Plus command prompt:
- SQL*Plus commands
- SQL commands
- PL/SQL blocks
- What is the basic SQL*Plus commands?
||Declare a variable (short: DEF)
||Lists the attributes of tables and other objects (short: DESC)
||Places you in an editor so you can edit a SQL command (short: ED)
|EXIT or QUIT
||Disconnect from the database and terminate SQL*Plus
||Retrieves a SQL file and places it into the SQL buffer
||Issue a operating system command (short: !)
||Displays the last command executed/ command in the SQL buffer (short: L)
||Display a text string on the screen.Eg prompt Hello World!!!
||List and Run the command stored in the SQL buffer (short: /)
||Saves command in the SQL buffer to a file.Eg "save x" will create a script file called x.sql
||Modify the SQL*Plus environment eg.SET PAGESIZE 23
||Show environment settings (short: SHO).Eg SHOW ALL, SHO PAGESIZE etc.
||Send output to a file.Eg "spool x" will save STDOUT to a file called x.lst
||Run a SQL script file (short: @)
- What is AFIEDT.BUF?
Answer: AFIEDT.BUF stands for A FIle EDiting BUFfer.
AFIEDT.BUF is the SQL*Plus default edit save file.When you issue the command "ed" or "edit" without arguments, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
- What is the difference between @ and @@?
Answer: The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts.
A single @ symbol runs the script relative to the current directory, the double at (@@) runs a command file relative to the directory of the current script
- What is the difference between & and &&?
Answer: "&" is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.
"&&" is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement.
- What is the difference between ! and HOST?
Answer: Both "!" and "HOST" will execute operating system commands as child processes of SQL*Plus.
The difference is that "HOST" will perform variable substitution (& and && symbols), whereas "!" will not.
- How can I trap errors in SQL*Plus?
Answer: Use the "WHENEVER OSERROR..." to trap operating system errors and the "WHENEVER SQLERROR..." command to trap SQL and PL/SQL errors.
SQL> WENEVER OSERROR EXIT 9
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
- Can I prevent users from executing devious commands?
Answer: Yes, command authorization is verified against the SYSTEM.PRODUCT_USER_PROFILE table.
This table is created by the V7PUP.SQL and PUPBLD.SQL scripts.
Note that this table is not used when someone signs on as user SYSTEM.
Eg.to disable all users whose names starts with OPS$ from executing the CONNECT command:
SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%', 'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);
- How can I disable SQL*Plus formatting?
Answer: SET ECHO OFF
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF