Mohammad Abdul Momin Arju Tips (SQL*Plus )

Change Prompt in SQL*Plus

Whenever you log on to SQL*Plus it shows you the prompt SQL>. However you can change it for temporary or for permanent.

To change SQL prompt permanently set environmental variable inside glogin.sql.
To set SQL prompt for a particular OS user set it inside login.sql.
To make it temporary set within SQL*Plus. Like,


To change your SQL*Plus prompt to display your connection identifier, enter:

SET SQLPROMPT "_CONNECT_IDENTIFIER > "
dbase >
To set the SQL*Plus command prompt to show the current user, enter

SET SQLPROMPT "_USER > "
ARJU >

To change your SQL*Plus prompt to display your the current date, the current user and the users privilege level, enter:

SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
01-JUN-08 ARJU >

You can also set a variable as you wish
set SQLPROMPT "JUST For Fun>"
JUST For Fun>

Text in nested quotes is not parsed for substitution. To have a SQL*Plus prompt of your username, followed by "@", and then your connection identifier, enter:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

How to set Environmental variable to SQL*Plus automatically

When SQL*Plus starts up, it looks for a global login script called glogin.sql in the $ORACLE_HOME/sqlplus/admin directory. If found, this script will be executed. If you want to set your SQL*Plus environmental variable like PAGESIZE or LINESIZE or SQLPROMPT then you can set within the file glogin.sql. Then all of the database users who use SQL*Plus will be affected of this site profile glogin.sql.

After checking site profile oracle checks for user profile named login.sql in the user's home. If you want to set environmental variable for a particular Oracle user then you can use user profile named login.sql. Other users of the OS will not be affected. You will create login.sql, put environmental variables inside it and place it to the user's home location.

In the following section I demonstrate the procedure with an example.

AT first I connected to database without any login.sql file. And then it displays prompt as SQL>
-bash-3.00$ sqlplus arju/a

SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 31 23:27:34 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Now I am creating login.sql and put an entry of sql prompt which will show username@database_name SQL>
SQL> !vi login.sql
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"

SQL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Now connect to database and see the prompt.
-bash-3.00$ sqlplus arju/a
SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 31 23:28:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ARJU@dbase SQL>

Along with connection identifier you set set other environmental variables like pagesize linesize for a user or globally.

Sql*plus Basics and Tips

The Sql Buffer
The Sql buffer stores most recently SQL command or most recently PL/SQL block.

SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters
you type to execute a command in the SQL buffer.

You can execute the command or block in the SQL buffer using the RUN or /(slash)
commands.

RUN commands displays the command or block in the buffer and then execute it.
/(slash) executes the command or block in the buffer but does not display it.

Below is the example.
A simple SQL command.
SQL> select sysdate from dual;
SYSDATE
---------
29-NOV-08

This one (Setting linesize) is SQL*plus command and hence is not stored in SQL buffer.
SQL> set linesize 100

Invoking list will show SQL buffer, not SQL*plus command.

SQL> list
1* select sysdate from dual

Run will execute the command after displaying it.
SQL> run
1* select sysdate from dual

SYSDATE
---------
29-NOV-08

/(Slash) will not display the command. It will only execute it.
SQL> /

SYSDATE
---------
29-NOV-08

Continuing a Long SQL*Plus Command on Additional Lines
Suppose you want to write a long sql*plus command or a sql command, a long identifier or a long word in the SQL*plus. You can put them in one line by entering hyphen(-) at the end and then entering a carriage return. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.

So hyphen(-) in Sql*plus is considered as continuation character. For example to format a long column you can use hyphen in sql*plus.
SQL> column salary format 99,999 -
> heading 'Monthly Salary'

Similarly,
SQL> select 200--
> 100 from dual;

200-100
----------
100
SQL> select 200-
> -100 from dual;

200-100
----------
100
Hyphen at the last is considered as continuation character. So hyphen at the last is ignored. Hence following will return error.
SQL> select 200-
> 100 from dual;
select 200 100 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Stopping a Command while it is Running
In order to stop displaying a long running output on Sql*plus press CTRL+C.

Displaying output page by page in Sql*plus
Normally whenever we run any SQL comamnd the output of it came all in a row. If the command return 1000 rows then it shows all on the console. You may also could not be able previous output by scrolling. This may be annoying. In order to display output page by page you have to set two variable.

SET PAGESIZE 30 --which will say in a page how many lines will be displayed.
SET PAUSE ON --which will pause the output to display unless you press enter.

Now run sql command like SELECT OBJECT_NAME FROM DBA_OBJECTS; and proceed further after pressing enter. Every new pressing of ENTER keyword will display you a new page.


Saving Changes to database automatically
With the Sql*plus variable AUTOCOMMIT option you can decide when to commit and when to not. By default it is set to OFF which means committing transaction is done whenever you give COMMIT explicitly. If you want committing will happen immediately then invoke,
SET AUTOCOMMIT ON

In order to do committing after 10 DML statements do,
SET AUTOCOMMIT 10
To turn the autocommit feature off again, enter
SET AUTOCOMMIT OFF

Sql*plus Error messages
Sql*plus errors begin will letter SP2.
Like,
SQL> @testing.sql
SP2-0310: unable to open file "testing.sql"

Sql*plus copy command error messages start with COPY.

What is the difference between $ ! and HOST?

Actually in terms of functionality there is no difference between $,! and host. They all perform the same task which is to execute Operating system command. Whenever we write any one of these inside SQL*Plus, SQL*Plus will execute operating system commands as child processes of SQL*Plus. Individually enter anyone of these like only host or ! will enter you into a OS prompt. But it does not affect the current SQL*Plus session. After you terminate the session you again enter to SQL*session.

Example:
-----------------------------------
SQL> host
bash-3.00$ hostname
neptune
bash-3.00$ exit
exit

SQL> --SQL prompt is returned

The host command is more generic. You can use it on any platform.
The ! command is for unix operating system only.
The $ command is for OS/390, VMS, and Windows OS only.

Example:
-------------------
SQL> !pwd
/export/home/oracle

for more please refer:
http://arjudba.blogspot.com/