CREATE TABLE MIG_ORA_EXT.mig_pkg_variables 
-- This table is created by DSC and it is used to hold all the Oracle package variable details
	 ( user_name 				VARCHAR(64)		DEFAULT UPPER(USER)					NOT NULL
	 , package_name 			VARCHAR(64)											NOT NULL
	 , spec_or_body 			CHAR(1)			DEFAULT 'S'							NOT NULL
	 , variable_name 			VARCHAR(64)											NOT NULL
	 , variable_type 			VARCHAR(64)		DEFAULT 'VARCHAR(1024)'				NOT NULL
	 , constant_i 				BOOLEAN			DEFAULT FALSE						NOT NULL
	 , default_value 			VARCHAR(1024)	DEFAULT NULL 	
	 , runtime_exec_i           BOOLEAN			DEFAULT FALSE						NOT NULL
	 , CONSTRAINT 				pk_mig_pkg_variables 
	   PRIMARY KEY 				( user_name, package_name, variable_name )
	 );


CREATE OR REPLACE PROCEDURE MIG_ORA_EXT.mig_fn_pkg_var_cret_tmp_table
-- This procedure is used to create the local temp table to hold the package variable details 
AS 
		l_rec_chk			NUMBER(1);
BEGIN 
	
	EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE #tmp_mig_pkg_variables
						 ( user_name 				VARCHAR(64)
						 , package_name 			VARCHAR(64)
						 , spec_or_body 			CHAR(1)	
						 , variable_name 			VARCHAR(64)
						 , variable_type 			VARCHAR(64)
						 , constant_i 				BOOLEAN
						 , variable_value 			VARCHAR(1024)
						 ) 
						 ON COMMIT PRESERVE ROWS';

EXCEPTION
  WHEN OTHERS THEN
		RAISE_APPLICATION_ERROR (-20000, 'Unexpected error (' || SQLCODE || ' - ' || SQLERRM || ') occurred in mig_fn_pkg_var_cret_tmp_table');
END;
/



CREATE OR REPLACE FUNCTION MIG_ORA_EXT.mig_fn_pkg_var_load_tmp_table
-- This function is used to load the package variable details if required
	( i_user_name			IN		VARCHAR
	, i_package_name		IN		VARCHAR	
	, i_variable_name		IN		VARCHAR
	, i_variable_value		IN		VARCHAR		:= 'MIG_PKG_VAR_VAL'
	) 
RETURN VARCHAR
AS 
	l_variable_value		VARCHAR(1024);
    l_cur_var_value			VARCHAR(1024);
BEGIN

	FOR pvar IN ( SELECT user_name, package_name, spec_or_body 
	                   , variable_name, variable_type, constant_i
	                   , default_value, runtime_exec_i  
					FROM MIG_ORA_EXT.mig_pkg_variables
				   WHERE user_name 	    	= i_user_name
					 AND package_name 		= i_package_name )
	LOOP		
		
		IF i_variable_name 		=  pvar.variable_name
		   AND i_variable_value	<> 'MIG_PKG_VAR_VAL'
		THEN
			l_variable_value	:= i_variable_value;
		ELSE
			IF pvar.runtime_exec_i = FALSE
			THEN
				l_variable_value := pvar.default_value;
			ELSE
				-- Function like SYSDATE is assigned to the variable
				EXECUTE IMMEDIATE 'SELECT ' || pvar.default_value INTO l_variable_value;
			END IF;	
		END IF;
		
		IF i_variable_name 		=  pvar.variable_name
		THEN
			l_cur_var_value		:= l_variable_value;
		END IF;
	
		INSERT INTO #tmp_mig_pkg_variables 
				  ( user_name, package_name, spec_or_body
				  , variable_name, variable_type, constant_i
				  , variable_value )
		   VALUES ( pvar.user_name, pvar.package_name, pvar.spec_or_body 
	              , pvar.variable_name, pvar.variable_type, pvar.constant_i 
				  , l_variable_value );

	END LOOP;
	
	RETURN l_cur_var_value;

EXCEPTION
  WHEN OTHERS THEN
		RAISE_APPLICATION_ERROR (-20000, 'Unexpected error (' || SQLCODE || ' - ' || SQLERRM || ') occurred in mig_fn_pkg_var_load_tmp_table');
END;
/


CREATE OR REPLACE PROCEDURE MIG_ORA_EXT.mig_fn_pkg_var_chek
-- This procedure is used to check the input parameters
	( i_user_name			IN 		VARCHAR
	, i_package_name		IN 		VARCHAR
	, i_variable_name		IN 		VARCHAR
	, o_user_name			OUT		VARCHAR
	, o_package_name		OUT 	VARCHAR
	, o_variable_name		OUT 	VARCHAR
	) 
AS 
	l_object_exists			NUMBER(1);
	l_error_handle_i		NUMBER(1)	:= 0;
