From: Subject: Oracle Support Date: Thu, 1 Jul 2004 09:57:02 +0100 MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_0020_01C45F51.C2483BC0"; type="multipart/alternative" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/top_left.gif R0lGODlhDAA7AKL/AP///8zMzJmZmWZmZjMzM8DAwAAAAAAAACH5BAEAAAUALAAAAAAMADsAQAMx CLogQ6WMSKu9OOvN+1WTJ45kaTHhCZls675wbDJAsFJLegabwN2yoHBILBqPyGQhAQA7 ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/lines.gif R0lGODlhCQAIAJH/AP///zMzMwAAAAAAACwAAAAACQAIAEACCoyPecLtD6OcswAAOw== ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0025_01C45F51.C24AACC0" ------=_NextPart_001_0025_01C45F51.C24AACC0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=47715.1 =EF=BB=BF Oracle Support ------=_NextPart_001_0025_01C45F51.C24AACC0 Content-Type: application/octet-stream Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_documents.showRelevancy?p_database_name=NOT&p_document_id=47715.1 =EF=BB=BF Oracle Support Services
Did this article help solve your problem? : = Would you recommend this document to others? = :=20
=
------=_NextPart_001_0025_01C45F51.C24AACC0-- ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/but_help.gif R0lGODlhRwAXAKL/AP///8zMzJmZmWZmZjMzM8DAwAAAAAAAACH5BAEAAAUALAAAAABHABcAQAPy WLrc/jDKRwa4GLghxCCTAwxKIDgEAIYS4a4N58FsrQykjbqP/OnAYOGlSGlinZ8OcCqYCkzPRxUI KEZB4ob06nq/4LB4LO51zui0es1uu8/FzCZJE9on2iPDVw9lAFYCASlTBAEjAipZPHMzQAIrOAqC XCdVA4NCWkiOd54hm5+iowsyb6eoqWcriXKcOLCxsrO0tba3kkMYjUqko1qJvH0TATkdKJmeRE17 dEBMJVYFlLoVMsOgjK/YEUwuVVAmVSmG0lhAoaXOSznggoTkhxyKOl1mnb4L4HfpN+v5pPoV4PCB jMGDCMf0SMiwYcICCQAAOw== ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: application/octet-stream Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_graphics.helpFrame =EF=BB=BF 3DHelp=20 ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: application/octet-stream Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_draw.showFrame?p_bgcolor=black =EF=BB=BF Oracle Support ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top.gif R0lGODlhCgAPALMAAP///8zM/5nMzJmZzGaZzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAACwAAAAACgAPAAAEHpDISUQAlAzA8bydR4ChRJZb2BFqKLTdCc90bd9zBAA7 ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top.gif R0lGODlhPAAPALMAAP///8zM/5nMzJmZzGaZzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAACwAAAAAPAAPAAAENRDISasNNustiP/EJo4kOYBhqa5kALJwXLmebMf0ra/n7pOpn9AiGBop mKNyyWw6n9Co1BcBADs= ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bottom.gif R0lGODlhCgAPALMAAP///8z//8zM/5nMzJmZzGaZzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAACwAAAAACgAPAAAEHxDISau9ONeQRR5ZwV0FgRUFaKGFV7HtCxMjALODEEQAOw== ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bottom.gif R0lGODlhDwAPAKIAAP///8z//8zM/5nMzJmZzGaZzAAAAAAAACwAAAAADwAPAAADIQi63P4wykln qEDgUUOpxDcNhRgJpfmgKRSEqQoEAhmXCQA7 ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: application/octet-stream Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=47715.1&p_showHeader=1&p_showHelp=1 =EF=BB=BF  =20
 
Bookmark Fixed=20 font Go to End

D= oc=20 ID: Note:47715.1
Subject: SQL*Plus Frequently Asked Questions
Type: FAQ
Status: PUBLISHED
 =
Content Type: TEXT/PLAIN
Creation Date: 05-NOV-1997
Last Revision Date: = 22-JAN-2004
O=
RACLE SQL*Plus  FREQUENTLY ASKED QUESTIONS =

------------------------------------------- 
 
CONTENTS =

-------- 
 
1.   How do I find or delete duplicate rows in a =
table? 
 
2.   How do I relink SQL*Plus on Unix? 
 
3.   How do =
I create a flat ASCII file without rows wrapping? 
 
4.   How do I =
create an output file with commas between the columns? 
 
5.   How =
do I calculate an average date with time component? 
 
6.   How do I =
insert data with ampersands (or special characters) in it? 
 
7.   =
How do I create quotation marks or put quotes around character strings =

     in SQL? 
 
8.   How do I do a case insensitive search using =
the 'LIKE' operator? 
 
9.   How do I create a column delimited flat =
file? 
 
10.  How do I query top "N" rows ordered by a column? 
 =

11.  Can one set default directory where SQL*Plus files are saved? =

 
12.  How do you convert a character field into date format =
without losing  
     the century data?   
 
13.  How can I get =
PUPBLD.SQL to drop public synonyms? 
 
14.  How do I set SQL*Plus =
search path for SQL scripts? 
 
15.  How do I get the system date =
and time over DBLINK? 
 
16.  How do I execute SQL scripts in batch? =

 
17.  How do I display time component of a date column? 
 
18. =
 How do you skip a page in a SQL*Plus report? 
 
19.  Is there a way =
to document my SQL command and file? 
 
20.  How do I include the =
current date and time in a SQL*Plus report 
     heading? 
 
 =

