DB Connect issue

[vpantangi]

I have been given this query to get data into dbconnect, it works perfectly fine for batch, but i want to run and get results for rising column by using EVENT_TIME from January 1st 2020. what should be added to the below query, i tried adding AND EVENT_TIME > ?
ORDER BY EVENT_TIME DESC and it gives java.sql.SQLDataException: ORA-01861: literal does not match format string. If i change EVENT_TIME to EVENT_NAME it works but i want rising column value for event time . Please help 

SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
)

[jakubzak]

I've been facing exactly the same issue reading data from 1 of 2 DB views (Ora 19). One was working OK, second one was having this issue. The only difference was a position of EVENT_TIMESTAMP column in view definition. One that was working OK had rising column (date) as first, second one had it as last column in view definition. I've redefined second view (moved EVENT_TIMESTAMP as first column) and error disappeared.

Full listing of columns tested at first without any success. 

[somesoni2]

Give this a try

SELECT * FROM (SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
sa.event_time AS rising_column
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
)) ev WHERE rising_column > ?

[vpantangi]

Same error, i noticed something new when pasting the queries you sent, previously i was able to select values in rising column section, now it shows no results.

[somesoni2]

Give this a try

SELECT
sa.main_location AS main_location,
sa.sub_location AS sub_location,
sa.event_name AS event_name,
sa.event_type AS event_type,
to_char (sa.event_time, 'mm/dd/yyyy hh24:mi:ss') AS event_time,
sa.entity_type AS entity_type,
su.sys_user_id AS rac
sa.event_time AS rising_column
FROM jiva.security_audit_info sa
LEFT JOIN jiva.sys_user su ON su.user_idn = sa.user_idn
LEFT JOIN jiva.entity e ON e.entity_idn = su.entity_idn
WHERE sa.event_name IN (
'user_login',
'user_logout'
) WHERE rising_column > ?

You had formatted EVENT_TIME value to string and that's the reason oracle was having hard time casting it to DATETIME /TIMESTAMP for rising column comparison. Create a new column to be used  for that.

[vpantangi]

it gives this error "java.sql.SQLException: Missing IN or OUT parameter at index:: 1"