def DropSamples(conn):
print("Dropping sample schemas and edition...")
SampleEnv.RunSqlScript(conn,
"DropSamples",
main_user=SampleEnv.GetMainUser(),
edition_user=SampleEnv.GetEditionUser(),
edition_name=SampleEnv.GetEditionName())
#
# This script requires cx_Oracle 5.0 and higher.
#------------------------------------------------------------------------------
import cx_Oracle
import SampleEnv
def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
if defaultType == cx_Oracle.CLOB:
return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize)
if defaultType == cx_Oracle.BLOB:
return cursor.var(cx_Oracle.LONG_BINARY, arraysize=cursor.arraysize)
connection = cx_Oracle.connect(SampleEnv.GetMainConnectString())
connection.outputtypehandler = OutputTypeHandler
cursor = connection.cursor()
# add some data to the tables
print("Populating tables with data...")
cursor.execute("truncate table TestClobs")
cursor.execute("truncate table TestBlobs")
longString = ""
for i in range(10):
char = chr(ord('A') + i)
longString += char * 25000
# uncomment the line below for cx_Oracle 5.3 and earlier
# cursor.setinputsizes(None, cx_Oracle.LONG_STRING)
cursor.execute("insert into TestClobs values (:1, :2)",
(i + 1, "STRING " + longString))
if actualValue is None:
raise ValueError("Key %s only supports values: %s" % \
(key, ", ".join(valueDict)))
stateParts.append("%s = %s" % (key, actualValue))
sql = "alter session set %s" % " ".join(stateParts)
cursor = conn.cursor()
cursor.execute(sql)
# assign the requested tag to the connection so that when the connection
# is closed, it will automatically be retagged; note that if the requested
# tag is None (no tag was requested) this has no effect
conn.tag = requestedTag
# create pool with session callback defined
pool = cx_Oracle.SessionPool(SampleEnv.GetMainUser(),
SampleEnv.GetMainPassword(), SampleEnv.GetConnectString(), min=2,
max=5, increment=1, threaded=True, sessionCallback=InitSession)
# acquire session without specifying a tag; since the session returned is
# newly created, the callback will be invoked but since there is no tag
# specified, no session state will be changed
print("(1) acquire session without tag")
with pool.acquire() as conn:
cursor = conn.cursor()
cursor.execute("select to_char(current_date) from dual")
result, = cursor.fetchone()
print("main(): result is", repr(result))
# acquire session, specifying a tag; since the session returned has no tag,
# the callback will be invoked; session state will be changed and the tag will
return
print("Message database name:", message.dbname)
print("Message tranasction id:", message.txid)
print("Message tables:")
for table in message.tables:
print("--> Table Name:", table.name)
print("--> Table Operation:", table.operation)
if table.rows is not None:
print("--> Table Rows:")
for row in table.rows:
print("--> --> Row RowId:", row.rowid)
print("--> --> Row Operation:", row.operation)
print("-" * 60)
print("=" * 60)
connection = cx_Oracle.connect(SampleEnv.GetMainConnectString(), events = True)
sub = connection.subscribe(callback = callback, timeout = 1800,
qos = cx_Oracle.SUBSCR_QOS_ROWIDS)
print("Subscription:", sub)
print("--> Connection:", sub.connection)
print("--> ID:", sub.id)
print("--> Callback:", sub.callback)
print("--> Namespace:", sub.namespace)
print("--> Protocol:", sub.protocol)
print("--> Timeout:", sub.timeout)
print("--> Operations:", sub.operations)
print("--> Rowids?:", bool(sub.qos & cx_Oracle.SUBSCR_QOS_ROWIDS))
sub.registerquery("select * from TestTempTable")
while registered:
print("Waiting for notifications....")
# DRCP.py
# This script demonstrates the use of Database Resident Connection Pooling
# (DRCP) which provides a connection pool in the database server, thereby
# reducing the cost of creating and tearing down client connections. The pool
# can be started and stopped in the database by issuing the following commands
# in SQL*Plus:
#
# exec dbms_connection_pool.start_pool()
# exec dbms_connection_pool.stop_pool()
#
# Statistics regarding the pool can be acquired from the following query:
#
# select * from v$cpool_cc_stats;
#
# There is no difference in how a connection is used once it has been
# established.
#
# This script requires cx_Oracle 5.0 and higher.
#------------------------------------------------------------------------------
import cx_Oracle
import SampleEnv
conn = cx_Oracle.connect(SampleEnv.GetDrcpConnectString(),
cclass="PYCLASS",
purity=cx_Oracle.ATTR_PURITY_SELF)
cursor = conn.cursor()
print("Performing query using DRCP...")
for row in cursor.execute("select * from TestNumbers order by IntCol"):
print(row)
# SetupSamples.py
#
# Creates users and populates their schemas with the tables and packages
# necessary for the cx_Oracle samples. An edition is also created for the
# demonstration of PL/SQL editioning.
#------------------------------------------------------------------------------
from __future__ import print_function
import cx_Oracle
import SampleEnv
import DropSamples
# connect as SYSDBA
conn = cx_Oracle.connect(SampleEnv.GetSysdbaConnectString(),
mode=cx_Oracle.SYSDBA)
# drop existing users and editions, if applicable
DropSamples.DropSamples(conn)
# create sample schema and edition
print("Creating sample schemas and edition...")
SampleEnv.RunSqlScript(conn,
"SetupSamples",
main_user=SampleEnv.GetMainUser(),
main_password=SampleEnv.GetMainPassword(),
edition_user=SampleEnv.GetEditionUser(),
edition_password=SampleEnv.GetEditionPassword(),
edition_name=SampleEnv.GetEditionName())
print("Done.")
# dbms_service.start_service('orcl-tg');
# end;
# /
#
# This script requires cx_Oracle 5.3 and higher.
#------------------------------------------------------------------------------
import cx_Oracle
import SampleEnv
import sys
# constants
CONNECT_STRING = "localhost/orcl-tg"
# create transaction and generate a recoverable error
pool = cx_Oracle.SessionPool(SampleEnv.GetMainUser(),
SampleEnv.GetMainPassword(),
CONNECT_STRING,
min=1,
max=9,
increment=2)
connection = pool.acquire()
cursor = connection.cursor()
cursor.execute("""
delete from TestTempTable
where IntCol = 1""")
cursor.execute("""
insert into TestTempTable
values (1, null)""")
input("Please kill %s session now. Press ENTER when complete." % \
SampleEnv.GetMainUser())
#------------------------------------------------------------------------------
# Editioning.py
# This script demonstrates the use of Edition-Based Redefinition, available
# in Oracle# Database 11.2 and higher. See the Oracle documentation on the
# subject for additional information. Adjust the contants at the top of the
# script for your own database as needed.
#
# This script requires cx_Oracle 5.3 and higher.
#------------------------------------------------------------------------------
import cx_Oracle
import SampleEnv
import os
# connect to the editions user and create a procedure
editionConnectString = SampleEnv.GetEditionConnectString()
connection = cx_Oracle.connect(editionConnectString)
print("Edition should be None, actual value is:", repr(connection.edition))
cursor = connection.cursor()
cursor.execute("""
create or replace function TestEditions return varchar2 as
begin
return 'Base Procedure';
end;""")
result = cursor.callfunc("TestEditions", str)
print("Function should return 'Base Procedure', actually returns:",
repr(result))
# next, change the edition and recreate the procedure in the new edition
cursor.execute("alter session set edition = %s" % SampleEnv.GetEditionName())
print("Edition should be", repr(SampleEnv.GetEditionName().upper()),
def __init__(self):
connectString = SampleEnv.GetMainConnectString()
print("CONNECT to database")
return super(Connection, self).__init__(connectString)
#------------------------------------------------------------------------------
# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# DropSamples.py
#
# Drops the database objects used for the cx_Oracle samples.
#------------------------------------------------------------------------------
import cx_Oracle
import SampleEnv
def DropSamples(conn):
print("Dropping sample schemas and edition...")
SampleEnv.RunSqlScript(conn,
"DropSamples",
main_user=SampleEnv.GetMainUser(),
edition_user=SampleEnv.GetEditionUser(),
edition_name=SampleEnv.GetEditionName())
if __name__ == "__main__":
conn = cx_Oracle.connect(SampleEnv.GetAdminConnectString())
DropSamples(conn)
print("Done.")
#------------------------------------------------------------------------------
# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# DropSamples.py
#
# Drops the database objects used for the cx_Oracle samples.
#------------------------------------------------------------------------------
from __future__ import print_function
import cx_Oracle
import SampleEnv
def DropSamples(conn):
print("Dropping sample schemas and edition...")
SampleEnv.RunSqlScript(conn,
"DropSamples",
main_user=SampleEnv.GetMainUser(),
edition_user=SampleEnv.GetEditionUser(),
edition_name=SampleEnv.GetEditionName())
if __name__ == "__main__":
conn = cx_Oracle.connect(SampleEnv.GetSysdbaConnectString(),
mode=cx_Oracle.SYSDBA)
DropSamples(conn)
print("Done.")
class Connection(cx_Oracle.Connection):
def cursor(self):
return Cursor(self)
class Cursor(cx_Oracle.Cursor):
def execute(self, statement, args=None):
prepareNeeded = (self.statement != statement)
result = super(Cursor, self).execute(statement, args or [])
if prepareNeeded:
description = self.description
if description:
names = [d[0] for d in description]
self.rowfactory = collections.namedtuple("GenericQuery", names)
return result
# create new subclassed connection and cursor
connection = Connection(SampleEnv.GetMainConnectString())
cursor = connection.cursor()
# the names are now available directly for each query executed
for row in cursor.execute("select ParentId, Description from ParentTable"):
print(row.PARENTID, "->", row.DESCRIPTION)
print()
for row in cursor.execute("select ChildId, Description from ChildTable"):
print(row.CHILDID, "->", row.DESCRIPTION)
print()