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 Report 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:

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

5.1 Building the Database Tables

Caution: 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 Reports 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 Reports 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.

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

The SQL scripts are not installed as part of the typical install. If you have not already done so, use the Oracle Installer to install the SQL scripts needed to build and administer the Reports Developer database tables.

To install the SQL scripts, use the following steps:

Start the Oracle Installer and select Custom Installation or Removal from the Reports Developer Installation Options dialog box. (Refer to Chapter 1 for more information on the Oracle Installer.)
Select Reports Developer Database Tables from the Available Products list.
Click Install.

Installing these scripts creates a Start menu item called Reports Developer R6i Admin and the associated submenu items Reports 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->Oracle Forms and ReportsR6i Admin->Build.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVBILD.SQL
Enter the password. The password installed with new systems is manager.
Enter the database connection. The connection is made up of the database name followed by .world. The database must appear in you tnsnames.ora file.
Follow the script.

5.2 Proper Use of these Tables

Reports 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.

For more information about database roles, see the Oracle8 or Oracle8i documentation.

5.3 Granting and Revoking User Access

After building the Reports 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.

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 Reports Developer database tables, use the following steps:

Click Start->Programs->Oracle Forms and Reports 6i Admin->Grant.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVGRNT.SQL username
Enter the password. The password installed with new systems is manager.
Enter the database connection. The connection is made up of the database name followed by .world. The database must appear in you tnsnames.ora file.
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 5.
Repeat steps 1 and 2 for each user to whom you want to grant access.

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 Reports Developer database tables, use the following steps:

Click Start->Programs->Oracle Forms and Reports R6i Admin->Revoke.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVRVKE.SQL username
Enter the password. The password installed with new systems is manager.
Enter the database connection. The connection is made up of the database name followed by .world. The database must appear in you tnsnames.ora file.
Enter the username of the user whose access you wish to revoke.

To revoke access from all users, enter PUBLIC and skip step 5.
Repeat steps 1 and 2 for all users whose access you wish to revoke.

5.4 Deleting the Database Tables

Caution: 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.1 for more information on backing up modules currently stored in your database.

To delete the Reports Developer database tables:

Click Start->Programs->Oracle Forms and Reports R6i Admin->Drop.

This menu item runs the following SQL script:

%ORACLE_HOME%\TOOLS\DBTAB60\DEVDRP.SQL
Enter the password. The password installed with new systems is manager.
Enter the database connection. The connection is made up of the database name followed by .world. The database must appear in you tnsnames.ora file.
Follow the script.

5.5 Upgrading the Database Tables

If you are upgrading from Release 1 to Release 6i of Oracle Reports 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.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:

Table 5-1 Application module extensions

Application Module	Extensions
Form Builder	.FMB, .MMB, and .PLL
Report Builder	.RDF and .SQL
Graphics Builder	.OGD
Procedure Builder	.PLL
Query Builder	.BRW and .SQL

5.5.1.1 Backing up a module

To back up a module, use the following steps:

Open the module in the appropriate Builder.
Save the module as an operating system file using the File->Save As command.

5.5.1.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.1.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, use the following steps:

Open the module in the appropriate Builder.
Save the module as a database module using the File->Save As command.

5.5.1.4 Step 4: Grant user access again

After you have 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.

5 Setting Up Tables to Store Applications