Forum OpenACS Development: Package Porting

Posted by Roberto Mello on
Hi all,

I need an example of how to port a package to include in the
PL/SQL-to-PL/pgSQL-HOWTO, and I don't want to put something wrong

I selected one simple package from within ACS 4 and would like to ask
anyone with more experience in Oracle packages that would send me (or
reply in this thread) how would that package be when ported to

This documentation is important for us, and the Postgres team has told
me that they want to include it in the main PostgreSQL documentation,
but I need to give it to them before they release 7.1, otherwise I'll
have to wait for the next release.

The "HOWTO" is at and the
section I am referring to is


Posted by Don Baccus on
You caught me, at least, at a bad time - I'm heading off later today
for a three-four day photo quickie.  Something I've been threatening
to do for about three weeks but have kept putting off.  Today's my
birthday and I'm going to go burn some film, damnit!

If no one gets around to it by early next week I'll be happy to take a
whack at it.

Posted by Roberto Mello on
Happy Birthday Don !
Posted by Neophytos Demetriou on
Happy Birthday!
Posted by Don Baccus on
Damn, it's not 'til tomorrow!  Oh well ... I tend to lose track of the
Posted by Vinod Kurup on
Well, since the grownups are away (grownups in wisdom, that is, not necessarily age), I'll give ya my 2 cents.

A package in Oracle is a way to encapsulate procedures & functions in a single unit.

So the following Oracle package (showing only the package header) defines 2 functions and 1 procedure.

create package acs as function add_user ( user_id in users.user_id%TYPE default null, ... member_state in membership_rels.member_state%TYPE default 'approved' ) return users.user_id%TYPE; procedure remove_user ( user_id in users.user_id%TYPE ); function magic_object_id ( name in ) return acs_objects.object_id%TYPE; end acs;
These would be called as:

    acs.add_user( ... );
    acs.remove_user( ... );
    acs.magic_object_id( ... );
Since postgres doesn't have a way to encapsulate functions together, we need to agree on a convention, which I think is described in the Guidelines document.

So in postgres, we would create 3 functions:

    create function acs__add_user( ... ) ...
    create function acs__remove_user( ... ) ...
    create function acs__magic_object_id( ... ) ...
Those are 2 underscores between the "package name" and the "function name". The function would then be ported just as any oracle function/procedure would be. One of the problems is going to be figuring out how to deal with all the default parameters.

This provides understandable function names (i.e. acs_user__new() creates a new user and party__new() creates a new party) and it potentially allows a way to create an API-browser by showing a list of "packages" and then the "functions" within those packages.

A few other differences between PL/SQL and plpgsql:

  • You can't use %TYPE types in the parameters or the return var. You can use them for variables that you create in the function
  • You can't reference parameter variables by prefacing them with the package/function name. For example, in many ACS functions, the use of v_varname variables have been removed. Instead, they're using the 'varname' directly (without the v_), but distinguishing it from columns of the same name by calling the full package_name.function_name.varname. This doesn't work in pg, so we need to reinstate v_varnames.
  • You can't call functions by specifying the parameter label in the call. So you can't do acs_permission.grant_permission( object_id => v_user_id ...). Instead, you need to send parameters in the order that they're defined.
So, here's how I would redefine one acs package function:
create package body acs as ... < other functions & procedures > function magic_object_id ( name in ) return acs_objects.object_id%TYPE is object_id acs_objects.object_id%TYPE; begin select object_id into magic_object_id.object_id from acs_magic_objects where name =; return object_id; end magic_object_id;
in postgres:
drop function acs__magic_object_id( varchar ); create function acs__magic_object_id ( varchar ) returns integer as ' declare v_name alias for $1; v_object_id acs_objects.object_id%TYPE; begin select into v_object_id object_id from acs_magic_objects where name = v_name; return v_object_id; end; ' language 'plpgsql';
All package calls in the tcl & sql code would then have to be changed to use these conventions.

I hope that this is what you were looking for and that the experts will liberally correct where necessary.

BTW, Happy Birthday tomorrow Don - you know that forgetting your birthday is a sure sign of age 😊

Posted by Roberto Mello on
Thanks Vinod,

What I wanted was actually an example of a ported package from one of the main people here to make sure I was doing it right. I guess I should have done what you did (since I know what packages are and read the guidelines) but I didn't.

So thanks, that helps a lot.

Posted by Vinod Kurup on
What I wanted was actually an example of a ported package from one of the main people here to make sure I was doing it right
Oops - I guess that makes more sense... Sorry for the clutter.
Posted by Ben Adida on
One of the issues is that there doesn't seem to be a standard,
automatic way of porting any given package from Oracle to
PostgreSQL. One important issue, as you point out Roberto, is
default values for parameters. Some of the ACS packages use
these default parameters quite often. Don and I discussed the
idea of using function overloading to represent the most
common forms of these function calls, given the way they are
used in ACS. Of course, this is not a complete solution.

So, this is going to be a hard section for you to write: it's a very
much a porting effort that requires examining the *use* made of
these function calls.