BEGIN 

	o_user_name			:= TRIM(i_user_name);
	o_package_name		:= TRIM(i_package_name);
	o_variable_name		:= TRIM(i_variable_name);

	-- If the input user name is empty, current_user should be considered.
	IF o_user_name IS NULL
	THEN
		o_user_name	:= USER;
	END IF;

	-- The input package name should not be empty.
	IF o_package_name IS NULL
	THEN
		l_error_handle_i	:= 1;
		RAISE_APPLICATION_ERROR (-20000, 'The package name cannot be empty.');
	END IF;

	-- The input variable name should not be empty.
	IF o_variable_name IS NULL
	THEN
		l_error_handle_i	:= 1;
		RAISE_APPLICATION_ERROR (-20000, 'The package variable name cannot be empty.');
	END IF;

	o_user_name		:= UPPER(o_user_name);
	o_package_name	:= UPPER(o_package_name);
	o_variable_name	:= UPPER(o_variable_name);

EXCEPTION
	WHEN OTHERS THEN
		IF l_error_handle_i	= 1
		THEN
			RAISE_APPLICATION_ERROR (-20000, SQLERRM);
		ELSE
			RAISE_APPLICATION_ERROR (-20000,'Unexpected error (' || SQLCODE || ' - ' || SQLERRM || ') occurred in mig_fn_pkg_var_chek');
		END IF;
END;
/




CREATE OR REPLACE FUNCTION MIG_ORA_EXT.mig_fn_get_pkg_variable
-- This function is used to get the package variable value.
	( i_user_name			IN		VARCHAR
	, i_package_name		IN		VARCHAR
	, i_variable_name		IN		VARCHAR
	) 
RETURN VARCHAR
AS 
	l_user_name				VARCHAR(64);
	l_package_name			VARCHAR(64);
	l_variable_name			VARCHAR(64);
	l_pkg_varbl_val			VARCHAR(1024);
	l_no_tab_0_rec			CHAR(2);
BEGIN 
	-- Validate the input parameters
	MIG_ORA_EXT.mig_fn_pkg_var_chek(i_user_name, i_package_name, i_variable_name, l_user_name, l_package_name, l_variable_name);
	
	BEGIN
		-- Get the package variable value
		SELECT variable_value
		  INTO l_pkg_varbl_val
		  FROM #tmp_mig_pkg_variables
		 WHERE user_name 	    = l_user_name
		   AND package_name 	= l_package_name
		   AND variable_name 	= l_variable_name
		 LIMIT 1;

	EXCEPTION 
	    WHEN NO_DATA_FOUND THEN
			l_no_tab_0_rec	:= '0R';
		WHEN OTHERS THEN
			l_no_tab_0_rec	:= 'NT';
	END;
	
	IF l_no_tab_0_rec = 'NT'
	/* If table not exists, create the local temp table */
	THEN
		MIG_ORA_EXT.mig_fn_pkg_var_cret_tmp_table ( );
		l_no_tab_0_rec	:= '0R';
	END IF;

	IF l_no_tab_0_rec	= '0R'
	/* if package variables not loaded into temp table, load the package variables  */
	THEN
		l_pkg_varbl_val := MIG_ORA_EXT.mig_fn_pkg_var_load_tmp_table ( l_user_name, l_package_name, l_variable_name );
	END IF;

    RETURN l_pkg_varbl_val;

END;
/


CREATE OR REPLACE PROCEDURE MIG_ORA_EXT.mig_fn_set_pkg_variable
-- This procedure is used to set the package variable value.
	( i_user_name			IN		VARCHAR
	, i_package_name		IN		VARCHAR
	, i_variable_name		IN		VARCHAR
	, i_variable_value		IN		VARCHAR
	) 
AS 
	l_user_name				VARCHAR(64);
	l_package_name			VARCHAR(64);
	l_variable_name			VARCHAR(64);
	l_no_tab_0_rec			CHAR(2);
	l_pkg_varbl_val			VARCHAR(1024);
BEGIN 
	-- Validate the input parameters
	MIG_ORA_EXT.mig_fn_pkg_var_chek(i_user_name, i_package_name, i_variable_name, l_user_name, l_package_name, l_variable_name);
	
	BEGIN
		-- Set the package variable value
		UPDATE #tmp_mig_pkg_variables
		   SET variable_value 	= i_variable_value
		 WHERE user_name 	    = l_user_name
		   AND package_name 	= l_package_name
		   AND variable_name 	= l_variable_name;

		IF SQL%ROWCOUNT = 0
		THEN
			l_no_tab_0_rec	:= '0R';	
		END IF;

	EXCEPTION 
		WHEN OTHERS THEN
			l_no_tab_0_rec	:= 'NT';
	END;
	
	IF l_no_tab_0_rec = 'NT'
	/* If table not exists, create the local temp table */
	THEN
		MIG_ORA_EXT.mig_fn_pkg_var_cret_tmp_table ( );
		l_no_tab_0_rec	:= '0R';
	END IF;

	IF l_no_tab_0_rec	= '0R'
	/* if package variables not loaded into temp table, load the package variables  */
	THEN
	   l_pkg_varbl_val := MIG_ORA_EXT.mig_fn_pkg_var_load_tmp_table ( l_user_name, l_package_name, l_variable_name, i_variable_value );
	END IF;

END;
/

