Forum OpenACS Development: 'ORA-00932: inconsistent datatypes' error on Oracle 8.1.7.0.1

I have a slight problem with the following query and, in particular, the "substr" and "length" functions. Some help solving it would be great. Constructive criticism on the query in general is also welcome, of course.

SQL> @test-query-oracle.sql
  1	 select   published.curriculum_id,
  2		  published.name as curriculum_name,
  3		  substr(published.description,1,11) as curriculum_desc,
  4		  case when length(published.description) > 11
  5		       then 1 else 0 end as curr_desc_trunc_p,
  6		  case when ucm.curriculum_id is null
  7		       then 0 else 1 end as undesired_p,
  8		  cee.element_id,
  9		  cee.name as element_name,
 10		  substr(cee.description,1,11) as element_desc,
 11		  case when length(cee.description) > 11
 12		       then 1 else 0 end as elem_desc_trunc_p,
 13		  cee.url
 14	 from	  (select   cc.*
 15		   from     cu_curriculums cc,
 16			    workflow_cases cas,
 17			    workflow_case_fsm cfsm
 18		   where    cc.package_id = 434
 19		   and	    cas.object_id = cc.curriculum_id
 20		   and	    cfsm.case_id = cas.case_id
 21		   and	    cfsm.current_state = 9
 22		  ) published,
 23		  cu_user_curriculum_map ucm,
 24		  cu_elements_enabled cee
 25	 where	  published.package_id = ucm.package_id(+)
 26	 and	  published.curriculum_id = ucm.curriculum_id(+)
 27	 and	  306 = ucm.user_id(+)
 28	 and	  published.curriculum_id = cee.curriculum_id(+)
 29	 order by published.sort_key,
 30*		  cee.sort_key
SQL> /
	     case when length(cee.description) > 11
                              *
ERROR at line 11:
ORA-00932: inconsistent datatypes

PS. when I comment out the "case" and "substr" rows in the select list the query returns the selection I expect it to.
Without the definition of cu_elements_enabled it's hard to see how one can be of much help.  What's the type of "description" in the underlying table I presume is being referenced by cu_elements_enabled which I presume is a VIEW?
The type on element/curriculum description is "clob", but the datatype I use in plsql *.new is "varchar" - I borrowed this concept from the forums package in order to extend the description from being limited to 4K (if I would've used varchar) up to 32K. See this thread for a little discussion about it: https://openacs.org/forums/message-view?message_id=51571

The initial data model is on CVS HEAD, btw:
http://cvs.openacs.org/cvs/openacs-4/packages/curriculum/sql/oracle/curriculum-element-create.sql?rev=1.1&content-type=text/x-cvsweb-markup

It shouldn't come to me as a surprise that clob is inconsistent with varchar, I suppose ...

You are correct that ce_enabled_elements is a view.

Suggestions?

Not sure if I understand the question correctly, but I would try to use the DBMS_LOB package, specifically the DBMS_LOB.GETLENGTH and DBMS_LOB.INSTR functions. Documentation can be found on Oracle OTN.
Thank you, Sebastiano! Those two functions made my test case work like it was supposed to.

The question that remains is whether or not we should be using the clob/varchar "hack" from Forums, or if there are better ideas ...

The hack's fine, you just have to be aware that "clob" and "varchar" aren't compatible operand types, though Oracle's perfectly happy letting you store a "varchar" in a "clob".

Besides everytime I wrestle with CLOBs I become even more appreciative of PG's long varchars!

That's very good, Don.

Just for the record, I have made some comments to this effect in the package create scripts (dunno how comprehensible it is, though):
http://cvs.openacs.org/cvs/openacs-4/packages/curriculum/sql/oracle/curriculum-element-package-create.sql?rev=1.1&content-type=text/x-cvsweb-markup

Whoever said it was easier to develop under Oracle? ... The command: "dbms_lob.instr(cee.description,1,11) as element_desc" returns zero (0)!

I'll have to check one more time that I'm using the correct syntax.

I don't think I've ever used dbms_lob.instr() before so you're on your own.  I can personally attest to the fact that the length function works, though!

Did someone actually say developing in Oracle's easier?  Silly  programmer!  Shame on them! :)

I suppose there's at least one cee.description > 4000 bytes (the limit for
varchar2), so Oracle cannot silently convert anymore. That's why you have to
go for LOB functions.

We should consistently use CLOBs in Oracle in fields where we want to allow
for column values > 4000 bytes (that's less characters with multibyte
characters btw). Oracle stores CLOBs pretty efficiently, i. e. the first 4000
bytes go into the datablock of the corresponding row, the overflow bytes go
into a lob segment. On a quick glance, it's how forums does it...so let's do
it like forums. (Err, the second paragraph might be a little off-topic)

There are no strings that long in the cee.description columns of my test database - only 100 characters or so, at the most.

I have never tested the query with a description type of varchar, but I would imagine everything would've worked fine doing so. The error when using length() and substr() occured, despite only having strings shorter than 4K, just because the column type is CLOB ... I *want* to go with CLOB because, as you say, it has advantages over varchar for big chunks of text  😊

The LOB functions made the error go away, and the query returned the proper number of rows but, unfortunately, dbms_lob.instr() does not return the substring, while substr() does ... on varchars, that is, not clobs.

I've not had time to study dbms_lob.instr() and clobs any further yet, but even if I don't find a solution I still have the possibility to do the truncation of the description in Tcl, although that seens like a worse way to do it.

I felt like clarifying, but I'm afraid I ended up talking in circles. Oh well.

I felt like clarifying my situation, nothing else.

Dirk knows Oracle much better than I do!

Sorry if my last posting came across the wrong way...

/Ola

Ah, Sebastiano led me in the right direction, after all.

The command that one should use in this particular case is dbms_lob.substr() - but it has a little quirk ... the syntax is upside-down compared to the regular substr() that you use for varchar.

If you want to return a string ranging from the first character to the eleventh, this is the synatx for VARCHAR:

substr(cee.description,1,11) as element_desc

For CLOB it goes like this:

dbms_lob.substr(cee.description,11,1) as element_desc

Anyway, thanks to all for giving suggestions!

/Ola