Forum OpenACS Development: Automatic tcl api for pl/sql procs?

Request notifications

Posted by Tilmann Singer on
I was wondering if it wouldn't be possible and make sense to have openacs generate a tcl proc automatically for each available pl/sql procedure, so that for example for the pl/sql proc a tcl proc db_plsql::bookmark::new is created, with all it's allowed parameters as named parameters.

So that instead of writing db_exec_plsql and tediously having to translate it for the two databases, you could just call:

db_plsql::bookmark::new -owner_id $owner_id -parent_id $parent_id -creation_user $user_id ...

Possibly even with optional pl/sql parameters resulting in an optional tcl parameter and required pl/sql resulting in required tcl parameters, so that the error messages make sense when calling it wrongly.

I don't know exactly how/if that would be possible, but I guess both databases provide us with some kind of information about the available pl/sql procedures, no?


Posted by Dave Bauer on
Til and I and Jeff Davis has discuess this on IRC. package_instantiate_object almost does this, but using it is not as straightforward as Til's suggestion. Here is what we came up with today:
db_plsql -package bookmark \
         -operation new \
         -args {
                object_id $object_id
                name $name
                content $content
where args contains a list in array get form of paramter names and values.

To make this work, functions in postgresql would need to be definied with define_function_args plpgsql procedure to capture the necessary parameter names and default value information that is build in to oracle.

Posted by Peter Marklund on
I like the new syntax for package_instantiate_object a lot! Very clean and obvious!