Lo script evento riportato di seguito mostra come effettuare una chiamata al database per cancellare i dati dalla tabella target, quindi utilizzare sqlldr per popolare la tabella target con un set di dati estratto da EPM. Questo esempio mostra come popolare un database cloud con sqlldr, ma la stessa tecnica può essere utilizzata per popolare altri database non Oracle di terze parti. Come l'esempio precedente, questo script viene fornito come esempio e non è garantito che sia esente da difetti. Gli utenti pertanto non possono presentare al supporto Oracle una richiesta di assistenza riguardante domande o problemi relativi allo script.
Il processo di reinserimento predefinito utilizza singole istruzioni di inserimento per inviare i dati al database target e, se il set i dati di reinserimento è di grandi dimensioni, si consiglia di eseguire la utility specifica del database per caricare i dati in massa invece di utilizzare il processo predefinito. In questo esempio, lo script mostra come chiamare la utility sqlldr Oracle e si presume che la utility sia stata scaricata da OTN e sia installata e accessibile per lo script evento.
#------------------------------------------------------------------------------------------#
# 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("#--------------------------------------#")