CODEWITHSUNDEEP
sqloracle-databasesqldatatypes
ssnone
ssnone

Reputation: 65

How to convert long data to char in oracle || ORA-00932: inconsistent datatypes: expected CHAR got LONG

I am trying to execute below query which fetches different data on the basis of few criteria. Here i have used like operator on cookies fields and my cookies field is of long datatype. 

select * from (
               select to_char(audit_logs.REQUEST_TIME, 'dd/mm/yyyy HH24:mm:ss') as event_time,
                    audit_logs.CLIENT_REQUEST as client_request,
                    audit_logs.RESPONSE_CODE as responsecode,
                    audit_logs.SIZEOFOBJECT as sizeofobject,
                    audit_logs.COOKIES as cookies,
                    audit_logs.ENV as env,
                    audit_logs.USERID as user_id,
                    audit_logs.POST_DATA as post_data,
                    audit_logs.AUTHSCHEME as authscheme,
                    audit_logs.AUTHMARKET as authmarket,
                    audit_logs.X_REQUESTED_WITH as x_requested_with,
                    audit_logs.METHOD_TYPE as method_type,
                    audit_logs.HOSTNAME as hostname,
                    audit_logs.SESSION_COOKIES as session_cookies,
                    audit_logs.X_FORWARDED_FOR as x_forwarded_for,
                    audit_logs.USER_AGENT as user_agent,
                    audit_logs.GEOIP_COUNTRY_CODE as country_code
               from audit_logs
               where
                    REQUEST_TIME >= to_date(:fromdate,'dd/mm/yyyy')
                    and REQUEST_TIME <= to_date(:todate,'dd/mm/yyyy HH24:MI:SS')
                    and COOKIES LIKE '%TANID=%'
               order by REQUEST_TIME DESC)
where ROWNUM <= :recordslimit
order by event_time DESC;

While executing above query i am getting below error:

ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Can someone please how to resolve that.

Note: Changing the datatype will be a time taking job, since my database is having lots of data.

Upvotes: 1

Views: 4723

Answers (1)

Ari Manninen
Ari Manninen

Reputation: 528

You are defining three named parameters in the query - fromdate, todate and recordslimit. The query uses to_date on fromdate and todate parameters so they should be of char datatype. So, please check what actual datatypes you are using when passing the actual values on these parameters. There may be other catches too but this is the only one I can think of.

Upvotes: 1

Related Questions