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).