CRUD in Oracle Forms

To create a basic CRUD in oracle form for creating and updating data consider the following. This is not a tuned application, but a beginer can try create a data entry form using ORACLE FORMS that can enebale an operator to add and update data using the following form.

Consider following table

TABLE NAME : CITY_MAST

Create Table City_mast (
City_code varchar2(4),
City_code varchar2(4),
City_name Varchar2(25),
State_code varchar2(4),     FK  -- state_mast table
Country_code Varchar(3),   FK  -- Country_mast table
City_Short_name Varchar2(5),
Dial_code number(8),
Latitude varchar2(8),
Longitude varchar2(8),
City_type varchar(2)        
)

FORM

TWO BLOCKS

1. CONTROL ( Base table "NO")

ITEMS In the Control Block

CITY_CODE ( Item Type Text Item)
CRUP ( ITEM TYPE RADIO GROUP) that has CREATE and UPDATE Radio Button

2. CITY     (BASE table "yes)  to create this block one cna your the wizard and select CITY_MAST table, include all the cols of CITY_MAST table and this will be displayed in the default canvas of the form.

3 BUTTONS (  blocks can have all the press buttons items like save, clear,exit,list,etc)

NAVIGATION In FOLLOWING CHRONOLOGICAL ORDER FOR THE VARIOUS ITEMS DISPLAYED ON THE FORM CANVAS.

01. Create / Update ( radio button in CRUP radio group)  ( control block)
02. CITY_CODE   ( ITEM TYPE TEXT ITEM ,  BLOCK CONTROL)
03. CITY_NAME   ( ITEM TYPE TEXT ITEM ,  BLOCK CITY )
04. CITY_SHORT_NAME   ( ITEM TYPE TEXT ITEM ,  BLOCK CITY )
05. CITY_TYPE    ( ITEM TYPE POPLIST,  BLOCK CITY ) Values in POP list can be  (Village,Tehsil,District)
06. COUNTRY_CODE  ( ITEM TYPE DISPLAY ITEM,  BLOCK CITY ) -- Value in this will be moved from LOV that exists on ITEM COUNTRY_NAME
07. COUNTRY_NAME  ( ITEM TYPE TEXT ITEM,  BLOCK CITY ) -- This field will have LOV to select state from COUNTRY_MAST  table.
08. STATE_CODE  ( ITEM TYPE DISPLAY ITEM,  BLOCK CITY ) -- Value in this will be moved from LOV that exists on ITEM STATE_NAME
09. STATE_NAME  ( ITEM TYPE TEXT ITEM,  BLOCK CITY ) -- This field will have LOV to select state from state_mast table. LOV to list states of relevant country thats seleted by the operator of the form.
10. DIAL_CODE ( ITEM TYPE TEXT ITEM,  BLOCK CITY )
11. LATITUDE ( ITEM TYPE TEXT ITEM,  BLOCK CITY )
12  LONGITUDE ( ITEM TYPE TEXT ITEM,  BLOCK CITY )
13. SAVE (ITEM TYPE BUTTON,  BLOCK BUTTONS )
14. CLEAR ( ITEM TYPE TEXT ITEM,  BLOCK CITY )
15  EXIT ( ITEM TYPE TEXT ITEM,  BLOCK CITY )

TRIGGERS THAT CAN NE WRITTEN ON THE VARIOUS ITEMS

1.CRUP ( Radio Group with Two Radio Button for [C]reate and [U]pdate ) WHEN_MOUSE_CLICK,KEY_NEXT_ITEM.
2. CITY_CODE (TEXT_ITEM) Key_Next_item
3. CITY_NAME,STATE_NAME,COUNTRY_NAME ( TEXT_ITEM) KEY_NEXT_ITEM, WHEN_NEW_ITEM_INSTANCE
4. SAVE.CLEAR,EXIT( BUTTON ) WHEN_BUTTON_PRESSED.

Following trigger text on KEY_NEXT_ITEM on CITY_CODE (Control Block) item can be  written for build CRUb in Oracle  forms

//

if :crup = 'U' -- C and U can be the default values for Create and Update Radio Button in CRUP radio group

Then

Next_block;

Execute_query(no_validate)

End If;

//

After above trigger is execute and curson can be on CITY_NAME item. press the DOWN ARROW key and see if records change..if this happens then someting incomplete...

at this stage..... i quit and one can spend some time on it and resolve it completely..

This artcile will continue.....