Re: using alter xx.xxx recompile to reset packages in other sessions

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Tue, 14 Jan 2014 18:11:20 -0400
Message-ID: <CAGYrQyvh+1j6KNTk-_kVLsaeb5UZ7D9kedokqzcis3A0rbwz1A_at_mail.gmail.com>

Thank you bob,
The idea is the system has different date than the server date, the date is in a table, and is moved to a variable in a package in every session.

This means every time you go to the next day, users must disconnect.

How can you do this Using an Oracle Context object to hold a global variable and value might be an option.

CREATE OR REPLACE PACKAGE DAZ.PCK_EMPRESA   IS
   FUNCTION GetToday RETURN DATE;
   procedure NextDay
   dMEM_FECHA_HOY DATE;
END; -- Package spec
/
CREATE OR REPLACE PACKAGE BODY DAZ.PCK_EMPRESA IS

   FUNCTION GetToday
   RETURN DATE
   IS
   BEGIN
    RETURN dMEM_FECHA_HOY ;
   END;    procedure NextDay
   IS
   BEGIN

• goto the next day update DAZ.VEW_MULTIEMPRESA set MEM_CF_FECHA_SIS=MEM_CF_FECHA_SIS+1; SELECT MEM_CF_FECHA_SIS INTO dMEM_CF_FECHA_SIS FROM DAZ.VEW_MULTIEMPRESA; END;

BEGIN

 SELECT MEM_CF_FECHA_SIS
  INTO dMEM_CF_FECHA_SIS
  FROM DAZ.VEW_MULTIEMPRESA;

END;
/

2014/1/14 Powell, Mark <mark.powell2_at_hp.com>

> More detail or the code involved might help someone provide you with a
> different approach. I do not understand why you would need to use a
> package to hold what is a constant for one day then update it apparently on
> the change in the date to the next day. Using an Oracle Context object to
> hold a global variable and value might be an option.
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Juan Carlos Reyes Pacheco
> *Sent:* Tuesday, January 14, 2014 9:17 AM
> *To:* ORACLE-L
> *Subject:* using alter xx.xxx recompile to reset packages in other
> sessions
>
>
>
> Hello,
>
>
> alter package xx.yy recompile;
>
> This force other session to reconnect or reload.
>
> The question is: "Is this a reasonable solution in 11g and upwards",
> because I remember from 11g the way Oracle decompile process had been
> optimized."
>
> Or this could cause problems.
>
>
> The explanation:
>
> I have a package where I set the date in a package variable, to improve
> performance.
>
> I need to reset that variables after I close de day, this means advance +1
> the date..
>
> The only way I found is to issue the alter compile.
>
>
>
> Thank you.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 14 2014 - 23:11:20 CET