How to Rollback an Uncommitted DML in SQL*Plus

Goal
In SQL*Plus, by default the DML (data manipulation language) transaction is committed to the database
upon exit.
Is there a way to rollback the transaction upon exit or prior to exiting the SQL*Plus session if the commit
has not been explicitly defined?
Solution
From a SQL*Plus standpoint, there are 1 of 2 options:
OPTION 1:

1. Use the following command prior to issuing any DML:

SQL> set transaction use rollback segment test1;

... where test1 = a name you wish to rollback to should you want to reverse the changes back to
this point

2. Before exiting, issue the rollback command:

SQL> rollback;

OPTION 2:

1. In the glogin.sql located in the $ORACLE_HOME/sqlplus/admin directory, add the below:

Set transaction use rollback segment &rollback_segment;

2. When entering SQL*Plus, you will be prompted for a name --

Enter value for rollback_segment: test1

NOTE: If you do not want to be prompted, then set a static name in place of the &rollback_segment
in step 1, like:

set termout off

set transaction use rollback segment test1;
set termout on

3. Then, if you wish to reverse the DML that has not been committed, then before exiting issue the
rollback command:

SQL> rollback;
How to Delete Line Feeds and Carriage Returns from Varchar2 Data?
Goal
How to delete carriage returns and line feeds contained within the varchar2 field data?
Solution
You can perform the following nested replace sql statement using the replace command to delete the
linefeeds and the carriage returns.

SELECT REPLACE (REPLACE (column name, CHR (13), ‘’), CHR (10), '')
FROM tablename;

How to Setup Date Format or Specific Variables in iSQL*Plus 9i?

Goal
How to set NLS_DATE_FORMAT in iSQL* plus 9.2?
Solution
A- For a session you can user command:

alter session set NLS_DATE_FORMAT...

B- To set it permanently:
1- Modify the $ORACLE_HOME/sqlplus/admin/isqplus.conf file
to add -initial-env for NLS_LANG and NLS_DATE_FORMAT as follow:

<IfModule mod_fastcgi.c>
FastCgiServer /emea/rdbms/32bit/app/oracle/product/9.2.0/bin/isqlplus -initial-env SHLIB_PATH
-initial-env LD_LIBRARY_PATH -initial-env ORACLE_HOME -initial-env ORACLE_SID -initial-env
TNS_ADMIN -initial-env NLS_LANG -initial-env NLS_NCHAR -initial-env NLS_LANG -initial-env
NLS_DATE_FORMAT -initial-env iSQLPlusNumberOfThreads=20 -initial-env
iSQLPlusTimeOutInterval=30
-initial-env iSQLPlusLogLevel=off -initial-env iSQLPlusAllowUserEntMap=none -idle-timeout 3600
</IfModule>

2- Set ENV variable at UNIX level NLS_LANG and NLS_DATE_FORMAT.

3- Restart apache.
4- Start sqlplus and the Date format will match NLS_DATE_FORMAT value.

What Is the Command that Replaces ''Spool'' in iSQL*Plus?

Goal
Spool command is no longer a valid command in iSQL*Plus. What is the new command to replace it?
Solution
iSQL*Plus works in a different way to generate the output to file. The file output is html instead of text.
Review iSQL*Plus -> Help -> The iSQL*Plus User Interface -> Output. It states:

File: when the contents of the input area are executed, the resulting output is saved to a file. You are
prompted to enter the name of the file. As the output is in HTML format, it is useful to give the saved
output file a .htm or .html extension.

Steps to generate the output to file:

1) Invoke iSQL*Plus:
http://host:port/isqlplus
2) In the User Interface, select output=file.
2) Select 'Execute' button.
3) Save the file. This is an html file.
4) Open the html file using your browser.

Commands Not Supported in iSQL*Plus

Goal

This article lists SQL*Plus commands not supported in the iSQL*Plus user interface. Attempting to use
any of the following unsupported commands or command options raises an SP2-0850 error message.

Solution

The following commands have no context in iSQL*Plus and have not been implemented.

ACCEPT PASSWORD
CLEAR SCREEN PAUSE

The following SET command variables have no context in iSQL*Plus and have not been implemented.

SET EDITFILE SET SQLBLANKLINES SET TAB

SET FLUSH SET SQLCONTINUE SET TERMOUT
SET NEWPAGE SET SQLNUMBER SET TIME
SET PAUSE SET SQLPREFIX SET TRIMOUT
SET SHIFTINOUT SET SQLPROMPT SET TRIMSPOOL
SET SHOWMODE SET SUFFIX

The following commands have security issues on the middle tier and have not been implemented.

GET SPOOL
HOST STORE

The following commands are SQL buffer editing commands which are not relevant in iSQL*Plus and
have not been implemented.

APPEND DEL INPUT

CHANGE EDIT SAVE
List/Table of General SQL*Plus 9.2 Limits

Goal

The purpose of this article is to provide a list of general SQL*Plus limits. The limits shown are valid for
most operating systems.

Solution

