Hi,
Help to convert the CLOB column to date . Oracle 12C.
create table test1(id number, comments clob);
create table test2(gid number, id number, app_date date);
insert into test1 values(1,'22-MAY-2021');
insert into test1 values(2,'20-MAY-2021');
insert into test1 values(3,'01-MAY-2021');
insert into test2 values(1,1,'09-MAY-2021');
insert into test2 values(2,2,'20-MAY-2021');
insert into test2 values(3,3,'08-MAY-2021');
SELECT t1.id,T1.COMMENTS,T2.APP_DATE
FROM TEST1 T1,
TEST2 T2
WHERE T1.ID = T2.ID
AND T1.COMMENTS <> T2.APP_DATE;
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 13 Column: 5
Expected Output:-
I'm aware that it can be achieved through a PLSQL block . But all i need here is to convert the CLOB to DATE in the SQL query itself.
Also please note , i dont have access to certain DBMS packages due to some business restrictions.