
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(CURRENT_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 'TEXT'						NOT NULL
	 , constant_i 				BOOLEAN			DEFAULT FALSE						NOT NULL
	 , default_value 			TEXT 			DEFAULT NULL 	
	 , runtime_exec_i           BOOLEAN			DEFAULT FALSE						NOT NULL
	 , CONSTRAINT 				pk_mig_pkg_variables 
	   PRIMARY KEY 				( user_name, package_name, variable_name )
	 ) DISTRIBUTE BY HASH ( user_name, package_name );


CREATE OR REPLACE FUNCTION MIG_ORA_EXT.mig_fn_pkg_var_cret_tmp_table ( )
-- This function is used to create the local temp table to hold the package variable details 
RETURNS VOID
AS 
$$
DECLARE	l_rec_chk			NUMBER(1);
BEGIN 

	CREATE LOCAL TEMPORARY TABLE tmp_mig_pkg_variables
		 ( user_name 				TEXT
		 , package_name 			TEXT
		 , spec_or_body 			CHAR(1)	
		 , variable_name 			TEXT
		 , variable_type 			TEXT
		 , constant_i 				BOOLEAN
		 , variable_value 			TEXT
		 ) 
		 ON COMMIT PRESERVE ROWS
		 DISTRIBUTE BY HASH ( user_name, package_name );

EXCEPTION
  WHEN OTHERS THEN
		RAISE EXCEPTION 'Unexpected error (% - %) occurred in mig_fn_pkg_var_cret_tmp_table', SQLCODE, SQLERRM;

END;
$$
LANGUAGE plpgsql;


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		TEXT
	, i_package_name		IN		TEXT	
	, i_variable_name		IN		TEXT
	, i_variable_value		IN		TEXT		:= 'MIG_PKG_VAR_VAL'
	) 
RETURNS TEXT
AS 
$$
DECLARE	l_variable_value		TEXT;
        l_cur_var_value			TEXT;
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 '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 EXCEPTION 'Unexpected error (% - %) occurred in mig_fn_pkg_var_load_tmp_table', SQLCODE, SQLERRM;
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_ORA_EXT.mig_fn_pkg_var_chek
-- This function is used to check the input parameters
	( i_user_name			INOUT		TEXT
	, i_package_name		INOUT		TEXT	
	, i_variable_name		INOUT		TEXT	
	) 
AS 
$$
DECLARE	l_object_exists			NUMBER(1);
		l_error_handle_i		NUMBER(1)	:= 0;
BEGIN 

	i_user_name			:= TRIM(i_user_name);
	i_package_name		:= TRIM(i_package_name);
	i_variable_name		:= TRIM(i_variable_name);

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

	-- The input package name should not be empty.
	IF i_package_name IS NULL
	THEN
		l_error_handle_i	:= 1;
		RAISE EXCEPTION 'The package name cannot be empty.';
	END IF;

	-- The input variable name should not be empty.
	IF i_variable_name IS NULL
	THEN
		l_error_handle_i	:= 1;
		RAISE EXCEPTION 'The package variable name cannot be empty.';
	END IF;
	
	i_user_name		:= UPPER(i_user_name);
	i_package_name	:= UPPER(i_package_name);
	i_variable_name	:= UPPER(i_variable_name);

EXCEPTION
	WHEN OTHERS THEN
		IF l_error_handle_i	= 1
		THEN
			RAISE EXCEPTION '%', SQLERRM;
		ELSE
			RAISE EXCEPTION 'Unexpected error (% - %) occurred in mig_fn_pkg_var_chek', SQLCODE, SQLERRM;
		END IF;
END;
$$
LANGUAGE plpgsql;



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		TEXT
	, i_package_name		IN		TEXT
	, i_variable_name		IN		TEXT
	) 
RETURNS TEXT
AS 
$$
DECLARE	l_pkg_varbl_val			TEXT;
		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);
	
	BEGIN
		-- Get the package variable value
		SELECT variable_value
		  INTO l_pkg_varbl_val
		  FROM tmp_mig_pkg_variables
		 WHERE user_name 	    = i_user_name
		   AND package_name 	= i_package_name
		   AND variable_name 	= i_variable_name
		 LIMIT 1;

	EXCEPTION 
	    WHEN NO_DATA_FOUND THEN
			l_no_tab_0_rec	:= '0R';
		WHEN UNDEFINED_TABLE THEN
			l_no_tab_0_rec	:= 'NT';
	END;
	
	IF l_no_tab_0_rec = 'NT'
	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'
	THEN
		l_pkg_varbl_val := MIG_ORA_EXT.mig_fn_pkg_var_load_tmp_table ( i_user_name, i_package_name, i_variable_name );
	END IF;

    RETURN l_pkg_varbl_val;

END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION MIG_ORA_EXT.mig_fn_set_pkg_variable
-- This function is used to set the package variable value.
	( i_user_name			IN		TEXT
	, i_package_name		IN		TEXT
	, i_variable_name		IN		TEXT
	, i_variable_value		IN		TEXT
	) 
RETURNS VOID
AS 
$$
DECLARE	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);
	
	BEGIN
		-- Set the package variable value
		UPDATE tmp_mig_pkg_variables
		   SET variable_value 	= i_variable_value
		 WHERE user_name 	    = i_user_name
		   AND package_name 	= i_package_name
		   AND variable_name 	= i_variable_name;

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

	EXCEPTION 
		WHEN UNDEFINED_TABLE THEN
			l_no_tab_0_rec	:= 'NT';
	END;
	
	IF l_no_tab_0_rec = 'NT'
	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'
	THEN
	   MIG_ORA_EXT.mig_fn_pkg_var_load_tmp_table ( i_user_name, i_package_name, i_variable_name, i_variable_value );
	END IF;

END;
$$
LANGUAGE plpgsql;

	