How to query for rows inserted after a certain date

I'm working with Python2, Oracle10g, and cxOracle.

In my Python code, I have a certain datetime, like this:

last_check_timestamp = datetime.today() - timedelta(hours = 1)
print last_check_timestamp
>>> last_check_timestamp = 2016-03-14 11:13:06.743626
print type(last_check_timestamp)
>>> type(last_check_timestamp) = <type 'datetime.datetime'>

I want to run a query that retrieves all the rows inserted after said datetime. This is how I'm trying to do it:

    query_string = ("SELECT * FROM {0} "
                    "WHERE SCN_TO_TIMESTAMP(ORA_ROWSCN) >= '{1}'".\
                    format(db_name, self.last_check_timestamp.timetuple()))
    logger.info("query_string = \n%s\n" % query_string)

So the resulting query string that is received by Oracle is this:

SELECT * FROM SYS.DUAL
WHERE SCN_TO_TIMESTAMP(ORA_ROWSCN) >= 'time.struct_time(tm_year=2016, tm_mon=3, tm_mday=14, tm_hour=11, tm_min=23, tm_sec=26, tm_wday=0, tm_yday=74, tm_isdst=-1)'

And this is the error I'm getting:

cx_Oracle.DatabaseError: ORA-01858: a non-numeric character was found where a numeric was expected

How should this date comparison be done in order for Oracle to acept the query?

Answers


Unless the rowdependencies is enabled the last SCN is stored at data block level not on row level. In order to have the SCN number for a row level you need to on the rowdependencies for a table.

select * from tablename where SCN_TO_TIMESTAMP(ORA_ROWSCN)='14-MAR-16 02.01.05.000000000 PM';

I believe you are passing the timestamp in incorrect format to oracle as time.struct_time is not an oracle function

SELECT * FROM SYS.DUAL
WHERE SCN_TO_TIMESTAMP(ORA_ROWSCN) >= 'time.struct_time(tm_year=2016, tm_mon=3, tm_mday=14, tm_hour=11, tm_min=23, tm_sec=26, tm_wday=0, tm_yday=74, tm_isdst=-1)'

Need Your Help

kaminari pagination ajax call renders to wrong controller

ruby-on-rails ajax rails-routing kaminari

First we start off with conversations/index.html.haml to create a message

How can I scan foreign characters (like ä, ß, ő, ű) and print them out to the console window as well?

c++ windows unicode getline

Here is my simplified code. I would like to scan the characters typed in by the user and print them in the following line. Obviously there are some problems arising with non-English characters. In ...