www.techonthenet.com Open in urlscan Pro
64.40.117.204  Public Scan

Form analysis
0 forms found in the DOM

Text Content

Advertisements


 1. Home
 2. Oracle / PLSQL

JavaScript is required for this website to work properly. Please re-enable
JavaScript in your browser settings.
 * Databases
 * SQL
 * Oracle / PLSQL
 * SQL Server
 * MySQL
 * MariaDB
 * PostgreSQL
 * SQLite

 * MS Office
 * Excel
 * Access
 * Word

 * Web Development
 * HTML
 * CSS
 * JavaScript
 * Color Picker

 * Programming
 * C Language

 * More
 * ASCII
 * Unicode
 * Linux
 * UNIX
 * Techie Humor

Advertisements




ORACLE BASICS

 * ALIASES
 * AND
 * AND & OR
 * BETWEEN
 * COMPARISON OPERATORS
 * DELETE
 * DISTINCT
 * EXISTS
 * FROM
 * GROUP BY
 * HAVING
 * IN
 * INSERT
 * INSERT ALL
 * INTERSECT
 * IS NOT NULL
 * IS NULL
 * JOIN
 * LIKE
 * MINUS
 * NOT
 * OR
 * ORDER BY
 * PIVOT
 * REGEXP_LIKE
 * SELECT
 * SUBQUERY
 * TRUNCATE
 * UNION
 * UNION ALL
 * UPDATE
 * WHERE


ORACLE ADVANCED

 * Alter Table
 * Alter Tablespace
 * Change Password
 * Check Constraints
 * Comments in SQL
 * Create Schema
 * Create Schema Statement
 * Create Table
 * Create Table As
 * Create Tablespace
 * Create User
 * Data Types
 * Declare Variables
 * Drop Table
 * Drop Tablespace
 * Drop User
 * Error Messages
 * Find Default Tablespace
 * Find Users
 * Find Users Logged In
 * Find Version Information
 * Functions
 * Global Temporary
 * Grant/Revoke Privileges
 * Indexes
 * Literals
 * Local Temporary
 * Primary Keys
 * Procedures
 * Roles
 * Sequences
 * Set Default Tablespace
 * Synonyms
 * System Tables
 * Unique Constraints
 * Views


ORACLE CURSORS

 * Close Cursor
 * Cursor Attributes
 * Declare Cursor
 * Fetch Cursor
 * Open Cursor
 * Select For Update
 * Where Current Of


ORACLE EXCEPTION HANDLING

 * Named Programmer-Defined Exception
 * Named System Exception
 * WHEN OTHERS Clause
 * SQLCODE
 * SQLERRM


ORACLE FOREIGN KEYS

 * Disable Foreign Key
 * Drop Foreign Key
 * Enable Foreign Key
 * Foreign Key
 * Foreign Key (cascade delete)
 * Foreign Key (set null delete)


ORACLE LOOPS/CONDITIONALS

 * CURSOR FOR LOOP
 * EXIT
 * FOR LOOP
 * GOTO
 * IF-THEN-ELSE
 * LOOP
 * REPEAT UNTIL LOOP
 * WHILE LOOP


ORACLE TRANSACTIONS

 * Commit Transaction
 * Lock Table
 * Rollback Transaction
 * Set Transaction


ORACLE TRIGGERS

 * After Delete Trigger
 * After Insert Trigger
 * After Update Trigger
 * Before Delete Trigger
 * Before Insert Trigger
 * Before Update Trigger
 * Disable All Triggers
 * Disable Trigger
 * Drop Trigger
 * Enable All Triggers
 * Enable Trigger


STRING/CHAR FUNCTIONS

 * ASCII
 * ASCIISTR
 * CHR
 * COMPOSE
 * CONCAT
 * Concat with ||
 * CONVERT
 * DECOMPOSE
 * DUMP
 * INITCAP
 * INSTR
 * INSTR2
 * INSTR4
 * INSTRB
 * INSTRC
 * LENGTH
 * LENGTH2
 * LENGTH4
 * LENGTHB
 * LENGTHC
 * LOWER
 * LPAD
 * LTRIM
 * NCHR
 * REGEXP_INSTR
 * REGEXP_REPLACE
 * REGEXP_SUBSTR
 * REPLACE
 * RPAD
 * RTRIM
 * SOUNDEX
 * SUBSTR
 * TRANSLATE
 * TRIM
 * UPPER
 * VSIZE


NUMERIC/MATH FUNCTIONS

 * ABS
 * ACOS
 * ASIN
 * ATAN
 * ATAN2
 * AVG
 * BITAND
 * CEIL
 * COS
 * COSH
 * COUNT
 * EXP
 * FLOOR
 * GREATEST
 * LEAST
 * LN
 * LOG
 * MAX
 * MEDIAN
 * MIN
 * MOD
 * POWER
 * REGEXP_COUNT
 * REMAINDER
 * ROUND (numbers)
 * ROWNUM
 * SIGN
 * SIN
 * SINH
 * SQRT
 * SUM
 * TAN
 * TANH
 * TRUNC (numbers)


