Thanks for the question, Andrew.

Asked: August 01, 2012 - 11:35 am UTC

Last updated: August 28, 2012 - 1:14 pm UTC

Version: 11.1.0.7

Viewed 10K+ times! This question is

You Asked

We have a package that calls a function in a remote database to do a lookup and return a value. The problem is that when the function is recompiled in the remote database, the local package will raise and ORA-4062 because the time stamp it (the local package) has for the remote function no longer matches. In order to clear this, we have to recompile the local package. I was thinking we might trap the exception and have the package recompile itself, then continue. However, we cannot do this because the compile command will wait for the package to complete before it executes so the session will wait on itself forever.

I am considering making a copy of the remote function locally, but then we have to maintain the function in two places. I am also considering that we might make a pass-through procedure that will recompile the package each time it is called, before passing control to it - I rather hate that idea. Else it seems we have to have a local practice of recompiling locally whenever the certain remote functions are recompiled.

Any suggestions?

and Tom said...

the package should recompile itself after it exits.

but in any case - why don't you use signature based dependency tracking? as long as the interface to the procedure doesn't change - you are OK.

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams206.htm#REFRN10182

or short of that, invoke the procedure dynamically using native dynamic SQL or dbms_sql.

I would suggest signature based dependency tracking first, dynamic sql as a far second.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using native dynamic SQL or dbms_sql

Sokrates, August 14, 2012 - 5:53 am UTC

or short of that, invoke the procedure dynamically using native dynamic SQL or dbms_sql.

That won't help ? See

REM
REM local
REM
sokrates@11.2 [local] create database link loopback connect to sokrates identified by ... using ...;

Database link created.

sokrates@11.2 [local] select * from dual@loopback;

D
-
X

sokrates@11.2 [local] alter session set remote_dependencies_mode=TIMESTAMP;

Session altered.



REM
REM remote
REM
sokrates@11.2 [remote] create or replace function f return int is begin return 1; end f;
  2  /

Function created.







REM
REM local
REM
sokrates@11.2 [local] create package p is procedure p; end p;
  2  /

Package created.

sokrates@11.2 [local] create package body p is procedure p is begin execute immediate 'begin dbms_output.put_line(f@loopback); end;'; end p; end p;
  2  /

Package body created.

sokrates@11.2 [local] exec p.p
1

PL/SQL procedure successfully completed.





REM
REM remote
REM
sokrates@11.2 [remote] create or replace function f return int is begin return 2; end f;
  2  /

Function created.





REM
REM local
REM
sokrates@11.2 [local] exec p.p
BEGIN p.p; END;

*
ERROR at line 1:
ORA-04062: timestamp of function "SOKRATES.F" has been changed
ORA-06512: at line 1
ORA-06512: at "SOKRATES.P", line 1
ORA-06512: at line 1


sokrates@11.2 [local] exec p.p
2

PL/SQL procedure successfully completed.




REM
REM local
REM
sokrates@11.2 [local] create or replace package body p is
  2  procedure p is
  3    l_cursor    int;
  4    l_status    int;
  5  begin
  6     l_cursor := dbms_sql.open_cursor;
  7
  8     dbms_sql.parse
  9     ( l_cursor,
 10       'begin dbms_output.put_line(f@loopback); end;',
 11        dbms_sql.native
 12     );
 13
 14     l_status := dbms_sql.execute( l_cursor );
 15
 16     dbms_sql.close_cursor(l_cursor);
 17
 18  end p;
 19  end p;
 20  /

Package body created.

sokrates@11.2 [local] exec p.p
2

PL/SQL procedure successfully completed.







REM
REM remote
REM
sokrates@11.2 [remote] create or replace function f return int is begin return 3; end f;
  2  /

Function created.









REM
REM local
REM
sokrates@11.2 [local] exec p.p
BEGIN p.p; END;

*
ERROR at line 1:
ORA-04062: timestamp of function "SOKRATES.F" has been changed
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1825
ORA-06512: at "SOKRATES.P", line 14
ORA-06512: at line 1

August 17, 2012 - 2:09 pm UTC

and if you just execute it again, what happens. that is something catchable and correctable.

dynamic sql doesn't solve ORA-04062

Rajeshwaran Jeyabal, August 14, 2012 - 7:18 am UTC

Tom:

Using dynamic sql doesn't solve ORA-04062.

/* did this in Remote Database */
iradsrpt@IRDDV02> create or replace procedure
  2  p(x int) as
  3  begin
  4     insert into t values(x);
  5     commit;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.57
iradsrpt@IRDDV02>

/* did this in LOCAL Database */
rajesh@ORA11GR2> declare
  2     i number :=1;
  3  begin
  4     execute immediate ' begin p@irddv02(:x); end;' using i;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.59
rajesh@ORA11GR2>
/* did this in Remote Database */
iradsrpt@IRDDV02>
iradsrpt@IRDDV02> create or replace procedure
  2  p(x int) as
  3  begin
  4     insert into t values(2);
  5     commit;
  6  end;
  7  /

Procedure created.

Elapsed: 00:00:00.57
iradsrpt@IRDDV02>

/* did this in LOCAL Database */
rajesh@ORA11GR2> declare
  2     i number :=1;
  3  begin
  4     execute immediate ' begin p@irddv02(:x); end;' using i;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-04062: timestamp of procedure "IRADSRPT.P" has been changed
ORA-06512: at line 1
ORA-06512: at line 4


