Dynamic Database Link in Stored Function

From: Ng TC <tcng_at_yas.com.sg>
Date: 4 Jun 1998 02:46:06 GMT
Message-ID: <01bd8f63$3bf220a0$ebce2aca_at_mis1-235.yas.com.sg>

Hi,

[Quoted] I have a simple function that takes in 2 parameters:

  FUNCTION GET_SET_OF_BOOKS_ID(COMP VARCHAR2, DB_LINK VARCHAR2)RETURN NUMBER; The function itself is a simple select statement:

      FUNCTION GET_SET_OF_BOOKS_ID( COMP VARCHAR2,
				    DB_LINK VARCHAR2 ) RETURN NUMBER IS
	SET_OF_BOOKS_ID NUMBER(15);
    BEGIN
	SELECT	hou.organization_id
	INTO	set_of_books_id
	FROM	hr_organization_units_v_at_<db_link> hou,
		hr_organization_information_at_<db_link> hoi
	WHERE	substr(hou.name,1,3) = comp
	AND	hou.organization_id = hoi.organization_id
	AND	hoi.org_information_context = 'CLASS'
	AND	hoi.org_information1='OPERATING_UNIT'
	AND	hoi.ORG_INFORMATION2='Y';

        RETURN SET_OF_BOOKS_ID;

    END; The value is taken from a remote Database, thus I have to use a database link.
If I am running in test environment, I will use the test environment database link, and when I am in production environment, I will use the production database link.

This is where I face problem.

[Quoted] If I do the following, the stored procedure would not compile/save.

        FROM hr_organization_units_v_at_DB_LINK hou,

How do I achieve this dynamic database link in the select statement?

Please advise.

Thanks,
Tse Chong Received on Thu Jun 04 1998 - 04:46:06 CEST