Re: Change SCHEMA in Stored Procedure

From: Daniel Roy <danielroy10_at_hotmail.com>
Date: 21 Apr 2003 15:07:48 -0700
Message-ID: <1b061893.0304211407.31936e4f@posting.google.com>

If your version is at least 8i, just use native dynamic SQL:

CREATE OR REPLACE PROCEDURE test (schema_name IN VARCHAR) AS

	/** Define variables **/
	CURSOR a IS
		SELECT	* FROM A;
BEGIN


                        .

                        .

	execute immediate 'ALTER SESSION SET current_schema = schema_name';
                         .
                         .

 END; Daniel Received on Mon Apr 21 2003 - 17:07:48 CDT