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())
#
# 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.")
# 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()),
"actual value is:", repr(connection.edition))
cursor.execute("""
create or replace function TestEditions return varchar2 as
begin
return 'Edition 1 Procedure';
end;""")
result = cursor.callfunc("TestEditions", str)
print("Function should return 'Edition 1 Procedure', actually returns:",
repr(result))
# next, change the edition back to the base edition and demonstrate that the
# original function is being called
cursor.execute("alter session set edition = ORA$BASE")
result = cursor.callfunc("TestEditions", str)