Item Limit
filename length system dependent
username length 30 bytes
user variable name length 30 bytes
user variable value length 240 characters
command-line length 2500 characters
length of a LONG value entered through SQL*Plus LINESIZE value
LINESIZE system dependent
LONGCHUNKSIZE value system dependent
output line size system dependent
line size after variable substitution 3,000 characters (internal only)
number of characters in a COMPUTE command
500 characters
label
number of lines per SQL command 500 (assuming 80 characters per line
maximum PAGESIZE 50,000 lines
60,000 characters for VMS; otherwise, 32,767
total row width
characters
maximum ARRAYSIZE 5000 rows
maximum number of nested scripts 20 for VMS, CMS, Unix; otherwise, 5
maximum page number 99,999
maximum PL/SQL error message size 2K
maximum ACCEPT character string length 240 Bytes
maximum number of DEFINE variables 2048

How to Get US Eastern Time No Matter of the Time Zone Location?

Goal

The goal of this document is to provide one way of getting US Eastern Time No Matter of the Time Zone
Location.

Solution
If Oracle 9i or later is used then use the following sql statement:

SELECT CURRENT_TIMESTAMP AT TIME ZONE '-5:00'

AS eastern_time
FROM dual;

That gives you a TIMESTAMP. If a regular DATE is needed then CAST it as shown below:

SELECT CAST (CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS DATE)

AS eastern_time
FROM dual;

How to Retrieve the Select Statement Used to Create a View?

Problem Description

How can you see the SELECT statement used to create a view?
When you try selecting the TEXT field from USER_VIEWS, you are not able to see the entire SELECT
statement.

Problem Solution

In SQL*Plus, you must first execute SET LONG n, where n is an integer value greater than or equal to
the value of the TEXT_LENGTH field in USER_VIEWS.

For example:

SQL> set long 999

SQL> select text from user_views
where view_name like 'MYVIEW';

You should now be able to see a complete SELECT statement used to create MYVIEW.

How to Restrict User Access to Database Instances in iSQL*Plus?

Goal

The article is intended to provide information about how to restrict user database instance access from
iSQL*Plus.
You may want to limit the databases instances that users can access in iSQL*Plus to a restricted list.
When restricted database access has been enabled, a dropdown list of available databases is displayed in
place of the Connection Identifier text field on the Login screen. This enables greater security for
iSQL*Plus Servers in hosted environments.

Solution

Connection identifiers are listed in the order defined in iSQLPlusConnectIdList.

Edit the $ORACLE_HOME/oc4j/j2ee/oc4j-applications/applications/isqlplus/isqlplus/WEB-INF/web.xml

file to restrict database access to iSQL*Plus users. Change the following entry to include a new param-
value element which contains the list of databases to which you want to restrict access.

For example, to only have ora10g and ora9i instance available:

NOTE: A period is added to the text for readability. Do not include in the web.xml

.<init-param>
.<param-name>iSQLPlusConnectIdList</param-name>
.<param-value>ora10g;ora9i</param-value>
.<description>The database(s) to which iSQL*Plus users are restricted. The list should contain the Oracle
SIDs or SERVICE_NAMEs, separated by a semicolon (;). If there are no entries, database access is not
restricted through iSQL*Plus.</description>
.</init-param>

Entries in the param-value element should be identical to the alias for SERVICE_NAMEs or SIDs set in
your $ORACLE_HOME/network/admin/tnsnames.ora file.

Connection identifiers are listed in the order defined in iSQLPlusConnectIdList.

For Oracle 9i release 2 make the following changes in $ORACLE_HOME/sqlplus/admin/isqlplus.conf

file

Change the following line

FastCgiServer <ORACLE_HOME>\bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20

-initial-env iSQLPlusTimeOutInterval=2 -initial-env iSQLPlusLogLevel=off -initial-env
iSQLPlusAllowUserEntMap=none -idle-timeout 3600

To

FastCgiServer <ORACLE_HOME>\bin\isqlplus -port 8228 -initial-env iSQLPlusNumberOfThreads=20

-initial-env iSQLPlusTimeOutInterval=2 -initial-env iSQLPlusLogLevel=off -initial-env
iSQLPlusAllowUserEntMap=none -initial-env iSQLPlusConnectIdList=Ora9iDB,Ora10gDB -idle-
timeout 3600

Restart iSQL*Plus for your changes to take effect.

Connection identifiers are case insensitive, and each connection identifier listed in the argument should be
identical to an alias in the tnsnames.ora file.
If no connection identifier is given, or if the one given does not match an entry in
iSQLPlusConnectIdList, the database connection is refused and the following error occurs:

SP2-0884: Connection to database database_name is not allowed

How to Output Two Records from a Single Record Depending on the Column
Value?
Goal
One of the column in a table has three valid values e.g. column value (name: cell) is 1, 2 and 3. The value
3 indicates that the record is both 1 and 2.

When you select the records along with other values, you will get all the three values (1, 2 and 3) in select
statement. Now in the output value = 1 or 2 the single is ok. But for value = 3, you want to create two
records that display values 1 and 2 (value for cell).

Actual output with standard select is as follows:

ID CELL
----- --------
721 1
722 2
723 3
and the required output is as follows :

ID CELL
----- --------
721 1
722 2
723 1
723 2

How to split and resolve this in SQL?

Fix

The following SQL statement to achieves the desired output:

select id, decode(cell, 3, 1, cell) cell from <your_table>

union all
select id, 2 from <your_table> where cell = 3

How to Exit with a Rollback from SQL*Plus

Goal

How to exit sqlplus with a rollback?

Fix

To prevent SQL*Plus from automatically committing all changes, such as INSERT, UPDATE, or
DELETE command, you can use the 'ROLLBACK' or 'EXIT ROLLBACK' command before exiting.

The SQL*Plus User Guide and Reference states the following:

EXIT
Purpose
Terminates SQL*Plus and returns control to the operating system.
Syntax
{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]

By default the exit is with commit.

To rollback you must do:

exit rollback

How to Generate Numbers 0 to N from a Query without PL/SQL?

Goal

How to generate numbers 0 to N from a query without any PL/SQL

Fix

select rownum-1 from ( select 1 from dual group by cube (1,1,1,1,1,1))

where rownum<&maxlimit+2

The logic behind this query is that the CUBE is generating 2^n rows where n is the number of arguments
to the CUBE function. In this case the inner subquery generates 2^6 rows i.e. 64 rows which means you
can generate numbers up to 63.

In case you want to generate numbers beyond this range, then you need to add one more argument to the
CUBE which would increase it to 2^7 rows i.e. 128 rows.

select rownum-1 from ( select 1 from dual group by cube

(1,1,1,1,1,1,1)) where rownum<&maxlimit+2

The range can be increased by increasing the number of arguments to the CUBE function.

How to Hide the Password Running a Script as a Different User?

Goal

How to hide a password to execute a script?

For example, connected as user2 you need to execute a script querying objects from Scott's schema like
select * from dept.

Fix

1. Create hide_pswd.sql script using hide option.

It will display the prompt "Password: ", place the reply in a char variable named hide_password, and
suppress the display:
accept hide_password char prompt 'Password: ' hide
connect scott/&&hide_password@aliasdb
/
select * from dept
/

2. This will prompt for user password and will show it in hide mode (* instead of password):
SQL> start hide_pswd.sql
SQL> accept hide_password char prompt 'Enter password: ' hide
Enter password: *****
SQL> connect scott/&&hide_password@aliasdb
Connected.
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

How to Generate A Delimiter in Spool Output (Excel CSV file)?

Goal

How to generate a delimiter in spool output (Excel .csv files)?

Fix

In order to generate a delimited file output, you need to concatenate columns using the desired delimiter
i.e. comma.

Example:

select empno|| ','||ename||'&'||mgr from X;

Other option is using:

SQL> set colsep ','
SQL> spool c:\testexcel.csv
SQL> select * from emp;
Change some of the default SQL*Plus parameters, that will be garbage for Excel:

feedback=off
newpage=none
termout=off
header=off

If some columns are empty, be aware to include the delimiter, too:

nvl(to_char(col2),',')
How to Select N-th Highest Value from a Table?

Goal

This document provides the SQL statement which can be used to find the N-th highest or lowest value
from a table column of NUMBER datatype. Also, provides the N-th highest or lowest DISTINCT value of
a table column.

For example:

- select the 5th highest salary record from the EMP table.
- select the 5th highest salary DISTINCT value from the EMP table.
- select the 5th lowest salary record from the EMP table.
- select the 5th lowest salary DISTINCT value from the EMP table.

Fix

Given the following data:

SQL>SELECT SAL FROM EMP

ORDER BY SAL DESC;

SAL
----------
5000
3000
3000
2975
2850 <--- Top 5th RECORD
2450 <--- Top 5th DISTINCT VALUE
1600
1500
1300 <--- Bottom 5th DISTINCT VALUE
1250 <--- Bottom 5th RECORD
1250
1100
950
800

1. The general syntax to retrieve the TOP N-th record is :

1.A. Select MIN(column_name) from (select column_name from table_name
order by column_name desc) where rownum<= N;
For example, the following can be used to find the 5th highest record from the EMP table:
SELECT MIN(sal) from (select sal from emp order by sal desc) where
rownum <= 5;
MIN(SAL)
 ----------
2850

1. B. A method without using MIN () to find the top 5-th record is:
SELECT distinct (temp.sal) FROM EMP temp WHERE 5= (SELECT COUNT
(tmp.sal) FROM EMP tmp WHERE temp.sal<=tmp.sal);
SAL
----------
2850

2. The general syntax to retrieve the TOP N-th DISTINCT VALUE is :

2.A. Select MIN(column_name) from (select distinct column_name from
table_name order by column_name desc) where rownum<= N;
For example, the following can be used to find the 5th highest distinct salary from the EMP table:
Select MIN(sal) from (select distinct sal from emp order by sal
desc) where rownum <= 5;
MIN(SAL)
----------
2450

2. B. A method without using MIN () to find the top N-th DISTINCT VALUE is:
SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT
(DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal<=tmp.sal);
SAL
----------
2450
3. The general syntax to retrieve the N-th RECORD from the BOTTOM is as follows:
3.A. Select MAX(column_name) from (select column_name from table_name
order by column_name ) where rownum<= N;
For example, the following can be used to find the 5th lowest RECORD from the EMP table:
SELECT MAX(sal) from (select sal from emp order by sal) where rownum
<= 5;
MAX(SAL)
----------
1250
3.B. A method without using MAX () to find the bottom N-th RECORD is:
SELECT distinct (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT
(tmp.sal) FROM EMP tmp WHERE temp.sal>=tmp.sal);
SAL
----------
1250
4. The general syntax to retrieve the N-th DISTINCT VALUE from the BOTTOM is as follows:
4.A. Select MAX(column_name) from (select distinct column_name from
table_name order by column_name ) where rownum<= N;
For example, the following can be used to find the 5th lowest DISTINCT value from the EMP table:
Select MAX(sal) from (select distinct sal from emp order by sal) where
rownum <= 5;
MAX(SAL)
----------
1300
4.B. A method without using MAX() to find the lowest N-th DISTINCT VALUE is:
SELECT DISTINCT (temp.sal) FROM EMP temp WHERE 5 = (SELECT COUNT
(DISTINCT (tmp.sal)) FROM EMP tmp WHERE temp.sal>=tmp.sal);
SAL
----------
1300

HOW TO MAKE THE SELECT STATEMENTS ABOVE MORE DYNAMIC?

In examples 1.A., 2.A., 3.A., and 4.A., use the following modification to the query:
From
… where rownum <= 5;
To
… where rownum <= &N;

In examples 1.B., 2.B., 3.B., and 4.B., use the following modification to the query:
From
… WHERE 5 = (SELECT COUNT…
To
…WHERE &N = (SELECT COUNT…

This change will cause the following prompt for the user:
Enter value for n: <user puts in value>

How to Stop 'Connected' Message when Running SQLPlus in Silent Mode?

Goal

When you start sqlplus in silent mode, you still get the connected message.

Example:
sqlplus -S /nolog
conn /
Connected.

How to stop the "Connected." from appearing?

Fix

1. Do not use the "/nolog" option. This is causing 2 connections. It is the second connection that is
echoed. Instead start sqlplus as:

sqlplus -s /

2. If you need the /nolog, then use grep to exclude the Connected line as follows:

sqlplus -S /nolog | grep -v Connected

How to use New Predefined Variables to Set SQLPROMPT to Display the User,
Date and/or Privileges?

Goal

To explain the new predefined variables introduced in 10g and how they can be used to make the
SQL*PLUS Prompt display more informative.

These variables are available only in 10g.

Fix

You can now use substitution variables in the SQL*Plus command-line prompt to display, for example,
the database and server you are connected to, or other information available through a substitution
variable you choose.

There are four new predefined variables:

Predefined Variables: _DATE, _PRIVILEGE, _USER, _CONNECT_IDENTIFIER

_DATE contains the current date or a user defined fixed string.

_PRIVILEGE contains the privilege level of the current connect. This will be either AS SYSDBA, AS
SYSOPER or blank to indicate a normal connection.

_USER contains the username as supplied by the user to make the current connection. This is the same as
the output from the SHOW USER command.

_CONNECT_IDENTIFIER contains the connection identifier information.

These variables can be accessed like any other substitution variable. For example, they could be used in
TTITLE, in '&' substitution variables,

They can used in your SQL*Plus command line prompt by using the SET SQLPROMPT command.
For example, to make your prompt always show your username (_USER), the @ symbol, and then your
connection identifier (_CONNECT_IDENTIFIER) during your session, enter:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

You can view the predefined variable definitions in the same way as you view other DEFINE definitions,
using the DEFINE command with no arguments, or with the specific argument you wish to display, for
example:

Using DEFINE to display these variables:

SQL> DEFINE
DEFINE _DATE = "16-MAR-04" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "isc101" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
---
---
or to display on one variable:
SQL> DEFINE _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

Uses UNDEFINE to remove variable definitions.

How to use these variables with SQLPROMPT?

SET SQLP [ROMPT] {SQL> | text}

Note: SET SQLPROMPT is not supported in iSQL*Plus

Variable substitution occurs each time SQLPROMPT is SET. If SQLPROMPT is included in glogin.sql,
then substitution variables in SQLPROMPT are refreshed with each login or connect.

You need the 'Select Any Table' privilege to successfully run the following example scripts.

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

SQL> SET SQLPROMPT "_CONNECT_IDENTIFIER > "
isc101 >

2. To set the SQL*Plus command prompt to show the current user, enter
SQL> SET SQLPROMPT "_USER > "
SCOTT >

3. To change your SQL*Plus prompt to display your the current date, the current user and the users
privilege level, enter:
SQL> SET SQLPROMPT "_DATE _USER _PRIVILEGE> "
17-MAR-04 SCOTT >

4. To change your SQL*Plus prompt to display a variable you have defined, enter:
SQL> DEFINE Instance = Prod
SQL> SET SQLPROMPT Instance> "
Prod>

5. Since 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:
SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
SCOTT@isc101 >

At present there is no predefined variable to display the database you are connected to.You can use the
notes referenced below for the same.

How To Interpret The Time Format

• goal: How To Interpret The Time Format

• fact: Oracle Server - Enterprise Edition 7
• fact: Oracle Server - Enterprise Edition 8
• fact: MS Windows NT
 • fact: SqlPlus

Fix:

SQL> set timing on

After the SQL statement is executed the time needed for processing the
statement is printed into the next line.

Interpretation:
Elapsed: 00:00:00.32
^ ^ ^ ^
| | | |
| | | milliseconds
| | seconds
| minutes
hours

SPECIAL NOTE:
The seconds field will represents the TOTAL TIME in seconds.
Abstract: ELAPSED TIME SHOWS INCORRECTLY FOR DBMS_LOCK.SLEEP VALUES GREATER
THAN 1 MINUTE

The seconds overflow (60 seconds = 1 minute) will not reset the seconds counter
so it's possible that the seconds field gets more than 2 digits (>60)!

Example: Elapsed: 01:05:3909.32

To find the actual seconds, the Hours and Minutes must be removed from the total.
Above example:
hours: 1
minutes: 5
seconds: 3909.35 - (1*3600) - (5*60) = 9.32
Converts to:
hours: 1
minutes: 5
seconds: 9
milliseconds: 32

How to Display a Master Detail Relationship in One Row?

PURPOSE

How to display columns as rows?

This is similar to other line-column transposition that may be in use. But here we use outer join to achieve
this.

SCOPE & APPLICATION

Developers and DBAs (For reporting)

SQL> create table parent (ChildNo varchar2(5), parent varchar2(20));

insert into parent values ('E0001', 'FATHER-JOHN');

insert into parent values ('E0001', 'MOTHER-NANCY');
insert into parent values ('E0002', 'FATHER-BLAKE');
insert into parent values ('E0002', 'MOTHER-SALLY');
insert into parent values ('E0003', 'FATHER-WILLIAM');
insert into parent values ('E0004', 'MOTHER-JULIE');

SQL> select * from parent;

CHILD PARENT
----- --------------------
E0001 FATHER-JOHN
E0001 MOTHER-NANCY
E0002 FATHER-BLAKE
E0002 MOTHER-SALLY
E0003 FATHER-WILLIAM
E0004 MOTHER-JULIE

SQL> select distinct c.childno, a.father, b.mother from

2 (select childno, parent father from parent where parent like
'FATHER%') a,
3 (select childno, parent mother from parent where parent like
'MOTHER%') b,
4 parent c
5 where c.childno=a.childno(+)
6* and c.childno=b.childno(+)

CHILD FATHER MOTHER

----- -------------------- --------------------
E0001 FATHER-JOHN MOTHER-NANCY
E0002 FATHER-BLAKE MOTHER-SALLY
E0003 FATHER-WILLIAM
E0004 MOTHER-JULIE

How To Calculate The Number of Days betweeen Two Dates Excluding

Weekends?

• Goal: How to calculate the number of days betweeen two dates excluding weekends
• fact: Embedded SQL

Fix:

A table called DATETABLE has two columns BEGDATE and ENDDATE:-

rem Calculates the difference between two dates and gives you a number
excluding weekends.
rem This part calculates the difference in whole weeks and then
subtracts out the weekends.
rem
rem (trunc(enddate,'D') - trunc(begdate+6,'D'))
rem - (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2)
rem
rem This part calculates the number of work days in the end week.
rem
rem +
decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0)
rem
rem This part calculates the number of work days in the start week
rem minus Sunday start day workaround.
rem
rem +
decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0)
rem days

eg.
select
(trunc(enddate,'D') - trunc(begdate+6,'D'))
- (((trunc(enddate,'D') - trunc(begdate+6,'D'))/7)*2)
+ decode(to_number(to_char(enddate,'D')),3,1,4,2,5,3,6,4,7,5,0)
+ decode(to_number(to_char(begdate,'D')),2,5,3,4,4,3,5,2,6,1,0)
days
from datetable
/

Setting the SQL*Plus Prompt to Show Instance Name (SID)

How to set the SQL*Plus prompt to show your SID.

set termout off

col x new_value y
select rtrim(instance,chr(0)) x from v$thread;
set sqlprompt '&y SQL> '
set termout on

Remember this is NOT dynamic. You need to reset it when you change instances, i.e. when you use the
connect command to logon to another instance.

To successfully run the above script, you need the Select Any Table privilege.

NOTE: from version 9.2 this syntax can be used to

SET SQLPROMPT "&_CONNECT_IDENTIFIER>"
How to Insert a Carriage Return in the Middle of a Select Statement
Goal

Trying to do a long select that will ultimately result in 3 lines of output separated by carriage returns.
For Example:
Select 'D',val1, val2, '^M', 'F',val1, val3,'^M','A',val1,val4,'^M'
from value_table.

Pipes will be used instead of comma delimiters and the output will need to be looked like this:
D|val1|val2|valx|
F|val1|val3|valx|
A|val1|val4|valx|

Fix

Basically, carriage return --> CHR(10) but has to be used with the concatenate symbol '||'.

TESTCASE 3 below is probably the closest to what you are looking for:

For Example:
SQL> ed
Wrote file afiedt.buf

1 select empno||','||ename||','||chr(10)||job
2* from emp
SQL> /

TESTCASE DETAILS:
TESTCASE 1:

SQL> ed
Wrote file afiedt.buf

1 select empno||chr(10)||ename||chr(10)
2* from emp
SQL> /

EMPNO||CHR(10)||ENAME||CHR(10)||JOB
------------------------------------------
7369
SMITH
CLERK

7499
ALLEN
SALESMAN

7521
WARD
SALESMAN

EMPNO||CHR(10)||ENAME||CHR(10)||JOB
------------------------------------------
7566
JONES
MANAGER

7654
MARTIN
SALESMAN

7698
BLAKE

EMPNO||CHR(10)||ENAME||CHR(10)||JOB
------------------------------------------
MANAGER

7782
CLARK
MANAGER

7788
SCOTT
ANALYST
...
etc

TESTCASE 2:
==========
SQL> ed
Wrote file a

1 select empno||','||ename||','||job||chr(10)
2* from emp
SQL> /

EMPNO||','||ENAME||','||JOB||CHR(10)
--------------------------------------------------------
7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7788,SCOTT,ANALYST
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK

EMPNO||','||ENAME||','||JOB||CHR(10)
--------------------------------------------------------
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK

14 rows selected.

TESTCASE 3:
Closest to the format -- D|val1|val2|valx|
-- would be something like this:

SQL> ed
Wrote file afiedt.buf

1 select empno||','||ename||','||chr(10)||job
2* from emp
SQL> /

EMPNO||','||ENAME||','||CHR(10)||JOB
----------------------------------------------------------
7369,SMITH,
CLERK

7499,ALLEN,
SALESMAN

7521,WARD,
SALESMAN

7566,JONES,
MANAGER
...
etc

How to show the values of a column in a row for both previous/current record

Goal

How To show the values of a column in a row for both previous/current record ?

Suppose a customer wants to show the

1) Profit of the company in the current quarter and Last quarter in same row. .
2) Profit of the company in the current quarter and Next quarter in same row. .

