English

How To: 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.


    [O-Image] Edit Oracle table using Avenue