In our codes we make it a practice that we sort of label the version on top of on the package, something like this:

CREATE OR REPLACE package calc_facl /* #B#05.13.3.014 */ as

by doing this, even if we wrap the package, we can verify that we have the correct version.

Tom,

Thanks for your time and input.

The only issue with your approach is that occasionally a deployer will run the script from an IDE tool that will reformat the SQL. Those changes will change the source code (capitalization, indenting etc.) and therefore the hash value, without changing the actual software that was deployed. That is why I was looking for a solution using something like the compiled size.

I like the labeling solution as it avoids the reformatting problem and the problems you noted with using the code_size. I will consider it as we work towards automating our deployment process.

Any other suggestions from the group would still be appreciated.

I like the idea of placing a version # inside the code. Also, when the separate group installs the PL/SQL code in the database, give them a complete "packaged" zip file plus an executable to run to install the PL/SQL for you. All that group has to do is run the executable. I used to do this when deploying changes in dev, sit, uat, and prod. All that was ran was a Korn Shell script, which called everything else. Those set of scripts were moved through the dev, sit, uat, and prod through a source code control system.

After they are finished running your code, make sure your code logs all output, and tell them they must provide evidence that your code was installed. How? From the logfile that was generated via your scripts. I would also require them to retain those logs on a sharable, read-only share (perhaps Samba?) that can be accessed by your team for review after the implementation of your change.

You could also place in there a publicly callable function that returns a result in which this result determines your version of this code at some point in time. Wrap the code so the "algorithm" is not known.

Just a few suggestions...

#b ahh: quiet is so much better.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:35336203098853#35830399513875 <code>

I did not realize that it was up to me to turn the bold off from the post before mine. The preview did not show that my test was all in bold.

Hi all,
I agree totally with the idea of providing a 'release' for installation.

Having the release information as a comment is a nice idea, particularly for view definitions etc.

I also define a function within each package that returns the current version information including the compilation date. Depending on the site this is either a string or a PL/Sql table.

This allows a
dbms_output.put_line(package.version) ;
to be performed as required.

Thanks Tom for a great site.
This allows a

I agree - we always wrap the code that is being deployed and then no dodgy deployment staff can mess with it (and no dodgy DBAs can do any ad-hoc "tuning" either).

We've always automatically embedded a version number as a constant in our pl/sql when it's checked in. This constant could still be retrieved, even in wrapped code, by the functions we've written. Well at least it could in Oracle 7, 8 and 9 (doh!) - in 10g the wrapping is more severe (it looks more like uuencoded output) and the constants are no longer detectable. We'll have to come up with some other variant for 10g. One of the early posts in this thread had a version number in a comment on the "create or replace" line - we'll probably have a play with this, but I suspect this can still be altered by deployment staff. We may have to move to some sort of checksum method instead.

A word of warning, we've encountered code which compiles OK when unwrapped but won't compile once it's wrapped. The errors can usually be got rid of by tweaking the code (eg: adding table name aliases to columns in an ORDER BY is a common one). Also the wrap utility tends to be a bit behind the pl/sql compiler - some of the newer syntax (eg: in-line correlated column sub-selects) defeats it and the wrap utility just outputs the code unwrapped.

More worryingly we also found code that worked differently once it was wrapped. A TOO_MANY_ROWS exception correctly changed the value of a variable the code was SELECTing INTO in the wrapped code. In the unwrapped code it left the variable with its original value prior to the SELECT. This problem was in 9.2.0.5 but seems to be fixed in 10g. For this reason if you do decide to deploy wrapped code it should really be tested in its wrapped state. We leave the code unwrapped in our development system for convenience though.

Also its a shame wrap doesn't do triggers (or anonymous blocks) - but that's a good reason to keep code minimal in triggers and let them call wrapped stored procedures. Then the code is available for use outside of the triggers as well.

But, how do you prevent them from doing the compile the wrong direction? ;)

This has happend to me a couple of times. :o

Dear Tom,
Can we wrap only procedures/functions/packages or we can wrap views and tables too?