SQL*Plus

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.
Oracle database tables
Oracle database tables - What is a Database Table?, How many types of tables supported by Oracle? Explain them, Can you explain how to create a aew table in your schema?, Explain with an example how to create a new table by selecting rows from another table, Depict a sample script for renaming an existing table...
Oracle constraints FAQs
Oracle constraints - Oracle constraints - overview. What are the types of constraints avaialable in oracle, Explain Oracle Check constraint, Explain Not Null constraint, primary key constraint, oracle foreign key constraint, unique Constraint...
Restricting and sorting data in oracle
Restricting and sorting data in oracle - Explain how to limit the rows that are retrieved by a query, Explain how to sort the rows that are retrieved by a query, What are the comparison operators in oracle. List them with description...
Post your comment
Discussion Board
DBA
i and my all freinds share . This is very good
praween kumar 01-26-2013

You may like