Been working on an application and needed to add comments which have been added to a service call. First few times I tried were unmitigated failure. So I backed up and worked out an idea to put the comments in a temp table and the rest of the data into another temp table (which will have data filtering in from outside our systems) and combine them into the evaluation table. That table is scanned to determine whether or not the call is emailed out to supervisors.
The comments are a clob collected from a variation of a function found through here and as long as it stays in the temp table, everything is fine. When the query tries to move that clob into the corresponding evaluation table clob, I get the ORA-00932 error for inconsistent data types: expected - got CLOB. (The destination column is a clob column as well.)
The select statement which I'm using is
INSERT INTO jc_hc_curent(ad_sec, eid, ad_ts, ag_id, tycod, sub_tycod, udts, xdts, estnum, edirpre, efeanme, efeatyp, edirsuf, eapt, ccity, unit_count, comments) SELECT cu.ad_sec, cu.eid, cu.ad_ts, cu.ag_id, cu.tycod, cu.sub_tycod, cu.udts, cu.xdts, cu.estnum, cu.edirpre, cu.efeanme, cu.efeatyp, cu.edirsuf, cu.eapt, cu.ccity, COUNT(cu.unid), cl.comments FROM hc_curent_temp cu JOIN hc_clob_temp cl ON cu.eid = cl.eid GROUP BY cu.ad_sec, cu.eid, cu.ad_ts, cu.ag_id, cu.tycod, cu.sub_tycod, cu.udts, cu.xdts, cu.estnum, cu.edirpre, cu.efeanme, cu.efeatyp, cu.edirsuf, cu.eapt, cu.ccity, cl.comments;
The hc_clob_temp.comments is a CLOB, as is the jc_hc_curent.comments into which it is going. the two ad_sec and eid columns are NUMBER(10,0) and the rest are VARCHAR2 of varying sizes.
So what am I missing and what have I done wrong here....?
Let me know if there's anything else you need to visualize the issue.
Thanks in advance,
Tony