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.

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: http://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?

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 ...

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 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)

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