Additional Example Showing the use of a BefExtract Event Script

The following event script shows how to make a database call to clear data from the target table, and then use sqlldr to populate the target table with a dataset extracted from EPM. This example shows how to populate a cloud database with sqlldr, but the same technique may be used to populate other third party non-Oracle databases. Like the prior example, this script is provided as an example and is not warranted against defects, and users may not file a service request with Oracle support in regards to any questions or issues related to the script.

The default write-back process uses individual insert statements to post data to the target database, and if the write-back dataset is large, it is recommended to use the specific database utility to load bulk data rather than by using the default process. In this example, the script shows how to call the Oracle sqlldr utility, and it is assumed that the utility has been downloaded from OTN and is installed and accessible to the event script.

#------------------------------------------------------------------------------------------#
# befExport.py
# This script is used to perform database operations as part of the writeback process
# The script also uses sqlldr, and this must be installed prior to executing the script
# References to sqlldr and the control file will be different from this script and should
# be updated to reference the location where sqlldr is installed in your environment. 
#-----------------------------------------#
# Housekeeping and startup                # 
#-----------------------------------------#
from java.sql import DriverManager, SQLException
import subprocess

def main():
  #---------------------------------------------------#
  # Print integration context details to the log file #
  #---------------------------------------------------#
  agentAPI.logInfo("#---------------------------------#") 
  agentAPI.logInfo("# Delete data in table MC_WB_TEST ")
  agentAPI.logInfo("# Location:    " + agentContext["LOCATION"])
  agentAPI.logInfo("# Integration: " + agentContext["INTEGRATION"])
  agentAPI.logInfo("# Data File:   " + agentContext["WRITEBACK_DATA_FILE"])
  agentAPI.logInfo("#---------------------------------#")

  #---------------------------------------#
  # Retrieve user, password, and JDBC URL #
  #---------------------------------------#
  cred = agentAPI.getConnectionDetails()
  url = cred.getJDBCUrl()
  user = cred.getUserName()
  password = cred.getPassword()
  agentAPI.logInfo("# Connection Details")
  agentAPI.logInfo("# URL:  " + url)
  agentAPI.logInfo("# User: " + user)

  #---------------------------------------------------------------------------#
  # Open connection to the database and execute SQL                           #
  # This step deletes existing data before loading new data                   #
  # For large tables it is recommended to truncate and the recreate the table #
  #---------------------------------------------------------------------------#
  cnx = DriverManager.getConnection(url, user, password)
  agentAPI.logInfo("# Successfully connected to Oracle Cloud DB using DriverManager")
  stmt = cnx.createStatement()
  stmt.executeQuery('Delete from MC_WB_TEST')  
  agentAPI.logInfo("# Deleted all rows from MC_WB_TEST #")
  agentAPI.logInfo("#----------------------------------#")
  
  #-------------------------------------------------#
  # Define the SQL*Loader command and its arguments #
  #-------------------------------------------------#
  agentAPI.logInfo("# Starting SQLLDR")
  
  #----------------------------------------#
  # Replace \ with \\ in the filename path #
  #----------------------------------------#
  data_file = agentContext["WRITEBACK_DATA_FILE"]
  new_file = data_file.replace("\\", "\\\\")
  
  #---------------------------------------------#
  # Specify control file and include \\ in path #
  #---------------------------------------------#
  ctr_file = "C:\\EPMAgent\\bin\\MyData\\scripts\\instantclient\\load.ctl"

  #--------------------------------------------------------#
  # Specify user, password and tnsnames.ora entry          #
  # The format is user/password@<SID from tnsnames.ora>    #
  # for cloud databases.                                   #
  #--------------------------------------------------------#
  user = user + "/" + password + "@mcebs19c_medium"

  #------------------------#
  # Specify path to sqlldr #
  #------------------------#
  sqlldr_path = "C:\\EPMAgent\\bin\\MyData\\scripts\\instantclient\\sqlldr"
    
  #----------------------#
  # Build sqlldr command #
  #----------------------#
  sqlldr_command = [
    sqlldr_path,
    "userid=" + user,  
    "control=" + ctr_file,
    "data=" + new_file
  ]

  #----------------------------#
  # Run the SQL*Loader command #
  #----------------------------#
  with open("sqlldr_output.txt", "w") as fout:
    exit_code = subprocess.call(sqlldr_command, stdout=fout, shell=True)
  agentAPI.logInfo("# exit code: " + str(exit_code))
  agentAPI.logInfo("#---------------------------------#")

  #-------------------------------------------------------------#
  # Skip additional agent sql processing                        #
  # This ensures that the default insert processing is bypassed #
  #-------------------------------------------------------------#
  agentAPI.skipAction('true')

  #----------------------------------------------#
  # Raise an error if needed as part of          #
  # the process.  This will halt the processing  #
  # of the write-back process                    #                      
  #----------------------------------------------#
  #agentAPI.logInfo("# Manually raising a ValueError to stop the process")
  #raise ValueError("This is a manually raised ValueError")

  return

#------------------------------------------------------------#
# Check for location and process if needed                   #
# The same script is used for data load and write-back,      #
# so make sure that each specific case is correctly captured #
#------------------------------------------------------------#
#
if agentContext["LOCATION"] == "1WB_TEST":
  main()
else:   
  agentAPI.logInfo("#--------------------------------------#")
  agentAPI.logInfo("# Location: " + str(agentContext["LOCATION"]))
  agentAPI.logInfo("# Script not required, exiting now...")
  agentAPI.logInfo("#--------------------------------------#")