9.1 SP1 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.1 SP1. 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.

910sp1_01_RxTables_oracle.sql

910sp1_02_ArcJoinViews_oracle.sql

910sp1_03_GenerateGrants_oracle.sql

Log in to the Responder schema as RESPONDER (i.e., schema owner). Use an application such as SQL Plus.
Execute the 910sp1_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.0 SP1 Upgrade Scripts\Oracle\910sp1_01_RxTables_oracle.sql
The 910sp1_02_ArcJoinViews_oracle.sql script creates the views that are used to display Responder data in ArcMap. Before executing this script, you may 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. Execute 910sp1_02_ArcJoinViews_oracle.sql using a similar statement as in step 2.
Open 910sp1_03_GenerateGrants_oracle.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run. 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.
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.
When you've finished adding statements for roles, close and save the file.
Execute 910sp1_03_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 910sp1_03_GenerateGrants_oracle.sql script. For example:
```
  @d:\Program Files\Miner and Miner\Responder\Developer Resources\
        9.1.0 SP1 Upgrade Scripts\Oracle\910sp1_03_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 910sp1_03_GenerateGrants_oracle.sql (script.sql in this example). The permissions assigned apply to all Responder tables and views.
```
       @d:\Program Files\script.sql
```