Forum OpenACS Development: Content Repository problems on Oracle

Our first couple of tests with Assessment on Oracle were a small fiasco due to the content repository and the fact that most upgrade scripts have only been written for PostgreSQL and not for Oracle. Oh, and acs_objects was also incomplete... Either way, Timo can comment more on this and will submit the fixes today. Let me encourage people though to either write upgrade scripts for both databases *at the same time* (if they fail, we realize it on upgrade, if they are not there they upgrade works but the package is broken), or make the change only on HEAD.

Be it as it is, we are now facing a different problem, which we might fix by using varchar(4000) instead of LOB, but maybe this is rooted somewhere deeper and anyone else has a clue:

When calling as:assessment::new, which is essentially a call for content::revision::new, we get the following error:

nsoracle.c:3904:ora_tcl_command: error in `OCIStmtExecute ()': ORA-25008: no
implicit conversion to LOB datatype in instead-of trigger

SQL: insert into as_assessmentsi
(revision_id, object_type, creation_user, creation_date,
creation_ip, title, description, item_id, text, mime_type , creator_id,
instructions, run_mode, anonymous_p, secure_access_p, reuse_responses_p,
show_item_name_p, entry_page, exit_page, consent_page, return_url,
start_time, end_time, number_tries, wait_between_tries, time_for_response,
ip_mask, show_feedback, section_navigation, survey_p)
values
(:revision_id, :content_type, :creation_user, :creation_date, :creation_ip, :title, :description, :item_id, :content, :mime_type , :creator_id, :instruction !>>>!, :run_mode, :anonymous_p, :secure_access_p, :reuse_responses_p, :show_item_name_p, :entry_page, :exit_page, :consent_page, :return_url, :start_time, :end_time, :number_tries, :wait_between_tries, :time_for_response, :ip_mask, :show_feedback, :section_navigation, :survey_p)

Any help is highly appreciated.

First, thank you for finding this error and looking for a solution.

It is not the case that work occurs on PostgreSQL while neglecting Oracle. It is possible that not every developer has the resources to maintain an Oracle installation for testing purposes. This is why it is so important for those who use Oracle to kindly volunteer to test before a release. With this information we can improve OpenACS.

On the LOB issue. It is interesting that the code orginally written for ACS does not work. It appears that content::revision::new can not use the view to set the content of the CLOB column. In this case we should change the procedure to use a two step process where the content is set as an update to the revision. Thank you for tracking this down.

Malte,

Of course, I agree completely that upgrade scripts need to be kept in synch for both databases. And that is my usualy procedure. I suspect a mistake in this case, and that is why I suggested better testing would catch such a mistake.

It sounds as though the basic CR API paradigm might be slightly broken, since CLOB/LOB columns can not be directly inserted (regardless of whether or not you're trying to do so using the auto-generated view for a content type).

It is necessary to do a two-step process, as Dave's saying perhaps is necessary. Yes, it is. Check out the old cr revision code that inserts a file into the database (or file system) to see this.

In order to make the writing of db-agnostic code possible the Tcl API should offer the two-step (INSERT of non-[C]LOB data followed by an UPDATE that adds the [C]LOB data) approach for both databases.

Unless someone else volunteers I'll fix content::revision::new tonight.

Malte,

Having a best practice for development is great. We also need developers to keep an eye on CVS and do code reviews as well as help with testing a release. All the rules won't help if no one tests before a release.

Malte - if you want your code to break, then put in an error statement, *not* code that may work or may not work or may make a developer think somebody else put it there on purpose.

It is much clearer and a lot less time-consuming for the person doing the port if he knows what is supposed to happen. If you write this:

error;

// This code needs to be ported from the file foo.sql

than:

insert into ....

which may fail with a bogus Oracle error message. Maybe worse: it does not fail, but it has weird side effects.

The bottom line is: don't commit code to a non-private branch that has not been tested (without a warning and/or commitment to fix the bugs yourself) and if you want to indicate future work, write that in prose, not in code which may or may not work.

This should be fixed on the oacs-5-1 branch. I modified the code to do a seperate update on the content column.