Forum OpenACS Development: Re: Differences between Oracle and PostgreSQL on db_exec_plsql function

Collapse
4: Re: Differences between Oracle and PostgreSQL on db_exec_plsql function (response to 1)
Posted by Dave Bauer on 12/10/08 09:16 PM
I am not sure why xowiki is calling file storage apis here. Since file storage package works fine in Oracle for years now, this is a bug in how that tcl procedure is being called.

You are correct, but in practice I haven't seen any issue with this difference in oracle and postgresql. Again it has nothing to do with calling a pl/sql function since we can't know what code is run in that function. I suppose there might be a way to rewrite the oracle function to return an NULL response if there are no rows but it seems problematic. It definitely would have to be fixed within the function that calls select into. I can't see any way you'd want to catch all errors in pl/sql on oracle and return an empty string. That would definitely lead to more problems than it would solve.

The problem here is that there is no file storage root folder when one is expected. If you returned an empty stirng the underlying bug would be hidden.

Collapse
5: Re: Differences between Oracle and PostgreSQL on db_exec_plsql function (response to 4)
Posted by Gustaf Neumann on 12/11/08 09:01 AM
Well, xowiki does not call fs::get_root_folder, but dotlrn does it. xowiki calls - in case dotlrn is installed - dotlrn_fs::get_community_shared_folder to determine the folder where the xinha plugin should add uploaded files. Since version 0.91 (August this year), xowiki programs around this problem with oracle.

Nevertheless, the behavior of an API call should be identical for PostgreSQL and Oracle. Since the API has no fs::exists_root_folder returning empty sounds ok to me in case the package has no root folder. Otherwise, the API documentation should mention a word about this. The argument, that the "file storage package works fine in Oracle for years now" cannot certainly mean that the fs::*-api is free of bugs.

Ignoring all errors in db_exec_plsql is certainly no good idea. For get_root_folder, adding an exception handler for "No data found" in file-storage-procs-oracle.xql seems like a good option to me (see for example
http://www.oracle.com/technology/oramag/oracle/03-jul/o43plsql.html).

By looking at the file-storage code: the procs fs_get_root_folder (and friends) should be replaced by fs::get_root_folder (and friends) (or marked as deprecated for the short range). Additionally, the code with the Oracle notation in fs_get_folder_name in file-storage-procs.tcl is at least misleading. So getting rid of fs_* should be on our todo list for the mid range.

-gustaf neumann