Das folgende Ereignisskript zeigt, wie Sie mit einem Datenbankaufruf Daten aus der Zieltabelle löschen und dann mit sqlldr die Zieltabelle mit einem aus EPM extrahierten Dataset auffüllen. Dieses Beispiel zeigt, wie Sie eine Cloud-Datenbank mit sqlldr auffüllen. Mit dieser Methode können jedoch auch andere Nicht-Oracle-Datenbanken von Drittanbietern aufgefüllt werden. Wie im vorherigen Beispiel dient auch dieses Skript nur als Beispiel, und es wird keine Garantie für Defekte gewährt. Benutzer dürfen keine Serviceanfrage an Oracle Support stellen, wenn sie Fragen oder Probleme im Zusammenhang mit dem Skript haben.
Der Standard-Writeback-Prozess verwendet einzelne Insert-Anweisungen, um Daten in die Zieldatenbank einzufügen. Wenn das Writeback-Dataset sehr groß ist, wird empfohlen, das spezifische Datenbankutility zum Laden von Massendaten anstelle des Standardprozesses zu verwenden. In diesem Beispiel zeigt das Skript, wie das Oracle-Utility sqlldr aufgerufen wird. Es wird davon ausgegangen, dass das Utility von OTN heruntergeladen und installiert wurde und für das Ereignisskript zugänglich ist.
#------------------------------------------------------------------------------------------#
# 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("#--------------------------------------#")