Document ID: 2194.1
Subject: COMMON SQL*PLUS QUESTIONS AND ANSWERS
Author: ILAHIRI
Q: What is the purpose of the COPY command ?
A: Copy command in SQL*Plus is useful when you want to transfer
data between Oracle and NON-Oracle databases. If you want
to copy Oracle table to another Oracle table, use
"create table .. as select .." or "create table, insert into
.. select .." statements.
Columns may lose precision if you copy from Oracle
rdbms to Oracle rdbms.
Q: How can I select data to the 'same line' in SQL*Plus:
A: For example you want to show sum_of_salaries/department in
one line instead of 4 lines:
select sum(decode(deptno,10,sal,0)) sal_10,
sum(decode(deptno,20,sal,0)) sal_20,
sum(decode(deptno,30,sal,0)) sal_30,
sum(decode(deptno,40,sal,0)) sal_40
from emp;
Q: How to start a SQL*Plus script when at least one row is found
from a table ? If no rows are found, then nothing should be
started.
A: Use a script like the following:
spool tmp.sql
Select decode(greatest(count(*),0),'0','start dummy','start script')
from tablex where colx = ¶meter;
spool off
@tmp
It will start the dummy (which does nothing) if no rows are
returned, otherwize it will start the "script".
Q: How to use "set pause" in SQL*Plus ?
A: For example to get 'Underlined prompt' in a VT220
terminal use the following code in your login.sql:
set pause [1m[4m'Underline prompt'[m
set pause on
Note that the must be a real esc, not the characters <,E,S,C
and >.
Q: How to show all of a long field ?
A: set long 4000
set array 1
Q: I get "ORA-907: Missing right parenthesis", but I have enough
parenthesis ? My select statement is:
select * from emp
where ('A%' is null
or empno in (select empno from emp e emp su
where empno=7999))
and 1=1;
A: Sometimes the error messages are misleading,
the actual error is a missing comma on line 3.
Q: Why is my update script getting slow when I update a lot of rows ?
A: If you want to update many rows in a table by a PL/SQL block,
and you use a cursor to that table, the rollback segments are
released only when you CLOSE the cursor. Thus, as time passes with
the cursor still open, the process slows down because it has to
keep searching an increasingly large rollback segment to build the
consistent picture of the the table as it was when the cursor was
opened. To avoid this problem, try to use a flag in the table
describing what rows are not updated and then close + re-open the
cursor every 1000 rows or so.
Q: Why I don't get the "No rows selected" if I issue
select max(empno) from emp where deptno = -10 ?
There are no emps with deptno -10.
A: The GROUP functions (min/max/avg...)
will ALWAYS fetch at least one row unless you
specify a group by clause.
Q: How can I remove duplicates from my table ?
A: To find duplicate keys from a table tx:
select key, count(key) no_of_duplicates
from tx
group by key
having count(key) > 1;
To remove duplicates from table tx
when IT DOES NOT MATTER WHICH ONE IS SAVED:
delete from tx
where key not in
(select min(key) from tx group by key);
Of course you can choose which one to save in the subselect.
Q: How to get the Operating system userid ?
A: select s.username
from v$session s, v$process p
where s.paddr = p.addr
and s.username = user
This works when you don't use SQL*Net connections.
Q: How to find the SID of the database in Forms/Plus etc ?
A: "Select value from sys.v$parameter where name ='db_name';"
Remember to grant select on that table to public using
user SYS.
Q: Ttitle do not show up if no rows are selected in a select statement. How
to get around that.
A: You have to select something that will definitely succed and then do a
noprint on that. Below is the relevent workaround.
ttitle center 'testing'
column x noprint
select 'x' x from dual;
select * from dept where deptno=77;
/
the result :
testing
no rows selected
Q: How to supress the date and page nos. from appearing .
A: By default the date and the time will appear if you just do a ttitle and
do not qualify it. Eg.
SQL> ttitle 'test'
SQL> select * from dual;
Output:
September 3, 1992 page 1
If you do not want date and page no.qualify the ttitle with something
(any of the clauses mentioned in pp 4-18 of sqlplus ref manual). eg.
SQL> ttitle left 'test'
SQL> select * from dual;
test
D
-
X
Q: How to show the time difference between two date columns ?
A: select floor((date1-date2)*24*60*60)/3600) || ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' timediff
from ...
Q: For Add_months() function if the first parameter is the last day of a
a month (i.e. '28-feb-91'), the result will be the last day of the
month (i.e. '31-aug-91'); the 27th of feb, of course, returns the
27th of aug. Is there any way to override this rule so that
add_months('28-feb-91',6) will return '28-AUG-91'?
A: select to_date(add_months(c1-1,6))+1 alias_col_name from Table;
where c1 is a date col holding the value 28-FEB-91.
Q: I tried to use UNION but I got
ORA-01789: query block has incorrect number of result columns.
I have only some of the columns in the other table.
A: You have to define the missing columns. Note the number
of spaces between the single quotes.
- character:
select ' ', ename from foo -- One space between the single quotes
union
select charcol, ename from table;
- number:
select 0, ename from foo
union
select numericcol, ename from table
- date:
select to_date(''), ename from foo --No space between the single quotes
union
select datecol, ename from table
Q: How do you find the days between two dates excluding weekends (Saturdays
and Sundays)?
A: This is a SQL SELECT statement that subtracts two dates and gives
you the number of days between them, excluding weekends.
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)
+ LEAST(7-DECODE)(TO_NUMBER(TO_CHAR(begdate,'D')),1,7,
TO_NUMBER(TO_CHAR(begdate,'D'))),5) DAYS
FROM datetable
Q. I have a number field which holds time in seconds. How can I display
this using an oracle time format?
A. select to_char(trunc(sysdate)+(time_field/86400),'HH:MI:SS')
from time_table;
*86400 is the number of seconds in a day