def test_1118_rollback_on_del(self):
"1118 - connection rolls back before destruction"
connection = test_env.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
other_connection = test_env.get_connection()
other_cursor = other_connection.cursor()
other_cursor.execute("insert into TestTempTable (IntCol) values (1)")
del other_cursor
del other_connection
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 0)
def test_1121_ctx_mgr_close(self):
"1121 - test context manager - close"
connection = test_env.get_connection()
with connection:
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
cursor.execute("insert into TestTempTable (IntCol) values (1)")
connection.commit()
cursor.execute("insert into TestTempTable (IntCol) values (2)")
self.assertRaises(oracledb.InterfaceError, connection.ping)
connection = test_env.get_connection()
cursor = connection.cursor()
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 1)
def test_2804_enq_and_deq_visibility(self):
"2804 - test visibility option for enqueue and dequeue"
queue = self.__get_and_clear_raw_queue()
# first test with ENQ_ON_COMMIT (commit required)
queue.enqOptions.visibility = oracledb.ENQ_ON_COMMIT
props1 = self.connection.msgproperties(payload="A first message")
props2 = self.connection.msgproperties(payload="A second message")
queue.enqMany([props1, props2])
other_connection = test_env.get_connection()
other_queue = other_connection.queue(RAW_QUEUE_NAME)
other_queue.deqOptions.wait = oracledb.DEQ_NO_WAIT
other_queue.deqOptions.visibility = oracledb.DEQ_ON_COMMIT
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 0)
self.connection.commit()
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 2)
other_connection.rollback()
# second test with ENQ_IMMEDIATE (no commit required)
queue.enqOptions.visibility = oracledb.ENQ_IMMEDIATE
other_queue.deqOptions.visibility = oracledb.DEQ_IMMEDIATE
queue.enqMany([props1, props2])
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 4)
other_connection.rollback()
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 0)
def test_1126_multiple_transactions(self):
"1126 - test multiple transactions on the same connection"
connection = test_env.get_connection()
with connection.cursor() as cursor:
cursor.execute("truncate table TestTempTable")
id_ = random.randint(0, 2**128)
xid = (0x1234, "%032x" % id_, "%032x" % 9)
connection.begin(*xid)
with connection.cursor() as cursor:
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (1, 'tesName')""")
self.assertEqual(connection.prepare(), True)
connection.commit()
for begin_trans in (True, False):
val = 3
if begin_trans:
connection.begin()
val = 2
with connection.cursor() as cursor:
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (:int_val, 'tesName')""",
int_val=val)
connection.commit()
expected_rows = [(1, "tesName"), (2, "tesName"), (3, "tesName")]
with connection.cursor() as cursor:
cursor.execute("select IntCol, StringCol from TestTempTable")
self.assertEqual(cursor.fetchall(), expected_rows)
def test_2712_delivery_mode_same_persistent_buffered(self):
"2712 - test enqueue/dequeue delivery modes the same"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.deliverymode = oracledb.MSG_PERSISTENT_OR_BUFFERED
enq_options.visibility = oracledb.ENQ_IMMEDIATE
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = test_env.get_connection()
deq_options = other_connection.deqoptions()
deq_options.deliverymode = oracledb.MSG_PERSISTENT_OR_BUFFERED
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_IMMEDIATE
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
other_connection.deq(self.book_queue_name, deq_options, props, book)
results = (book.TITLE, book.AUTHORS, book.PRICE)
other_connection.commit()
self.assertEqual(results, self.book_data[0])
def test_3804(self):
"3804 - input type handler and output type handler on connection level"
self.cursor.execute("truncate table TestTempTable")
building_one = Building(1, "The First Building", 5)
building_two = Building(2, "The Second Building", 87)
sql = "insert into TestTempTable (IntCol, StringCol) values (:1, :2)"
connection = test_env.get_connection()
connection.inputtypehandler = self.input_type_handler
cursor_one = connection.cursor()
cursor_two = connection.cursor()
cursor_one.execute(sql, (building_one.building_id, building_one))
cursor_two.execute(sql, (building_two.building_id, building_two))
connection.commit()
expected_data = [
(building_one.building_id, building_one),
(building_two.building_id, building_two)
]
connection.outputtypehandler = self.output_type_handler
cursor_one.execute("select IntCol, StringCol from TestTempTable")
self.assertEqual(cursor_one.fetchall(), expected_data)
cursor_two.execute("select IntCol, StringCol from TestTempTable")
self.assertEqual(cursor_two.fetchall(), expected_data)
other_cursor = self.connection.cursor()
self.assertRaises(oracledb.NotSupportedError, other_cursor.execute,
sql, (building_one.building_id, building_one))
def test_2708_visibility_mode_commit(self):
"2708 - test enqueue visibility option - ENQ_ON_COMMIT"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.visibility = oracledb.ENQ_ON_COMMIT
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = test_env.get_connection()
deq_options = other_connection.deqoptions()
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
message_id = other_connection.deq(self.book_queue_name, deq_options,
props, book)
self.assertTrue(message_id is None)
self.connection.commit()
message_id = other_connection.deq(self.book_queue_name, deq_options,
props, book)
self.assertTrue(message_id is not None)
def __connect_and_drop(self):
"""Connect to the database, perform a query and drop the connection."""
connection = test_env.get_connection(threaded=True)
cursor = connection.cursor()
cursor.execute("select count(*) from TestNumbers")
count, = cursor.fetchone()
self.assertEqual(count, 10)
def test_1104_autocommit(self):
"1104 - test use of autocommit"
connection = test_env.get_connection()
cursor = connection.cursor()
other_connection = test_env.get_connection()
other_cursor = other_connection.cursor()
cursor.execute("truncate table TestTempTable")
cursor.execute("insert into TestTempTable (IntCol) values (1)")
other_cursor.execute("select IntCol from TestTempTable")
rows = other_cursor.fetchall()
self.assertEqual(rows, [])
connection.autocommit = True
cursor.execute("insert into TestTempTable (IntCol) values (2)")
other_cursor.execute(
"select IntCol from TestTempTable order by IntCol")
rows = other_cursor.fetchall()
self.assertEqual(rows, [(1, ), (2, )])
def test_1135_calltimeout(self):
"1135 - test connection call_timeout"
conn = test_env.get_connection()
conn.call_timeout = 500 # milliseconds
self.assertEqual(conn.call_timeout, 500)
self.assertRaises(oracledb.DatabaseError,
conn.cursor().callproc,
test_env.get_sleep_proc_name(), [2])
def test_1108_change_password_negative(self):
"1108 - test changing password to an invalid value"
connection = test_env.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
new_password = "******" * 150
self.assertRaises(oracledb.DatabaseError, connection.changepassword,
test_env.get_main_password(), new_password)
def test_1132_autocommit_during_reexecute(self):
"1132 - test use of autocommit during reexecute"
sql = "insert into TestTempTable (IntCol, StringCol) values (:1, :2)"
data_to_insert = [(1, "Test String #1"), (2, "Test String #2")]
connection = test_env.get_connection()
cursor = connection.cursor()
other_connection = test_env.get_connection()
other_cursor = other_connection.cursor()
cursor.execute("truncate table TestTempTable")
cursor.execute(sql, data_to_insert[0])
other_cursor.execute("select IntCol, StringCol from TestTempTable")
rows = other_cursor.fetchall()
self.assertEqual(rows, [])
connection.autocommit = True
cursor.execute(sql, data_to_insert[1])
other_cursor.execute("select IntCol, StringCol from TestTempTable")
rows = other_cursor.fetchall()
self.assertEqual(rows, data_to_insert)
def test_3304_repr(self):
"3304 - test SodaDatabase representation"
con1 = self.connection
con2 = test_env.get_connection()
soda_db1 = self.connection.getSodaDatabase()
soda_db2 = con1.getSodaDatabase()
soda_db3 = con2.getSodaDatabase()
self.assertEqual(str(soda_db1), str(soda_db2))
self.assertEqual(str(soda_db2), str(soda_db3))
def __deq_in_thread(self, results):
connection = test_env.get_connection(threaded=True)
books_type = connection.gettype(self.book_type_name)
queue = connection.queue(self.book_queue_name, books_type)
queue.deqoptions.wait = 10
props = queue.deqone()
if props is not None:
book = props.payload
results.append((book.TITLE, book.AUTHORS, book.PRICE))
connection.commit()
def __deq_in_thread(self, results):
connection = test_env.get_connection()
books_type = connection.gettype(self.book_type_name)
book = books_type.newobject()
options = connection.deqoptions()
options.wait = 10
props = connection.msgproperties()
if connection.deq(self.book_queue_name, options, props, book):
results.append((book.TITLE, book.AUTHORS, book.PRICE))
connection.commit()
def test_1129_threading_single_connection(self):
"1129 - single connection to database with multiple threads"
with test_env.get_connection(threaded=True) as connection:
threads = [
threading.Thread(target=self.__verify_fetched_data,
args=(connection, )) for i in range(3)
]
for t in threads:
t.start()
for t in threads:
t.join()
def __deq_in_thread(self, results):
connection = test_env.get_connection(threaded=True)
queue = connection.queue(RAW_QUEUE_NAME)
queue.deqOptions.wait = 10
queue.deqOptions.navigation = oracledb.DEQ_FIRST_MSG
while len(results) < len(RAW_PAYLOAD_DATA):
messages = queue.deqMany(5)
if not messages:
break
for m in messages:
results.append(m.payload.decode(connection.encoding))
connection.commit()
def test_1107_change_password(self):
"1107 - test changing password"
connection = test_env.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
sys_random = random.SystemRandom()
new_password = "".join(sys_random.choice(string.ascii_letters) \
for i in range(20))
connection.changepassword(test_env.get_main_password(), new_password)
cconnection = oracledb.connect(test_env.get_main_user(), new_password,
test_env.get_connect_string())
connection.changepassword(new_password, test_env.get_main_password())
def test_1123_closed_connection_attributes(self):
"1123 - test closed connection attribute values"
connection = test_env.get_connection()
connection.close()
attr_names = [
"current_schema", "edition", "external_name", "internal_name",
"stmtcachesize"
]
if test_env.get_client_version() >= (12, 1):
attr_names.append("ltxid")
for name in attr_names:
self.assertRaises(oracledb.InterfaceError, getattr, connection,
name)
def test_1101_app_context(self):
"1101 - test use of application context"
namespace = "CLIENTCONTEXT"
app_context_entries = [(namespace, "ATTR1", "VALUE1"),
(namespace, "ATTR2", "VALUE2"),
(namespace, "ATTR3", "VALUE3")]
connection = test_env.get_connection(appcontext=app_context_entries)
cursor = connection.cursor()
for namespace, name, value in app_context_entries:
cursor.execute("select sys_context(:1, :2) from dual",
(namespace, name))
actual_value, = cursor.fetchone()
self.assertEqual(actual_value, value)
def test_1134_dbms_output(self):
"1134 - test dbms_output package"
conn = test_env.get_connection()
cursor = conn.cursor()
test_string = "Testing DBMS_OUTPUT package"
cursor.callproc("dbms_output.enable")
cursor.execute("""
begin
dbms_output.put_line(:val);
end; """,
val=test_string)
string_var = cursor.var(str)
number_var = cursor.var(int)
cursor.callproc("dbms_output.get_line", (string_var, number_var))
self.assertEqual(string_var.getvalue(), test_string)
def test_1128_exception_creating_global_txn_after_local_txn(self):
"1128 - test creating global txn after a local txn"
connection = test_env.get_connection()
with connection.cursor() as cursor:
cursor.execute("truncate table TestTempTable")
val = 2
with connection.cursor() as cursor:
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (:int_val, 'tesName')""",
int_val=val)
id_ = random.randint(0, 2**128)
xid = (0x1234, "%032x" % id_, "%032x" % 9)
self.assertRaises(oracledb.DatabaseError, connection.begin, *xid)
def test_1125_transaction_begin(self):
"1125 - test begin, prepare, cancel transaction"
connection = test_env.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
connection.begin(10, 'trxnId', 'branchId')
self.assertEqual(connection.prepare(), False)
connection.begin(10, 'trxnId', 'branchId')
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (1, 'tesName')""")
self.assertEqual(connection.prepare(), True)
connection.cancel()
connection.rollback()
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 0)
def test_1113_connect_with_handle(self):
"1113 - test creating a connection using a handle"
connection = test_env.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
int_value = random.randint(1, 32768)
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (:val, null)""",
val=int_value)
connection2 = oracledb.connect(handle=connection.handle)
cursor = connection2.cursor()
cursor.execute("select IntCol from TestTempTable")
fetched_int_value, = cursor.fetchone()
self.assertEqual(fetched_int_value, int_value)
cursor.close()
self.assertRaises(oracledb.DatabaseError, connection2.close)
connection.close()
def test_1133_current_schema(self):
"1133 - test current_schame is set properly"
conn = test_env.get_connection()
self.assertEqual(conn.current_schema, None)
user = test_env.get_main_user().upper()
proxy_user = test_env.get_proxy_user().upper()
cursor = conn.cursor()
cursor.execute(f'alter session set current_schema={proxy_user}')
self.assertEqual(conn.current_schema, proxy_user)
conn.current_schema = user
self.assertEqual(conn.current_schema, user)
cursor.execute("""
select sys_context('userenv', 'current_schema')
from dual""")
result, = cursor.fetchone()
self.assertEqual(result, user)
def test_1103_attributes(self):
"1103 - test connection end-to-end tracing attributes"
connection = test_env.get_connection()
if test_env.get_client_version() >= (12, 1) \
and not self.is_on_oracle_cloud(connection):
sql = "select dbop_name from v$sql_monitor " \
"where sid = sys_context('userenv', 'sid')" \
"and status = 'EXECUTING'"
self.__verify_attributes(connection, "dbop", "oracledb_dbop", sql)
sql = "select sys_context('userenv', 'action') from dual"
self.__verify_attributes(connection, "action", "oracledb_Action", sql)
sql = "select sys_context('userenv', 'module') from dual"
self.__verify_attributes(connection, "module", "oracledb_Module", sql)
sql = "select sys_context('userenv', 'client_info') from dual"
self.__verify_attributes(connection, "clientinfo", "oracledb_cinfo",
sql)
sql = "select sys_context('userenv', 'client_identifier') from dual"
self.__verify_attributes(connection, "client_identifier",
"oracledb_cid", sql)
def test_1109_parse_password(self):
"1109 - test connecting with password containing / and @ symbols"
connection = test_env.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
sys_random = random.SystemRandom()
chars = list(
sys_random.choice(string.ascii_letters) for i in range(20))
chars[4] = "/"
chars[8] = "@"
new_password = "".join(chars)
connection.changepassword(test_env.get_main_password(), new_password)
try:
arg = "%s/%s@%s" % (test_env.get_main_user(), new_password,
test_env.get_connect_string())
oracledb.connect(arg)
finally:
connection.changepassword(new_password,
test_env.get_main_password())
def test_1130_cancel(self):
"1130 - test connection cancel"
conn = test_env.get_connection()
def perform_cancel():
time.sleep(0.1)
conn.cancel()
thread = threading.Thread(target=perform_cancel)
thread.start()
try:
with conn.cursor() as cursor:
self.assertRaises(oracledb.OperationalError, cursor.callproc,
test_env.get_sleep_proc_name(), [2])
finally:
thread.join()
with conn.cursor() as cursor:
cursor.execute("select user from dual")
user, = cursor.fetchone()
self.assertEqual(user, test_env.get_main_user().upper())
def test_3001_deprecations(self):
"3001 - test to verify deprecations"
connection = test_env.get_connection(threaded=True, events=True)
self.assertRaises(oracledb.ProgrammingError,
connection.subscribe,
ip_address='www.oracle.in',
ipAddress='www.oracle.in')
self.assertRaises(oracledb.ProgrammingError,
connection.subscribe,
grouping_class=1,
groupingClass=1)
self.assertRaises(oracledb.ProgrammingError,
connection.subscribe,
grouping_value=3,
groupingValue=3)
self.assertRaises(oracledb.ProgrammingError,
connection.subscribe,
grouping_type=2,
groupingType=2)
self.assertRaises(oracledb.ProgrammingError,
connection.subscribe,
client_initiated=True,
clientInitiated=True)
def test_2709_visibility_mode_immediate(self):
"2709 - test enqueue visibility option - ENQ_IMMEDIATE"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.visibility = oracledb.ENQ_IMMEDIATE
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = test_env.get_connection()
deq_options = other_connection.deqoptions()
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_ON_COMMIT
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
other_connection.deq(self.book_queue_name, deq_options, props, book)
results = (book.TITLE, book.AUTHORS, book.PRICE)
other_connection.commit()
self.assertEqual(results, self.book_data[0])