HOW TO
This article provides examples on how to connect to Oracle and perform various operations within Oracle using Avenue.
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
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
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.
Article ID:000003923
Get help from ArcGIS experts
Download the Esri Support App