0

We make extensive use of PLSQL packages for reporting purposes. We have the need to change these report generating packages at the beginning of each year. I am looking for a way to deliver the change for 2014 before it is needed for acceptance testing (and to keep things flowing rather than delivering several all at once).

We would like to have both the 2013 and 2014 package installed on the db at the same time and use effective dating to determine which is called if possible. Is this possible? Is there another way to approach. For various reasons it would be difficult to use a solution that required storing these packages with different names or API's.

Tim B
  • 40,716
  • 16
  • 83
  • 128
user3017724
  • 1
  • 1
  • 1
    I would look at changing the report packages to remove any dependencies on the year - i.e. make the year a parameter (or simply derive it from SYSDATE). – Jeffrey Kemp Nov 22 '13 at 06:03

1 Answers1

2

Maybe you can work around name restrictions with synonyms.

CREATE PACKAGE report_2013 AS...

CREATE PACKAGE report_2014 AS...

then use just 

DROP SYNONYM report_package;
CREATE SYNONYM report_package FOR report_2013;

and 

DROP SYNONYM report_package;
CREATE SYNONYM report_package FOR report_2014;

to switch between them.

jva
  • 2,797
  • 1
  • 26
  • 41