Sequences

1
Sequences

• Sequences a database object that generates
  unique numbers from an arithmetic
• series. Used to
  populated surrogate keys.
• Syntax CREATE SEQUENCE ltsequence_namegt
• INCREMENT BY ltintervalgt
  START WITH ltvaluegt,
• MAXVALUE ltmaximum valuegt
  NOMAXVALUE
• CACHE lt of values to cachegt
  NOCACHE

SQLgt create sequence vendor_seq start with 1
increment by 1 Sequence created.

• Using sequences
• generate new sequence values
  sequence_name.nextval
• access the current sequence value
  sequence_name.currval
• undefined until the first sequence value is
  generated

SQLgt select vendor_seq.nextval from dual
NEXTVAL --------- 1
SQLgt select vendor_seq.currval from dual
CURRVAL --------- 1
2
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

3
Layout editor
Radio button Text item Display item
4
(No Transcript)
5
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.itemi
d_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

6
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
7
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
  SQLPlus editor and check if
• the record has been posted in the database
• If you get the error message
• FRM40735 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 SQLPlus editor.