Package with DB-Link including recursive function call returns in

From: Schmid Thomas <Thomas.Schmid_at_itf-edv.de>
Date: Tue, 04 Jun 2002 08:23:30 -0800
Message-ID: <F001.004738F2.20020604082330@fatcity.com>

        

we've created a package which analyzes data in a remote database and fills a table with the results of the analysis in the local db. in order to analyze
the data across the db-link it's necessary to call a function recursively as shown
in the script below.
the function GET_PHYSICAL_DATACHANNEL is called recursively. the function IS_EVAL
analyzes the data of the remote db using a synonym which describes the db-link.
we are using Oracle 8.1.6 at the remote site, and 9.0.1 at the local site, this was also tested
with Oracle Version 9.0.1 at both sites.

when executing the package the select statement as you can see in IS_EVAL returns
the result with the previous p_iDatachannelID, this happens after a couple of

regards,
Thomas Schmid

CREATE OR REPLACE PACKAGE BODY DBI_PROFILE IS    

   FUNCTION IS_EVAL(p_iDatachannelID IN PLS_INTEGER)    RETURN BOOLEAN
   IS

      v_iRetCode                       PLS_INTEGER := 0; 
      v_iState                         PLS_INTEGER := 0;
      v_iEvalCnt                       PLS_INTEGER := 0;
   BEGIN
      SELECT count(id) INTO v_iEvalCnt FROM dbi_datachannel_lnk_def WHERE

result_channel_id = p_iDatachannelID;

      IF (v_iEvalCnt > 0) THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
      
      RETURN TRUE;

   END IS_EVAL;

--------!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

   FUNCTION GET_PHYSICAL_DATACHANNEL(p_iDatachannelID IN PLS_INTEGER)    RETURN PLS_INTEGER
   IS

      v_fIsEval                           BOOLEAN := FALSE;
      
      v_iDatachannelID                    PLS_INTEGER := p_iDatachannelID;
   BEGIN
      -- the datachannel represents a evaluation ???
      v_fIsEval := IS_EVAL(v_iDatachannelID);
      
      IF (v_fIsEval) THEN
         v_iDatachannelID := GET_PHYSICAL_DATACHANNEL(v_iDatachannelID);
      ELSE
         RETURN v_iDatachannelID;
      END IF;

   END GET_PHYSICAL_DATACHANNEL; BEGIN

• initializes the package END DBI_PROFILE; /
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Schmid Thomas INET: Thomas.Schmid_at_itf-edv.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jun 04 2002 - 11:23:30 CDT