PLSQL packaged function in view 1

[WP]

hi,

I have the following packaged function / view scenario that I'm hoping to improve the performance.

CREATE OR REPLACE PACKAGE Pkg_Sub_Time AS

FUNCTION CHGDATM(ts TIMESTAMP) RETURN TIMESTAMP;

END Pkg_Order_Archive;
/

CREATE OR REPLACE PACKAGE BODY Pkg_Sub_Time AS

FUNCTION chgdatm(ts TIMESTAMP) RETURN TIMESTAMP IS
wLTS TIMESTAMP;
BEGIN
SELECT ts - (SELECT to_number(VALUE) FROM OSMCFG WHERE NAME='MinusTime') INTO wLTS
FROM dual;
RETURN wLTS;
END chgdatm;

END Pkg_Sub_Time ;
/

The function is then used in a view passing a timestamp field.

The lookup of the time to subtract gets called for each row but SELECT to_number(VALUE) FROM OSMCFG WHERE NAME='MinusTime' is static data.

Can I call this just once and have it set on package load?

WP

http://www.neptune400.com

 

[Dagon]

You can indeed:

CREATE OR REPLACE PACKAGE BODY Pkg_Sub_Time AS
v_MinusTime number;
   FUNCTION chgdatm(ts TIMESTAMP) RETURN TIMESTAMP IS
      wLTS TIMESTAMP;
      BEGIN
       SELECT ts - v_MinusTime
       INTO wLTS
       FROM dual;
     RETURN wLTS;
   END chgdatm;
begin
   SELECT to_number(VALUE) into v_MinusTime
    FROM OSMCFG WHERE NAME='MinusTime';
END Pkg_Sub_Time ;
/

The section immediately before the final end will be executed only once when the package is first initiated.

 

[WP]

Great!

Now how do I reset the variable after package load.

The idea is that the time from the config table can be changed by the application support staff - they will have little or no knowledge of Oracle.

WP

http://www.neptune400.com

 

[Dagon]

That's quite tricky without polling the table all the time. What you could do is to force a recompile of the package (perhaps using a trigger on the table). This would invalidate it for anyone using it. They would receive an error the first time they tried to run a procedure in it, but the second time it would work and reset the initiation variables.

Obviously, getting the error wouldn't be desirable but you might be able to get round this by putting a wrapper round the calls to the packaged procedures. Create another package whose job is to call the procedues and functions in the main package. Each function in the new package would have an exception handler which suppresses the error and retries the operation.

 

[WP]

Dagon,

I added a Proc to refresh the sub time var.

CREATE OR REPLACE PACKAGE BODY Pkg_Sub_Time AS
v_MinusTime number;
FUNCTION chgdatm(ts TIMESTAMP) RETURN TIMESTAMP IS
wLTS TIMESTAMP;
BEGIN
SELECT ts - v_MinusTime
INTO wLTS
FROM dual;
RETURN wLTS;
END chgdatm;

PROCEDURE Update_Time_var IS

BEGIN
SELECT to_number(VALUE)into v_MinusTime
FROM OSMCFG WHERE NAME='MinusTime';

End Update_Time_var;

begin
SELECT to_number(VALUE) into v_MinusTime
FROM OSMCFG WHERE NAME='MinusTime';
END Pkg_Sub_Time ;
/

And will call the Proc from the trigger ... another great idea .... THANKS!

WP

http://www.neptune400.com