Forms - Creating data block 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.