|
| 1 | +#------------------------------------------------------------------------------ |
| 2 | +# Editioning.py |
| 3 | +# This script demonstrates the use of editioning, available in Oracle |
| 4 | +# Database 11.2 and higher. See the Oracle documentation on the subject for |
| 5 | +# additional information. This script makes use of SYSDBA but can be adjusted |
| 6 | +# to make use of any user that has the ability to create users. It creates the |
| 7 | +# user and edition noted in the constants at the top of the script. |
| 8 | +#------------------------------------------------------------------------------ |
| 9 | + |
| 10 | +from __future__ import print_function |
| 11 | + |
| 12 | +import cx_Oracle |
| 13 | + |
| 14 | +# define constants used throughout the script; adjust as desired |
| 15 | +USER_NAME = "CX_ORACLE_TESTEDITIONS" |
| 16 | +PASSWORD = "dev" |
| 17 | +EDITION_NAME = "CX_ORACLE_E1" |
| 18 | + |
| 19 | +# create user dropping it first, if necessary |
| 20 | +connection = cx_Oracle.Connection(mode = cx_Oracle.SYSDBA) |
| 21 | +cursor = connection.cursor() |
| 22 | +cursor.execute(""" |
| 23 | + select username |
| 24 | + from dba_users |
| 25 | + where username = :name""", |
| 26 | + name = USER_NAME) |
| 27 | +names = [n for n, in cursor] |
| 28 | +for name in names: |
| 29 | + print("Dropping user", name) |
| 30 | + cursor.execute("drop user %s cascade" % name) |
| 31 | +print("Creating user", USER_NAME) |
| 32 | +cursor.execute("create user %s identified by %s" % (USER_NAME, PASSWORD)) |
| 33 | +cursor.execute("grant create session, create procedure to %s" % USER_NAME) |
| 34 | +cursor.execute("alter user %s enable editions" % USER_NAME) |
| 35 | + |
| 36 | +# create edition, dropping it first, if necessary |
| 37 | +cursor.execute(""" |
| 38 | + select edition_name |
| 39 | + from dba_editions |
| 40 | + where edition_name = :name""", |
| 41 | + name = EDITION_NAME) |
| 42 | +names = [n for n, in cursor] |
| 43 | +for name in names: |
| 44 | + print("Dropping edition", name) |
| 45 | + cursor.execute("drop edition %s" % name) |
| 46 | +print("Creating edition", EDITION_NAME) |
| 47 | +cursor.execute("create edition %s" % EDITION_NAME) |
| 48 | +cursor.execute("grant use on edition %s to %s" % (EDITION_NAME, USER_NAME)) |
| 49 | + |
| 50 | +# now connect to the newly created user and create a procedure |
| 51 | +connectString = "%s/%s" % (USER_NAME, PASSWORD) |
| 52 | +connection = cx_Oracle.Connection(connectString) |
| 53 | +print("Edition should be None at this point, actual value is", |
| 54 | + connection.edition) |
| 55 | +cursor = connection.cursor() |
| 56 | +cursor.execute(""" |
| 57 | + create or replace function TestEditions return varchar2 as |
| 58 | + begin |
| 59 | + return 'Base Edition'; |
| 60 | + end;""") |
| 61 | +result = cursor.callfunc("TestEditions", str) |
| 62 | +print("Function call should return Base Edition, actually returns", result) |
| 63 | + |
| 64 | +# next, change the edition and recreate the procedure in the new edition |
| 65 | +cursor.execute("alter session set edition = %s" % EDITION_NAME) |
| 66 | +print("Edition should be %s at this point, actual value is" % EDITION_NAME, |
| 67 | + connection.edition) |
| 68 | +cursor.execute(""" |
| 69 | + create or replace function TestEditions return varchar2 as |
| 70 | + begin |
| 71 | + return 'Edition 1'; |
| 72 | + end;""") |
| 73 | +result = cursor.callfunc("TestEditions", str) |
| 74 | +print("Function call should return Edition 1, actually returns", result) |
| 75 | + |
| 76 | +# next, change the edition back to the base edition and demonstrate that the |
| 77 | +# original function is being called |
| 78 | +cursor.execute("alter session set edition = ORA$BASE") |
| 79 | +result = cursor.callfunc("TestEditions", str) |
| 80 | +print("Function call should return Base Edition, actually returns", result) |
| 81 | + |
| 82 | +# the edition can be set upon connection |
| 83 | +connection = cx_Oracle.Connection(connectString, edition = EDITION_NAME) |
| 84 | +cursor = connection.cursor() |
| 85 | +result = cursor.callfunc("TestEditions", str) |
| 86 | +print("Function call should return Edition 1, actually returns", result) |
| 87 | + |
| 88 | +# it can also be set via the environment variable ORA_EDITION |
| 89 | +os.environ["ORA_EDITION"] = EDITION_NAME |
| 90 | +connection = cx_Oracle.Connection(connectString) |
| 91 | +print("Edition should be %s at this point, actual value is" % EDITION_NAME, |
| 92 | + connection.edition) |
| 93 | +cursor = connection.cursor() |
| 94 | +result = cursor.callfunc("TestEditions", str) |
| 95 | +print("Function call should return Edition 1, actually returns", result) |
| 96 | + |
0 commit comments