From a code readability and maintainability point of view, parameter lists are by far the best way to communicate between procedures. Any other mechanism serves to hide what's really going on, including records, and globals (CREATE VARIABLE) might be the worst. In the old days this was called "coupling".

Hey, guess what? you can't edit your own comments, at least I can't see how. Anyway, I was thinking my rant about globals might have seemed harsh... I am not as dogmatic as that comment might seem. Question for you: How did you like the experience of using lots of CREATE VARIABLE statements? I have only ever used them for special purposes, like passing parameters to triggers ( go ahead, all you Anti-Global people, tell me there's another way to do that :)

I'm not a big fan of globals for this, especially with create variable. We've used it in a few other places with success. But error handling leaving needs to be real good, or the variables are left active, so it just adds headaches.

In this case we're just trying to clean up some code where we have a complicated 20 line expression in multiple spots within the code. I've converted it to a stored procedure and it seems to be working fine with the parameters, so I'll take your advice and leave it as is. Thanks, Jon.

I didn't answer the Oracle question. I have not performed performance testing, so cannot answer that question. I'd say we use the records mostly for convenience. The ability to declare a record with

%ROWTYPE and <cursor name="">%ROWTYPE is very convenient. Especially with cursors as adding to the select no longer means changing the FETCH statements and declaring new variables. But overall it just seems cleaner, you're right it may hide some code, but parameter lists of 60 parameters just seem wrong or something. :)