Oracle SQLPlus selecting/converting to CLOB - ORA-00932: inconsistent datatypes: expected - got CLOB

is ORDER_NARRATIVE.NARRATIVE  a CLOB or a VARCHAR2?

>>ORA-00932: inconsistent datatypes: expected - got CLOB

I'm unable to reproduce this error with 10.2.0.3 and sqlplus.

Can you post your version?

Now if I make narative a varchar2(4000) and max it out, I can get:
ORA-01489: result of string concatenation is too long

If I mess around with a narrative as a clob, or reduce the length of the string I build to < 4000, it runs fine for me.

Here is my test case:

drop table tab1 purge;
create table tab1(line number, narrative varchar2(4000));

insert into tab1 values(1,'This is line 1');
insert into tab1 values(2,'This is line 2');
insert into tab1 values(3,null);
commit;


--make a long string for line 3
declare
	myclob clob;
begin
	dbms_lob.createtemporary(myclob,TRUE);
	dbms_lob.open(myclob,dbms_lob.lob_readwrite);
	for i in 1..4000 loop
		dbms_lob.writeappend(myclob,1,'a');
	end loop;
	dbms_lob.close(myclob);
	update tab1 set narrative=myclob where line=3;

	dbms_lob.freetemporary(myclob);
end;
/

commit;


select
  RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", NARRATIVE||chr(32)) order by LINE), '/x/text()').getclobval(),',')
from tab1;

select dbms_lob.getlength(narrative) from tab1;

Select allOpen in new window

ORDER_NARRATIVE.NARRATIVE  is varchar2(1000)

The challenge is to take the string data and convert it to CLOB in the sql statement to display.  I was thinking I might could use a virtual column.  Not even sure it can be done.  Trying to keep it as simple as I can vs using staging tables to update.

which row are you going to update?


let's say you have


1 Line 1
2 Line 2
3 Line 3

which row will become Line 1 Line 2 Line 3?

all of them? one of them? the first? the last? random?

Line 1 probably.  So, what I currently do, is update each row into the CLOB in LINE order of all NARRATIVE data.  Takes a ton of time and I only need the one CLOB value for each ACCESSION_NUM so that is why I was hoping to eliminate the update in a staging table.

This was my test case, worked for me.


CREATE TABLE order_narrative
AS
    SELECT name v_accession_num, line, text narrative
      FROM user_source us
     WHERE TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY');

ALTER TABLE order_narrative ADD (column_clob CLOB);

SELECT   v_accession_num,
         RTRIM(
             EXTRACT(
                 XMLAGG(XMLELEMENT("x", order_narrative.narrative || CHR(32))
                        ORDER BY order_narrative.line
                       ),
                 '/x/text()').getclobval(),
             ',')
    FROM order_narrative
GROUP BY v_accession_num;


UPDATE order_narrative a
   SET column_clob      =
           (SELECT RTRIM(
                       EXTRACT(XMLAGG(XMLELEMENT("x", b.narrative || CHR(32)) ORDER BY b.line),
                               '/x/text()'
                              ).getclobval(),
                       ',')
              FROM order_narrative b
             WHERE a.v_accession_num = b.v_accession_num)
 WHERE line = 1;

SELECT   v_accession_num, line, column_clob
    FROM order_narrative
ORDER BY v_accession_num, line;


SELECT DBMS_LOB.getlength(column_clob)
  FROM order_narrative
 WHERE line = 1;


Results of the final query...

your results will be different of course, I'm simply showing that it did concatenate into a CLOB, not a 4K varchar2 or smaller

This test on 11.2.0.1

1	267
2	567
3	454
4	6065
5	162
6	5555
7	594
8	143
9	157
10	396
11	13070
12	502
13	96
14	301
15	755
16	8088
17	5640
18	135
19	164
20	179
21	45770
22	45991
23	191
24	26746
25	4388
26	9337
27	5337
28	33574
29	23864
30	87769
31	4046
32	12008
33	19418
34	57813
35	27880
36	161
37	364
38	4148
39	154
40	6379
41	244
42	4865
43	4851
44	282
45	81
46	54261
47	93
48	8022
49	592
50	6044
51	650
52	257
53	91
54	259
55	221
56	149
57	347
58	473
59	348
60	5650
61	717
62	11609
63	475
64	198
65	236
66	80
67	235
68	220
69	338
70	452
71	4664
72	527
73	134
74	5947
75	356
76	106
77	61393
78	648
79	155
80	105
81	139
82	155
83	156
84	156
85	126
86	529
87	129
88	8524
89	650
90	9876
91	638
92	714
93	4633
94	209
95	329
96	9265
97	191
98	223
99	72
100	18915
101	48112
102	407
103	41372
104	194
105	143
106	3669
107	251
108	2818
109	880
110	2960
111	637
112	2257
113	563
114	991
115	1038
116	2861
117	793
118	723
119	1039
120	1459
121	1072
122	480
123	961
124	3946
125	865
126	300
127	109
128	2143
129	1160
130	37608
131	874
132	952
133	3302
134	1297
135	715
136	426
137	725
138	3905
139	876
140	479
141	484
142	497
143	915
144	863
145	1977
146	795
147	3868
148	606
149	3834
150	1224
151	1618
152	1451
153	959
154	2835
155	688
156	656
157	1632
158	156
159	246
160	201
161	2897
162	2446
163	1242
164	2913
165	577
166	140
167	1214
168	428
169	362
170	2761
171	3573
172	809
173	113
174	200
175	366
176	1172
177	2848
178	620
179	2743
180	3089
181	871
182	1070
183	856
184	1349
185	910

Select allOpen in new window

What version of Oracle are you testing?  Getting new error with your code.
ORA-31061: XDB error: special char to escaped char conversion failed.

11.2.0.1

Interesting.  Me, too.

Sure - will do but will be tomorrow.  Thanks so much for your help.

Testing shows that when I only select from a few columns, few tables it works as you suggested.  When I add it to my larger sql selecting from 15+ tables with joins, etc., I get this error:

         RTRIM(
         *
ERROR at line 34:
ORA-00932: inconsistent datatypes: expected - got CLOB

I will continue to play with it and see if I can get it to work.
Thanks so much for your help.

Narrow down the tables/columns it is failing on and see if you can post a test case that reproduces the error so we can see what is happening and run it on our end.

diannagibbs


please explain your close - the accepted answer is just a series of questions.