Fix

Let us assume customer has the following table structure.

SQL> desc QR_Profit

Name Null? Type
----------------------------------------- -------- -------------------
YRQR DATE
PROFIT NUMBER

SQL> select * from QR_Profit;

YRQR PROFIT
--------- ----------
31-DEC-02 10
31-MAR-03 11
30-JUN-03 12
30-SEP-03 12

Following query will show the " profit for current quarter and Last quarter in same row "

SQL> select yrqr , profit,lag(profit,1,0) over (order by yrqr)

Last_Quarter from QR_Profit; ;

YRQR PROFIT LAST_QUARTER

--------- ---------- ------------
31-DEC-02 10 0
31-MAR-03 11 10
30-JUN-03 12 11
30-SEP-03 12 12

Following query will show "Profit in the current quarter and Next quarter in same row. ".

SQL> select yrqr, profit,lead(profit,1,0) over (order by yrqr)

Next_Quarter from QR_Profit;

YRQR PROFIT NEXT_QUARTER

--------- ---------- ------------
31-DEC-02 10 11
31-MAR-03 11 12
30-JUN-03 12 12
30-SEP-03 12 0

How to Create a Matrix Report in SQLPLUS

Goal

When you query any table in relational database, the datas are represented as row format. That means
each record in the table is represented in row. But to analyze the data the above format would not be
enough , there you need data in 3-dimensional format i.e. to represent in rectangular format ( popularly
know as matrix format )

