Solved

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

Posted on 2013-02-01
14
5,562 Views
Last Modified: 2013-04-04
Thanks to guru's on this list, my update is working as expected.  However I've decided to see if I can get it to work without using a staging table.

So, in summary, I have a table with a column for each line number and then a narrative for each line.

My goal is to load the narrative into one CLOB column.

LINE      NARRATIVE

1         This is line 1
2         This is line 2
3         This is line 3
etc.



with goal for CLOB column containing:

This is line 1 This is line 2 This is line 3 etc.




I can update table fine with this SQL:

update table a
  SET COLUMN_CLOB=(select RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", ON_NARRATIVE||chr(32)) order by ON_LINE), '/x/text()').getclobval(),',')
                                                 from table b
                                                where a.V_ACCESSION_NUM = b.V_ACCESSION_NUM
                                                )
/





But I want to try to get it to display without first loading into a staging table and updating:



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



ERROR at line 33:
ORA-00932: inconsistent datatypes: expected - got CLOB




I've tried to_char, TO_CLOB, DBMS_LOG.SUBSTR but I can't get it to work.

Any ideas if this is doable?
0
Comment
Question by:diannagibbs
  • 6
  • 5
  • 3
14 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
is ORDER_NARRATIVE.NARRATIVE  a CLOB or a VARCHAR2?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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;

Open in new window

0
 

Author Comment

by:diannagibbs
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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?
0
 

Author Comment

by:diannagibbs
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

Open in new window

0
 

Author Comment

by:diannagibbs
Comment Utility
What version of Oracle are you testing?  Getting new error with your code.
ORA-31061: XDB error: special char to escaped char conversion failed.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
11.2.0.1
0
 

Author Comment

by:diannagibbs
Comment Utility
Interesting.  Me, too.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I don't see a response when I asked what version of Oracle you are running (all 4 numbers please).

I'm not able to reproduce the error you originally posted.

Is your test case a little to simplistic?

Can you run the test I posted (the exact test I posted will error but change the loop to something less thant 4000 and it should run).

Can you post a slightly more advanced test case or a little more about your existing setup?
0
 

Author Comment

by:diannagibbs
Comment Utility
Sure - will do but will be tomorrow.  Thanks so much for your help.
0
 

Author Comment

by:diannagibbs
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
diannagibbs


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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now