Life experiences to make your life easier...: Designing with Oracle (Database and Forms)

How to create your first database software with Oracle Database 11g and 6i forms...

I know that 6i forms are old enough but to work with but believe me that still they are awesome and really efficient to design any kind of Database Application...

I will show you in steps that how to:

· Plan your database

· Write Database Script for designing tables

· Design Forms

First 'Plan your Database':

You must think about all the aspects of designing a database for your client, better if you can get some written information like copies of sample stock reports (manually maintained), expense sheets etc...

After you finish with your client sit back relax and think about putting the raw information into tables... for example:

If you need to record vendors of the business and you are trying to record the following fields:

Vendor Table:

Vid

vname

contact

contperson

address

city

debbalance

crebalance

where:

Vid	Vendor ID
Vname	Vendor name
Contact	Vendor's contact number
Contperson	The person we directly contact at vendor (our contact)
Address	Vendor's address
City	The city from which our vendor belongs
Email	Email address of the vendor
Debbalance	Debit Balance of the vendor
Crebalance	Credit Balance of the vendor

Now if you notice each and every information about vendor 'ABC' will be recorded once.

Next record the purchase information, now the case will get different in the database as I will show the purchase transaction once but the stock items can be more than one...so i will try to design the table with relationships like this:

Purchase Table:

Purno

pdate

vid

vname

Amount

where:

Purno	PurchaseID
Pdate	Date of purchase
Vid	ID of the vendor
Vname	Name of the Vendor
Amount	Total amount of items purchased

I think I know what you are thinking that where the client will record the purchased item record, information about the purchased items. The next table will be recording stock...

Purchase_Item Table:

Purno

sno

Stcode

stname

capacity

company

made

Rate

Qty

Amount

Purno	Same PurcahseID (in this table to put a foreign key)
Sno	Serial no.
Stcode	Stock Code
Stname	Stock Name
Capacity	Details of the stock (length, weight etc)
Company	Which company item or stock
Made	Which country make
Rate	Purchase price of the item
Qty	How much quantity we are about to purchase
Amount	Total of quantity * rate = this column

Now if you notice and as I mentioned earlier, under purchase transaction I can purchase more than one item into my stock... so the entries in the database that are recorded once will be recorded in first table 'Purchase', but the next table will record multiple rows of records. Except purno in 'Purchase_Item' table as it will be reference to connect with 'Purchase' which is parent table...

Furthermore after explaining the procedure of tables that how it works, now it comes to give u the script for designing tables...but with forms design I would like to explain the different constraints of tables...mostly primary key and foreign key is used...

The first table of vendor coding will be:

create table vendor(vid number, vname varchar2(100), contact number, contperson varchar2(100), address varchar2(255), city varchar2(100), email varchar2(100), debbalance number(8,2), crebalance number(8,2), constraint pk_vendor_vid primary key (vid));

Now explanation of the table...

create table - (command to create table)

vendor - (table name defined)

vid number, vname varchar2(100) - (column names and data types to define whether the data type recorded will be number or text)

constraint pk_vendor_vid primary key (vid) - (constraint name, type and the column that will be used as primary key)

As u can see that under training user in Oracle Database the table vendor is created...

Stock table script:

create table stock (stcode number, stname varchar2(100), capacity varchar2(100), company varchar2(100), made varchar2(100), qty number, cost_price number, sal_price number, constraint pk_stock_stcode primary key(stcode));

Purchase table script:

create table purchase(purno number, pdate date, vid number, vname varchar2(100), amount number, constraint pk_purchase_purno primary key(purno), constraint fk_purchase_vender_vid foreign key (vid) references vendor(vid));

Purchase_Item scipt:

create table purchase_item(purno number, serialno number, stcode number, stname varchar2(100), capacity varchar2(100), company varchar2(100), made varchar2(100), rate number, qty number, amount number, constraint pk_pur_item_sno_purno_stcode primary key (purno, serialno, stcode), constraint fk_purchaseitem_purno foreign key (purno) references purchase(purno));

alter table purchase_item add constraint fk_stock_stcode foreign key (stcode) references stock(stcode);

Now if you notice the constrains i have used in above table script...

constraint pk_pur_item_sno_purno_stcode primary key (purno, serialno, stcode), constraint fk_purchaseitem_purno foreign key (purno) references purchase(purno)

let me define them for you..

The first one will make columns purno, sno and stcode a combine primary key which will record the reference data from Purchase table and 'Stock' table and will record the unique entries of data which can be multiple records...

alter table purchase_item add constraint fk_stock_stcode foreign key (stcode) references stock(stcode);

Above alter table command will add another constraint of foreign key to apply relation between stock and purchase_item table...

The effect of foreign key constraints are to validate the data you are trying to enter in your referred table... i.e. Whenever you will try to enter items to purchase_item table, on each row it will automatically make sure that the item you entered is already there in stock table, further it will be cleared to you in designing forms session...

Now come to form design...

First we will design stock and vendor form to record this initial data cause whenever we need to purchase or sale the items we will need to select a vendor as well as stock...

When opening the Oracle Form Builder for the first time will open the dialogue box on the screen...

The data block wizard is used to import the table columns to canvas...

The table or view option is already selected so simply click 'Next'...

On above screen you will browse for the table to use columns on canvas...

Give your username password and service name of your Oracle Database...

We are creating form for vendor that is why we will select the vendor table...

Here you can see all the columns that you will select in the Data Block...

Select all the columns by clicking on >>...

Now it will ask about the create the canvas for placing your Data Block items where you will see the text fields and where you will enter the data or modify your data...

Click next to continue..

Simply click 'Next' if you want to continue with content style and not tab style...

Again select all columns or selective items that you want to show on canvas...

Set column width which can be set later also...

Select Form type of data or Tabular type of data entry...

Give frame name and number of records you want to display...

Simply click on Finish to see your finished canvas...

This is your canvas ready with details...

Now you need to run the form to complete the process of at least one complete form in working condition...
Here are the steps:

Once you press Ctrl+R the form will give above message and when you press 'Yes' it will ask for username password and service name again...

Give username and password as you have given to select the table columns when you were creating the form...

Here it is the final form running to accept the database records...

There are buttons on the top to insert the new record and save the records...
Click on