Transcript Sequences • Sequences: a database object that generates unique numbers... series. Used to populated surrogate keys.

Sequences
• Sequences: a database object that generates unique numbers from an arithmetic
series. Used to populated surrogate keys.
• Syntax: CREATE SEQUENCE <sequence_name>
[INCREMENT BY <interval>] [START WITH <value>],
[MAXVALUE <maximum value>] [NOMAXVALUE]
[CACHE <# of values to cache>] [NOCACHE];
SQL> create sequence vendor_seq start with 1 increment by 1;
Sequence created.
• Using sequences
• generate new sequence values: sequence_name.nextval
SQL> select vendor_seq.nextval from dual;
NEXTVAL
--------1
• access the current sequence value: sequence_name.currval
• undefined until the first sequence value is generated
SQL> select vendor_seq.currval from dual;
CURRVAL
--------1
Creating custom forms
•
•
•
•
•
•
•
•
Adding more interface items and making it more user-friendly
Step0: Connect with the database server
Step1: Create a non-database block manually (not using the wizard)
Step2: Create a canvas from object navigator
Step3: Go to the layout editor
Step4: Add interface items (buttons, text items, radio buttons, etc.)
Step5: Change the properties of the interface items from property pallette
Creating radio buttons:
• Step1: Go to the layout editor
• Step2: Select the radio button icon from the left hand margin and
place it on the canvas
• Step3: From the property palette, change the name, label, and value
• Step4: From the object navigator, select the radio group
• Step5: Change the name, and set a initial value for the radio group
• Creating control buttons (refer to page 190 in book)
• Create buttons
• Change labels
• Change hints (see help property group in property palette
• Create WHEN-BUTTON-PRESSED triggers
Layout editor
Radio button
Text item
Display item
Trigger codes
• CREATE_BUTTON (refer to page 193):
--clear the form
CLEAR_FORM;
--assign form state to insert
:GLOBAL.mode := 'INSERT';
select itemid_seq.nextval
into :item_control_block.itemid_text
from dual;
• Explanation:
• Global variables are used for form characteristics
• Purpose: clear any information in the form
• use the sequence itemid_seq (created previously) to generate a new
item id
• :GLOBAL.mode: the form can be in various modes
• enter_query, execute_query, insert, update
• :item_control_block.itemid_text: an item is referred to as
:block-name.item-name
• dual: a dummy table used to complete SQL syntax
Trigger code
• SAVE_BUTTON (refer to page 198)
if :global.mode = 'INSERT' then
insert into item values
(:item_control_block.itemid_text, :item_control_block.itemdesc_text,
:item_control_block.category_radio_group);
:global.mode := 'UPDATE';
elsif :global.mode = 'UPDATE' then
update item
set itemdesc = :item_control_block.itemdesc_text, category =
:item_control_block.category_radio_group where itemid = :item_control_block.itemid_text;
end if;
commit;
--clear_form;
• The last command (clear_form) is commented so that you can see the
record after it is saved
• DELETE_BUTTON (see page 209)
--delete the current record
delete from ITEM where itemid = :item_control_block.itemid_text;
--commit the changes
commit;
--clear the form
clear_form;
Format mask (refer to page 236)
• Purpose: to display data in certain formats
• item property set from property palette
Testing the form
•
•
•
•
Ids (itemID, LOCID, etc.) should be display items.
Right now, the form is not ready for querying
You can only create a new record, and update a record that you just created
Once you create a record and save it, go to SQL*Plus editor and check if
the record has been posted in the database
• If you get the error message:
FRM:40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception
ORA-01407
it means that you are trying to enter a null value to a non-null field
• Ignore the message: FRM: 40401: No changes to save, but check if the changes
• have been made from the SQL*Plus editor.