Create Oracle tables from ArcView using Avenue

Summary

This article provides examples on how to connect to Oracle and perform various operations within Oracle using Avenue.

Procedure

1. Create a Data Source Name (DSN) named 'Oracle' to the Oracle database.
   
   
   A. Click Start > Settings > Control Panel.
   B. Double-click the ODBC32 Administrator icon.
   C. Select the User DSN tab, then click the Add button.
   D. Select the Oracle ODBC Driver and click the Finish button.
   E. Type in a name for the DSN in the Data Source Name field, for example 'Addresses'.
   F. Enter the Service Name.
   G. Click OK.
   H. Click OK to close the ODBC Administrator dialog box.
   
   
2. Open four new script windows.
   
   
   A. Activate the Project window.
   B. Click the Scripts icon.
   C. Click New.
   
   
3. Copy and paste each of the following scripts into a separate script window.
   
   

   Code:
   '-- Script Name: SQLConnect.Oracle
   
   '-- CONNECT TO ORACLE
   
   '-- This SQLCON statement is specific to our ODBC definition.
   '-- You will need to replace DIRPIMS 7 with your database name.
   _theSQL=SQLCon.Find("oracle")
   _theSQL.Login("login/password")
   
   '-- Verify connection to Oracle was susccessful.
   if (_theSQL.IsLogin=false) then
     MsgBox.Info("         LOGIN UNSUCCESSFUL." +NL++NL++TAB+ "LOGIN UNSUCCESSFUL.", "LOGIN UNSUCCESSFUL")
     exit
   end

   
   
   The following scripts create three tables in Oracle and put some records in them.
   
   

   Code:
   '-- Script Name: SQLConnect.Tables
   
   '-- Create ARCVIEW_SESSION table in Oracle
   theCreateSesTableSQL="Create table arcview_session (session_id number(3), session_name char(15), end_dt date)"
   _theSQL.ExecuteSQL(theCreateSesTableSQL)
   
   '-- Create ARCVIEW_USER table in Oracle
   theCreateUserTableSQL="Create table arcview_user (user_id number(3), user_name char(15))"
   _theSQL.ExecuteSQL(theCreateUserTableSQL)
   
   '-- Create USER_SESSION table in Oracle
   theCreateUserSesTableSQL="Create table user_session (user_id number(3), session_id number(3))"
   _theSQL.ExecuteSQL(theCreateUserSesTableSQL)
   
   '-- Insert new ARCVIEW_SESSION record
   theInsertSessionSQL="Insert into arcview_session (session_id, session_name) values(1, 'TestSession 1')"
   _theSQL.ExecuteSQL(theInsertSessionSQL)
   
   '-- Insert another ARCVIEW_SESSION record
   theInsertSession2SQL="Insert into arcview_session (session_id, session_name) values(2, 'TestSession 2')"
   _theSQL.ExecuteSQL(theInsertSession2SQL)
   
   '-- Insert new ARCVIEW_USER record
   theInsertSessionSQL="Insert into arcview_user values(5, 'Sam Smith')"
   _theSQL.ExecuteSQL(theInsertSessionSQL)
   
   '-- Insert another ARCVIEW_USER record
   theInsertSession2SQL="Insert into arcview_user values(6, 'Jenny Jones')"
   _theSQL.ExecuteSQL(theInsertSession2SQL)
   
   '-- Insert another ARCVIEW_USER record
   theInsertSession3SQL="Insert into arcview_user values(7, 'Mike McMann')"
   _theSQL.ExecuteSQL(theInsertSession3SQL)
   
   '-- Insert new USER_SESSION record
   theUserSesSQL="Insert into user_session values(6,2)"
   _theSQL.ExecuteSQL(theUserSesSQL)

   
   
   

   Code:
   '-- Script Name: SQLSession.Record
   
   '-- This script gets the SESSION_ID and USER_ID from Oracle and creates a session record
   
   '-- Get the USER_ID from Oracle
   theUserIDSQL="Select user_id from arcview_user where user_name = 'Sam Smith'"
   theUserIDVTAB=VTAB.MakeSQL(_theSQL,theUserIDSQL)
   for each record in theUserIDVTab
     theUserIDField=theUserIDVTAB.FindField("user_id")
     _theUserID=theUserIDVTAB.ReturnValueString(theUserIDField,record)
   end
   
   '-- Get the SESSION_ID from Oracle
   theSessionIDSQL="Select session_id from arcview_session where session_name ='TestSession 1'"
   theSessionIDVTAB=VTAB.MakeSQL(_theSQL,theSessionIDSQL)
   for each record in theSessionIDVTab
     theSessionIDField=theSessionIDVTAB.FindField("session_id")
     _theSessionID=theSessionIDVTAB.ReturnValueString(theSessionIDField,record)
   end
   
   '-- Create new USER_SESSION record
   theUserSessionSQL="Insert into user_session values("+_theUserID+","+_theSessionID+")"
   _theSQL.ExecuteSQL(theUserSessionSQL)
   

   
   
   

   Code:
   '-- Script Name: SQLAdd.Date
   
   '-- Add the SESSION END DATE to the SESSION record
   theEndDTSQL="Update arcview_session set end_dt = (select sysdate from dual) where session_id ="+_theSessionID+""
   _theSQL.ExecuteSQL(theEndDTSQL)
   
   '-- Delete the USER_SESSION record for this user and session
   theDeleteSQL="Delete from user_session where session_id ="+_theSessionID+" and user_id="+_theUserID+""
   _theSQL.ExecuteSQL(theDeleteSQL)
   
   '-- Log off from Oracle
   _theSQL.Logout
   
   '-- If you run this script, be sure to rerun the &1LogOntoOracle
   '-- and &3CreateSesRecord scripts again to reconnect to Oracle and
   '-- Re-enter the USER_SESSION record that was deleted here
   

   
   
4. Rename each script to its appropriate name in the header.
   
   
   A. Select Properties from the Script menu.
   B. Type in a new name in the Name field.
   C. Click OK.
   
   
5. Attach each script to a button on the Project GUI.
   
   
   A. Compile the script.
   B. Switch to the Project window.
   C. Select Customize from the Project menu.
   D. On the Customize dialog, select Project under Type dropdown.
   E. Select Buttons under Category.
   F. Click the New button.
   G. Double-click the Click property in the Customize dialog box.
   H. Enter the name of the script in the Script Manager and click Select.
   I. Close the Customize dialog box.
   
   For more information, see 'Customize dialog box' in ArcView Help.
   
   
   
6. Click each button in this order:
   
   

   a. Click the button for SQLConnect.Oracle script.
   b. Click the button for SQLCreate.Tables script.
   c. Click the button for SQLSession.Record script. 
   d. Click the button for SQLAdd.Date script.