Forum OpenACS Development: Weird problems with db_exec_plsql

I'm porting an oracle query which is executed by a db_exec_plsql block like this:

        begin
        for v_comment in (select c.comment_id
                          from general_comments c
                          where c.object_id = :selection_id) loop
        acs_message.delete(v_comment.comment_id);
        end loop;
        end;

This should be pretty straightforward to port:

      select acs_message__delete(c.comment_id)
      from general_comments c
      where c.object_id = :selection_id

This works fine when launched from psql. But... db_exec_plsql doesn't seem to like my port at all. When there's many comments that should be deleted, I get the following error:

Database operation "0or1row" failed (exception NSINT, "Query returned more than one row.")
    while executing
"ns_pg_bind 0or1row nsdb0 {

      select acs_message__delete(c.comment_id)
      from general_comments c
      where c.object_id = :selection_id

In contrast, when there is no comments related, the error is as follows:

no such set: 
    while executing
"ns_set value $selection 0"
    invoked from within
"return [ns_set value $selection 0]"
    ("uplevel" body line 17)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle -dbn $dbn db {
            # plsql calls that are simple selects bypass the plpgsql 
            # mechanism for creating anonymous fun..."
    (procedure "db_exec_plsql" line 57)
    invoked from within
"db_exec_plsql delete_comments {}"

So the only case when this works is when there's one single comment related to the given object.

I understand that 0or1row bombs when there's more than one row returned but I don't understand why db_exec_plsql uses it and then chokes with queries like mine. What I don't get at all is why the query bombs when there's no rows returned.

So I'd be very happy if someone could explain this behaviour to me and even propose some workaround for this.

Jade,

I don't quite get what you mean. The query I wrote works very well in psql, so I guess that either the __delete function gets always the id in my query (it should, at least), or it works even with cursor.

Either way, the problem is rather that it returns a value for every row that matches so it breaks 0or1row, like Don says.

I solved this by moving all this code to the package api of users-selection, where it imho should have been in the first place. That way, us_selection__delete takes care of cleaning up stuff that is related to it, and every developer calling for that proc doesn't have to delete the comments explicitly.

I thought about something like Don's wrapper trick, but came to a conclusion that moving the code is actually not that big a task and it should make the overall code cleaner.

Thanks for the feedback!