Forum OpenACS Q&A: Problem with one page

Posted by Carlos Augusto Lone Saenz on 05/03/03 09:30 PM

i'm a very very newbie with openACS, i have installed the software, but i got a problem with one page that belongs to acs_admin package the page name is one.tcl and the error i got is

REQUEST ERROR
ora.c:3930:ora_tcl_command: error in 'OCIStmExecute()' ora-00904: invalid column name

SQL: select first_name,last_name, email, coalesce(screen_name,'&lt none set up &gt') as screen_name

i got a problem with caoalesce() call, i try to find it but, it was not there in the database.

i'll be waiting for some answers thanks !!!

2: Re: Problem with one page (response to 1)

Posted by Don Baccus on 05/03/03 09:50 PM

Hmmm ... looks like coalesce is only supported in Oracle 9i (which we won't support until our next major release this summer.) I'll fix this for the upcoming 4.6.3 release by using nvl() ...

3: Re: Problem with one page (response to 1)

Posted by Don Baccus on 05/04/03 01:54 AM

This appears to have been fixed in OpenACS 4.6.2, at least in my copy it appears to be fixed.

What version of OpenACS are you using?

4: Re: Problem with one page (response to 1)

Posted by Carlos Augusto Lone Saenz on 05/05/03 04:25 AM

I got openACS 4.6 i haven't upgrade it to 4.6.something anyways, coalesce() function is only available in oracle 9i, what does that function for anyway?, will i get rid off this problem by upgrading my version of openACS?,or theres another way to fix the problem by using the nvl() function? by the way, thanks for the previos responses Don ...

regards

5: Re: Problem with one page (response to 1)

Posted by Don Baccus on 05/05/03 05:31 PM

If your site is still under development, by far the easiest approach would be to upgrade to OpenACS 4.6.2, which also fixes a lot of other bugs.

Or you could upgrade. Upgrading datamodels is simply a matter of grabbing the tarball, replacing your existing source code, and using the ACS Package Manager (APM) to run the SQL upgrade scripts. But if you've customized templates or .tcl scripts, then upgrading involves a lot more work than simply upgrading the datamodel and must be done by you, manually.

A simple approach, if you just want to fix this problem without going through the trouble of a full upgrade, is to grab the tarball and to copy all of the current one* files into your existing copy of acs-admin/www/users. I think that will work fine because I think the changes are minimal, perhaps only that one bug fix, to be honest.

coalesce() is an extended version of nvl(). It takes a list of arguments and returns the first one that's not NULL, much as nvl() does for two arguments. We use coalesce in the default query file because it is part of the SQL 99 standard. Postgres supports this standard function, and as mentioned above Oracle 9i also supports this standard function, but Oracle 8i and earlier versions do not.

6: Re: Problem with one page (response to 1)

Posted by Carlos Augusto Lone Saenz on 05/06/03 01:25 AM

Thanks for your help Don, i know this will work fine, regards !!!