Auto insert timestamp

[gulhaugen]

Hi

We have an application which we develop inhouse, and distribute to several other schemas. Now, to ensure that the packages are the same on all schemas we thought about including a version tag at the top of each package upon editing it. However this is a source of error, since the the developer needs to remember to change the version number. Instead, we would like to insert a timestamp in the header upon on each compile (similar to the created timestamp).

Is there a plug-in that can achieve such a thing?

After what I can see, there is none. Does anyone have any ideas?

I guess doing a checksum of some sort will work as well, but it would be nice to have a timestamp

 

[Marco Kalter]

There is currently no function or Plug-In for this. You could write your own Plug-In, or use a Version Control System with keyword expansion/substitution.

 

[Claus Pedersen]

Just an idea:

Create a table with columns for package name and update timestamp.

Then create a DDL statement trigger (fire when a package is altered/compiled) that inserts/updates the timestamp in the table.

Afterwards the table data can be compared between users.

 

[gulhaugen]

Claus: The idea is similar, and we have this in place. But the package can be recompiled by other schemas(for quick fixes, critical patch) and will not reliably tell if one package is the same "version" as the other.

I think I will look for plug-in tutorials.

 

[gulhaugen]

On the plug-in path:
1. I do not know C++
2. I do not know Delphi
3. I do not know C

So I figured it's easiest to try Delphi

I have downloaded the Free Pascal compiler, but there I am kind of stranded.

Is it possible to compile a plugin for plsqldev with this compiler?

 

[Marco Kalter]

If it can create DLL's, and supports the data types mentioned in the Plug-In documentation, then it should be doable.

 

[Claus Pedersen]

Another idea:

In the package header, in a comment you add a well defined tag like this:

/* @date */

In the script that runs the package header, you add some code to read all the lines from the new package header:

SELECT text
FROM   user_source
WHERE  name  = 'NNN'
AND    type  = 'PACKAGE'
ORDER BY line

where NNN is the package name.

Then find the date comment in one of the returned strings, replace the date with the current date and use dbms_sql.execute to execute all the lines (in one statement) to replace the package header, this time with the new date.

Remember to add a CREATE AND REPLACE in front of the first line PACKAGE NNN IS ...

Could this work?

The following code snippet illustrates the principle (the code has been read from the database and the date tag replaced by the real date):

DECLARE
 stmt  VARCHAR2(10000);
 curs  NUMBER;
 dummy NUMBER;
BEGIN
  stmt :=
'CREATE OR REPLACE PACKAGE test IS
   /* 23-OCT-2007 */

   FUNCTION MyFunc (param1 IN VARCHAR2)
     RETURN NUMBER;

   PROCEDURE MyProc (param2 IN NUMBER)
     RETURN VARCHAR2;

 END test;';

  curs := dbms_sql.open_cursor;
  dbms_sql.parse(curs, stmt, dbms_sql.native);
  dummy := dbms_sql.execute(curs);
  dbms_sql.close_cursor(curs);
END;

I do not know the max. length of the string to be run by dbms_sql.execute, but that can be investigated.

Unfortunately you can not use EXECUTE IMMEDIATE for this purpose, it seems to get confused by the many semicolons in the DDL statement.

 

[gulhaugen]

I'm wondering Claus, if we are working at the same place. We did this as well, in a trigger on the schema. However I cannot use DDL there. The only way to make that work is to submit a job, or something similar to do that.

I tend to like the idea of a plug-in though.

Morten

 

[William Robertson]

Any decent source code library application (e.g. Subversion which is excellent and free) supports tags which it can expand on checkout. Then it just becomes a matter of including a 'version' function in every package, and the whole thing's done for you. I've put this in templates before and it's worked well. In fact I consider this type of approach absolutely essential in any project that matters.