QUESTIONS & ANSWERS 
 
1.  How do I find or delete duplicate =
rows in a table? 
 
Answer 
------ 
To delete duplicate rows =
from a table, you need to make use of a correlated  
subquery that =
includes the ROWID.  Please look at references for the syntax. 
 =

References 
---------- 
[PR:1004425.6] 
 
 
2.  How do I relink =
SQL*Plus on Unix? 
 
Answer 
------ 
Login as ORACLE and use =
SQLPLUS.MK install. 
 
References 
---------- 
[PR:1007202.6] 
 
 
3.  How do I create a flat =
ASCII file without rows wrapping? 
 
Answer 
------ 
You need to =
use the 'SPOOL', 'SET LINESIZE' and 'SET BUFFER' commands. 
 =

References 
---------- 
[PR:1020334.6] 
 
 
4.  How do I create an =
output file with commas between the column? 
 
Answer 
------ =

You will need to produce a .SQL file for the table so that the table =
output  
will have comma's between the columns.  
 
 
5.  How do =
I calculate an average date with time component? 
 
Answer 
------ =

Use SELECT Avg( To_Number( To_Char(Col,'MMDDYYHHMI') ) ) FROM Table; =

 
References 
---------- 
[PR:1015849.6] 
 
 
6.  How do I insert data =
with ampersands (or special characters) in it? 
 
Answer 
------ =

Set DEFINE OFF or change the substitution character or use Escape \. =

 
References 
---------- 
[PR:1009415.6] 
 
 
7.  How do I create =
quotation marks or put quotes around character strings 
    in SQL? =

 
Answer 
------ 
To create a single quotation mark, use 4 =
single quotes: ''''. 
  
To place single quotes around a character =
string, enclose the character string  
within 3 single quotes: '''.  =

 
References 
---------- 
[PR:1005607.6] 
 
 
8.  How do I do a case =
insensitive search using the 'LIKE' operator? 
 
Answer 
------ =

Use 'UPPER' function on field to compare using the 'LIKE' operator. =

 
References 
---------- 
[PR:1025420.6] 
 
 
9.  How do I create a =
column delimited flat file? 
 
Answer 
------ 
1. Concatenate =
column separator in query and spool. 
2. SQL*Plus 3.2.x and above - =
use SET COLSEP command and spool. 
 
References 
---------- 
[PR:1012455.6] 
 
 
10.  How do I query top =
"N" rows ordered by a column? 
 
Answer 
------ 
Use a subquery =
in a where clause to count the fetched rows, and use an Order By  =

clause on the column. 
 
References 
---------- 
[PR:1009966.6] 
 
 
11.  Can one set default =
directory where SQL*Plus files are saved? 
 
Answer 
------ =

Currently, you cannot set an environment variable in the ORACLE.INI =
file that  
directs where to save SQL*Plus files.  Please look at =
references for a  
workaround. 
  
References 
---------- 
[PR:1016084.6] 
 
 
12.  How do you convert a =
character field into a date format without losing  
     the century =
data?   
 
Answer 
------ 
To retain the century information, =
you must convert the date field back into a  
character string.  
 =

References 
---------- 
[PR:1014213.6] 
 
 
13.  How can I get =
PUPBLD.SQL to drop public synonyms? 
 
Answer 
------ 
You may =
need to run specific 'DROP' commands for the Product User Profile  =

objects. 
 
References 
---------- 
[PR:1026049.6] 
 
 
14.  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 =3D =
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. 
 
References 
---------- =

[PR:1033568.6] 
 
 
15.  How do I get the =
system date and time over DBLINK? 
 
Answer 
------ 
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; 
 
 
16.  How do I execute SQL scripts in batch? =

 
Answer 
------ 
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.  
 
 
17.  How do I =
display the time component of a date column? 
 
Answer 
------ =

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 :=3D =
To_Char(:time1, 'DD-MON-YY HH:MI:SS');  
  
   The data returns in =
the following format:    
   01-SEP-94 15:01:01  
 
 
18.  How =
do you skip a page in a SQL*Plus report? 
 
Answer 
------ 
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=3Dy =

    GROUP BY fld1,fld2 ORDER BY fld1; 
 
 
19.  Is there a way =
to document my SQL command and file? 
 
Answer 
------ 
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; 
 
 =

20.  How do I include the current date and time in a SQL*Plus report =
heading? 
 
Answer 
------ 
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.
.
Copyright (c)=20 1995,2000 Oracle Corporation. All Rights Reserved.=20 Legal Notices and Terms of Use.
------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/foot_left.gif R0lGODlhDAALALMAAP///8zMzJmZmWZmZjMzMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAACwAAAAADAALAAAEIZCUSWsZNodsgeQTIIAhgIFA+mWpyrXpWcGpsBZ0GwxSBAA7 ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-Location: http://metalink.oracle.com/images/metalink/generic/usaeng/foot_right.gif R0lGODlhCwALALMAAP///8zMzJmZmWZmZjMzMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAACwAAAAACwALAAAEILDISQm9ZWAa9gReIYDbAJAXcaKTul7mihKjDBBDYK8RADs= ------=_NextPart_000_0020_01C45F51.C2483BC0 Content-Type: application/octet-stream Content-Transfer-Encoding: quoted-printable Content-Location: http://metalink.oracle.com/metalink/plsql/ml2_draw.showFooterFrame =EF=BB=BF Oracle Support
 
------=_NextPart_000_0020_01C45F51.C2483BC0--