Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: Sequence Manipulation
Oracle Tips by Burleson
 

In addition to Oracle style sequence manipulation (sequence.NEXTVAL and sequence.CURRVAL), EnterpriseDB provides PostgreSQL style manipulation including the ability to set the sequence value.

NEXTVAL:  Advance a sequence counter and return the value:

* Parameter 1 = VARCHAR2, Valid Sequence name
* Returns = NUMBER

CURRVAL:  Return the current value of a sequence:

* Parameter 1 = VARCHAR2, Valid Sequence name
* Returns = NUMBER

SETVAL:  Set a sequence's CURRVAL and return that value:

* Parameter 1 = VARCHAR2, Valid Sequence name
* Parameter 2 = NUMBER
* Returns = NUMBER

CREATE SEQUENCE seq1;
CREATE SEQUENCE seq2;

SELECT nextval('seq1') pg_style,
       seq2.nextval ora_style
  FROM DUAL;

SELECT currval('seq1') pg_style,
       seq2.currval ora_style
  FROM DUAL;

SELECT setval('seq1', 5),
       setval('seq2', 50)
  FROM DUAL;

SELECT nextval('seq1') pg_style,
       seq2.nextval ora_style
  FROM DUAL;

edb=# CREATE SEQUENCE seq1;
CREATE SEQUENCE
edb=# CREATE SEQUENCE seq2;
CREATE SEQUENCE

edb=# SELECT nextval('seq1') pg_style,
edb-#        seq2.nextval ora_style
edb-#   FROM DUAL;

 pg_style | ora_style
----------+-----------
        1 |         1

(1 row)

edb=#

edb=# SELECT currval('seq1') pg_style,
edb-#        seq2.currval ora_style
edb-#   FROM DUAL;

 pg_style | ora_style
----------+-----------
        1 |         1

(1 row)

edb=# SELECT setval('seq1', 5),
edb-#        setval('seq2', 50)
edb-#   FROM DUAL;

 setval | setval
--------+--------
      5 |     50

(1 row)

edb=#

edb=# SELECT nextval('seq1') pg_style,
edb-#        seq2.nextval ora_style
edb-#   FROM DUAL;

 pg_style | ora_style
----------+-----------
        6 |        51

(1 row)

Greatest and Least

GREATEST:  Return the highest number in a list of numbers:

* Parameter X = Variable list of NUMBER
* Returns = NUMBER

LEAST:  Return the lowest number in a list of numbers:

* Parameter X = Variable list of NUMBER
* Returns = NUMBER

SELECT GREATEST(2, 7, 25, 16, 1, 5, 4) grt,
       LEAST(2, 7, 25, 16, 1, 5, 4) lst
  FROM DUAL;

edb=# SELECT GREATEST(2, 7, 25, 16, 1, 5, 4) grt,
edb-#        LEAST(2, 7, 25, 16, 1, 5, 4) lst
edb-#   FROM DUAL;

 grt | lst
-----+-----
  25 |   1

(1 row)

Miscellaneous

DECODE:  Perform IF logic on a variable set of expressions:

* Parameter 1 = NUMBER, TEXT, Date, Expression
* Returns = Data Type of input

The logic is IF parameter 1 = Parameter 2 THEN return Parameter 3, ELSE return Parameter 4.

Parameters 2 and 3 are repeatable, so that the logic continues:

IF parameter 1 = Parameter 2 THEN return Parameter 3, ELSE IF Parameter 1 = Parameter 4 THEN Return Parameter 5 ELSE IF …..

The last even numbered parameter is the default in the case there is no match.  If the decode parameters end on an odd number, then there is no default.  If no default is defined, the result will be NULL.

SELECT decode('A', 'B', 'C', 'D');
SELECT decode('A', 'B', 'C');
SELECT decode('A', 'A', 'C', 'D');
SELECT decode('A', 'B', 'C', 'D', 'A', 'A', 'F', 'G');
SELECT decode('A', 'B', 'C', 'D', 'A', 'B', 'F', 'G');
SELECT decode('A', 'B', 'C', 'D', 'A', 'B', 'F');

edb=# SELECT decode('A', 'B', 'C', 'D');

 decode
--------
 D

(1 row)

edb=# SELECT decode('A', 'B', 'C');

 decode
--------

(1 row)

edb=# SELECT decode('A', 'A', 'C', 'D');

 decode
--------
 C

(1 row)

edb=# SELECT decode('A', 'B', 'C', 'D', 'A', 'A', 'F', 'G');

 decode
--------
 F

(1 row)

edb=# SELECT decode('A', 'B', 'C', 'D', 'A', 'B', 'F', 'G');

 decode
--------
 G

(1 row)

edb=# SELECT decode('A', 'B', 'C', 'D', 'A', 'B', 'F');

 decode
--------

(1 row)

edb=#

NVL:  Assign a default when a column or expression is NULL:

* Parameter 1 = NUMBER, TEXT, DATE, Expression
* Parameter 2 =NUMBER, TEXT, DATE, Expression, the Default Value
* Returns = NUMBER, TEXT, DATE, Expression

SELECT nvl(NULL,'A'), NVL('A','B'), NVL(1+6,0), NVL(NULL,0);

edb=# SELECT nvl(NULL,'A'), NVL('A','B'), NVL(1+6,0), NVL(NULL,0);

 nvl | nvl | nvl | nvl
-----+-----+-----+-----
 A   | A   |   7 |   0

(1 row)

edb=#

TRUNC:  Truncate the decimals from a floating-point number or the time from a timestamp:

* Parameter 1 = NUMBER, DATE or TIMESTAMP
* Parameter 2 = NUMBER or VARCHAR2 Date Format Mask, Optional, number of decimals positions to keep on numeric input or date/time portion to keep on timestamp input
* Returns = NUMBER or TIMESTAMP

SELECT TRUNC(40.123456,3),
       TRUNC(40.123456,0),
       TRUNC(40.123456),
       TRUNC(SYSDATE),
       TRUNC(SYSDATE, 'MM'),
       TRUNC(SYSDATE, 'YYYY')
  FROM DUAL;

edb=# SELECT TRUNC(40.123456,3),
edb-#        TRUNC(40.123456,0),
edb-#        TRUNC(40.123456),
edb-#        TRUNC(SYSDATE),
edb-#        TRUNC(SYSDATE, 'MM'),
edb-#        TRUNC(SYSDATE, 'YYYY')

edb-#   FROM DUAL;

 trunc  | trunc | trunc |       trunc        |       trunc        |       trunc
--------+-------+-------+--------------------+--------------------+--------------40.123 |    40 |    40 | 11-NOV-06 00:00:00 | 01-NOV-06 00:00:00 | 01-JAN-06 00:00:00

(1 row)

             
This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter