What is SQL*Plus? Explain its features
SQL*plus allows SQL and PL/SQL scripts to interactively run in command line.
It allows three kinds of commands to run; SQL, PL/SQL and SQL * Plus commands. It’s most commonly used by DBA’s to interact with the oracle database.
Explain how to use the SQL*Plus utility.
In order to start this utility you can run the “sqlplus” in $ORACLE_HOME/bin directory. userid/password@db command is used to specify the connection details while @myscript is used to start executing a script.
What are the basic SQL*Plus commands?
Below are few basic commands:-
1. START- Used to run a SQL script.
2. ACCEPT- Accepts input from user.
3. GET- Gets the sql file from user to place in buffer.
4. LIST- Displays the last command executed.
5. RUN- Used to list and run the command in buffer
6. SHOW- Shows the environment settings.
Example: SHOW PAGESIZE
What is AFIEDT.BUF?
When any file is attempted to edit without any arguments (example ed or edit), the last SQL command is saved in AFIEDT.BUF. It acts like a default buffer file for edit commands.
Explain how to enable the SQL*Plus HELP facility.
Change the directory to $ORACLE_HOME/bin and run the “helpins” command to enable the HELP utility.
On executing the command @help SELECT, help command can be enabled.
Difference between ? and HELP.
Both ? and HELP are used for the same purpose. They are both used to invoke help for a particular command.
Example HELP INSERT will give help on how to use INSERT command.
Difference between @ and @@.
@ runs the script that is in the current directory or the directory as specified with a path. While @@ runs the script in the same directory as the script that called it.
Example:
SQL> @myscript1.sql
Difference between & and &&.
When a variable is defined using &, the value entered is held temporally with the variable. This means it will prompt you for a value each time it’s referenced. On the other hand when a variable is defined using &&, the value entered is held permanently with the variable. This means it will not prompt you for a value each time it’s referenced. Instead, it will use that value. && works more or less like #define.
Difference between ! and HOST.
HOST command performs variable substitution of & and && symbols while execute operating system commands as child processes of SQL*Plus, while ! will not do such substitution.
Example:
SQL> ! whoami oracle
SQL> DEFINE cmd="whoami"
SQL> HOST &&cmd
oracle
Explain how to trap errors in SQL*Plus.
In order to Trap errors in SQL*Plus, use the "WHENEVER SQLERROR" command for sql /plsql errors and “WHENEVER OSERROR “ to trap OS errors
Example:
WHENEVER OSERROR EXIT 9
How does one trace (and explain) SQL statements from SQL*Plus?
Using the Autotrace command (set autotrace=ON) before executing a query, one can trace the SQL statements. On execution of query, it displays the execution plan and statistics of number of rows processed, bytes sent and received etc.
How can one prevent SQL*Plus connection warning messages?
On executing the PUPBLD.SQL script by running the command @ PUPBLD.SQL script, the warning messages can be avoided. You need to login as SYSTEM in order to run this command.
Explain how to restore session state in SQL*Plus.
All the current settings and state can be initially saved in a file using the STORE SET command.
Example:
SQL> STORE SET filename REPLACE
On executing the file, the settings can be restored.
SQL> @filename
Can one pass operating system parameters to SQL*Plus?
Yes, one can pass operating system parameters to SQL*Plus.
Example:
sqlplus scott/tiger @a.sql '"one parameter"' dual
here, Parameter one parameter will be mapped to SQL*Plus variable &1
How does one copy data from one database to another in SQL*Plus?
COPY command can be used to copy data. It Copies data from a query to a table in the same or another database.
Example:
SQL> COPY FROM scott/tiger@dab1 TO scott/tiger@dba2 INSERT employee USING select * from employee;
Explain how to generate HTML reports from SQL*Plus.
By executing the command “set markup HTML on” once can generate HTML reports. The file can then later be modified for CSS etc.