Where you will have a row attribute, column attribute and cell which shows the values for the
corresponding row and column attribute .
Eg.

Col1 Col2 Col3 Col4

Row1
Row2
Row3
Row4

How to achieve the same in sqlplus without using any other tools?

Note :- The same output format can be easily designed thru Oracle Reports writer .

Fix

Here I am showing an example shows the no.of people joined in a department on year basis. The query is
based on Scott.EMP table.

SQL> select empno,deptno,hiredate from emp;

EMPNO DEPTNO HIREDATE

---------- ---------- ---------
7369 20 17-DEC-80
7499 30 20-FEB-81
7521 30 22-FEB-81
7566 20 02-APR-81
7654 30 28-SEP-81
7698 30 01-MAY-81
7782 10 09-JUN-81
7788 20 19-APR-87
7839 10 17-NOV-81
7844 30 08-SEP-81
7876 20 23-MAY-87

EMPNO DEPTNO HIREDATE

---------- ---------- ---------
7900 30 03-DEC-81
7902 20 03-DEC-81
7934 10 23-JAN-82

14 rows selected.

Now the same query represented in matrix format .

select deptno,
sum(decode(to_char(hiredate,'RRRR'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'RRRR'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'RRRR'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'RRRR'),'1987',1,0)) "1987"
from emp
group by deptno
DEPTNO 1980 1981 1982 1987
---------- --------- ---------- ---------- ----------
10 0 2 1 0
20 1 2 0 2
30 0 6 0 0

Which shows the no. of employee joined to departments in the above years.

Note: - The only drawback here is the column can not be added automatically. That means for each
column and corresponding cell values to display, then the column need to be defined in query.

How to Check a Particular Format for Any Field thru SQL

Goal

How to validate the data saved in a column ?

Let us assume a table CUSTOMER with lot of records saved. We want to check the column CUSTID
with a particular format like 99 .So that we can update all records of the table.column in the same format.

Fix

Let us take an example for table Customer

SQL> desc customer

Name Null? Type
----------------------------------------- --------
CUSTID VARCHAR2(10)

SQL> select * from customer;

CUSTID
----------
IN-CTC-001
IN-BNG-001
IN-BNG-12
IN-MUM-01

We need to check if any of these records keeps the format 'XX-XXX-999'' .Write the query to get the
result

SQL> SELECT custid FROM customer where

TRANSLATE(CUSTID, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX-') ='XX-XXX-999';

CUSTID
----------
IN-CTC-001
IN-BNG-001
View the Source Code Of A Procedure

Goal

Is it possible to provide a Grant such that User can view only the source code of Procedure but not
execute it.

Fix

A Grant option would allow the user to execute the Procedure. To allow the User to just view the source
code of the Procedure, try the following:

1) Create a view like:

create view view_name as select text from all_source where

type='PROCEDURE' and name='PROCEDURE-NAME';

2) Grant select on the View created above.

This way, the user can do a select of the View, and see the source code of the Procedure.

How to Find or Delete Duplicate Rows in a Table

PROBLEM DESCRIPTION:

How do you find or delete duplicate rows in a table?

SOLUTION DESCRIPTION:

The following SELECT statement will find and display all duplicate rows in a table, except the row with
the maximum ROWID. The example uses the dept table:

SELECT * FROM dept a

