Has satisfactory SQL background
Sequences are database objects used to implement the auto-incrementing feature on a column, especially on a PRIMARY KEY column. These objects generate integer values following a desired ascending numerical order.
A sequence can be created using the CREATE SEQUENCE statement followed by configuration clauses.
CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;
This will create an empty sequence that starts with 1 and is incrementing by 1 every time a next value is requested.
SQLPlus or SQL Developer might prevent you from creating sequences because of the absence of CREATE SEQUENCE privilege, or in simpler terms, you are not granted with the CREATE SEQUENCE privilege (unless the user is granted with all system privileges).
To grant the user ICT12A with the CREATE SEQUENCE privilege (and other privileges), do the following steps:
1. Type disc and press Enter to disconnect.
2. Type conn and enter the administrator's credentials to connect to the database.
3. Enter GRANT CREATE SEQUENCE, CREATE PROCEDURE TO ict12a; to grant the necessary privileges to the target user.
4. Once granted, repeat the steps 1 and 2 but with the user credentials.
5. Try to create the sequence again. This should create it without any issue.
Sequences are usually employed on INSERT statements to generate unique IDs in each record.
INSERT INTO users VALUES (my_sequence.nextval, 'Your Name', 18);
Each INSERT statement includes the next value identifier of the sequence, which will be evaluated during the execution of a statement. If you have an existing sequence to use in an INSERT statement, make sure to replace my_sequence with the name of another sequence.
To determine the current value of a sequence, use the following syntax:
SELECT [my_sequence].currval FROM DUAL;
Where [my_sequence] is a sequence available in your database system.
To delete a sequence, use the following syntax, that is, almost the same as dropping other types of database objects:
DROP SEQUENCE [my_sequence];
Resetting a sequence may seem complicated, but no need to worry, you will have just to copy and paste the code below to SQLPlus or SQL Developer to create the procedure. A procedure is usually a PL/SQL script containing various instructions to accomplish a particular task. Ensure [my_sequence] is replaced with the sequence you want to reset.
CREATE OR REPLACE PROCEDURE RESET_SEQUENCE AS
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE [my_sequence]';
EXECUTE IMMEDIATE 'CREATE SEQUENCE [my_sequence] START WITH 1 INCREMENT BY 1';
END RESET_SEQUENCE;
Note: In SQLPlus, type "\" without double quotes in a new line, then press enter to terminate the entire PL/SQL statement (although it is terminated with a semicolon).
If you are eager to know what does this do, then the explanation will be provided as promised.
The created procedure basically drops the sequence and creates it again with the same configuration clauses (should start with 1 and increment by 1). DROP statements are not allowed within procedure definitions. EXECUTE IMMEDIATE is used to execute ordinary SQL statements including DDL statements (CREATE and DROP).