topic Re: DB Connect issue in Getting Data In

DB Connect issue

vpantangi — Wed, 09 Dec 2020 22:12:13 GMT

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

Re: DB Connect issue

somesoni2 — Wed, 09 Dec 2020 22:35:39 GMT

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.

Re: DB Connect issue

vpantangi — Wed, 09 Dec 2020 22:39:22 GMT

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

Re: DB Connect issue

somesoni2 — Wed, 09 Dec 2020 23:19:36 GMT

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 > ?

Re: DB Connect issue

vpantangi — Wed, 09 Dec 2020 23:24:17 GMT

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.

Re: DB Connect issue

jakubzak — Thu, 29 Apr 2021 05:50:01 GMT

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.