展示如何使用 BefExtract 事件指令碼的其他範例

以下事件指令碼展示如何進行資料庫呼叫以從目標表格清除資料，然後使用 sqlldr 以在目標表格中填入從 EPM 擷取的資料集。此範例展示如何使用 sqlldr 植入雲端資料庫，但相同技術也可用來植入其他第三方非 Oracle 資料庫。與前一個範例相似，提供此指令碼是作為範例，不保證它毫無缺點，因此使用者不可向 Oracle 客戶服務部提出關於此指令碼任何相關疑問或問題的服務要求。

預設的寫回程序會使用個別的插入陳述式將資料發佈至目標資料庫，如果寫回資料集很大，則建議您使用特定的資料庫公用程式來載入大量資料，而不是使用預設程序。在此範例中，指令碼展示如何呼叫 Oracle sqlldr 公用程式，並假設已從 OTN 下載該公用程式，而且已安裝並可供事件指令碼存取。

#------------------------------------------------------------------------------------------#
# 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("#--------------------------------------#")