Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Query - to convert CLOB to DATE

LazarAug 18 2021 — edited Aug 18 2021

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:-
image.png
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.

Comments

Post Details

Added on Aug 18 2021
8 comments
2,256 views