Oracle Form Data Entry Sample

I shared a data entry example form here in this post for Oracle Forms beginner developers, so that they may take an idea to how to develop a simple data entry form in Oracle Forms. The form can be fully modified.

Example is given for an emp table and other related tables. You can download Emp.fmb form from Google drive by clicking here Emp.Fmb Form

To run this form you would require some tables in Payroll schema, so create a user/schema with name Payroll and run the following script:

-- start
CREATE TABLE DESIG
(
  DNAME  VARCHAR2(60 BYTE)
);


CREATE TABLE DEPT
(
  DNAME  VARCHAR2(60 BYTE)
)
;


CREATE TABLE EMP
(
  ECODE     VARCHAR2(10 BYTE),
  ENAME     VARCHAR2(60 BYTE),
  DESIG     VARCHAR2(60 BYTE),
  DEPT      VARCHAR2(60 BYTE),
  ADDR1     VARCHAR2(60 BYTE),
  ADDR2     VARCHAR2(60 BYTE),
  CITY      VARCHAR2(30 BYTE),
  STATE     VARCHAR2(30 BYTE),
  PINCODE   VARCHAR2(6 BYTE),
  PHONE     VARCHAR2(30 BYTE),
  DOJ       DATE,
  DOR       DATE,
  DOP       DATE,
  PFNO      VARCHAR2(30 BYTE),
  ESINO     VARCHAR2(60 BYTE),
  BASIC     NUMBER(10)                          DEFAULT 0,
  HRA       NUMBER(10)                          DEFAULT 0,
  CONV      NUMBER(10)                          DEFAULT 0,
  MEDC      NUMBER(10)                          DEFAULT 0,
  BONUS     NUMBER(10)                          DEFAULT 0,
  TEL       NUMBER(10)                          DEFAULT 0,
  OTHA      NUMBER(10)                          DEFAULT 0,
  CL        NUMBER(3)                           DEFAULT 0,
  EL        NUMBER(5)                           DEFAULT 0,
  SL        NUMBER(3)                           DEFAULT 0,
  PFA       NUMBER(10)                          DEFAULT 0,
  TDS       NUMBER(10)                          DEFAULT 0,
  TDSMB     NUMBER(2)                           DEFAULT 0,
  ADVTOTAL  NUMBER(10)                          DEFAULT 0,
  ADVDED    NUMBER(10)                          DEFAULT 0,
  BANKAC    VARCHAR2(50 BYTE),
  FY        VARCHAR2(9 BYTE),
  CP        VARCHAR2(30 BYTE),
  FYCP      VARCHAR2(40 BYTE),
  ESI       NUMBER(10,2),
  TDSDED    NUMBER(10,2)
)
;


CREATE TABLE FYCPT
(
  FY    VARCHAR2(9 BYTE),
  CP    VARCHAR2(30 BYTE),
  FYCP  VARCHAR2(40 BYTE)
)
;