Elapsed: 00:00:00.82
rajesh@ORA11GR2>

August 17, 2012 - 2:10 pm UTC

what happens when you run it the second time after the failure.

why ?

Sokrates, August 20, 2012 - 8:04 am UTC

and if you just execute it again, what happens. that is something catchable and correctable.

ok, I see, this one works:

sokrates@11.2 [remote] create or replace function f return int is begin return 1; end f;
  2  /

Function created.

and

sokrates@11.2 [local] create or replace package p is
  2    procedure p;
  3  end p;
  4  /

Package created.


sokrates@11.2 [local] create or replace package body p is
  2    procedure p( vrecursive_count in int ) is
  3      ts_of_remote_proc_changed exception;
  4      pragma exception_init( ts_of_remote_proc_changed, -4062 );
  5      begin
  6
  7        if vrecursive_count > 1 then
  8          raise_application_error(-20999, 'vrecursive_count > 1');
  9        end if;
 10
 11        begin
 12          execute immediate 'begin dbms_output.put_line(f@loopback); end;';
 13        exception when ts_of_remote_proc_changed then
 14          dbms_output.put_line('ts_of_remote_proc_changed ! - remotecall once again');
 15          p( vrecursive_count => vrecursive_count + 1);
 16        end;
 17     end p;
 18
 19    procedure p is
 20      begin
 21        p ( vrecursive_count => 0 );
 22      end p;
 23  end p;
 24  /

Package body created.

sokrates@11.2 [local] exec p.p
1

PL/SQL procedure successfully completed.

sokrates@11.2 [remote] create or replace function f return int is begin return 2; end f;
  2  /

Function created.

now

sokrates@11.2 [local] exec p.p
ts_of_remote_proc_changed ! - remotecall once again
2

PL/SQL procedure successfully completed.

and we have

sokrates@11.2 [local] select referenced_type, referenced_name
  2  from user_dependencies
  3  where name='P' and type = 'PACKAGE BODY' and referenced_link_name = 'LOOPBACK';

no rows selected

I would have expected the same behaviour not using dynamic sql, since the STATUS of package P doesn't change when dependent objects change remotely. But the behaviour is not the same:

sokrates@11.2 [local] create or replace package body p is
  2
  3    procedure p( vrecursive_count in int ) is
  4      ts_of_remote_proc_changed exception;
  5      pragma exception_init( ts_of_remote_proc_changed, -4062 );
  6      begin
  7
  8        if vrecursive_count > 10 then
  9          raise_application_error(-20999, 'vrecursive_count > 10');
 10        end if;
 11
 12        begin
 13          dbms_output.put_line(f@loopback);
 14        exception when ts_of_remote_proc_changed then
 15          dbms_output.put_line('ts_of_remote_proc_changed ! - remotecall once again');
 16          p( vrecursive_count => vrecursive_count + 1);
 17        end;
 18     end p;
 19
 20    procedure p is
 21      begin
 22        p ( vrecursive_count => 0 );
 23      end p;
 24  end p;
 25  /

Package body created.

now, we have

sokrates@11.2 [local] select referenced_type, referenced_name
  2  from user_dependencies
  3  where name='P' and type = 'PACKAGE BODY' and referenced_link_name = 'LOOPBACK';

REFERENCED_TYPE                REFERENCED_NAME
------------------------------ ------------------------------
FUNCTION                       F

sokrates@11.2 [local] exec p.p
2

PL/SQL procedure successfully completed.

sokrates@11.2 [remote] create or replace function f return int is begin return 3; end f;
  2  /

Function created.

and then local:

sokrates@11.2 [local] select status, object_type from user_objects where object_name='P';

STATUS  OBJECT_TYPE
------- -------------------
VALID   PACKAGE
VALID   PACKAGE BODY

however, -4062 seems to be raised infinitely open:

sokrates@11.2 [local] exec p.p
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
ts_of_remote_proc_changed ! - remotecall once again
BEGIN p.p; END;

*
ERROR at line 1:
ORA-20999: vrecursive_count > 10
ORA-06512: at "SOKRATES.P", line 9
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 16
ORA-06512: at "SOKRATES.P", line 22
ORA-06512: at line 1

So, we really have to recompile the caller in order to pick up the new timestamp.
http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm#sthref1897

I don't really see the idea behind this implemented behaviour especially we can simply workaround it by using dynamic sql.

Your link to the docs

Kim Berg Hansen, August 21, 2012 - 5:23 am UTC

Tom

Your link to documentation links to parameter REDO_TRANSPORT_USER?

Did you not mean to link to REMOTE_DEPENDENCIES_MODE? :-)

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams207.htm

(Or did Oracle change the docs?)

August 28, 2012 - 1:14 pm UTC

that would be a cut and paste error on my part :)

thanks for the right one.

@Kim

Sokrates, August 23, 2012 - 4:33 am UTC

I could bet, that last week when I clicked on
http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams206.htm#REFRN10182
, I landed on
the reference for REMOTE_DEPENDENCIES_MODE, interesting, looks for me they changed something.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

using native dynamic SQL or dbms_sql

dynamic sql doesn't solve ORA-04062

why ?

Your link to the docs

@Kim

More to Explore

PL/SQL demos

PL/SQL docs

Package recompile itself.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

using native dynamic SQL or dbms_sql

dynamic sql doesn't solve ORA-04062

why ?

Your link to the docs

@Kim

More to Explore

PL/SQL demos

PL/SQL docs