download native barcode generator for crystal reports Using Sequences in Font

Painting QR Code in Font Using Sequences

Oracle uses a sequence generator to automatically generate a unique sequence of numbers that users can use in their operations. Sequences are commonly used to create a unique number to generate a unique primary key for a column. We ll look at using an Oracle sequence to generate employee numbers during a data insert.

If users were to use programmatically created sequence numbers instead, Oracle would have to constantly lock and unlock records holding the maximum value of those sequences to ensure an orderly incrementing of the sequence. This locking would result in users waiting for the next value in the sequence to be issued to their transactions. Oracle s automatic generation of sequences increases database concurrency.

You have several options to choose from to create a sequence. We will use a plain vanilla sequence that starts at 10,000 and is incremented by 1 each time. The sequence is never recycled or reused, because we want distinct sequence numbers for each employee.

Note There are two pseudo-columns called currval and nextval that you can use to query sequence values. The currval pseudo-column provides you with the current value of the sequence, and the nextval pseudocolumn gets you the new or next sequence number.

First, create a sequence as shown in the following example. This is usually the way you use a sequence to generate a unique primary key for a column. SQL> CREATE SEQUENCE employee_seq START WITH 10000 INCREMENT BY 1 NO MAXVALUE NO CYCLE; Sequence created. SQL> Second, select the current sequence number by using the following statement: SQL> SELECT employee_seq.currval FROM dual;

Third, insert a new row into the employee table using nextval from the employee_seq sequence: SQL> 2 3 4* INSERT INTO employees(employee_id, first_name, last_name, email, phone_number, hire_date) VALUES (employee_seq.nextval,'sam','alapati','salapati.tnt.org' ,345-555-5555,to_char('21-JUN-2005'); 1 row created. SQL> COMMIT; Commit complete. Finally, check to make sure the employee_id column is being populated by the employee_seq sequence: SQL> SELECT employee_id, first_name, last_name FROM employees WHERE last_name = 'alapati'; EMPLOYEE_ID FIRST_NAME LAST_NAME ---------------------------------------------10011 sam alapati SQL>

When you use sequences, make sure that you drop them before performing a table import to avoid inconsistent data.

Note that you can have an Oracle sequence that is incremented continuously, but there may be occasional gaps in the sequence numbers. This is because Oracle always keeps 20 values (by default) in memory, and that s where it gets the nextval from. If there should be a database crash, the numbers stored in memory will be lost, and there will be a gap in that particular sequence.

Oracle triggers are similar to PL/SQL procedures, but they are automatically fired by the database based on specified events. For DBAs, triggers come in handy in performing audit- and securityrelated tasks. Besides the standard Oracle triggers, which fire before or after DML statements, there are powerful triggers based on system events, such as database startup and shutdown and the users logging on and logging off. 11 shows you how to use triggers to enhance database security. You create a trigger with the CREATE TRIGGER statement. You can choose to have the trigger fire BEFORE, AFTER, or INSTEAD OF the triggering event. The following example shows the structure of the CREATE TRIGGER statement for a BEFORE event trigger. Before a DML statement can delete, insert, or update a row in the employee table, Oracle automatically fires this trigger: SQL> CREATE TRIGGER scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON emp . . . /* Your SQL or PL/SQL code here

When you create a trigger, it is enabled by default. If you want to temporarily disable a trigger for some reason, you use the following statement: SQL> ALTER TRIGGER test DISABLE; You can re-enable this trigger by using the following command: SQL> ALTER TRIGGER test ENABLE;