def drop_samples(conn):
    print("Dropping sample schemas and edition...")
    sample_env.run_sql_script(conn,
                              "drop_samples",
                              main_user=sample_env.get_main_user(),
                              edition_user=sample_env.get_edition_user(),
                              edition_name=sample_env.get_edition_name())
#
# The primary advantage to this approach over the equivalent approach shown in
# SessionCallback.py is when DRCP is used, as the callback is invoked on the
# server and no round trip is required to set state.
#
# This script requires cx_Oracle 7.1 or higher.
#
# Also see SessionCallback.py
#
#------------------------------------------------------------------------------

import cx_Oracle
import sample_env

# create pool with session callback defined
pool = cx_Oracle.SessionPool(user=sample_env.get_main_user(),
                             password=sample_env.get_main_password(),
                             dsn=sample_env.get_connect_string(),
                             min=2,
                             max=5,
                             increment=1,
                             threaded=True,
                             sessionCallback="pkg_SessionCallback.TheCallback")

# truncate table logging calls to PL/SQL session callback
with pool.acquire() as conn:
    cursor = conn.cursor()
    cursor.execute("truncate table PLSQLSessionCallbacks")

# acquire session without specifying a tag; the callback will not be invoked as
# a result and no session state will be changed
# performs a database sleep while another performs a query. A more typical
# application might be a web service that handles requests from multiple users.
# Note only one operation (such as an execute or fetch) can take place at a time
# on each connection.
#
# Also see session_callback.py.
#
#------------------------------------------------------------------------------

import threading

import cx_Oracle as oracledb
import sample_env

# Create a Connection Pool
pool = oracledb.SessionPool(user=sample_env.get_main_user(),
                            password=sample_env.get_main_password(),
                            dsn=sample_env.get_connect_string(),
                            min=2,
                            max=5,
                            increment=1)


def the_long_query():
    with pool.acquire() as conn:
        cursor = conn.cursor()
        cursor.arraysize = 25000
        print("the_long_query(): beginning execute...")
        cursor.execute("""
                select *
                from
#------------------------------------------------------------------------------
# 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.
#------------------------------------------------------------------------------

import cx_Oracle

import sample_env
import DropSamples

# connect as administrative user (usually SYSTEM or ADMIN)
conn = cx_Oracle.connect(sample_env.get_admin_connect_string())

# drop existing users and editions, if applicable
DropSamples.drop_samples(conn)

# create sample schema and edition
print("Creating sample schemas and edition...")
sample_env.run_sql_script(conn,
                          "SetupSamples",
                          main_user=sample_env.get_main_user(),
                          main_password=sample_env.get_main_password(),
                          edition_user=sample_env.get_edition_user(),
                          edition_password=sample_env.get_edition_password(),
                          edition_name=sample_env.get_edition_name())
print("Done.")