Forum Stats

  • 3,769,349 Users
  • 2,252,952 Discussions
  • 7,874,993 Comments

Discussions

SQL Query - to convert CLOB to DATE

Lazar
Lazar Member Posts: 190 Blue Ribbon
edited Aug 18, 2021 5:51AM in SQL & PL/SQL

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.

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    On Oracle 19 the following select works OK:

    select t.*, to_date(comments default null on conversion error,'dd-mon-yyyy') x_date

    from test1 t

    ;

    In Oracle 12c you don't have the facility of giving a default value on conversion error, so, in case you don't have the proper date format, the select below will fail:

    select t.*, to_date(comments,'dd-mon-yyyy') x_date

    from test1 t

    ;

    Just insert the select using this to_date and it should work if you have the proper date format in each of the rows. But that's a very iffish question... Are you sure each row will have the proper date in that CLOB column. If not, then the only thing I could think of would be to try to insert row by row:

    declare

    v_date date;

    begin

    for r in (select t.* from test 1) loop

    begin

    v_date:=to_date(r.comments,'dd-mon-yyyy');

    exception

    when others then

    --- how to handle that error if not by saying it is null date???

    v_date:=null;

    end;

    insert into test3 (id, app_date) values(r.id,v_date);

    end loop;

    commit;

    end;


    And then use the test3 table instead of the one having the CLOB.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond


    The first question should be... Why have you got dates stored in a CLOB in the first place?

    There's clearly been some issue with the design somewhere. Best solution is to fix that at source and ensure the dates are stored correctly in a DATE datatype in the first place.

    BEDEFrank Kulash
  • User_H3J7U
    User_H3J7U Member Posts: 675 Silver Trophy

    To avoid conversion errors - to_char. But to_date is more flexible for date variations.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    Good point. Yet in a CLOB one may have anything, including date with a different date format...

    So, like Blue Shadow said, best fix the design issue so as to avoid having such a thing become every ay practice. If it's to be a one time only data processing, then it is admissible.

    And one more thing for the beginners: never ever rely on implicit conversions.

    Then, of course to_char for date or number will always work, but sometimes it would be it is better to convert to number or possibly date if there is an index on that column.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    HI, @Lazar

    create table test2(gid number, id number, app_date date);

    ...

    insert into test2 values(1,1,'09-MAY-2021');

    Don't try to insert a string value (such as '09-MAY-2021') into a DATE column (such as app_date). Use TO_DATE or DATE literals instead. Trying to insert a string into a DATE column is an example of an implicit conversion which Bede warned about. It might not cause an error today - it might even do what you want - but the exact same code may not work tomorrrow.

    BEDE
  • BEDE
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy

    That depends on the setting of nls_date_format. That may vary from one database to another and it may also very from one session to another. You should not rely that everyone has the same nls_date_format. An alternative to using format masks is alter session set nls_date_format='dd-mon-yyyy' before running that code.

  • User_44U40
    User_44U40 Member Posts: 1 Green Ribbon

    Following sql works fine I tested it and it gave the output

    SELECT t1.id,T1.COMMENTS,T2.APP_DATE


    FROM TEST1 T1,


     TEST2 T2


    WHERE T1.ID = T2.ID


    AND to_date(T1.COMMENTS) <> T2.APP_DATE;


    Result Set 4


    IDCOMMENTSAPP_DATE122-MAY-202109-MAY-21301-MAY-202108-MAY-21Download CSV

    2 rows selected.

  • User_H3J7U
    User_H3J7U Member Posts: 675 Silver Trophy

    @User_44U40

    Following sql works fine I tested it and it gave the output

    to_date has already been mentioned with cautions that you have ignored.

    cormaco