显示使用 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("#--------------------------------------#")