Home » Other » Suggestions & Feedback » SQL*Plus FAQ for client tools forum
SQL*Plus FAQ for client tools forum [message #619655] Thu, 24 July 2014 01:04 Go to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
This topic would be used to prepare the FAQ sticky for SQL*Plus in client tools forum.
Re: SQL*Plus FAQ for client tools forum [message #619658 is a reply to message #619655] Thu, 24 July 2014 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can start with SQL*Plus FAQ.

Re: SQL*Plus FAQ for client tools forum [message #619661 is a reply to message #619658] Thu, 24 July 2014 02:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes Michel, was looking into it. Thanks.
Re: SQL*Plus FAQ for client tools forum [message #619679 is a reply to message #619661] Thu, 24 July 2014 06:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. Why don't I see output of DBMS_OUTPUT in SQL*Plus?

Before executing the PL/SQL unit, you should SET SERVEROUTPUT ON. It is required to do once in a session.

./fa/12069/0/
Re: SQL*Plus FAQ for client tools forum [message #619680 is a reply to message #619679] Thu, 24 July 2014 06:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
2. How to format the SQL output? Few columns are too long.

column <column_name> format <model>

Below test case has a table with two columns of NUMBER and VARCHAR2 data types respectively. When the columns are projected, the display of output is not formatted properly. So use FORMAT command to format the display as per your need. Different data types have different format model.

./fa/12070/0/
Re: SQL*Plus FAQ for client tools forum [message #619681 is a reply to message #619680] Thu, 24 July 2014 06:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
To clear the formatting on a single column and all columns together:

./fa/12071/0/
Re: SQL*Plus FAQ for client tools forum [message #619684 is a reply to message #619681] Thu, 24 July 2014 06:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
3. How to remove the repeating column names after a set of rows?

Increase the pagesize using following command:
SET PAGESIZE <value>

./fa/12072/0/
  • Attachment: pagesize.jpg
    (Size: 35.60KB, Downloaded 186 times)
Re: SQL*Plus FAQ for client tools forum [message #619691 is a reply to message #619684] Thu, 24 July 2014 07:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
4. I don't want to print certain columns returned by query.

Column <column_name> noprint

In below test case, I don't want to print the MGR and COMM columns.

./fa/12074/0/
  • Attachment: noprint.jpg
    (Size: 47.92KB, Downloaded 175 times)
Re: SQL*Plus FAQ for client tools forum [message #619695 is a reply to message #619691] Thu, 24 July 2014 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't want to print certain columns returned by query.


The question will be then "why do you select them?". Smile

Re: SQL*Plus FAQ for client tools forum [message #619696 is a reply to message #619695] Thu, 24 July 2014 09:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Thu, 24 July 2014 19:15

"why do you select them?". Smile


For lazy developers who stick to "select *" and still want specific columns NOT to be displayed. We saw such question in recent past I guess Smile
Re: SQL*Plus FAQ for client tools forum [message #619699 is a reply to message #619696] Thu, 24 July 2014 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My purpose was to emphasize to serious need of this. For instance, to order the result on something you don't want to get:
SQL> col dt noprint
SQL> select hiredate dt, to_char(hiredate,'DD/MM/YYYY') hiredate, ename from emp order by dt;
HIREDATE   ENAME
---------- ----------
17/12/1980 SMITH
20/02/1981 ALLEN
22/02/1981 WARD
02/04/1981 JONES
01/05/1981 BLAKE
09/06/1981 CLARK
08/09/1981 TURNER
28/09/1981 MARTIN
17/11/1981 KING
03/12/1981 JAMES
03/12/1981 FORD
23/01/1982 MILLER
19/04/1987 SCOTT
23/05/1987 ADAMS

An order on the first displayed column (second in the query) will not give the result in the expected order.
Another example is when you want to display a set of queries in UNION ALL in a specific order then you can add a pseudo-column which numbers the query in the order you want in the final result and order this UNION ALL on this column (and possibly other ones).

[Updated on: Thu, 31 July 2014 14:36]

Report message to a moderator

Re: SQL*Plus FAQ for client tools forum [message #619703 is a reply to message #619699] Thu, 24 July 2014 11:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Good point Michel. I am thinking to include a note for this FAQ and use your demonstration. If possible, would you, please post a test case, I will use the same citing your contribution.
Re: SQL*Plus FAQ for client tools forum [message #619720 is a reply to message #619703] Thu, 24 July 2014 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's one (but you can find others I wrote on AskTom, DBA-Village and maybe even here).

Re: SQL*Plus FAQ for client tools forum [message #619723 is a reply to message #619720] Thu, 24 July 2014 13:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks Michel. I will reproduce your test case posted in asktom. In DBA-village and OraFAQ it would be easy to find your stuff as I am registered and also a regular visitor. Will try my best to consolidate your test cases and post it in test forum for you to review, will intimate you via PM once I am done.
Re: SQL*Plus FAQ for client tools forum [message #619726 is a reply to message #619723] Thu, 24 July 2014 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I find the following ones here:
http://www.orafaq.com/forum/mv/msg/184285/567572/#msg_567572
http://www.orafaq.com/forum/mv/msg/161876/477059/#msg_477059
http://www.orafaq.com/forum/mv/msg/189561/596259/#msg_596259

Re: SQL*Plus FAQ for client tools forum [message #620842 is a reply to message #619726] Wed, 06 August 2014 10:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
FYI...
I am currently in transistion state from previous organization to my new employer. I might need a week's time to start working on this topic again. Unfortunately, I have lost the backup (I maintained a word document) of the above posted FAQs.
Re: SQL*Plus FAQ for client tools forum [message #620846 is a reply to message #620842] Wed, 06 August 2014 10:52 Go to previous messageGo to next message
Littlefoot
Messages: 19883
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So we'll have to take care about what we do with this topic. On the other hand, it doesn't require too much effort to reverse the process and copy these messages into a brand new Word document.
Re: SQL*Plus FAQ for client tools forum [message #620848 is a reply to message #620846] Wed, 06 August 2014 11:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes. I will first download the images and prepare a backup document to use it further for future posts.

It's such a pain to explain and convince the "risk & compliance" team, while trying to send the documents to personal email which are needed for future use. Unfortunately, I couldn't convince them during my last working day and I lost my docs.
Re: SQL*Plus FAQ for client tools forum [message #621277 is a reply to message #620848] Tue, 12 August 2014 06:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
5. How to find where the SQL*Plus client is installed?

In your OS command line, type the following :

where sqlplus


The returned output is the directory where it resides.


Re: SQL*Plus FAQ for client tools forum [message #621278 is a reply to message #621277] Tue, 12 August 2014 06:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
6. How to get the list of all RESERVED WORDS in SQL and PL/SQL?

Use the SQL*Plus HELP utility.

SQL> help reserved

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE

 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION

 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*
Re: SQL*Plus FAQ for client tools forum [message #621279 is a reply to message #621277] Tue, 12 August 2014 06:28 Go to previous messageGo to next message
Littlefoot
Messages: 19883
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think so (regarding #5)

./fa/12098/0/
  • Attachment: where_xp.png
    (Size: 5.61KB, Downloaded 89 times)

[Updated on: Tue, 12 August 2014 06:28]

Report message to a moderator

Re: SQL*Plus FAQ for client tools forum [message #621281 is a reply to message #621279] Tue, 12 August 2014 06:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Tue, 12 August 2014 16:58
I don't think so (regarding #5)


Hmm, the path variable in environment variables is not set.
Re: SQL*Plus FAQ for client tools forum [message #621288 is a reply to message #621281] Tue, 12 August 2014 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59976
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

About 6#, only if the help was installed by the DBA which is quite rare.
Otherwise:
SQL> help reserved
SP2-0171: HELP system not available.

SP2-0171 HELP system not available 
Cause: Command-line SQL*Plus help is not installed in this Oracle instance.
Action: Command-line SQL*Plus help is not installed in this Oracle instance. 
Use the sqlplus/admin/help/hlpbld.sql script to install HELP on this database: 
sqlplus system @hlpbld.sql helpus.sql
Re: SQL*Plus FAQ for client tools forum [message #621289 is a reply to message #621281] Tue, 12 August 2014 07:40 Go to previous messageGo to next message
ThomasG
Messages: 3121
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Lalit Kumar B wrote on Tue, 12 August 2014 13:42
Littlefoot wrote on Tue, 12 August 2014 16:58
I don't think so (regarding #5)


Hmm, the path variable in environment variables is not set.


Doesn't help. Wink

~ $ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/pfk/app/oracle/product/11.2.0//bin:/pfk/bin:/home/oracle/bin
~ $ where sqlplus
-bash: where: command not found
~ $ which sqlplus
/pfk/app/oracle/product/11.2.0/bin/sqlplus


Although: Thanks, I didn't know "newer" versions of Windows had a "where" command that works like the "which" command in *nix. (But XP doesn't seen to have it).
Re: SQL*Plus FAQ for client tools forum [message #621312 is a reply to message #621281] Tue, 12 August 2014 09:10 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
This depends on the version of Windows.
Re: SQL*Plus FAQ for client tools forum [message #621317 is a reply to message #621312] Tue, 12 August 2014 09:15 Go to previous message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
To clarify, I did that in Win 7. I will include these points in that point# 5 in a constructive way.
Previous Topic: Site statistics
Next Topic: DB-Pub.com, a replica of OraFAQ? Is it legal or plagiarised?
Goto Forum:
  


Current Time: Thu Dec 18 03:52:09 CST 2014

Total time taken to generate the page: 0.12048 seconds