Home » SQL & PL/SQL » SQL & PL/SQL » Is there any way to refresh the package initialization section ?
Is there any way to refresh the package initialization section ? [message #284945] Sun, 02 December 2007 23:51 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi All,

This is regarding the package Initialisation section where it is used to get called Only once in a session. But I just want to know is there any method by which we can refresh section (Call once again in the same session) .

In the following example ..


SQL> select banner from V$version;

BANNER
---------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64b
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> SELECT sal FROM EMP WHERE EMPNO= 7369;

       SAL
----------
      8000

SQL> Create Or replace Package p_test
  2  AS
  3  Function retur_x return number;
  4  END;
  5  /

Package created.

SQL> Create Or replace Package Body p_test
  2  AS
  3  x     number;
  4  Function retur_x  return number as
  5  BEGIN
  6  RETURN X;
  7  END;
  8
  9  BEGIN
 10
 11  SELECT SAL
 12  INTO   x
 13  FROM   EMP
 14  WHERE  EMPNO= 7369;
 15  EXCEPTION
 16  WHEN OTHERS THEN
 17  NULL;
 18  END;
 19  /

Package body created.

SQL> SELECT P_TEST.retur_x FROM DUAL ;

   RETUR_X
----------
      8000
SQL> UPDATE EMP
  2  SET    SAL=1000
  3  WHERE  EMPNO= 7369;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT P_TEST.retur_x FROM DUAL ;

   RETUR_X
----------
      8000

SQL> SELECT sal FROM EMP WHERE EMPNO= 7369;

       SAL
----------
      1000
SQL>


Is it possible to Call Initialization section once again once the emp table is updated in the same session ( without re-connecting and even not by moving the SELECT section to different procedrue Or even without re-compiling Smile )

Thumbs Up
rajuvan

[Updated on: Mon, 03 December 2007 00:04]

Report message to a moderator

Re: Is there any way to refresh the package initialization section ? [message #284967 is a reply to message #284945] Mon, 03 December 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No, it is not possible, afaik.
But you can use a procedure called in the initialization section and that you can also called when you want.

For instance, put your select in a "refresh_x" procedure that you call in the initialization section and each time you want to refresh the value.

Regards
Michel


Re: Is there any way to refresh the package initialization section ? [message #284972 is a reply to message #284945] Mon, 03 December 2007 00:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks Dear Michel for your reply ,

But I even had tried your logic , but didn't do any help.

If i am not wrong , You suggested as ..

SQL> select banner from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE OR REPLACE PACKAGE P_TEST
  2  AS
  3    FUNCTION retur_x RETURN NUMBER;
  4    PROCEDURE assign_x ;
  5  END;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY P_TEST
  2  AS
  3
  4  x     NUMBER;
  5
  6  FUNCTION retur_x  return number as
  7  BEGIN
  8      RETURN X;
  9  END;
 10
 11  PROCEDURE assign_x AS
 12  BEGIN
 13     SELECT SAL
 14     INTO   x
 15     FROM   EMP
 16     WHERE  EMPNO= 7369;
 17  EXCEPTION
 18               WHEN OTHERS THEN
 19               NULL;
 20  END;
 21
 22  BEGIN
 23  assign_x;
 24  END;
 25  /

Package body created.

SQL>  SELECT sal FROM EMP WHERE EMPNO= 7369;

       SAL
----------
      2000

SQL> SELECT P_TEST.retur_x FROM DUAL ;

   RETUR_X
----------
      2000

SQL>  UPDATE EMP
  2   SET    SAL=5000
  3   WHERE  EMPNO= 7369;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT P_TEST.retur_x FROM DUAL ;

   RETUR_X
----------
      2000

SQL>  SELECT sal FROM EMP WHERE EMPNO= 7369;

       SAL
----------
      5000

SQL>


Still not refreshed !!!!

Thumbs Up
Rajuvan.
Re: Is there any way to refresh the package initialization section ? [message #284974 is a reply to message #284972] Mon, 03 December 2007 00:52 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You are trying to cache values that should not be cached. This is a wrong use of package variables. The idea of a package variable is that its value is not influenced by outside factors (like the content of table-columns).
What Michel meant was you need to explicitly call assign_x
Re: Is there any way to refresh the package initialization section ? [message #284978 is a reply to message #284945] Mon, 03 December 2007 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Frank ,

Thanks for the reply .

Actually we are in a process of modularising some processes as package and in allmost all Package procedures we are referring to one Parameter table .

Right now Its field values are assigned to one table.field%RowType in each package body and this is referred in corresponding procedures .

This table values are almost fixed for a project , though there is a very rare chance of updation in that table . My query for interest is whether there is any chance to refresh the package initialization section automatically without altering session .

Michel told,

Quote:

put your select in a "refresh_x" procedure that you call in the initialization section and each time you want to refresh the value.


I did the same if i am not wrong Sad

SQL> CREATE OR REPLACE PACKAGE BODY P_TEST
  2  AS
  3
  4  x     NUMBER;
  5
  6  FUNCTION retur_x  return number as
  7  BEGIN
  8      RETURN X;
  9  END;
 10
 11  PROCEDURE assign_x AS
 12  BEGIN
 13     SELECT SAL
 14     INTO   x
 15     FROM   EMP
 16     WHERE  EMPNO= 7369;
 17  EXCEPTION
 18               WHEN OTHERS THEN
 19               NULL;
 20  END;
 21
 22  BEGIN
 23  assign_x;
 24  END;
 25  /


Or is there any better way of acheiving the same ?

Thumbs Up
Rajuvan.

[Updated on: Mon, 03 December 2007 01:08]

Report message to a moderator

Re: Is there any way to refresh the package initialization section ? [message #284987 is a reply to message #284978] Mon, 03 December 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And I also (and above all) said: "and each time you want to refresh the value".
If you want the real value, you have to explicitly call "assign_x".

What you want to do is a local cache.
But Oracle does not know what you put in your cache.
So it can't invalidate it.
You have to either implement an invalidation mechanism or refresh each time you want to have the latest value.
If you always want the latest value, then it is better you directly execute the select statement.

Regards
Michel
Re: Is there any way to refresh the package initialization section ? [message #284988 is a reply to message #284978] Mon, 03 December 2007 01:28 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
what if you'd make another part bold:
Quote:


put your select in a "refresh_x" procedure that you call in the initialization section and each time you want to refresh the value.



[Edit: again I'm replying at the same time Michel is..]

[Updated on: Mon, 03 December 2007 01:28]

Report message to a moderator

Re: Is there any way to refresh the package initialization section ? [message #284991 is a reply to message #284945] Mon, 03 December 2007 01:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks frank and Michel ,

I got point now Smile . Its coooool solution. Cool

Thumbs Up
Rajuvan
Re: Is there any way to refresh the package initialization section ? [message #285003 is a reply to message #284991] Mon, 03 December 2007 01:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
It's not a cool solution, you have a bigger problem than this!
When will you call this method? If you'd update the table in another session, there is no way of letting your session know the data is out-of-date. (at least not with the current config).
Your design is flawed; again, you should not cache variables that can be changed by another process.
Re: Is there any way to refresh the package initialization section ? [message #285015 is a reply to message #284945] Mon, 03 December 2007 02:06 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Thanks for your advice

But even then it is a cool solution for me because

1. This modularisation is not part of our application. This is for some migration activity and it will be used just once for the one application implementation.(once time activity)

2. This table will be changed very rarely (as o i told you already) and by the time of these script excution , these parameter tables value will be stable.

ie, These temporarily solution could be used in Testing phase.

Thanks Frank and Michel Once again

Thumbs Up
Rajuvan.
Previous Topic: how can i remove the garbage table name from tab
Next Topic: To Call Procedure from another procedure
Goto Forum:
  


Current Time: Sat Nov 09 14:57:45 CST 2024