def __init__(self, connectionString, debug=False):
if debug:
print "You're using Oracle Client Tools v" + ".".join(map(str,cx_Oracle.clientversion()))
self._conn = cx_Oracle.connect(connectionString)
self._conn.autocommit = True
self._curs = self._conn.cursor()
self.fetchall = self._curs.fetchall
self.description = self._curs.description
self.columns = dict()
def client_version():
"""
Oracle Client Version
CLI Example:
.. code-block:: bash
salt '*' oracle.client_version
"""
return ".".join((str(x) for x in cx_Oracle.clientversion()))
def client_version():
'''
Oracle Client Version
CLI Example:
.. code-block:: bash
salt '*' oracle.client_version
'''
return '.'.join((str(x) for x in cx_Oracle.clientversion()))
def getSodaDatabase(
self,
minclient=(18, 3),
minserver=(18, 0),
message="not supported with this client/server combination"):
client = cx_Oracle.clientversion()[:2]
if client < minclient:
self.skipTest(message)
server = tuple(int(s) for s in self.connection.version.split("."))[:2]
if server < minserver:
self.skipTest(message)
if server > (20, 1) and client < (20, 1):
self.skipTest(message)
return self.connection.getSodaDatabase()
def ConnDb(db_type='ora', alias_db=None):
if db_type == 'ora':
try:
new_dsn = cx_Oracle.makedsn(host=alias_db['host'],
port=alias_db['port'],
service_name=alias_db['sid'])
connection = cx_Oracle.connect(alias_db['user'], alias_db['passw'],
new_dsn)
connection.autocommit = False
logger.info("> connected to Oracle, client {0}".format('.'.join(
[str(i) for i in cx_Oracle.clientversion()])))
return connection
except cx_Oracle.DatabaseError as exc:
error, = exc.args
logger.error('{0}\n'.format(error.message.strip()))
return False
else:
logger.error("Only ORACLE connection available in this version")
return False
def testPLSQLSessionCallbacks(self):
"test PL/SQL session callbacks"
clientVersion = cx_Oracle.clientversion()
if clientVersion < (12, 2):
self.skipTest("PL/SQL session callbacks not supported before 12.2")
pool = TestEnv.GetPool(
min=2,
max=8,
increment=3,
getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT,
sessionCallback="pkg_SessionCallback.TheCallback")
tags = [
"NLS_DATE_FORMAT=SIMPLE", "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC",
"NLS_DATE_FORMAT=FULL;TIME_ZONE=MST"
]
actualTags = [None, None, "NLS_DATE_FORMAT=FULL;TIME_ZONE=UTC"]
# truncate PL/SQL session callback log
conn = pool.acquire()
cursor = conn.cursor()
cursor.execute("truncate table PLSQLSessionCallbacks")
conn.close()
# request sessions with each of the first two tags
for tag in tags[:2]:
conn = pool.acquire(tag=tag)
conn.close()
# for the last tag, use the matchanytag flag
conn = pool.acquire(tag=tags[2], matchanytag=True)
conn.close()
# verify the PL/SQL session callback log is accurate
conn = pool.acquire()
cursor = conn.cursor()
cursor.execute("""
select RequestedTag, ActualTag
from PLSQLSessionCallbacks
order by FixupTimestamp""")
results = cursor.fetchall()
expectedResults = list(zip(tags, actualTags))
self.assertEqual(results, expectedResults)
def connectNoDSN(self, user, pswd, serviceName, host, port=1521):
'''
Allows for the creation of a database connection when the
client does not have a valid TNS file. Allows you to connect
using port and host name.
:param user: schema that you are using to connnect to the database
:type user: str
:param pswd: password that goes with the schema
:type pswd: str
:param serviceName: The database serviceName (service_name) that is being connected to.
:type serviceName: str
:param host: The host that the serviceName resides on
:type host: str
:param port: the port that the database listener is attached to.
:type port: int
'''
try:
clientVer = cx_Oracle.clientversion()
self.logger.debug("client version: %s", clientVer)
cxOraPath = os.path.abspath(cx_Oracle.__file__)
self.logger.debug("cx_oracle path is: %s", cxOraPath)
# dsn = cx_Oracle.makedsn(host, port, service_name=serviceName)
dsn = cx_Oracle.makedsn(host, port, service_name=serviceName) # pylint: disable=no-member
self.logger.info("successfully connected to host/sn %s/%s", host,
serviceName)
except Exception as e: # pylint: disable=broad-except
msg = u'Got an error trying to create the dsn using the ' + \
u'service_name keyword. Trying with no keywords'
self.logger.debug(msg)
self.logger.debug(repr(e))
msg = u'input params are, host: {0}, port: {1}, inst {2}'
msg = msg.format(host, port, serviceName)
dsn = cx_Oracle.makedsn(host, port, serviceName).replace(
u'SID', u'SERVICE_NAME') # pylint: disable=no-member
self.logger.debug(u'dsn returned is: %s', dsn)
self.connectParams(user, pswd, dsn)
def get_app():
info = [
('Startup time', startup_time.strftime(app.config['DATETIME_FORMAT'])),
('Oracle client version', '.'.join((str(x) for x in clientversion()))),
('OS version ', platform())
]
with lock:
if target_pool:
info.append(('Session pools ', ', '.join(target_pool.keys())))
info.append(('Task worker', 'ON' if worker.is_alive() else 'OFF'))
info.append(('Chat bot', 'ON' if bot.is_alive() else 'OFF'))
if app.config['DND_HOURS']:
info.append(('Do not disturb hours',
f"from {app.config['DND_HOURS'][0]}:00"
f" to {app.config['DND_HOURS'][1]}:00"))
t = render_template('administration.html',
info=info,
active_connections=active_connections,
task_pool=task_pool,
task_id=request.args.get('task_id', ''))
return t
import os
import platform
LOCATION = r"instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2"
print("ARCH:", platform.architecture())
print("FILES AT LOCATION:")
for name in os.listdir(LOCATION):
print(name)
os.environ["PATH"] = LOCATION + ";" + os.environ["PATH"]
import cx_Oracle
print(cx_Oracle.version)
cx_Oracle.clientversion()
connection = cx_Oracle.connect(user="******",
password="******",
dsn="localhost/orcl3")
print("Successfully connected to Oracle Database")
cursor = connection.cursor()
def test(cursor):
for row in cursor.execute('select * from tablenames'):
print(row)
def ConnDb(db_type='ora', **kwargs):
if db_type=='ora':
try:
new_dsn = cx_Oracle.makedsn(host=kwargs['host'], port=kwargs['port'], service_name=kwargs['sid'])
connection = cx_Oracle.connect(kwargs['user'], kwargs['passw'], new_dsn)
connection.autocommit = False
logging.info("> connected to Oracle, client {0}".format('.'.join([str(i) for i in cx_Oracle.clientversion()])))
return connection
except cx_Oracle.DatabaseError as exc:
error, = exc.args
logging.error('{0}\n'.format(error.message.strip()))
return False
# elif db_type=='sqlite':
# connection = sqlite.connect(":memory:")
# logging.info(" > connected to SQLITE {0}".format(sqlite.sqlite_version))
# logging.info(' > ...OK\n')
# return connection
else:
logging.error("Only ORACLE connection available in this version")
return False
def GetClientVersion():
return cx_Oracle.clientversion()
# coding:utf-8
import cx_Oracle
import os
import json
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
print("cx_Oracle版本：", cx_Oracle.clientversion())
u'''Oracle数据库相关操作
连接数据库名称如：xxx
查询一组数据：oracle_getrows
查询某个字段对应的字符串：oracle_getstring
执行sql语句：oracle_sql
关闭oracle连接：oracle_close
'''
dbname = {"user": "******",
"pwd": "123456",
"dsn": "127.0.0.1:1521/ORCL"}
class OracleUtil():
def __init__(self):
''' 连接池方式'''
self.db_info = dbname
self.conn = OracleUtil.__getConnect(self.db_info)
@staticmethod
def __getConnect(db_info):
''' 静态方法，从连接池中取出连接'''
try:
con = cx_Oracle.connect(db_info['user'], db_info['pwd'], db_info['dsn'])
def client_version():
return cx_Oracle.clientversion()
class BackUp:
'''class used to back up projects'''
# use a separate config file to store these values
# ------------------------------------------------------------------------------------
# set the connection properties TNS/DB connection
# ------------------------------------------------------------------------------------
src_host = Settings.DB_HOST
src_service = Settings.DB_SERVICE_NAME
port = Settings.DB_PORT
db_username = Settings.DB_USERNAME
db_passwd = Settings.DB_PASSWORD
dsn_tns = cx_Oracle.makedsn(src_host, port, service_name=src_service)
# ---------------------------------------------------------------------------------------
# Get Connection properties
# ---------------------------------------------------------------------------------------
client_version = cx_Oracle.clientversion()
# ---------------------------------------------------------------------------------------
# Folders and file locations
# ---------------------------------------------------------------------------------------
backup_path = Settings.BACKUP_PATH
log_filepath = Settings.LOG_FILEPATH
batch_file = Settings.BATCH_FILE
log_filename = r'log.txt'
# count of projects
project_count = 0
def write_to_file(self, line, filename, path):
''' Helper function for writing lines into a file.
Appends to an existing or creates a new one. Takes string to be written,
the file name, and absolute path as arguments '''
if path and path[-1] != '\\':
path = path + '\\'
if line.strip() and filename:
file = open(path + filename, 'a')
# write to file
file.write(str(line) + '\n')
# print to console
print(line)
file.close()
else:
print('No valid file name or path provided.')
def run_cmdline(self, file):
''' Run the .bat file that does the actual back up process.
Takes the absolute path/file name as argument'''
msg = ''
try:
if file.strip():
file_details = file.split('\\')
filename = file_details[len(file_details) - 1]
BackUp.write_to_file(self, f"\nRunning {filename} .....",
self.log_filename, self.log_filepath)
# run the subroutine
subprocess.CompletedProcess = subprocess.run(
[file], text=True, capture_output=True)
# get the results from the run command
ret_code = subprocess.CompletedProcess.returncode
ret_error = subprocess.CompletedProcess.stderr
ret_sdtout = subprocess.CompletedProcess.stdout
if ret_code == 0:
# successfully ran .bat file
msg = f"End running {filename} file.....\n{ret_sdtout}"
else:
msg = f'\nReturn code: {ret_code}\n{ret_error}'
else:
msg = "Please provide a complete file location"
except:
msg = "There was an error running the .bat file"
finally:
BackUp.write_to_file(self, msg, self.log_filename,
self.log_filepath)
# ------------------------------------------------------------------------------------
# Connect to the database
# get the list of projects
# write it to a file
# ------------------------------------------------------------------------------------
def conntect_to_db(self, username, passwd):
self.username = username
self.password = passwd
msg = ''
try:
conn = cx_Oracle.connect(user=self.username,
password=self.password,
dsn=self.dsn_tns)
msg = '\nSuccessfully connected to ' + self.src_service
return conn
except cx_Oracle.DatabaseError as ex:
msg = '\nDatabase Error:\n ' + str(ex)
except cx_Oracle.InterfaceError as ix:
msg = '\nInterface Error:\n ' + str(ix)
finally:
BackUp.write_to_file(self, msg, self.log_filename,
self.log_filepath)
def get_projects(self):
''' Queries the list of changed/new
projects and writes it to a file '''
self.project_count = 0
conn = BackUp.conntect_to_db(self, self.db_username, self.db_passwd)
if conn:
cursor = conn.cursor()
cursor.execute("""
SELECT projectname,
projectdescr,
lastupddttm,
lastupdoprid
FROM SYSADM.PSPROJECTDEFN
WHERE lastupddttm > (SELECT CREATED
FROM
V$DATABASE)""")
for projectname, projectdescr, lastupddttm, lastupdoprid in cursor:
projectname = projectname
projectdescr = projectdescr
lastupddttm = lastupddttm
lastupdoprid = lastupdoprid
# create projectlist.txt file
BackUp.write_to_file(self, projectname, 'projectlist.txt',
BackUp.backup_path)
self.project_count += 1
# close thecursor object and connection
cursor.close()
conn.close()
return self.project_count
else:
msg = 'No project list created. Connection was not established to the database'
BackUp.write_to_file(self, msg, self.log_filename,
self.log_filepath)
return self.project_count
# Copy projects to a file
def backup_to_file(self):
''' Copy projects to file. Calls the run_cmdline() method only if project files
were successfully created by the get_projects() method.'''
BackUp.write_to_file(
self, f'\n' + ('*' * 20) + 'BEGIN - ' + str(dttm.now(tz=None)) +
' ' + ('*' * 20), self.log_filename, self.log_filepath)
queried_projects = BackUp.get_projects(self)
BackUp.write_to_file(self, f'{queried_projects} projects to back up.',
self.log_filename, self.log_filepath)
if queried_projects and queried_projects >= 0:
BackUp.run_cmdline(self, self.batch_file)
BackUp.write_to_file(
self, f'\n' + ('*' * 20) + 'END - ' + str(dttm.now(tz=None)) +
' ' + ('*' * 20), self.log_filename, self.log_filepath)
# To implemented later
def backup_to_database(self):
''' Copy projects to a database. To be implemented '''
pass
def main(argv):
global totalFileSize
# Parameter 1 - Import Directory
# Parameter 2 - Subfolder Level
# Remember the start time of the program
start_time = time.clock()
usage_string = " DocumentLoader.py -s <src> -r <recursive Level> -d <oracle_directory_path> -c <config_path>"
path_name = '-'
dest_name = '-'
recursiveLevel = 0
config_path = 'dataLoader.conf'
# -- Parameter from the config file
oracle_port = '-'
oracle_host = '-'
oracle_service = '-'
oracle_user = '******'
oracle_pwd = '-'
magicFilePath = '-'
ingoreFileExt = []
# Path of the oracle Info Archive
ora_dir_path = '-'
try:
opts, args = getopt.getopt(argv, "hs:d:r:d:c:", ["src=", "rec=", "dir=", "config="])
except getopt.GetoptError:
print("usage: {0}".format(usage_string))
sys.exit(2)
# read the parameter
for opt, arg in opts:
if opt == '-h':
print("usage: {0}").format(usage_string)
sys.exit()
elif opt in ("-s", "--src"):
path_name = arg
elif opt in ("-d", "--dir"):
ora_dir_path = arg
elif opt in ("-c", "--config"):
config_path = arg
elif opt in ("-r", "--rec"):
recursiveLevel = int(arg)
# read the config file
config = configparser.ConfigParser()
# check if the file exits
if os.path.exists(config_path) == False:
# use normal configparser to write the template
print("--" + 80 * "!")
print("-- Error to read file {0}".format(config_path))
print("-- Error usage: {0}".format(usage_string))
config['DEFAULT'] = {'MagicFile': 'd:\\tools\\file\\bin\\file.exe', 'ignoreFileExt': '.iso'}
config['ORACLE_DB_CONNECT'] = {'Host': 'localhost', 'Port': '1521', 'Service': 'ORCL', 'DB_User': '******',
'DB_Password': '******'}
with open(config_path, 'w') as configfile:
config.write(configfile)
print("--" + 80 * "!")
print("-- Info create Configuration Template :: {0}".format(config_path))
print("-- Info fillout the configuration file with your personal values and start again!")
print("--" + 80 * "!")
sys.exit(2)
else:
print("-- Info read config file {0}".format(config_path))
config.read(config_path)
# Parameter of the application
general_configuration = config['DEFAULT']
magicFilePath = general_configuration['MagicFile']
ingoreFileExtString = general_configuration['ignoreFileExt']
ingoreFileExt = str.split(str.replace(ingoreFileExtString, ' ', ''), ',')
# Oracle DB Connect
oracle_db_configuration = config['ORACLE_DB_CONNECT']
oracle_port = oracle_db_configuration['Port']
oracle_host = oracle_db_configuration['Host']
oracle_service = oracle_db_configuration['Service']
oracle_user = oracle_db_configuration['DB_User']
oracle_pwd = oracle_db_configuration['DB_Password']
# check if Directory exists and if the * is necessary
# BUG ! if more then 1 then the * not match the documents on root level??
# FIX IT!
# Source
if os.path.isdir(path_name):
if path_name.endswith(os.path.sep):
path_name += ("*" + os.path.sep) * recursiveLevel
path_name += "*.*"
else:
path_name += os.path.sep
path_name += ("*" + os.path.sep) * recursiveLevel
path_name += "*.*"
else:
print("-- Error :: 05 Source Directory (-s) {0} not found".format(path_name))
print("usage: {0}").format(usage_string)
sys.exit(2)
# connect to the database
print("--" + 40 * "=")
print("-- Info :: Oracle Client Library Version :: {0}".format(cx_Oracle.clientversion()))
# get the connection to the database
print("-- Info :: oracle_host {0} oracle_port {1} oracle_service {2} oracle_user {3} oracle_pwd ********".format(
oracle_host, oracle_port, oracle_service, oracle_user, oracle_pwd))
connection = cx_Oracle.connect(
oracle_user + '/' + oracle_pwd + '@' + oracle_host + ':' + oracle_port + '/' + oracle_service)
# Version der DB ausgeben
print("-- Info :: Oracle Database Version :: {0}".format(connection.version))
print("--" + 40 * "=")
print("-- Info :: Environment Settings :: Language :: {0} - Char Set ::{1}".format(locale.getdefaultlocale()[0],
locale.getdefaultlocale()[1]))
print("--" + 40 * "=")
print("-- Info :: Read all files from {0}".format(path_name))
print("-- Info :: Copy files to {0}".format(dest_name))
print("-- Info :: Not index this file types ::" + str(ingoreFileExt))
print("--" + 40 * "=")
fileCount = 0
fileExistsCount = 0
dirCount = 0
dirPathList = []
totalFileSize = 0
# Get the list of all Files
fileList = glob.glob(path_name)
# remove Thumbs.db if exist from the list
thumbsDBFile = "Thumbs.db"
for file in fileList:
if file.endswith(thumbsDBFile):
fileList.remove(file)
# Loop one read files in Import Directory
for file in fileList:
fileCount += 1
#read only some files
if fileCount > 20:
exit
# do the work
try:
# check if directoy
if ntpath.isdir(file):
print("-- Info :: found dirctory {0} ::".format(file))
dirCount += 1
else:
# get only the filename without the path
filename = ntpath.basename(file)
# get directory
fileDirectoryName = ntpath.dirname(file)
# get Create date
fileAccessDate = datetime.datetime.fromtimestamp(ntpath.getatime(file))
fileModDate = datetime.datetime.fromtimestamp(ntpath.getmtime(file))
fileCreateDate = datetime.datetime.fromtimestamp(ntpath.getctime(file))
# get md5 hash
md5checkSum = getMD5(file)
# get File Type over file from external, Python Lib magic not working, error with magic file!
# now I implement this stupid solution
fileType = getFileType(file, " ", magicFilePath)
# Call file with -i to get the mime Type
fileMimeType = getFileType(file, "-i", magicFilePath)
# get Extenstion
fileExt = getFile_ext(filename)
# getFileSize
fileSize = os.path.getsize(file)
# Remember for statistic
setStatisticTotalSize(fileSize)
# not add url files to the index
# endswith(".url")
if fileExt in ingoreFileExt:
# encode the output with UTF-8 to avoid errors with stange things in filenames
print("-- Info :: Not index this file types ::" + str(ingoreFileExt))
print("-- Info :: Not index this file::{0}".format(repr(filename.encode('utf-8'))))
print("-- Info :: Not index this Dir::{0}".format(repr(fileDirectoryName.encode('utf-8'))))
print("-- --")
else:
# record
fileInfo = dict(filename=str(filename), filepath=str(fileDirectoryName), fileADate=fileAccessDate,
fileMDate=fileModDate, fileCDate=fileCreateDate, md5=md5checkSum,
fileBType=fileType,
fileMtype=fileMimeType, fileExtention=fileExt, fileSize=fileSize)
##print("-- Index this Dir::{0}".format(repr(fileDirectoryName.encode('utf-8'))))
# encode the output with UTF-8 to avoid errors with stange things in filenames
##print("-- Index this file ::{0}".format(repr(filename.encode('utf-8'))))
# write to DB
insertFileInfo(fileInfo, ora_dir_path, connection)
except OSError as exception:
if exception.errno != errno.EEXIST:
print("-- Error :: Error read file :: see error {1}".format(file, sys.exc_info()[0]))
# print statistics
print("--" + 40 * "=")
print("-- Finish with :: {0} files in {1} new directories".format(fileCount, dirCount))
print("-- The run needs :: {0:5.4f} seconds".format(time.clock() - start_time))
print("-- Read size :: {0:5.3f} MB".format(totalFileSize / 1024 / 1024))
print("--" + 40 * "=")
# Close the DB Connection
connection.close()
#------------------------------------------------------------------------------
# json_direct.py
# Shows some JSON features of Oracle Database 21c.
# See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN
#
# For JSON with older databases see json_blob.py
#------------------------------------------------------------------------------
import sys
import json
import cx_Oracle as oracledb
import sample_env
connection = oracledb.connect(sample_env.get_main_connect_string())
client_version = oracledb.clientversion()[0]
db_version = int(connection.version.split(".")[0])
# this script only works with Oracle Database 21
if db_version < 21:
sys.exit("This example requires Oracle Database 21.1 or later. "
"Try json_blob.py")
# Create a table
cursor = connection.cursor()
cursor.execute("""
begin
execute immediate 'drop table customers';
exception when others then
# Portions Copyright 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta,
# Canada. All rights reserved.
#------------------------------------------------------------------------------
"""Runs all defined unit tests."""
import cx_Oracle
import os
import sys
import TestEnv
import unittest
# display version of cx_Oracle and Oracle client for which tests are being run
print("Running tests for cx_Oracle version", cx_Oracle.version, "built at",
cx_Oracle.buildtime)
print("File:", cx_Oracle.__file__)
print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion()))
sys.stdout.flush()
# verify that we can connect to the database and display database version
connection = TestEnv.GetConnection()
print("Server Version:", connection.version)
sys.stdout.flush()
# define test cases to run
moduleNames = [
"Module", "Connection", "Cursor", "CursorVar", "DateTimeVar", "DbTypes",
"DMLReturning", "Error", "IntervalVar", "LobVar", "LongVar", "NCharVar",
"NumberVar", "ObjectVar", "SessionPool", "StringVar", "TimestampVar", "AQ",
"BulkAQ", "Rowid", "Subscription"
]
clientVersion = cx_Oracle.clientversion()
def create_connection(self):
service_check_tags = ['server:%s' % self._server]
service_check_tags.extend(self._tags)
try:
# Check if the instantclient is available
cx_Oracle.clientversion()
except cx_Oracle.DatabaseError as e:
# Fallback to JDBC
use_oracle_client = False
self.log.debug(
'Oracle instant client unavailable, falling back to JDBC: %s',
e)
connect_string = self.JDBC_CONNECT_STRING.format(
self._server, self._service)
else:
use_oracle_client = True
self.log.debug('Running cx_Oracle version %s', cx_Oracle.version)
connect_string = self.CX_CONNECT_STRING.format(
self._user, self._password, self._server, self._service)
try:
if use_oracle_client:
connection = cx_Oracle.connect(connect_string)
elif JDBC_IMPORT_ERROR:
self.log.error(
"Oracle client is unavailable and the integration is unable to import JDBC libraries. You may not "
"have the Microsoft Visual C++ Runtime 2015 installed on your system. Please double check your "
"installation and refer to the Datadog documentation for more information."
)
raise JDBC_IMPORT_ERROR
else:
try:
if jpype.isJVMStarted(
) and not jpype.isThreadAttachedToJVM():
jpype.attachThreadToJVM()
jpype.java.lang.Thread.currentThread(
).setContextClassLoader(
jpype.java.lang.ClassLoader.getSystemClassLoader())
connection = jdb.connect(self.ORACLE_DRIVER_CLASS,
connect_string,
[self._user, self._password],
self._jdbc_driver)
except Exception as e:
if "Class {} not found".format(
self.ORACLE_DRIVER_CLASS) in str(e):
msg = """Cannot run the Oracle check until either the Oracle instant client or the JDBC Driver
is available.
For the Oracle instant client, see:
http://www.oracle.com/technetwork/database/features/instant-client/index.html
You will also need to ensure the `LD_LIBRARY_PATH` is also updated so the libs are reachable.
For the JDBC Driver, see:
http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html
You will also need to ensure the jar is either listed in your $CLASSPATH or in the yaml
configuration file of the check.
"""
self.log.error(msg)
raise
self.log.debug("Connected to Oracle DB")
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.OK,
tags=service_check_tags)
except Exception as e:
self.service_check(self.SERVICE_CHECK_NAME,
AgentCheck.CRITICAL,
tags=service_check_tags)
self.log.error(e)
raise
self._connection = connection
import cx_Oracle
if __name__ == "__main__":
name = raw_input("Enter Lan ID ")
HC_ID = raw_input("Enter HC_ID ")
BATCH = raw_input("Enter BATCH_ID ")
#os.startfile('"C:\TEMP\putty\putty.exe"')
#putty = putty.exe
#if putty:
# print "etlt1.bankofthewest.com"
SQL = "SELECT HC_ID, BATCH_ID, HC_REPORT FROM AUDITDATA.HC_RUN WHERE HC_ID = 5510501 AND BATCH_ID = 551 ;"
print("Python version: " + platform.python_version())
print("cx_Oracle version: " + cx_Oracle.version)
print("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ', '.'))
pwd = getpass.getpass()
connection = cx_Oracle.connect('' + name + '@TEDW/etlt1.bankofthewest.com')
print("Oracle DB version: " + connection.version)
print("Oracle client encoding: " + connection.encoding)
print 'sqlplus'
print SQL
import conval
time.sleep(60)
connection.close()
import cx_Oracle as ora
import os
print(ora.clientversion())
if __name__ == '__main__':
# 文字コードの指定を環境変数で行う
os.environ["NLS_LANG"] = "JAPANESE_JAPAN.JA16SJISTILDE"
try:
tns = ora.makedsn("localhost", 1521, "XE")
con = ora.connect("SAMPLE", "SAMPLE", tns)
cur = con.cursor()
# SELECT文
cur.execute("select * from tbl_sample")
rows = cur.fetchall()
for r in rows:
print(r)
print(r[0], ":", r[1])
# INSERT文
cur.execute("insert into tbl_sample values (:id, :name)",
id=11,
name="user11")
con.commit()
# SELECT文
cur.execute("select * from tbl_sample")
# PART 1 - connecting to Oracle with cx_Oracle
# Let code speak for itself!
# ====================================================
import sys
import getpass
import platform
import cx_Oracle
# -----------------------------------------------------
# Display versions of python, cx_Oracle module
# and Oracle client being used...
# -----------------------------------------------------
print ("Python version: " + platform.python_version())
print ("cx_Oracle version: " + cx_Oracle.version)
print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.'))
connection = cx_Oracle.connect("tom/oracle@XENITH")
print ("Oracle DB version: " + connection.version)
print ("Oracle client encoding: " + connection.encoding)
connection.close()
"""
#-----------------------------------------------------------------------
# How to connect to database
#-----------------------------------------------------------------------
# Basic connection using Oracle tns alias (specified in
# in tnsnames.ora, ldap or in obsolete Oracle Names server)
connection = cx_Oracle.connect("tom/oracle@XENITH")
# By asking user for the password (suitable for
# myscript.py
import cx_Oracle
# Use following command to initialize lib dir.
cx_Oracle.init_oracle_client(lib_dir="/Users/xuansong/.bin/instantclient_19_8")
# http://www.dominicgiles.com/blog/files/a4698a719e20e8b3b9542ed43d51e0a2-178.html
# dsn_tns = cx_Oracle.makedsn('Host Name', 'Port Number', service_name='Service Name')
# conn = cx_Oracle.connect(user=r'User Name', password='******', dsn=dsn_tns)
# Read how to find SERVER_NAME with sql
# select value from v$parameter where name like '%service_name%';
# https: // www.stechies.com/difference-between-oracle-sids-and-oracle-service-names/
print("Client version: " + str(cx_Oracle.clientversion()))
# cx_Oracle.makedsn(host, port, sid=None, service_name=None, region=None, sharding_key=None, super_sharding_key=None)
# Either use server name or sid
# Option 1:
# dsn_tns = cx_Oracle.makedsn('localhost', '1521', service_name='ORCLCDB.localdomain')
# Option 2:
dsn_tns = cx_Oracle.makedsn('localhost', '1521', sid='ORCLCDB')
conn = cx_Oracle.connect(user=r'hr', password='******', dsn=dsn_tns)
print("Connection version:"+ conn.version)
# Connect as user "hr" with password "welcome" to the "orclpdb1" service running on this computer.
# connection = cx_Oracle.connect("hr", "hr", "localhost/ORCLCDB")
# cursor()??
#------------------------------------------------------------------------------
# JSON.py
# Shows some JSON features of Oracle Database 21c.
# See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN
#
# For JSON with older databases see JSONBLOB.py
#------------------------------------------------------------------------------
import sys
import json
import cx_Oracle
import sample_env
connection = cx_Oracle.connect(sample_env.get_main_connect_string())
client_version = cx_Oracle.clientversion()[0]
db_version = int(connection.version.split(".")[0])
# this script only works with Oracle Database 21
if db_version < 21:
sys.exit("This example requires Oracle Database 21.1 or later. "
"Try JSONBLOB.py")
# Create a table
cursor = connection.cursor()
cursor.execute("""
begin
execute immediate 'drop table customers';
exception when others then
"uLobVar",
"LongVar",
"uLongVar",
"NCharVar",
"NumberVar",
"uNumberVar",
"ObjectVar",
"uObjectVar",
"SessionPool",
"uSessionPool",
"StringVar",
"uStringVar",
"TimestampVar",
"uTimestampVar"
]
if cx_Oracle.clientversion()[0] >= 12:
moduleNames.insert(0, "uArrayDMLBatchError")
moduleNames.insert(0, "ArrayDMLBatchError")
class BaseTestCase(unittest.TestCase):
def setUp(self):
global cx_Oracle, TestEnv
self.connection = cx_Oracle.connect(TestEnv.USERNAME,
TestEnv.PASSWORD, TestEnv.TNSENTRY)
self.cursor = self.connection.cursor()
self.cursor.arraysize = TestEnv.ARRAY_SIZE
def tearDown(self):
del self.cursor
del self.connection
Scrive quanto contenuto in p_data nella tabella oracle UT_QREADER
"""
# Apertura del cursore e scrittura nella tabella di lavoro fatta con nome utente e il dato appena letto
v_oracle_cursor = v_oracle_db.cursor()
# Controllo se record utente esiste già
v_istruzione_sql = "select count(*) from UT_QREADER where USER_CO = '" + getpass.getuser(
) + "' and CODIC_CO = '" + p_data + "'"
v_oracle_cursor.execute(v_istruzione_sql)
# Se esiste aggiorno
if v_oracle_cursor.fetchone()[0] > 0:
v_istruzione_sql = "update UT_QREADER set CODIC_CO = '" + p_data + "'"
# altrimenti aggiungo
else:
v_istruzione_sql = "insert into UT_QREADER values('" + getpass.getuser(
) + "','" + p_data + "')"
print(v_istruzione_sql)
v_oracle_cursor.execute(v_istruzione_sql)
# Committo
v_oracle_db.commit()
# Chiudo il cursore
v_oracle_cursor.close()
print('Avvio test....')
print(cx_Oracle.clientversion())
connect_string = "SMILE/SMILE@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(" "COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.0.4.11)(Port=1521)))(" "CONNECT_DATA=(SID=SMIG)))"
print(connect_string)
v_oracle_db = cx_Oracle.connect(connect_string)
scrivi_in_ut_qreader('test qreader')
print('Fine test premere invio per continuare...')
a = input()
def check_version(self):
'''查看oracle版本'''
print(cx_Oracle.clientversion())
#!/usr/bin/python
# encoding: utf-8
# -*- coding: utf8 -*-
"""
Created by PyCharm.
File: LinuxBashShellScriptForOps:pyConnectOracle.py
User: Liuhongda
Create Date: 2016/11/24
Create Time: 10:39
"""
import cx_Oracle as orcl
print(orcl.clientversion())
username = "******"
password = "******"
host = "localhost"
port = "1521"
sid = "xe"
dsn = orcl.makedsn(host, port, sid)
con = orcl.connect(username, password, dsn)
cursor = con.cursor()
sql = "SELECT * FROM HELP"
cursor.execute(sql)
result = cursor.fetchall()
print("Total: " + str(cursor.rowcount))
for row in result:
print(row)
cursor.close()
con.close()
from __future__ import print_function
import cx_Oracle
import imp
import os
import sys
import TestEnv
import unittest
inSetup = (os.path.basename(sys.argv[0]).lower() == "setup.py")
print("Running tests for cx_Oracle version", cx_Oracle.version, "built at",
cx_Oracle.buildtime)
print("File:", cx_Oracle.__file__)
print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion()))
sys.stdout.flush()
connection = cx_Oracle.Connection(TestEnv.MAIN_USER,
TestEnv.MAIN_PASSWORD,
TestEnv.CONNECT_STRING,
encoding=TestEnv.ENCODING,
nencoding=TestEnv.NENCODING)
print("Server Version:", connection.version)
sys.stdout.flush()
if len(sys.argv) > 1 and not inSetup:
moduleNames = [os.path.splitext(v)[0] for v in sys.argv[1:]]
else:
moduleNames = [
"Connection", "Cursor", "CursorVar", "DateTimeVar", "DMLReturning",
import TestEnv
inSetup = (os.path.basename(sys.argv[0]).lower() == "setup.py")
if len(sys.argv) > 1 and not inSetup:
moduleNames = [os.path.splitext(v)[0] for v in sys.argv[1:]]
else:
moduleNames = [
"Connection", "uConnection", "Cursor", "uCursor", "CursorVar",
"uCursorVar", "DateTimeVar", "uDateTimeVar", "IntervalVar",
"uIntervalVar", "LobVar", "uLobVar", "LongVar", "uLongVar", "NCharVar",
"NumberVar", "uNumberVar", "ObjectVar", "uObjectVar", "SessionPool",
"uSessionPool", "StringVar", "uStringVar", "TimestampVar",
"uTimestampVar"
]
if cx_Oracle.clientversion()[0] >= 12:
moduleNames.insert(0, "uArrayDMLBatchError")
moduleNames.insert(0, "ArrayDMLBatchError")
moduleNames.append("BooleanVar")
class BaseTestCase(unittest.TestCase):
def setUp(self):
global cx_Oracle, TestEnv
self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD,
TestEnv.TNSENTRY)
self.cursor = self.connection.cursor()
self.cursor.arraysize = TestEnv.ARRAY_SIZE
def tearDown(self):
del self.cursor
import cx_Oracle
print 'cx_Oracle client version:', cx_Oracle.clientversion()
connection = cx_Oracle.connect('gins_user', 'report', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=seaputmsdb03.amer.gettywan.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TEAMSREP)))')
cursor = connection.cursor()
cursor.execute("""
select ra.*
from report_asset_alias raa
left join report_assets ra on
(raa.UOI_ID = ra.UOI_ID)
where raa.ALIAS_TYPE='iStockphoto' and raa.PORTAL_ALIAS = 'Y' and raa.ALIAS = :asset_id""",
{'asset_id': str(44)})
values = list(cursor.fetchone())
columns = [x[0] for x in cursor.description]
pairs = zip(columns, values)
for pair in pairs:
print pair
#print zip(columns, values)
print 'done'
if sys.version_info[0] < 3:
moduleNames.extend([
"uConnection",
"uCursor",
"uCursorVar",
"uDateTimeVar",
"uIntervalVar",
"uLobVar",
"uLongVar",
"uNumberVar",
"uObjectVar",
"uSessionPool",
"uStringVar",
"uTimestampVar"
])
clientVersion = cx_Oracle.clientversion()
if clientVersion[:2] >= (12, 1):
moduleNames.append("BooleanVar")
moduleNames.append("Features12_1")
class BaseTestCase(unittest.TestCase):
def setUp(self):
import cx_Oracle
import TestEnv
self.connection = cx_Oracle.connect(TestEnv.USERNAME,
TestEnv.PASSWORD, TestEnv.TNSENTRY,
encoding = TestEnv.ENCODING, nencoding = TestEnv.NENCODING)
self.cursor = self.connection.cursor()
self.cursor.arraysize = TestEnv.ARRAY_SIZE
#!/usr/bin/env python
#-*- coding: UTF-8 -*-
#
import cx_Oracle as orcl
if __name__ == "__main__":
print(orcl.clientversion())
# conn = cx_Oracle.connect('uopsett_b_xz/[email protected]/xz_test')
username = "******"
passwd = "uopsett"
host = "192.168.109.2"
port = "1521"
sid = "xzttest"
dsn = orcl.makedsn(host, port, sid)
con = orcl.connect(username, passwd, dsn)
cursor = con.cursor()
sql = "SELECT * FROM tp_dealtime"
cursor.execute(sql);
result = cursor.fetchall()
print("Total: " + str(cursor.rowcount))
for row in result:
print(row)
print row[1]
cursor.close()
con.close()
print(orcl.Date(2015,3,13))
#------------------------------------------------------------------------------
# versions.py (Section 1.6)
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# Copyright (c) 2017, 2018, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
from __future__ import print_function
import cx_Oracle
import db_config
con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)
print(cx_Oracle.version)
print("Database version:", con.version)
print("Client version:", cx_Oracle.clientversion())
# coding:utf-8 import cx_Oracle print cx_Oracle.clientversion() username = "******" #oracle 用户名 passwd = "demo" #oracle密码 host = "192.168.31.159" port = "1521" sid = "dbsrv2" dns = cx_Oracle.makedsn(host,port,sid) con = cx_Oracle.connect(username,passwd,dns) cur = con.cursor() sq = '''select upper(f.tablespace_name) "表空间名", d.tot_grootte_mb "表空间大小(m)", d.tot_grootte_mb - f.total_bytes "已使用空间(m)", to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100, 2), '990.99') "使用比", f.total_bytes "空闲空间(m)", f.max_bytes "最大块(m)" from (select tablespace_name, round(sum(bytes) / (1024 * 1024), 2) total_bytes, round(max(bytes) / (1024 * 1024), 2) max_bytes from sys.dba_free_space group by tablespace_name) f, (select dd.tablespace_name, round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb from sys.dba_data_files dd group by dd.tablespace_name) d where d.tablespace_name = f.tablespace_name order by 4 desc; '''
