procedure compilable over db link?

[Thiemo Kellner]

Hi,

we have come across the problem that the execution of a stored
procedure by our scheduling tool (cronacle -> over db link) fails with
the error message:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of procedure "LARA.PRC_BERECHNE_ZINSFAKTOR" has
been changed
ORA-06512: at "LARA.LARA_PRC_BERECHNE_ZINSFAKTOR", line 15
ORA-06512: at "LARA.LARA_PRC_BERECHNE_ZINSFAKTOR", line 34
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1118
ORA-06512: at "SYS.DBMS_SQL", line 316
ORA-06512: at "SYSJCS.DDL", line 493
ORA-06512: at line 2
JCS-00215: in statement RSOJ_EXECUTE_JOB

We could not find any process that could have changed the timestamp of
this procedure. Therefore we are thinking about recompiling the
procedure over the same db link that is used to execute the stored
procedure. However, test have come to no positive results. The
recompilation over db link gives the following error:
alter procedure TEST_PRC_SIR389@keth_2_lara_keth compile
*
ERROR at line 1:
ORA-00922: missing or invalid option

If we encapsulate the procedure with a synonym, we get a different
error that leads to the assumption that a synonym is no appropriate
procedure wrapper with respect to compilation:
alter procedure SYN_TEST_PRC_SIR389 compile
*
ERROR at line 1:
ORA-04043: object SYN_TEST_PRC_SIR389 does not exist

We could eliminate the uncertainty that the quality of the test
procedure is causing the problem. a local installation of the
procedure compiles without problems.

Is ORA-00922 only a misleading error message and procedures cannot be
compiled over db links whatsoever or are we missing some important
points?

Cheers

Thiemo

[mcstock]

couple ideas:

set the INIT.ORA parameter 'remote_dependencies_mode = signature'

the default is TIMESTAMP which leads to useless ORA-04052 errors

you should no longer have a need to compile remotely, but if there is still
some reason to compile remotely it looks like you might need to create a
remote procedure to encapsulate the ALTER PROCEDURE command -- however,
after setting remote_dependencies_mode it should never be necessary.(accent
on 'should')
--
----------------------------------------
Mark C. Stock
www.enquery.com
[phone]
"Thiemo Kellner" <th****@gmx.ch> wrote in message
news:c1**************************@posting.google.c om...

Hi,

we have come across the problem that the execution of a stored
procedure by our scheduling tool (cronacle -> over db link) fails with
the error message:
ORA-04068: existing state of packages has been discarded
ORA-04062: of has been changed
ORA-04062: timestamp of procedure "LARA.PRC_BERECHNE_ZINSFAKTOR" has
been changed
ORA-06512: at "LARA.LARA_PRC_BERECHNE_ZINSFAKTOR", line 15
ORA-06512: at "LARA.LARA_PRC_BERECHNE_ZINSFAKTOR", line 34
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1118
ORA-06512: at "SYS.DBMS_SQL", line 316
ORA-06512: at "SYSJCS.DDL", line 493
ORA-06512: at line 2
JCS-00215: in statement RSOJ_EXECUTE_JOB

We could not find any process that could have changed the timestamp of
this procedure. Therefore we are thinking about recompiling the
procedure over the same db link that is used to execute the stored
procedure. However, test have come to no positive results. The
recompilation over db link gives the following error:
alter procedure TEST_PRC_SIR389@keth_2_lara_keth compile
*
ERROR at line 1:
ORA-00922: missing or invalid option

If we encapsulate the procedure with a synonym, we get a different
error that leads to the assumption that a synonym is no appropriate
procedure wrapper with respect to compilation:
alter procedure SYN_TEST_PRC_SIR389 compile
*
ERROR at line 1:
ORA-04043: object SYN_TEST_PRC_SIR389 does not exist

We could eliminate the uncertainty that the quality of the test
procedure is causing the problem. a local installation of the
procedure compiles without problems.

Is ORA-00922 only a misleading error message and procedures cannot be
compiled over db links whatsoever or are we missing some important
points?

Cheers

Thiemo

[padderz]

Suggest you read about remote dependency checking (and the
remote_dependencies_mode parameter) in Oracle documentation,
specifically Oracle Application Developer's Guide - Fundamentals,
chapter 9.
--
Posted via http://dbforums.com

[Thiemo Kellner]

Thanks for your replies. They helped.

Cheers

Thiemo