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()