9.2 Upgrade Scripts

The following implementation scripts must be run on your Responder Oracle database to add the correct tables, views, and indices to upgrade Responder to version 9.2. When upgrading Responder execute ONLY the Upgrade script(s) that fall between your previous release and the new release to which you are upgrading. Do NOT execute any Implementation Scripts.

Responder requires two schemas: Responder and Archive. Some scripts are run on the Responder schema and some on the Archive schema. In the list below, the schema on which the script should be executed is in parentheses.

920_01_RxTables_oracle.sql (Responder)

920_02_ArcJoinViews_oracle.sql (Responder)

920_03_ArchiveTables_oracle.sql (Archive)

920_04_GenerateGrants_oracle.sql (Responder and Archive)

Log in to the Responder schema as RESPONDER (i.e., schema owner). Use an application such as SQL Plus.
Execute the 920_01_RxTables_oracle.sql script by entering "@" followed by a path to the script (an example follows) and pressing Enter. This script creates the database tables required by Responder.
```
@ D:\Program Files\Miner and Miner\Responder\Developer Resources\
9.1.2 SP1 - 9.2.0 Upgrade Scripts\Oracle\920_01_RxTables_oracle.sql
```

The 920_02_ArcJoinViews_oracle.sql script creates the views that are used to display Responder data in ArcMap. Execute 920_02_ArcJoinViews_oracle.sql using a similar statement as in step 2. Even if you executed this script as part of the initial implementation, execute it again.

Before executing this script, you will need to modify the feature class names within it to match those in your geodatabase. Open the script and ensure that the feature class names in the script match the corresponding feature class names in your geodatabase. If you have done this for a previous release, you may use the prior script and copy/paste the new views into it. This would prevent the need to re-update feature class names for all views.

Log in to the Archive schema as ARCHIVE (i.e., schema owner).
Execute the 920_03_ArchiveTables_oracle.sql script in the same manner as the first script. Enter "@" followed by a path to the script and press Enter. This script creates the database tables required by Responder Archive Explorer.

Open 912_04_GenerateGrants_oracle.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run on both the Responder and Archive schemas. This script ensures permissions are assigned to tables created by the RxTables and ArchiveTables scripts run in previous steps. Before executing, you must determine the roles and permissions to assign them. You may assign permissions to several different roles. Look for the following lines of code in the script:

select 'grant insert,delete,update,select on ' ||table_name||' to rx_user;' from user_tables;
select 'grant select on ' ||view_name||' to rx_user;' from user_views;

The first line assigns permissions to a role for tables. The second line assigns permissions to a role for views. You will need a line for each role to assign permissions to tables and views. To assign permissions to a role on both tables and views, that role will require two lines of code. The next few steps describe how to perform the modifications you can make for each role.

You may execute the same script for the Responder database and Archive database. The script MUST be executed on both databases.

The "grant insert,delete,update,select" portion determines which permissions are to be assigned. Modify these permissions as necessary.
Replace "rx_user" with the name of the role to assign the permissions. By default, the only role available is rx_user. You may have your DBA create custom roles.
When you've finished adding statements for roles, close and save the file.
Execute 920_04_GenerateGrants_oracle.sql using the following steps.
- Use the "spool" command to generate a text file containing another script that must also be executed. Specify a path and a file name. For example:
```
    spool d:\Program Files\script.sql
```
- Execute the 920_04_GenerateGrants_oracle.sql script. For example:
```
     @d:\Program Files\Miner and Miner\Responder\Developer Resources\
        9.1.2 SP1 - 9.2.0 Upgrade Scripts\Oracle\920_04_GenerateGrants_oracle.sql
```
- Turn off the spool command:
```
   spool off
```
Open the file created by the spool command (script.sql in this example) and remove any lines that do not begin with "grant". Generally this will be the first and last lines in the file. Blank lines do not need to be deleted.
Execute the script generated by the 920_04_GenerateGrants_oracle.sql (script.sql in this example). The permissions assigned apply to all Responder tables and views.
```
  @d:\Program Files\script.sql
```