DATE/TIME FUNCTIONS

 * ADD_MONTHS
 * CURRENT_DATE
 * CURRENT_TIMESTAMP
 * DBTIMEZONE
 * EXTRACT
 * LAST_DAY
 * LOCALTIMESTAMP
 * MONTHS_BETWEEN
 * NEW_TIME
 * NEXT_DAY
 * ROUND (dates)
 * SESSIONTIMEZONE
 * SYSDATE
 * SYSTIMESTAMP
 * TRUNC (dates)
 * TZ_OFFSET


CONVERSION FUNCTIONS

 * BIN_TO_NUM
 * CAST
 * CHARTOROWID
 * FROM_TZ
 * HEXTORAW
 * NUMTODSINTERVAL
 * NUMTOYMINTERVAL
 * RAWTOHEX
 * TO_CHAR
 * TO_CLOB
 * TO_DATE
 * TO_DSINTERVAL
 * TO_LOB
 * TO_MULTI_BYTE
 * TO_NCLOB
 * TO_NUMBER
 * TO_SINGLE_BYTE
 * TO_TIMESTAMP
 * TO_TIMESTAMP_TZ
 * TO_YMINTERVAL


ANALYTIC FUNCTIONS

 * CORR
 * COVAR_POP
 * COVAR_SAMP
 * CUME_DIST
 * DENSE_RANK
 * FIRST_VALUE
 * LAG
 * LAST_VALUE
 * LEAD
 * LISTAGG
 * NTH_VALUE
 * RANK
 * STDDEV
 * VAR_POP
 * VAR_SAMP
 * VARIANCE


ADVANCED FUNCTIONS

 * BFILENAME
 * CARDINALITY
 * CASE
 * COALESCE
 * DECODE
 * EMPTY_BLOB
 * EMPTY_CLOB
 * GROUP_ID
 * LNNVL
 * NANVL
 * NULLIF
 * NVL
 * NVL2
 * SYS_CONTEXT
 * UID
 * USER
 * USERENV

--------------------------------------------------------------------------------


ORACLE / PLSQL: SEQUENCES (AUTONUMBER)

This Oracle tutorial explains how to create and drop sequences in Oracle with
syntax and examples.


DESCRIPTION

In Oracle, you can create an autonumber field by using sequences. A sequence is
an object in Oracle that is used to generate a number sequence. This can be
useful when you need to create a unique number to act as a primary key.


CREATE SEQUENCE

You may wish to create a sequence in Oracle to handle an autonumber field.


SYNTAX

The syntax to create a sequence in Oracle is:

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

sequence_name The name of the sequence that you wish to create.


EXAMPLE

Let's look at an example of how to create a sequence in Oracle.

For example:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

This would create a sequence object called supplier_seq. The first sequence
number that it would use is 1 and each subsequent number would increment by 1
(ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll
cover how to retrieve a value from this sequence object. To retrieve the next
value in the sequence order, you need to use nextval.

For example:

supplier_seq.NEXTVAL;

This would retrieve the next value from supplier_seq. The nextval statement
needs to be used in a SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The
supplier_id field would be assigned the next number from the supplier_seq
sequence. The supplier_name field would be set to Kraft Foods.


DROP SEQUENCE

Once you have created your sequence in Oracle, you might find that you need to
remove it from the database.


SYNTAX

The syntax to a drop a sequence in Oracle is:

DROP SEQUENCE sequence_name;

sequence_name The name of the sequence that you wish to drop.


EXAMPLE

Let's look at an example of how to drop a sequence in Oracle.

For example:

DROP SEQUENCE supplier_seq;

This example would drop the sequence called supplier_seq.


FREQUENTLY ASKED QUESTIONS

One common question about sequences is:

Question: While creating a sequence, what does cache and nocache options mean?
For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

Answer: With respect to a sequence, the cache option specifies how many sequence
values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure
occurs, all cached sequence values that have not be used, will be "lost". This
results in a "gap" in the assigned sequence values. When the system comes back
up, Oracle will cache new numbers from where it left off in the sequence,
ignoring the so called "lost" sequence values.

TIP: To recover the lost sequence values, you can always execute an ALTER
SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option
may sacrifice some performance, however, you should not encounter a gap in the
assigned sequence values.

--------------------------------------------------------------------------------

Question: How do we set the LASTVALUE value in an Oracle Sequence?

Answer: You can change the LASTVALUE for an Oracle sequence, by executing an
ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would
like to reset the sequence to serve 225 as the next value. You would execute the
following commands.

ALTER SEQUENCE seq_name
INCREMENT BY 124;

SELECT seq_name.nextval FROM dual;

ALTER SEQUENCE seq_name
INCREMENT BY 1;

Now, the next value to be served by the sequence will be 225.

NEXT: Functions
Share on:


Advertisements



Home | About Us | Contact Us | Testimonials | Donate



While using this site, you agree to have read and accepted our Terms of Service
and Privacy Policy.

Copyright © 2003-2022 TechOnTheNet.com. All rights reserved.