def Connect(connectString, rolesToEnable = None):
"""Connect to the database prompting for the password if necessary."""
connection = cx_OracleEx.Connection(GetConnectString(connectString))
if rolesToEnable is not None:
cursor = connection.cursor()
cursor.callproc("dbms_session.set_role", (rolesToEnable,))
return connection
def Process(self, processor):
connection = processor.connection
cursor = connection.cursor()
cursor.execute("select user from dual")
user, = cursor.fetchone()
parser = cx_OracleParser.SimpleParser()
sql = open(self.fileName).read()
connectStatementClass = parser.parser.processor.ConnectStatement
try:
for statement in parser.IterParse(sql, user):
if isinstance(statement, connectStatementClass):
connection = cx_OracleEx.Connection(
statement.user, statement.password
or connection.password, statement.dsn
or connection.dsn)
cursor = connection.cursor()
cx_Logging.Trace("%s", statement.GetLogMessage(cursor))
parser.parser.processor.owner = statement.user
else:
try:
statement.Process(cursor)
except cx_Exceptions.BaseException as error:
lineNumber = statement.lineNumber
if isinstance(error, cx_OracleEx.DatabaseException) \
and error.dbErrorOffset is not None:
offset = error.dbErrorOffset
lineNumber += statement.sql[:offset].count("\n")
cx_Logging.Error("Error at line %s", lineNumber)
if not processor.onErrorContinue:
raise
cx_Logging.Error("%s", error.message)
except cx_OracleParser.ParsingFailed as value:
cx_Logging.Error("Parsing failed at line %s (%s...)",
value.arguments["lineNumber"],
value.arguments["remainingString"][:100])
def Connect(connectString, rolesToEnable=None):
"""Connect to the database prompting for the password if necessary."""
pos = connectString.find(" as sysdba")
if pos < 0:
pos = connectString.find(" as sysoper")
if pos < 0:
mode = 0
else:
mode = cx_Oracle.SYSOPER
connectString = connectString[:pos]
else:
mode = cx_Oracle.SYSDBA
connectString = connectString[:pos]
connectString = GetConnectString(connectString)
connection = cx_OracleEx.Connection(connectString, mode=mode)
if rolesToEnable is not None:
cursor = connection.cursor()
cursor.callproc("dbms_session.set_role", (rolesToEnable, ))
connection.trimMessage = False
return connection
def RecompileInvalidObjects(connection,
includeSchemas,
excludeSchemas=[],
raiseError=True,
logPrefix="",
connectAsOwner=False):
"""Recompile all invalid objects in the schemas requested."""
# determine whether or not to use dba views or not
if len(includeSchemas) == 1 and not excludeSchemas \
and connection.username.upper() == includeSchemas[0]:
singleSchema = True
viewPrefix = "all"
else:
singleSchema = False
viewPrefix = "dba"
# prepare a cursor to determine if object is still invalid
invalidCursor = connection.cursor()
invalidCursor.prepare("""
select count(*)
from %s_objects
where owner = :owner
and object_name = :name
and object_type = :type
and status = 'INVALID'""" % viewPrefix)
invalidCursor.setinputsizes(owner=connection.STRING,
name=connection.STRING,
type=connection.STRING)
# prepare a cursor to determine the errors for stored source
errorsCursor = PrepareErrorsCursor(connection, viewPrefix)
# fetch all of the invalid objects
numErrors = 0
numCompiled = 0
compileCursor = connection.cursor()
cursor = connection.cursor()
cursor.arraysize = 25
cursor.execute("""
select
owner,
object_name,
object_type
from %s_objects
where status = 'INVALID'
and object_type != 'UNDEFINED'
order by owner""" % viewPrefix)
for owner, name, type in cursor.fetchall():
# ignore if this schema should be ignored
if includeSchemas and owner not in includeSchemas:
continue
if excludeSchemas and owner in excludeSchemas:
continue
# ignore if prior compiles have made this object valid
invalidCursor.execute(None, owner=owner, name=name, type=type)
invalid, = invalidCursor.fetchone()
if not invalid:
continue
# perform compile
numCompiled += 1
if singleSchema:
compileName = name
else:
compileName = "%s.%s" % (owner, name)
cx_Logging.Trace("%sCompiling %s (%s)...", logPrefix, compileName,
type)
parts = type.lower().split()
statement = "alter " + parts[0] + " " + compileName + " compile"
if len(parts) > 1:
statement += " " + parts[1]
if connectAsOwner and connection.username.upper() != owner:
connection = cx_OracleEx.Connection(owner, connection.password,
connection.dsn)
compileCursor = connection.cursor()
compileCursor.execute(statement)
try:
CheckForErrors(errorsCursor,
owner,
name,
type,
"has",
logPrefix=logPrefix)
except:
if raiseError:
raise
numErrors += 1
# all done
if numErrors:
cx_Logging.Trace("%sAll objects compiled: %s error(s).", logPrefix,
numErrors)
elif numCompiled:
cx_Logging.Trace("%sAll objects compiled successfully.", logPrefix)
else:
cx_Logging.Trace("%sNo invalid objects to compile.", logPrefix)
def RecompileInvalidObjects(connection, includeSchemas, excludeSchemas,
password, raiseError):
"""Recompile all invalid objects in the schemas requested."""
# determine whether or not to use dba views or not
if len(includeSchemas) == 1 and not excludeSchemas \
and connection.username.upper() == includeSchemas[0]:
prevOwner = includeSchemas[0]
viewPrefix = "all"
compileConnection = connection
compileCursor = connection.cursor()
else:
prevOwner = None
viewPrefix = "dba"
# prepare a cursor to determine if object is still invalid
invalidCursor = connection.cursor()
invalidCursor.prepare("""
select count(*)
from %s_objects
where owner = :owner
and object_name = :name
and object_type = :type
and status = 'INVALID'""" % viewPrefix)
invalidCursor.setinputsizes(owner = connection.STRING,
name = connection.STRING, type = connection.STRING)
# prepare a cursor to determine the errors for stored source
errorsCursor = PrepareErrorsCursor(connection, viewPrefix)
# fetch all of the invalid objects
numErrors = 0
numCompiled = 0
cursor = connection.cursor()
cursor.arraysize = 25
cursor.execute("""
select
owner,
object_name,
object_type
from %s_objects
where status = 'INVALID'
and object_type != 'UNDEFINED'
order by owner""" % viewPrefix)
for owner, name, type in cursor.fetchall():
# ignore if this schema should be ignored
if includeSchemas and owner not in includeSchemas:
continue
if excludeSchemas and owner in excludeSchemas:
continue
# ignore if prior compiles have made this object valid
invalidCursor.execute(None,
owner = owner,
name = name,
type = type)
invalid, = invalidCursor.fetchone()
if not invalid:
continue
# switch schemas, if applicable
if owner != prevOwner:
prevOwner = owner
print >> sys.stderr, "Connecting to", owner
sys.stderr.flush()
compileConnection = cx_OracleEx.Connection(owner, password,
connection.dsn)
compileCursor = compileConnection.cursor()
# perform compile
numCompiled += 1
print >> sys.stderr, " Compiling", name.lower(), "(" + type + ")..."
sys.stderr.flush()
parts = type.lower().split()
statement = "alter " + parts[0] + " " + name.lower() + " compile"
if len(parts) > 1:
statement += " " + parts[1]
compileCursor.execute(statement)
try:
CheckForErrors(errorsCursor, owner, name, type, "has")
except:
if raiseError:
raise
numErrors += 1
# all done
if numErrors:
print >> sys.stderr, "All objects compiled:", numErrors, "error(s)."
elif numCompiled:
print >> sys.stderr, "All objects compiled successfully."
else:
print >> sys.stderr, "No invalid objects to compile."
sys.stderr.flush()