Re: procedure compilable over db link?
From: mcstock <mcstockx_at_xenquery.com>
Date: Fri, 31 Oct 2003 06:24:37 -0500
Message-ID: <o96dncLqteAG2z-iRVn-ig_at_comcast.com>
Date: Fri, 31 Oct 2003 06:24:37 -0500
Message-ID: <o96dncLqteAG2z-iRVn-ig_at_comcast.com>
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" <thiemo_at_gmx.ch> wrote in message news:c1a00b4f.0310310231.1fe18f19_at_posting.google.com...Received on Fri Oct 31 2003 - 12:24:37 CET
> 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_at_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