WHERE ROWID <>(SELECT MAX(ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname
AND a.loc = b.loc);

The following statement will delete all duplicate rows in a table, except the row with the maximum
ROWID:

DELETE FROM dept a

WHERE ROWID <> (SELECT MAX (ROWID)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname
AND a.loc = b.loc);
Alternatively:

DELETE FROM dept a

WHERE 1 < (SELECT COUNT (deptno)
FROM dept b
WHERE a.deptno = b.deptno
AND a.dname = b.dname
AND a.loc = b.loc);

EXPLANATION

Using the pseudo column ROWID is the fastest way to access a row. ROWID represents a unique storage
identification number for a single row in a table (Note: Two rows on different tables but stored in the
same cluster may have the same rowid value).

Duplicate rows which contain only null columns, however, will not be identified by either of the two
methods above.

In such a case, simply:

DELETE FROM dept WHERE deptno IS NULL AND
dname IS NULL AND
loc IS NULL;

How do I create a flat ASCII file without rows wrapping?

PROBLEM DESCRIPTION:

You want to create a flat file with the output from a SQL query. When you execute the query, the rows
are wrapped around. How can you prevent this wrap-around?

SOLUTION DESCRIPTION:

To store the SQL query results in a file, you need to enter the SPOOL command in SQL*Plus:

SQL> SPOOL file_name

If you do not follow the filename with a period and an extension, SPOOL adds a default file extension to
the filename to identify it as an output file. The default varies with the host operating system. On most
hosts it is LST or LIS.

To create a flat file in SQL*Plus, you first must enter the following:

SET commands in SQL*Plus:

SET NEWPAGE O
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF

After entering these commands, use the SPOOL command as above to create the flat file.

(If the line size is too small, you may set it to a larger number. If you do this, you may need to use
another command 'SET BUFFER xxxx' where xxxx is the same size as the line size).

How do I create an output file with commas between the columns?

You will need to produce a .SQL file for the table so that the table output will have commas between the
columns.

How do I calculate an average date with time component?

Problem Explanation:

You want to calculate the difference between two average timestamps.

Solution Description:

Use a format mask without characters. If a table (x) has a date field (a) with two rows

01-jan-96 01:10
01-jan-96 01:20

SQL> select avg( to_number( to_char(a,'MMDDYYHHMI') ) ) from x;

AVG(TO_NUMBER(TO_CHAR(A,'MMDDYYHHMI')))
---------------------------------------
101960115

The average time of 1:10 and 1:20 gives 1:15 for the hours, date is the same.

Solution Explanation:

1. The following SELECT gives you the seconds past midnight, then displays in hh:mm:ss

select to_char(sysdate, 'sssss'), to_char(sysdate, 'hh:mm:ss') from

dual;

2. The following SELECT will convert the seconds past midnight back to display as hh:mm:ss

select substr(to_char(sysdate, 'sssss'),1,8),

substr(to_char(sysdate, 'hh:mi:ss'),1,8),
substr(trunc(to_char(sysdate, 'sssss')/3600), 1, 10),
substr(trunc(mod(to_char(sysdate, 'sssss'),3600)/60), 1, 10),
 substr(mod(mod(to_char(sysdate, 'sssss'),3600),60), 1, 10)
from dual;

How to insert & character or special character into Database using SQL*Plus.

• goal: How to insert & character or special character into Database using SQL*Plus.
• fact: Oracle Server - Enterprise Edition

Fix:

Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET
SCAN OFF" to turn off the definition of the ampersand as a substitution variable.

Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES ('AT&T');
/
1 row created

Solution 2:
If you are using substitution variables, set the substitution character to one which will not be encountered
when inserting data.

Example:
SQL> SET DEFINE %
SQL> INSERT INTO <temp_table> VALUES ('AT&T')
/
1 row created.

Solution 3:
If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON,
then SQL*Plus will understand the special character following the escape symbol is to be treated as a
regular character.

Example:

SQL>set escape on
SQL>show escape
escape "\" (hex 5c)

SQL> INSERT INTO temp_table VALUES ('select * from emp where ename =
\& 1');

1 row created.

How to embed Single Quote in a string

Problem Description:

How do you embed single quotes ( ' ) into a character string?

How do you concatenate a quote in SQL?
How do you place quotes around a character string in a SQL query?
How do you store an apostrophe into a character variable in PL/SQL?

Problem Explanation:

Example 1
When you issue either of the following SELECT statements:

SQL> SELECT ' FROM dual;

SQL> SELECT ''' FROM dual;

the following error occurs:

ORA-0xxx: quoted string not properly terminated

Example 2

When you issue the following SELECT statement:

SQL> SELECT ''character string in quotes'' FROM dual;

the following error occurs:

ORA-00923: FROM keyword not found where expected

CONCATENATE CHR(39) TO CREATE LITERAL SINGLE QUOTE

Solution Description:

To create a single quote, concatenate CHR(39) to the string.

Example 1
---------
SQL> SELECT 'test' || CHR(39) || 'case' result
2> FROM dual;

RESULT
---------
test'case

Example 2
---------
SQL> SELECT CHR(39) FROM dual;

C
-
'
Solution Explanation:

To return the ASCII value of the single quote ( ' ):

SQL> SELECT ASCII('''') FROM dual;

ASCII('''')
-----------
39

USE 2 SINGLE QUOTES TO CREATE 1 SINGLE QUOTE

Solution Description:

Keep the following two rules in mind:

1. Enclose every character string in single quotes. The single quote is a string delimiter.
2. Inside a string literal, use two consecutive single quotes to create a literal single quote.

Example 1
---------
6 single quotes: 'test' || '''''' || 'case' ---> test''case
8 single quotes: 'test' || '''''''' || 'case' ---> test'''case

You can also implement the above in the following way:

'test''case' ---> test'case

'test''''case' ---> test''case

Hence:

a. To create a single quote, concatenate 4 single quotes: ''''

The two single quotes in the middle define the single quote. The outside single quotes are the single
quotes that must surround a string.

Example 2
---------
SQL> SELECT '''' FROM dual;

'
-
'

Example 3
---------
SQL> SELECT 'test' || '''' || 'case' result
2> FROM dual;
 RESULT
---------
test'case

b. To place single quotes around a character string, enclose the character string within 3 single quotes: '''
At the start of a character string: the first single quote defines the start of the character string; It is one
of the two single quotes that surround the string. The second and third single quotes define the literal
single quote.

At the end of the character string: the first and second single quotes define the literal single quote.
The third single quote closes the character string; it is the other single quote that surrounds the string.

Example 4
---------
SQL> SELECT '''character string in quotes''' result
2> FROM dual;

RESULT
----------------------------
'character string in quotes'

Solution Explanation:

More examples:

Example 5
---------
SRW.DO_SQL('SELECT DECODE(dname, ''NONE'', NULL, ''A'')
FROM dept
WHERE deptno = 10');

As a result, this is the SELECT statement sent to the database:

SELECT DECODE(dname, 'NONE', NULL, 'A')
FROM dept
WHERE deptno = 10

Example 6
---------
DECLARE
a VARCHAR2(200);
q CHAR(1) := '''';
BEGIN
a := '''this is a ' || q || 'quoted'' string' || q;
END;

String "a" stores: 'this is a 'quoted' string'

How to do a case insensitive search using the 'LIKE' operator?

Problem Description:

How can a user perform a case insensitive match using the LIKE comparison operator?

Problem Explanation:

The field in a table may be entered in different cases, for example, 'MacDonald' and MACDONALD' -
how can a single SQL query using the LIKE operator find all occurrences of the name MacDonald.

Solution Description:

You can use the UPPER () function to perform a case insensitive match as in the following condition;
For example, to find an employee name in a string beginning with the letters 'SM' -

SQL > select * from emp where UPPER (ename) like 'SM%' ;

Solution Explanation:

The UPPER function converts the ename field results in the searched column to upper case. For example
the strings 'smith' and 'Smith' and 'SMITH' will all become 'SMITH'.

How to Create Column Delimited Flat File

Problem Description:

How can SQL*Plus be used to create a delimited flat file?

Problem Explanation:

When moving data between different software products, it is sometimes necessary to use a "flat" file ( an
operating system file with no escape characters, headings, or extra characters embedded ).

CONCATENATE COLUMN SEPARATOR IN QUERY AND SPOOL

Solution Description:

Concatenate the desired column separator into the query.

For example:

To delineate column text with TABs:

-----------------------------------
SQL> select DEPTNO || CHR(9) || DNAME FROM DEPT;

DEPTNO||CHR(9)||DNAME
-------------------------------------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

The CHR() SQL character function is used to embed the TAB in the query text.

To delineate column text with COMMAs:

-------------------------------------

SQL> select DEPTNO || ',' || DNAME FROM DEPT;

DEPTNO||','||DNAME
-------------------------------------------------------
10,ACCOUNTING
20,RESEARCH
30,SALES
40,OPERATIONS

To send your query results to a file, use the following SQL*Plus SET commands before running your
query:

SET TERMOUT OFF

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL file_name

< your query>

SPOOL OFF
SET TERMOUT ON

Solution Explanation:

SQL*Plus versions 3.0.x and 3.1.x do not have a SET command to specify a column separator to
delineate text between columns.

NOTE:
The result of concatenating two character strings is another character string. If both character strings are
of datatype CHAR, the result has datatype CHAR and is limited to a maximum result of 255 characters.
If either string is VARCHAR2, the result has datatype VARCHAR2 and is limited to a maximum result
of 2000 characters.

Solution Description:
Use the SQL*Plus 3.2.x COLSEP SET command.

For example:
To send your query results to a comma delimited flat file, use the following SQL*Plus commands:

SET TERMOUT OFF

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET COLSEP ','
SPOOL file_name

< your query>

SPOOL OFF
SET TERMOUT ON

Solution Explanation:

SQL*Plus 3.2.x has the COLSEP SET command. Previous versions of SQL*Plus did not have COLSEP.

This solution will send the query results to a file, with the text printed between the SELECTed columns as
specified by the SET COLSEP command.

If the COLSEP variable contains blanks or punctuation characters, you must enclose the text with single
quotes.

Method to create .SQL file with comma-delimited output.

SOLUTION DESCRIPTION:

The SQL script below when executed will produce a second .SQL file for the table so that the listed table
output will have comma's between the columns.

REM
*********************************************************************
*****
REM COMMA.SQL
REM ------
REM Generate SELECT statements using Catalog tables
REM
REM Usage: @COMMA tablename outputfilename
REM
REM tablename: Any table name (standard SQL wildcards accepted, e.g.
% for all)
REM outputfilename: Target SQL file name (no extension, .SQL
REM extension supplied automatically
REM
*********************************************************************
*
 SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGES 999
SET HEADING OFF

COL tabname NOPRINT

COL seqno NOPRINT
COL seq2 NOPRINT

SELECT
table_name tabname
, 1 seqno
, 0 seq2
, 'SELECT ' txt
FROM
user_tables
WHERE table_name LIKE UPPER('&1')
UNION
SELECT
table_name tabname
, 2 seqno
, column_id seq2
, DECODE(column_id
,1,''
,'||'',''||')||column_name txt
FROM
user_tab_columns
WHERE table_name LIKE UPPER('&1')
and exists (
select 1
from user_tables
where user_tables.table_name = user_tab_columns.table_name )
UNION
SELECT
table_name
, 3 seqno
, 0 seq2
, 'FROM '||table_name||';' txt
FROM
user_tables
WHERE table_name LIKE UPPER('&1')
ORDER BY 1,2,3

SPOOL &2..sql
//
SPOOL OFF

SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
SET HEADING ON
How to Query top "N" rows ordered by a column
Problem Description:

What SQL SELECT statement do you use to retrieve the top "n" rows ordered by a particular "column"?

Problem Explanation:

Solution Description:

To return exactly "n" rows ordered by a column, create a query similar to the following:

SELECT *
FROM table A
WHERE n >= (SELECT COUNT(*)
FROM table B
WHERE B.column >= A.column)
ORDER BY column DESC;

where "n" is the number of rows to be retrieved and ordered, "table" is the name of the table and
"column" is the name of the column.

The first SELECT statement controls the number of rows, while the nested, second SELECT statement
controls the condition.

The query returns the first "n" ordered rows. If n=10 and Rows 8-12 share the same column value, this
only return Rows 9 and 10. The above SELECT statement does not take duplicate column values into
consideration.

Solution Explanation:

To return any row whose column value is one of the top "n" ordered column values, create a query similar
to the following:

SELECT *
FROM table A
WHERE n >= (SELECT COUNT (DISTINCT column)
FROM column B
WHERE B.column >= A.column)
ORDER BY column DESC;

Specifying DISTINCT return all rows whose column (e.g. sal) value falls into one of the top "n" values.
Hence, the query returns more than "n" rows when several rows share the same column value.

How do you convert a character field into a date format without losing the century
data?

Problem Description:
When you convert a character field into a date field, the century information is lost. How do you convert
a character field into date format without losing the century data?

Solution Description:

To retain the four digit century information, you must convert the date field back into a character string.

For example the query is:

 
SQL> select TO_CHAR (HIREDATE, 'DD­MON­YYYY') from EMP 
     Where HIREDATE = TO_DATE ('17­NOV­1981', 'DD­MON­YYYY'); 
  
The results of the query which show the full four character century are:
 
TO_CHAR (HIREDATE,'DD­MON­YYYY') 
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ 
17­NOV­1981

How do I set the SQL*Plus search path for SQL scripts?

Answer

Set SQLPATH environment variable. Add all the directories you want SQL*Plus to search for the SQL
scripts, each separated by a semicolon.

Example:

SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS;

In Windows environment, if you are using 32-bit SQL*Plus then set this variable in the Registry, or if you
are using 16-bit SQL*Plus then set this variable in the file Oracle.ini

How do I get the system date and time over DBLINK?

You cannot use SYSDATE or USER, to get the SYSDATE, USER over DBLINK due to a known
restriction with these functions. For this, you can use the following workaround:

Step-1:

On the remote machine, populate a table with the SYSDATE, USER, values using a stored procedure on
the remote machine.

CREATE TABLE db_table (mydate DATE);

CREATE OR REPLACE PROCEDURE db_date as mydate date;
BEGIN
         DELETE FROM db_table;
         SELECT SYSDATE INTO mydate FROM  DUAL;
         INSERT INTO db_table VALUES (mydate);
END;

Step­2:

On local machine, select the data from the table which has been populated using the stored procedure.

EXECUTE db_date@dblink
SELECT mydate FROM db_table@dblink;

How do I execute SQL scripts in batch?

You can execute any SQL script using the SQL*Plus command in a batch file. The format of the
command is as follows:
 
SQL*Plus­Executable USERNAME/PASSWORD@CONNECT_STRING Sql­script

For Example:
 
Plus31 scott/tiger@orcl @c: \script.sql
 
will execute the script script.sql on 'c' drive.

Suppose the contents of script.sql is as follows:

 
SELECT * FROM emp;
host dir/p
EXIT;
 
Executing the above example command will display the emp records in the SQL*Plus window and would
list the directory in a command window. The 'exit' command in the last line will close the SQL*Plus
session.

How do I display the time component of a date column?

To retrieve the time information, use the TO_CHAR function with a format mask.
 
Examples:  
   SELECT To_Char(datecolumn, 'DD­MON­YY HH24:MI:SS') FROM mytable;  
   :global.hi := To_Char(:time1, 'DD­MON­YY HH:MI:SS'); 
 
   The data returns in the following format:
   01­SEP­94 15:01:01 

How do you skip a page in a SQL*Plus report?

You can skip a page in SQL*Plus reports by making use of the following statement:
    BREAK ON X SKIP PAGE;
    COMPUTE SUM OF Y ON X;
    SELECT fld1,fld2
    FROM tab1,tab2
    WHERE x=y
    GROUP BY fld1,fld2 ORDER BY fld1;

Is there a way to document my SQL command and file?

You can do this in two ways :

          1.  SQL comment delimiters (/*........*/)

               EX:    SQL> select ename /*this is a comment
                           delimiter example*/ from /*This is
                           another example*/ emp;

          2.   REMARK ­  Begins a remark in a command file.

                      ­  Must appear at the beginning of a line and
                         ends at the end of the line.

                      ­  A line cannot contain both a remark and
                         a command.

                 For example:   get rem.sql
                 
                      1  Rem This is an example of how REM is used.
                      2  Column sal format $99,999.99 heading ­
                      3  'Monthly|salary'
                      4  Compute sum od sal on deptno
                      5  Rem This sql script will total the salaries
                      6  Rem of all employees in each department
                      7  Select deptno, ename, sal from emp
                      8  order by deptno;

How do I include the current date and time in a SQL*Plus report heading?

The following SQL*Plus command file segment shows how to put the current date into a variable and
then include that variable in the title. To get the time, just change the mask in the to_char function to
include that as well.

     COLUMN SYSDATE new_value today
     SELECT To_Char(SYSDATE,'mm/dd/yy') "sysdate" FROM DUAL
      /
     TTITLE left today center 'MY HEADING OR TITLE' skip 2

The use of "new_value" shown here applies in general to any value selected into a column specified in the
COLUMN xxx NEW_VALUE command. It can be used to display various data in the title of a report.
How To Trim Lines When Spooling

To remove trailing blanks at the end of each line enter the following statement on the command prompt
before the spool command:
SQL> set trimspool on

How to insert ‘&’ character or special character into Database using SQL*Plus

Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can do a "SET DEFINE OFF" or "SET
SCAN OFF” to turn off the definition of the ampersand as a substitution variable.

Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES ('AT&T');
/
1 row created

Solution 2:
If you are using substitution variables, set the substitution character to one which will not be encountered
when inserting data.

Example:
SQL> SET DEFINE %
SQL> INSERT INTO <temp_table> VALUES ('AT&T')
/
1 row created.

Solution 3:
If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON,
then SQL*Plus will understand the special character following the escape symbol is to be treated as a
regular character.

Example:

SQL>set escape on
SQL>show escape
escape "\" (hex 5c)

SQL> INSERT INTO temp_table VALUES ('select * from emp where ename = \&
1');

1 row created.

How to Insert Special Characters Into Database

First pay attention the database you use is able to deal with the special characters you want to insert. That
means you have to check if the special character is included in your database characterset.
Here just an example how to insert these special characters:
Assume you want to insert the following sample string, consisting of several single characters, which are
concated to one string.

-Ob?''!#*)EBK+`#a"^?#c\QH'

To be able to do this, mask each single ' (single quote) with just one preceeding '(single quote).If just two
''(two single quotes) signs have to be inserted use four '''' (four single quotes):

The following will work:

SQL>insert into test VALUES ('-Ob?''''!#*)EBK+`#a"^?#c\QH''');

1 row created.

SQL> select * from test;

A
--------------------------
-Ob?''!#*)EBK+`#a"^?#c\QH'

Be aware of the characters (' at the beginning and the characters ') at the end of the string you want to
insert.

How To Create a Flat File In SQL*Plus Without Showing Statement or ''Spool

Off''

Create a script, e.g. report.sql, with the following contents:

set NEWPAGE 0
set SPACE 0
set LINESIZE 80
set PAGESIZE 0
set ECHO OFF
set FEEDBACK OFF
set HEADING OFF
spool report.txt
select sysdate from dual; <--- your SQL statement here
spool off

Run the script from SQL*Plus:

SQL> @report.sql

How to SELECT Columns as Rows Using SQL

Example:

Using a UNION and VIEW should do the transformation like:

create table p ( product varchar(16), attr1 varchar(16),

 attr2 varchar(16), attr3 varchar(16) );
insert into p values ( 'product_1', 'attr_11', 'att_12', 'attr_13');
insert into p values ( 'product_2', 'attr_21', 'att_22', 'attr_23');
insert into p values ( 'product_3', 'attr_31', 'att_32', 'attr_33');
commit;

create view vv as select * from

(select product , attr1 from p
union
(select product , attr2 from p
union
(select product , attr3 from p );

Output:
SQL> select * from vv;

PRODUCT ATTR1
---------------- ----------------
product_1 att_12
product_1 attr_11
product_1 attr_13
product_2 att_22
product_2 attr_21
product_2 attr_23
product_3 att_32
product_3 attr_31
product_3 attr_33

How to Change the Displayed Font in SQL*Plus (GUI)

To Change the Windows GUI Font:

1. Select Run from the the Start menu and then enter regedit in the Open field and Click OK to start the
registry Editor.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0.

Note:If you have more than one Oracle installation, you must select the HOME entry associated
with the Windows GUI you want to change. HOME0 is the registry entry for an Oracle installation. A
subsequent Oracle installation will have the registry entry HOME1 and the next HOME2 and so on.
Changes only affect a SQL*Plus Windows GUI started from the associated Oracle installation,
so you can use different settings for each Oracle installation.

3. Click New String Value in the Edit menu. A new string value, with the default name, NewValue #1 is
created at the bottom of the right pane of the Registry Editor. The default name of the new string value is
selected ready for you to replace with the name you want.

4. Enter SQLPLUS_FONT as the name of the new font face string value.If you miskey the name or
inadvertently enter it in mixed or lower case, you can edit the name by selecting Rename from the Edit
menu.

5. Click Modify from the Edit menu or press Enter again to display the Edit String dialog.
 6. Enter the font name you want to use, such as Courier New, in the Value Data: field. SQL*Plus will
use the new font the next time you start the SQL*Plus Windows GUI.
Modify Password and Suppressing Typed Password in SQLPLUS

Use sqlplus functionality passw just enter on sqlplus prompt:

SQLPLUS> passw
and new password wil not be echoed.

How to Select and Display a Certain Number of Records

Problem Description

Customer needs to select from a table and display only a limited number of records. Example would be
select all Smiths from address table, but only display the first 10 or what ever.

Solution Description

Several solutions are available, from very simple to slightly complex

Solution 1 (simple)

SQL> select empno from emp where empno like '%7%' and rownum <= 10;

    EMPNO
­­­­­­­­­
     7369
     7499
     7521
     7566
     7654
     7698
     7782
     7788
     7839
     7844
10 rows selected.

Solution 2

SQL> l
  1  declare
  2   v_counter number(3):=+0;
  3   v_empno  emp.empno%type;
  4   cursor emp_cursor is
  5        select empno
  6               from emp
  7               where empno like '7%';
  8  begin
  9    open emp_cursor;
 10     loop
 11        exit when emp_cursor%notfound;
 12    fetch emp_cursor into v_empno;
 13           v_counter := v_counter +1;
 14     exit when emp_cursor%notfound or v_counter > 10;
 15         dbms_output.put_line(v_empno ||' ' || v_counter);
 16     end loop;
 17    close emp_cursor;
 18* end;
SQL> /
7369 1
7499 2
7521 3
7566 4
7654 5
7698 6
7782 7
7788 8
7839 9
7844 10

PL/SQL procedure successfully completed.

Solution 3

set  serveroutput on
accept select_info prompt 'please enter select: ' 
accept where_info prompt  'please enter where: ' 

declare 
 v_counter number(3):=+0; 
 v_&select_info  emp.&select_info%type;
 cursor emp_cursor is 
      select &select_info
             from emp 
             where &select_info like &where_info;  
begin
  open emp_cursor; 
   loop
      exit when emp_cursor%notfound;
         fetch emp_cursor into v_&select_info;
         v_counter := v_counter +1;     
     exit when emp_cursor%notfound or v_counter > 10;
      dbms_output.put_line(v_&select_info ||' ' || v_counter);    
   end loop;
  close emp_cursor; 
end; 
/

How to Number Rows After They Have been Sorted

Problem description

SQL*Plus users sometimes want to have rows numbered in a sorted order. This can't be achieved using
the ROWNUM pseudo column, since the ordering is done after the values for ROWNUM had been
assigned to rows as they were selected in random order.

This is illustrated in the following example.

The first selection shows the unsorted selection of ename and rownum from emp, and the second
selection illustrates how ROWNUM is useless as a numbering device for ordered selections because the
values of ROWNUM are assigned before the ordering is performed.

SQL> select ename, rownum from emp;

ENAME          ROWNUM
­­­­­­­­­­ ­­­­­­­­­­
ALLEN               1
JONES               2
BLAKE               3
CLARK               4
KING                5
ADAMS               6
JAMES               7
FORD                8

SQL> select ename, rownum from emp order by ename;

ENAME          ROWNUM
­­­­­­­­­­ ­­­­­­­­­­
ADAMS               6
ALLEN               1
BLAKE               3
CLARK               4
FORD                8
JAMES               7
JONES               2
KING                5

Solution Explanation

The following select statement achieves the desired ordering/numbering:

SQL> select   A.ename, count(*) position
  2  from     emp A, emp B
  3  where    A.ename > B.ename
  4  or       A.ename = B.ename and A.empno >= B.empno
  5  group by A.empno, A.ename
  6  order by A.ename, A.empno;

ENAME        POSITION
­­­­­­­­­­ ­­­­­­­­­­
ADAMS               1
ALLEN               2
BLAKE               3
CLARK               4
FORD                5
JAMES               6
JONES               7
KING                8

This method works by counting the number of records that a particular record is superior than (or equal
to) in alphabetical order of employee name. The empno column acts as a unique key to discern between
records in case identical names occur in the table. This would not be an efficient method against tables
with large numbers of rows.

Another alternative is to create an index on the order column, and include a meaningless where clause to
force use of the index.

SQL> create index sort on emp (ename);

Index created.

SQL> select ename, rownum from emp where ename > ' ';

ENAME          ROWNUM
­­­­­­­­­­ ­­­­­­­­­­
ADAMS               1
ALLEN               2
BLAKE               3
CLARK               4
FORD                5
JAMES               6
JONES               7
KING                8

The where clause in the query forces SQL*Plus to use the index created on the ename column, and since
the index is used, the rows are returned in ascending order. Since this method depends on the inherent
order of the index, it cannot be used to return rows numbered in descending order. The last method
presented takes advantage of a feature of the database kernel optimizer.

SQL> select rownum, ename
  2  from emp , dual
  3  where emp.ename = dual.dummy (+);

    ROWNUM ENAME
­­­­­­­­­­ ­­­­­­­­­­
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING

The optimizer evaluates the outer join in this example by using a sort/merge join, which results in the
desired sorted order.

Missing Label when Computing is Done on the First Column

PROBLEM:

   Your label is missing when doing computes of the first column on a report break. In other words,
suppose you do the following in SQL*Plus:

SQL> break on report
SQL> compute count of deptno on report
SQL> select * from dept;

    DEPTNO DNAME          LOC
­­­­­­­­­­ ­­­­­­­­­­­­­­ ­­­­­­­­­­­­­
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
­­­­­­­­­­
         4

Notice the lack of the 'count' label. This happens in report breaks where the computation is performed on
the first column. Normally, the compute labels for a report break always appear in the first column. For
example, if I had performed the count computation on the second column, DNAME, I would have gotten:

SQL> clear computes
Computes cleared
SQL> compute count of dname on report
SQL> select * from dept;

    DEPTNO DNAME          LOC
­­­­­­­­­­ ­­­­­­­­­­­­­­ ­­­­­­­­­­­­­
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
           ­­­­­­­­­­­­­­
count                   4

In this case, no computation is performed on the first column so SQL*Plus is free to place the "count"
label in the first column.

RESOLUTION:

   The following approach was proposed: use a "dummy" first column to hold the compute label when
you want a compute of the first column on a report break. Here's how it works with our original example:
SQL> clear computes
computes cleared
SQL> clear breaks
breaks cleared
SQL> break on report
SQL> compute count of deptno on report
SQL> col summary format a7
SQL> select ' ' summary, dept.* from dept;

SUMMARY     DEPTNO DNAME          LOC
­­­­­­­ ­­­­­­­­­­ ­­­­­­­­­­­­­­ ­­­­­­­­­­­­­
                10 ACCOUNTING     NEW YORK
                20 RESEARCH       DALLAS
                30 SALES          CHICAGO
                40 OPERATIONS     BOSTON
        ­­­­­­­­­­
count            4

Thus, we create a dummy left most column named SUMMARY (or whatever is appropriate) to hold the
"count" label. Notice that as the first element of the SELECT list we specify the constant expression ' ' (a
blank string) and give it a column alias of SUMMARY.

How to exit multiple connection attempts to DB from SQL*Plus when first

connection attempt fails.

Purpose

The purpose of this article is to demonstrate how to make only one connection attempt to the database and
then exit so SQL*Plus does not bring up the username prompt again.

This technique can then be used in scripts or batch files to automatically handle failing SQL*Plus
connections e.g. In backup scripts or batch jobs.

SQL*Plus example from Unix Prompt

                  
sqlplus /nolog <<EOF                                                         
whenever sqlerror exit                                                       
connect scott/tiger@notthere                                                 
EOF                    

SQL*Plus example for Windows

                      
Create a SQL Script connect.sql as follows:
                                
whenever sqlerror exit
connect scott/tiger@notthere 

From DOS Prompt, Start this script as follows:

 sqlplus /nolog @connect.sql

How to restrict SQL commands using PRODUCT_USER_PROFILE

It is possible to disable the following commands:

ALTER,
AUDIT,
CREATE,
DELETE,
DROP,
GRANT,
INSERT,
LOCK,
NOAUDIT,
RENAME,
REVOKE,
SELECT,
UPDATE,
VALIDATE.

Example To disable the SELECT statement for SCOTT:

1. Insert in the table PRODUCT_USER_PROFILE the next row:

SQL> insert into product_user_profile values

('SQL*Plus', 'SCOTT', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);

2. When the user SCOTT tries to execute the command SELECT he will obtain the error:

SP2-0544: Invalid command: SELECT.

How to Get a Heading After Set Pagesize 0

When setting PAGESIZE to 0 then the column headings will be suppressed.

Use the following example to generate a heading:

SQL-script (t1.sql)
set pagesize 0
set feedback off
select 'Number Name' from dual;
select '---------- -------' from dual;
select empno, ename from emp
/

SQL> @t1
=> Result:
Number Name
---------- -------
7369 SMITH
7499 ALLEN
 7521 WARD
7566 JONES

How to Include a New Line Character in an Insert Statement from SQL*Plus

Concatenate the ASCII value CHR (10) in between the data where the carriage return is required.

E.G. INSERT INTO <TABLE NAME> VALUES ('HELLO'||CHR (10) ||'HOW ARE YOU')

The above insert statement will insert into the table as two separate lines.

How to remove whitespace when spooling a select statement

Example:-
Create table test as below.

SQL> desc test;

Name Null? Type

-------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(20)

SQL> select concat(ltrim(to_char(col1)),ltrim(col2)) from test;

Removes all whitespace between columns.

Set heading off - removes headings

Set pagesize 0 - removes pagebreaks

How To Append a Carriage Return To the End of a Record In a Spooled File

A typical scenario would be if a file is created with multiple columns piped together placing a carriage
return (CHR(10)) at the end of each record. The carriage return is placed at the end of the record length,
set by LINESIZE, instead of immediately following the last column. Use SET TRIMSPOOL ON to have
the carriage return follow the last column.

Example:

spool <filename>
set linesize 100;
set heading off;

set trimspool off

select dname||'|'||loc||'|'||chr(10) from dept; --carriage return set at
position 100

set trimspool on
select dname||'|'||loc||'|'||chr(10) from dept; --carriage return after
last ¿|¿ character
spool off

How To Pass Parameters Containing Spaces To SQL*PLUS

On Unix the parameter(s) follow on command line and have to be quoted

SQLPLUS scott/tiger @test "This is a test."

They then can be selected with

SELECT '&1'
FROM sys.dual;

ON VMS that has to happen as follows:

SQLPLUS scott/tiger @test "'This is a test.'"

SQLPLUS scott/tiger @test """This is a test""&quot;

How To Display a Long Column in Oracle SQL*Plus

Use the SET command SET Long xxxx

How to Create a Flat ASCII File From SQL*Plus

To create a flat file from within SQL*Plus, use the following SET commands in SQL*Plus:

SQL> SET NEWPAGE O

SQL> SET SPACE 0
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 0
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF

Use the SPOOL command to store query result in a file:

SQL> SPOOL <file_name>

SQL*Plus stores all information displayed on the screen after the SPOOL command is entered, in the
specified file. If no file extension is specified, SPOOL add a default file extension to the filename to
identify it as an output file, usually LIS or LST, depending on host operating system.

SQL*Plus continues to spool information to the file until spooling is turned off using the following
command:

SQL> SPOOL OFF

How To Remove The Default Single Space Between Fields From The Output Of a
SELECT Statement In SQL*Plus
This can be done using the SET COLSEP command.

Use this command before executing the SELECT statement:

SET COLSEP ''

NOTE: Do not leave space between the single quote symbols.

How to Suppress The 'rows will be truncated' Message in SQL*Plus

When the SQL*Plus setting WRAP is set to OFF and rows need to be displayed which are larger then the
setting of LINESIZE the message 'rows will be truncated' will be reported.

There are two solutions to circumvent this:

1. Set the SQL*Plus setting LINESIZE to a value larger than the maximum length of the rows to be
selected;
SQL> set wrap off
SQL> set linesize 32767 (32K is maximum value)

2. Enable wrapping, do not truncate rows;

SQL> set wrap on

How to Display the Entire Contents of Long Fields

Use the command SET LONG <n>, where <n> is the max length of the column that will be showed.

Example:
SQL> SET LONG 500