469,275 Members | 1,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

procedure compilable over db link?

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
Jul 19 '05 #1
3 15732
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
(888) 512-2048
"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

Jul 19 '05 #2

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
Jul 19 '05 #3
Thanks for your replies. They helped.

Cheers

Thiemo
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dan Loomis | last post: by
15 posts views Thread by Jarrod Morrison | last post: by
3 posts views Thread by Serge Rielau | last post: by
6 posts views Thread by Rod Snyder | last post: by
reply views Thread by SOI_0152 | last post: by
3 posts views Thread by Thiemo Kellner | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.