CREATE TABLE MNTRANS
(
  TRANID      NUMBER(10),
  TRANDATE    DATE,
  ECODE       VARCHAR2(10 BYTE),
  ENAME       VARCHAR2(60 BYTE),
  NOOFABS     NUMBER(3),
  NOOFWD      NUMBER(3),
  NETBASIC    NUMBER(10,2),
  NETCONV     NUMBER(10,2),
  NETMED      NUMBER(10,2),
  NETBONUS    NUMBER(10,2),
  NETTEL      NUMBER(10,2),
  NETHRA      NUMBER(10),
  OT_HRS      NUMBER(10),
  OT_HRSAMT   NUMBER(10,2),
  OT_SAL      NUMBER(10,2),
  OTHER_A     NUMBER(10),
  GRS_SAL     NUMBER(10,2),
  PFA         NUMBER(10),
  ESIA        NUMBER(10),
  TDS         NUMBER(10,2),
  ADVI        NUMBER(10),
  OTH_DED     NUMBER(10),
  NETPAYABLE  NUMBER(10,2),
  CLT         NUMBER(2),
  ELT         NUMBER(2),
  SLT         NUMBER(2),
  TL          NUMBER(2),
  CAA         VARCHAR2(1 BYTE),
  FY          VARCHAR2(9 BYTE),
  CP          VARCHAR2(30 BYTE),
  FYCP        VARCHAR2(40 BYTE),
  FOR_MNTH    VARCHAR2(20 BYTE),
  FOR_YEAR    NUMBER(4)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



SET DEFINE OFF;
Insert into DEPT
   (DNAME)
Values
   ('COMMERCIAL');
Insert into DEPT
   (DNAME)
Values
   ('MAINT & SUPPORT');
Insert into DEPT
   (DNAME)
Values
   ('SYSTEMS');
Insert into DEPT
   (DNAME)
Values
   ('OPERATIONS');
Insert into DEPT
   (DNAME)
Values
   ('QUALITY CONTROL');
Insert into DEPT
   (DNAME)
Values
   ('ACCOUNTS');
Insert into DEPT
   (DNAME)
Values
   ('PACKING & DISPATCH');
Insert into DEPT
   (DNAME)
Values
   ('ADMINISTRATION');
Insert into DEPT
   (DNAME)
Values
   ('STORE');
Insert into DEPT
   (DNAME)
Values
   ('MAINTENANCE');
Insert into DEPT
   (DNAME)
Values
   ('DESIGNING');
Insert into DEPT
   (DNAME)
Values
   ('PURCHASE & STORE');
COMMIT;

SET DEFINE OFF;
Insert into DESIG
   (DNAME)
Values
   ('AGM');
Insert into DESIG
   (DNAME)
Values
   ('CHIEF MANAGER');
Insert into DESIG
   (DNAME)
Values
   ('SR. MANAGER');
Insert into DESIG
   (DNAME)
Values
   ('MANAGER');
Insert into DESIG
   (DNAME)
Values
   ('SCY. TO CMD & COMPLIANCE OFFICER');
Insert into DESIG
   (DNAME)
Values
   ('DY. MANAGER');
Insert into DESIG
   (DNAME)
Values
   ('ASST. MANAGER');
Insert into DESIG
   (DNAME)
Values
   ('SR. EXECUTIVE');
Insert into DESIG
   (DNAME)
Values
   ('EXECUTIVE');
Insert into DESIG
   (DNAME)
Values
   ('OMR OPERATOR');
Insert into DESIG
   (DNAME)
Values
   ('SR. ASSISTANT');
Insert into DESIG
   (DNAME)
Values
   ('ASSISTANT');
Insert into DESIG
   (DNAME)
Values
   ('MANAGER SYSTEMS');
COMMIT;

SET DEFINE OFF;
Insert into EMP
   (ECODE, ENAME, DESIG, DEPT, ADDR1, 
    ADDR2, CITY, STATE, PINCODE, PHONE, 
    DOJ, DOR, DOP, PFNO, ESINO, 
    BASIC, HRA, CONV, MEDC, BONUS, 
    TEL, OTHA, CL, EL, SL, 
    PFA, TDS, TDSMB, ADVTOTAL, ADVDED, 
    BANKAC, FY, CP, FYCP, ESI, 
    TDSDED)
Values
   ('1000', 'ABC', 'xyz', 'abc', NULL, 
    NULL, 'NEW DELHI', 'DELHI', '110020', NULL, 
    TO_DATE('08/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL, '12345', '654123', 
    8000, 2500, 500, 1000, 5000, 
    500, 250, 7, 31, 8, 
    780, 1000, 5, 10000, 500, 
    '254252525632548', '2010-2011', 'LTD.', '2010-2011 INTERNATIONAL LTD.', 210, 
    NULL);
Insert into EMP
   (ECODE, ENAME, DESIG, DEPT, ADDR1, 
    ADDR2, CITY, STATE, PINCODE, PHONE, 
    DOJ, DOR, DOP, PFNO, ESINO, 
    BASIC, HRA, CONV, MEDC, BONUS, 
    TEL, OTHA, CL, EL, SL, 
    PFA, TDS, TDSMB, ADVTOTAL, ADVDED, 
    BANKAC, FY, CP, FYCP, ESI, 
    TDSDED)
Values
   ('93', 'VINISH KAPOOR', 'MANAGER SYSTEMS', 'OPERATIONS', 'MARIYAM NAGAR', 
    NULL, 'GHAZIABAD', 'UP', '110020', '9540377373', 
    TO_DATE('01/10/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL, NULL, NULL, 
    10000, 4000, 1000, 1000, 5000, 
    1000, 2000, 7, 31, 8, 
    780, 2000, 2, 50000, 1000, 
    NULL, '2010-2011', 'INTERNATIONAL LTD.', '2010-2011 INTERNATIONAL LTD.', 0, 
    NULL);
Insert into EMP
   (ECODE, ENAME, DESIG, DEPT, ADDR1, 
    ADDR2, CITY, STATE, PINCODE, PHONE, 
    DOJ, DOR, DOP, PFNO, ESINO, 
    BASIC, HRA, CONV, MEDC, BONUS, 
    TEL, OTHA, CL, EL, SL, 
    PFA, TDS, TDSMB, ADVTOTAL, ADVDED, 
    BANKAC, FY, CP, FYCP, ESI, 
    TDSDED)
Values
   ('93993', 'VIN', 'AGM', 'ACCOUNTS', NULL, 
    NULL, 'NEW DELHI', 'DELHI', '110020', NULL, 
    TO_DATE('01/01/2000 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, NULL, NULL, NULL, 
    33333, 0, 0, 0, 0, 
    0, 0, 7, 31, 8, 
    780, NULL, 1, NULL, NULL, 
    NULL, 'FY', 'FY', 'FY', 0, 
    NULL);
COMMIT;

SET DEFINE OFF;
Insert into FYCPT
   (FY, CP, FYCP)
Values
   ('2010-2011', 'INTERNATIONAL LTD.', '2010-2011 INTERNATIONAL LTD.');
COMMIT;

-- end