HOW TO SAVE THE FORM IN THE DATABASE?

Usually when you save the form, it gets saved in the filesystem by default.

However, if you want to save it in the database, you will need to do some

work in the database. You will need to run scripts in the database.  These

scripts are usually installed when you install the "database table" from the

CD. ( Without this tables you receive ORA-942 Table not exist)

 

Here are step by step of what you need to do starting with the installation

of the script:

 

1) Install Database table from the developer CD.  By doing so, the following

scripts will be installed:

          -- frm60bld.sql

          -- frm60drp.sql

          -- frm60grt.sql

          -- frm60rvk.sql

 

You can find these scripts under the following directory:

 

     $oracle_home$\tools\dbtab60\forms60\

 

The frm60bld.sql will create the following objects:

 

      --  Table  FRM50__OBJECT

      --  index ifrm50_object on frm50__object

      --  public synonym FRM50_OBJECT for system.FRM50__OBJECT

      --  Table FRM50__BUFFER

      --  index ifrm50_buffer on frm50__buffer

      --  public synonym FRM50_BUFFER for system.FRM50__BUFFER

      --  Table FRM50__BINDVAR

      --  index ifrm50_bindvar on frm50__bindvar

      --  public synonym FRM50_BINDVAR for system.FRM50__BINDVAR

      --  Table FRM50__GRP

      --  unique index iFRM50_grp on FRM50__grp

      --  public synonym FRM50_GRP for system.FRM50

__GRP

      --  view FRM50_ENABLED_ROLES

      --  public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES

      --  role ORAFORMS$OSC

      --  role ORAFORMS$DBG

      --  role ORAFORMS$BGM

 

Here is a copy of the script in case you would need it. 

create table FRM50__OBJECT
(
   OWNER         VARCHAR2(32),
   MODID         NUMBER(9),
   ITEMID        NUMBER,
   NAME          VARCHAR2(32),
   OBJECTTYPE    NUMBER,
   SEQUENCE      NUMBER,
   RAWLEN        NUMBER,
   TEXTLEN       NUMBER,
   CHUNKNO       NUMBER,
   SCOPEID       NUMBER,
   SCOPE1        VARCHAR2(32),
   SCOPE2        VARCHAR2(32),
   SCOPE3        VARCHAR2(32),
   RAWDATA       RAW(250),
   TEXTDATA1     VARCHAR2(2000),
   TEXTDATA2     VARCHAR2(2000),
   TEXTDATA3     VARCHAR2(2000),
   TEXTDATA4     VARCHAR2(2000),
   PROGRAMUNITID NUMBER
);
create index ifrm50_object on frm50__object(modid,itemid);
create public synonym FRM50_OBJECT for system.FRM50__OBJECT;

create table FRM50__BUFFER
(
   OWNER        VARCHAR2(32),
   MODID        NUMBER(9),
   STARTADDR    NUMBER,
   STARTREF     NUMBER,
   DATATYPE     NUMBER,
   LONGID       NUMBER
);
create index ifrm50_buffer on frm50__buffer(modid);
create public synonym FRM50_BUFFER for system.FRM50__BUFFER;

create table FRM50__BINDVAR
(
   OWNER        	VARCHAR2(32),
   MODID        	NUMBER(9),
   ITEMID      		NUMBER,
   NEXTBPOS     	NUMBER,
   PLSQLBV_EP   	NUMBER,
   TOTAL_BINDVAR	NUMBER
);
create index ifrm50_bindvar on frm50__bindvar(modid, itemid);
create public synonym FRM50_BINDVAR for system.FRM50__BINDVAR;


create table FRM50__GRP
(
   OWNER       VARCHAR(30),
   MODID       DECIMAL(9),
   ITEMID      DECIMAL(9),
   GRPNAME     VARCHAR(30),
   GRPFLAG     NUMBER
);
create unique index iFRM50_grp on FRM50__grp(modid,itemid);
create public synonym FRM50_GRP for system.FRM50__GRP;

create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
   'ORAFORMS$OSC',2,
   'ORAFORMS$BGM',4,
   'ORAFORMS$DBG',1,0)) flag
from  sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
  and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;

create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES;

create role ORAFORMS$OSC;
create role ORAFORMS$DBG;
create role ORAFORMS$BGM;

The frm60drp.sql script will drop the objects as follows:

drop table FRM50__OBJECT;
drop table FRM50__BUFFER;
drop table FRM50__BINDVAR;
drop table FRM50__GRP;
drop view  FRM50_ENABLED_ROLES;
drop public synonym FRM50_OBJECT;
drop public synonym FRM50_BUFFER;
drop public synonym FRM50_BINDVAR;
drop public synonym FRM50_GRP;
drop public synonym FRM50_ENABLED_ROLES;
drop role ORAFORMS$OSC;
drop role ORAFORMS$DBG;
drop role ORAFORMS$BGM;

The frm60grt.sql script will grant select, insert, delete and update on the

previously created objects as follows:

accept uname char prompt 'Grant access to user: ';

set verify off;
grant select, insert, delete, update on FRM50_OBJECT       to &uname; 
grant select, insert, delete, update on FRM50_BUFFER       to &uname;
grant select, insert, delete, update on FRM50_BINDVAR      to &uname;
grant select, insert, delete, update on FRM50_GRP          to &uname;
grant select                         on FRM50_ENABLED_ROLES to &uname;

the frm60rvk.sql script will revoke the grant as follows:

accept uname char prompt 'Revoke access from user: ';

set verify off;

revoke all on FRM50_OBJECT           from &uname; 
revoke all on FRM50_BUFFER           from &uname;
revoke all on FRM50_BINDVAR          from &uname;
revoke all on FRM50_GRP              from &uname;
revoke all on FRM50_ENABLED_ROLES    from &uname;

NOTE:  If you do not want to install the database tables, you can cut and paste

the above scripts in a notepad and save them in .sql file. 

 

2)  Sign onto the database as system and run the frm60bld.sql script to create

    those objects mentioned above.

 

3) Once you run the script, you can go ahead and start the forms builder, and

    set the access to be database instead of file.  In order to do that, you

    need to do the following:

     a.    Go to the Tools from the menu in forms builder

     b.    Go to Preferences

     c.    Go to the Access tab

     d.    Check the Database box.

 

Once you set the Access to database, when you save the form as "Save As", you

will get prompted to save it in Database.  You can now save it in the database

without any problem.  You would not have been able to save the form in the

database without first running the script mentioned above.

 

NOTE:  make sure you won't get any errors when you run the frm60bld.sql script.

If you run to any errors, make sure that these objects do not already exist on

the database.