Creating Data Blocks using Stored procedures:-

Create table student using the following query at SQL*Plus environment.

SQL> create table student ( roll  number(3),
                                                name  varchar2(20),
                                                course  varchar2(20));

Create package specification using the following code

create or replace package mypak
is
type srec  is  record ( stu_id   student.roll%type,
                                        fname  student.name%type,
                                       cname  student.course%type);

 

type stutab is table of srec index by binary_integer;

procedure stuqry ( data  in out  stutab);
procedure stuins ( data  in  stutab);

end mypak;
/

Create package body using the following code

create or replace package body  mypak
is

procedure stuqry ( data in out  stutab)
is
i  number;
cursor  scur is   select * from student;
begin

open  scur;
i :=1;
loop
fetch scur  into  data(i).stu_id,  data(i).fname, data(i).cname;
exit when scur%notfound;
i := i+1;
end loop;
end stuqry;

procedure stuins ( data in stutab)
is
i  number;
ct  number;
begin
ct := data.count;
for i in  1.. ct loop
insert into student values ( data(i).stu_id, data(i).fname,  data(i).cname);
end loop;
end stuins;

end mypak;
/

Open the form builder tool
Create new form using Data Block wizard.
Tools Data Block wizard Next Select Stored Procedure Next Procedure Enter the procedure to query data for your data block

Procedure --  mypak.stuqry     Refresh Move all the columns from available columns to Database Items

Next Enter procedure to insert data for your data block
Procedure --  MYPAK.STUINS  Refresh

Next  Enter procedure to update data for your data block ( As we have not created procedure to update, leave it blank )  Next Enter procedure to delete data for your data block ( As we have not created procedure to delete, leave it blank )  Next  Enter procedure to lock data for your data block ( As we have not created procedure to lock, leave it blank ) Next Data Block Name -- Student Next Finish

Now, we will get Layout wizard,  complete the layout wizard.

File Save as STUDENT.FMB
Compile the form
Run the form.

Enter the following details  and save.

Stuid  -- 1
Fname -- karthik
Cname -- java

We get the confirmation message as "Transaction Completed"

Now, we can see the record at our SQL*Plus environment.

Insert multiple records and check the execute query functionality.

 

Advantage

We can have custom code for data manipulations and retrieval purpose rather than depending on system defined code given by forms.
It improves the perfomance.