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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
