5
Setting Up Tables to Store Applications

You or your database administrator can set up tables in the database that will allow application modules to be saved to the Oracle Server.

For Form Builder, and most of the other Builders, this is an optional step; applications do not need to be saved in the server. However, for Translation Builder and Schema Builder, these database tables are a requirement.

If they have the option, most customers choose to not store their applications in the server, and they skip this step. Those who do choose to store applications in the server usually do so for the benefits of centralized administrative control.

The following topics are included in this chapter:

• Building the Database Tables

• Proper Use of these Tables

• Granting and Revoking User Access

• Deleting the Database Tables

• Upgrading the Database Tables

Creating database tables may require administrator privileges. Consult with your database adminstrator if such authorization is required.

5.1 Building the Database Tables

WARNING: These instructions are for new installations only. If you already have database tables installed for a previous version of an Oracle Developer product, be aware that removing the existing tables or attempting to install the database tables for the current version of Forms Developer will destroy any Oracle Developer module currently stored in the database. Please see Section 5.5, "Upgrading the Database Tables" for instructions on upgrading existing database tables.

When you create applications with Forms Developer, you can either save your modules as files or save them to the server. Saving modules to the server conserves disk space on client machines and allows shared access to the modules, although access to modules stored on the server can be slower than when they are stored as files on the client machine. You may wish to consider the following information when deciding where to store your modules.

Storage Location	When to Use
Database server	When you want security that is not provided by your operating system. When you want to store applications on the client machine, but you do not have enough disk space. When you want to share applications with other users. When module access speed is not as important as the considerations listed above.
File system	When the above criteria are not true or not important.

Before users can save modules to the server, a system administrator must build tables to accommodate the modules and grant access privileges to users who will work with those tables.

5.1.1 Step 1: Installing the SQL scripts with the Oracle Installer

If you have not already done so, use the Oracle Installer to install the SQL scripts needed to build and administer the Forms Developer database tables.

To install the SQL scripts:

1. Start the Oracle Installer and select Custom Installation or Removal from the Forms Developer Installation Options dialog box. (Refer to Chapter 1 for more information on the Oracle Installer.)

2. Select Forms Developer Database Tables from the Available Products list.

3. Click Install.

Installing these scripts creates a Start menu item called Forms Developer R6i Admin and the associated submenu items Forms Developer Build, Drop, Grant, and Revoke. These menu items, when selected, run a script or a series of scripts to perform database administration tasks.

5.1.2 Step 2: Building the Oracle Developer database tables

To build the Oracle Developer database tables:

• Click Start'Programs'Forms Developer R6i Admin'Forms Developer Build.Start->Programs->Oracle Developer R6i Admin->Oracle Developer Build.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVBILD.SQL

5.2 Proper Use of these Tables

Form Builder and the other Builders use the database tables for storing modules. The database tables are not intended for any other use. The structure and organization of the tables are likely to change from release to release. We strongly recommend that you do not try to use SQL queries against the tables to produce reports or documentation about your modules. Use of the tables in this way is not supported by Oracle. For this reason, the structure and content of the tables are not provided.

5.3 Granting and Revoking User Access

After building the Form Developer database tables, you must grant user access to the tables. If you wish to withdraw access from a user, you must revoke it. Both of these tasks are accomplished with SQL scripts provided through the Oracle Installer.

For more information about database roles, see the Oracle8/8i documentation.

5.3.1 Granting access to the database tables

You must perform this task for each user who requires access to the tables. Alternatively, you can grant access to all users simultaneously by granting access for user PUBLIC.

To grant access to the Forms Developer database tables:

1. Click Start'Programs'Forms Developer R6i Admin'Forms Developer Grant.Start->Programs->Oracle Developer R6i Admin->Oracle Developer Grant. You will be prompted to enter a username.

2. Enter the username of a user to whom you will grant access to the tables.

   To grant access to all users, enter PUBLIC and skip step 3.

3. Repeat steps 1 and 2 for each user to whom you want to grant access.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVGRNT.SQL username

5.3.2 Revoking access to the database tables

You must perform this task for each user whose access to the tables you want to revoke. Alternatively, you can revoke access for all users simultaneously by revoking access for user PUBLIC.

To revoke access to the Forms Developer database tables:

1. Click Start'Programs'Forms Developer R6i Admin'Forms Developer Revoke.Start->Programs->Oracle Developer R6i Admin->Oracle Developer Revoke. You will be prompted to enter a username.

2. Enter the username of the user whose access you wish to revoke.

   To revoke access from all users, enter PUBLIC and skip step 3.

3. Repeat steps 1 and 2 for all users whose access you wish to revoke.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVRVKE.SQL username

5.4 Deleting the Database Tables

WARNING: Deleting the database tables erases any module stored in those tables. Do not delete database tables until you have verified that you do not need any module currently stored in the table or tables you intend to delete. See Section 5.5.0.1 for more information on backing up modules currently stored in your database.

To delete the Forms Developer database tables:

• Click Start'Programs'Forms Developer R6i Admin'Forms Developer Drop.Start->Programs->Oracle Developer R6i Admin->Oracle Developer Drop.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVDRP.SQL

5.5 Upgrading the Database Tables

If you are upgrading from Release 1 to Release 6i of Oracle Forms Developer, the only database tables that need upgrading are those for Translation Builder. Tables for the other Builders (Form Builder, Graphics Builder, Procedure Builder, Query Builder, and Schema Builder), and the common tables, do not need to be upgraded.

You can run one or more upgrade scripts instead of creating the tables from scratch. Doing so updates the administration information in your existing tables.

5.5.0.1 Step 1: Back up the database contents

If you have any modules stored in the database that you wish to continue using after you upgrade the database tables, you will need to back up these modules. Any modules that you do not back up will be lost.

Before you back up existing modules, it might be useful to have a list of all available modules currently stored in the database. To do this, log on to the database and run the following SQL script:

column product format a15
column modtype format a15
SELECT product, modtype, owner, modname
FROM tool_module
ORDER BY product, modtype, owner;

You can recognize the source files for application modules by their file extensions:

• Form Builder: .FMB, .MMB, and .PLL files

• Graphics Builder: .OGD files

• Procedure Builder: .PLL files

• Query Builder: .BRW and .SQL files

To back up a module:

1. Open the module in the appropriate Builder.

2. Save the module as an operating system file using the File'Save AsFile->Save As command.

5.5.0.2 Step 2: Upgrade the tables

Once you have backed up the database, you can upgrade the Translation Builder database tables.

Note: Run all SQL scripts under the SYSTEM account.

Upgrade the existing tables by running the following script:

%ORACLE_HOME%\TOOLS\DBTAB60\OTM60\SQLMIG.SQL

5.5.0.3 Step 3: Restore the database contents

Once you have completed upgrading the database tables, you can restore the modules to the database.

To restore a module:

1. Open the module in the appropriate Builder.

2. Save the module as a database module using the File'Save AsFile->Save As command.

5.5.0.4 Step 4: Grant user access again

After you've upgraded from a previous version, you must once again grant user access to the database tables. See Section 5.3 for information about how to do this.