Be sure to run the scripts in the order listed.

The following implementation scripts must be run on your Responder Oracle database to add the correct tables, views, and indices. When performing a new Responder implementation execute ONLY the Implementation Scripts. Do NOT execute any Upgrade 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.

Be sure to run the scripts in the order listed.

• 01_RxTables.sql (Responder)
• 02_ArcJoinViews.sql (Responder)
• 03_GenerateGrants.sql (Responder)
• 04_ArchiveTables.sql (Archive)
• 05_Archive_Grants.sql (Archive)
• 06_MMTables.sql (owner of the ArcFM System Tables)

1. Create the RESPONDER user in Oracle (this is the schema owner). This user must be granted the following privileges:

   Grant  CREATE SESSION to responder;

   Grant  CREATE TABLE to responder;

   Grant  CREATE VIEW to responder;

2. Log in to the Responder schema as RESPONDER (i.e., schema owner). Use an application such as SQL Plus. If you're using a tool such as SQL Tools 1.5, you may need to open the script and copy the contents into the application (e.g., SQL Tools 1.5) to execute it.
3. Execute the 01_RxTables_oracle.sql script on the Responder schema 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 (x86)\Miner and Miner\Responder\Developer
    Resources\Implementation Scripts\Oracle\01_RxTables.sql

4. The 02_ArcJoinViews.sql script creates the views that are used to display Responder data in ArcMap. Before executing this script on the Responder schema, 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. The ownership is set to ArcFM and needs to be modified if you are not using ArcFM (such as SQL users.) Execute 02_ArcJoinViews.sql using a similar statement as in step 3.
5. Open 03_GenerateGrants.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run on the Responder schema. This script ensures permissions are assigned to tables created by the RxTables 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 above 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.
6. 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.
7. When you've finished adding statements for roles, close and save the file.
8. Execute 03_GenerateGrants.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 03_GenerateGrants_oracle.sql script. For example:

 @d:\Program Files (x86)\Miner and Miner\Responder\Developer Resources\
        Implementation Scripts\Oracle\03_GenerateGrants.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 03_GenerateGrants.sql (script.sql in this example). The permissions assigned apply to all Responder tables and views.

@d:\Program Files\script.sql

9. Log in to the Archive schema as ARCHIVE (i.e., schema owner).
10. Execute 04_ArchiveTables.sql on the Archive schema. This script creates the tables used by Archive Services and Archive Explorer.
11. Open 05_ArchiveGrants.sql in a text editor (e.g., Notepad, WordPad). This script generates a grant script that must also be run on the Archive schema. This script ensures permissions are assigned to tables created by the ArchiveTables script 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_archive;' from user_tables;
select 'grant select on ' ||view_name||' to rx_archive;' 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.

12. The "grant insert,delete,update,select" portion determines which permissions are to be assigned. Modify these permissions as necessary.
13. Replace "rx_archive" with the name of the role to assign the permissions. By default, the only role available is rx_archive. You may have your DBA create custom roles.
14. When you've finished adding statements for roles, close and save the file.
15. Execute 05_ArchiveGrants.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 05_ArchiveGrants.sql script. For example:

  @d:\Program Files (x86)\Miner and Miner\Responder\Developer Resources\
        Implementation Scripts\Oracle\05_ArchiveGrants.sql

• Turn off the spool command:

spool off

16. 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.
17. Execute the script generated by the 05_ArchiveGrants.sql (script.sql in this example). The permissions assigned apply to all Archive tables and views.

 @d:\Program Files\script.sql

18. The 06_MMTables.sql prevents the RX_ELEMENT_LOCKS and RX_PENDING_ELEMENT_LOCKS tables from appearing in ArcCatalog. This is necessary only for Oracle implementations where the geodatabase and the Responder tables reside in the same instance.

This script assumes that the ArcFM System Tables are owned by SDE. If this is not true, modify the script to reflect the correct owner. Execute the script on the ArcFM System Tables.

Send Comment to ArcFMdocumentation@schneider-electric.com