|
|
|
|
|
|
|
|
|
|
Re: SQL*Plus FAQ for client tools forum [message #619699 is a reply to message #619696] |
Thu, 24 July 2014 10:52 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #619723 is a reply to message #619720] |
Thu, 24 July 2014 13:23 |
Lalit Kumar B
Messages: 3174 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 #620842 is a reply to message #619726] |
Wed, 06 August 2014 10:19 |
Lalit Kumar B
Messages: 3174 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 #620848 is a reply to message #620846] |
Wed, 06 August 2014 11:11 |
Lalit Kumar B
Messages: 3174 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 #621278 is a reply to message #621277] |
Tue, 12 August 2014 06:27 |
Lalit Kumar B
Messages: 3174 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 #636211 is a reply to message #621317] |
Mon, 20 April 2015 01:57 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
7. How to get rid off the underline below column headers
In below test case, I don't want to print the underline for the column headers.
SQL> SELECT empno, ename FROM emp WHERE ROWNUM <= 5;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
SQL> SET UNDERLINE OFF
SQL> SELECT empno, ename FROM emp WHERE ROWNUM <= 5;
EMPNO ENAME
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
SQL>
|
|
|