CREATE OR REPLACE PACKAGE BODY PS_Global 
IS

-- Define table types for each datatype supported by PSGlobal
-- Note: maximum size of 1000 bytes allowed for VARCHAR2 values..

	TYPE number_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
	TYPE varchar2_tabtype IS TABLE OF VARCHAR2(1000) INDEX BY 
BINARY_INTEGER;
	TYPE date_tabtype IS 	TABLE OF DATE INDEX BY BINARY_INTEGER;
	TYPE stack_tabtype IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

-- Table type of named globals (don't need 1000 characters, so have to 
-- a separate table type for this one). Maximum length of global name in
-- this implementation is 60 bytes.

	TYPE name_tabtype IS 	TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
 
-- Define the tables which hold the global values.

	number_table number_tabtype;
	varchar2_table varchar2_tabtype;
	date_table date_tabtype;
	name_table name_tabtype;
	
-- Starting positions for named global values of various datatypes.
-- The allowable rows are segregated to avoid conflict in the names table.

	number_start_row CONSTANT BINARY_INTEGER := -1;
	varchar2_start_row CONSTANT BINARY_INTEGER := -10001;
	date_start_row CONSTANT BINARY_INTEGER := -20001;

-- Variable to keep track of maximum row number in use for each table.
-- There is a separate number for globals-by-number (rows_in...) and 
-- globals-by-name (names_in...) even though they are in the same table.

	rows_in_number_table BINARY_INTEGER := 0;
	rows_in_varchar2_table BINARY_INTEGER := 0;
	rows_in_date_table BINARY_INTEGER := 0;
	names_in_number_table BINARY_INTEGER := 0;
	names_in_varchar2_table BINARY_INTEGER := 0;
	names_in_date_table BINARY_INTEGER := 0;

-- Define the stacks to keep track of available rows. Need a separate stack
-- for globals-by-name and globals-by-number.

	number_stack stack_tabtype;
	varchar2_stack stack_tabtype;
	date_stack stack_tabtype;
	named_number_stack stack_tabtype;
	named_varchar2_stack stack_tabtype;
	named_date_stack stack_tabtype;

-- Variables to keep track of maximum row number in use in each stack.

	rows_in_number_stack BINARY_INTEGER := 0;
	rows_in_varchar2_stack BINARY_INTEGER := 0;
	rows_in_date_stack BINARY_INTEGER := 0;
	names_in_number_stack BINARY_INTEGER := 0;
	names_in_varchar2_stack BINARY_INTEGER := 0;
	names_in_date_stack BINARY_INTEGER := 0;

-- Magic values in the package: many of my internal modules (called by the 
-- programs listed in the specification, but not visible outside of the 
-- package) perform actions based on the datatype. Rather than hard-code
-- those datatype names throughout the programs, I assign them as constants
-- in this section and then reference the constants, instead of the 
-- literals.

	number_stg CONSTANT VARCHAR2(6) := 'NUMBER';
	varchar2_stg CONSTANT VARCHAR2(8) := 'VARCHAR2';
	date_stg CONSTANT VARCHAR2(4) := 'DATE';
	named_number_stg CONSTANT VARCHAR2(12) := 'NAMED_NUMBER';
	named_varchar2_stg CONSTANT VARCHAR2(14) := 'NAMED_VARCHAR2';
	named_date_stg CONSTANT VARCHAR2(10) := 'NAMED_DATE';
/*
||--------------------- End of Variables: Time for Programs! --------------
---
||
|| Before I get to the implementation of the "public" package modules -- 
those
|| which users of PSGlobal can call -- I first need to define some 
"private"
|| modules which the public modules call, but are inaccessible outside of 
the
|| package. These modules are:
||
||		in_stack		Return TRUE if a row is currently in the stack
||						of reusable rows. (a global was cleared).
||
||		pop				Pop from the stack the last row added.
||
||		push			Push a row onto the stack.
||
||		next_row		Get the next available row for one of the global values 
||						tables. Next row might come from stack of reusable rows.
||
||		name_row		Find the row for a specified global name.
||
||		name_type		Return the datatype of the global based on its row 
number.
||-------------------------------------------------------------------------
---
*/

	FUNCTION in_stack (type_in IN VARCHAR2, binary_in IN BINARY_INTEGER)
		RETURN BOOLEAN
	IS
	/*
	|| For each type of stack (number, named_number, etc.), loop through 
the
	|| rows in the table and see if the specified row (binary_in) is 
present.
	|| This module is called by the globals-by-number getval procedures to 
see 
	|| if you are asking for a value which has been erased. Why, you might 
	|| ask, didn't I create a local module containing the loop you see 
below
	|| and then call that loop in the body? When you pass tables as 
parameters,
	|| you dramatically increase performance overhead.
	||
	|| Very repetitive code, isn't it? You'll see lots of that below...
	|| But your learn through repetition, right?
	*/
		type_int VARCHAR2(30) := UPPER (type_in);
		no_match BOOLEAN := TRUE;
		return_value BOOLEAN := FALSE;
		current_row BINARY_INTEGER := 1;
	BEGIN
	IF type_int = number_stg
	THEN
		WHILE no_match AND current_row <= rows_in_number_stack
		LOOP
			no_match := number_stack (current_row) != binary_in;
			IF no_match THEN current_row := current_row + 1; END IF;
		END LOOP;
		RETURN current_row <= rows_in_number_stack;
	ELSIF type_int = varchar2_stg
	THEN
		WHILE no_match AND current_row <= rows_in_varchar2_stack
		LOOP
			no_match := varchar2_stack (current_row) != binary_in;
			IF no_match THEN current_row := current_row + 1; END IF;
		END LOOP;
		RETURN current_row <= rows_in_varchar2_stack;
	ELSIF type_int = date_stg
	THEN
		WHILE no_match AND current_row <= rows_in_date_stack
		LOOP
			no_match := date_stack (current_row) != binary_in;
			IF no_match THEN current_row := current_row + 1; END IF;
		END LOOP;
		RETURN current_row <= rows_in_date_stack;
	ELSIF type_int = named_varchar2_stg
	THEN
		WHILE no_match AND current_row <= names_in_varchar2_stack
		LOOP
			no_match := named_varchar2_stack (current_row) != binary_in;
			IF no_match THEN current_row := current_row + 1; END IF;
		END LOOP;
		RETURN current_row <= rows_in_varchar2_stack;
	ELSIF type_int = named_date_stg
	THEN
		WHILE no_match AND current_row <= names_in_date_stack
		LOOP
			no_match := named_date_stack (current_row) != binary_in;
			IF no_match THEN current_row := current_row + 1; END IF;
		END LOOP;
		RETURN current_row <= rows_in_date_stack;
	END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;
			
	FUNCTION pop (type_in IN VARCHAR2) RETURN BINARY_INTEGER 
	IS
	/*
	|| For each type of stack, pop the last value added to the table
	|| and decrement the total number of rows in the stack.
	*/
		type_int VARCHAR2(30) := UPPER (type_in);
		temp_row BINARY_INTEGER;
	BEGIN
		IF type_int = number_stg
		THEN
			IF rows_in_number_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := rows_in_number_stack;
				rows_in_number_stack := rows_in_number_stack - 1;
				RETURN number_stack (temp_row);
			END IF;
		ELSIF type_int = varchar2_stg
		THEN
			IF rows_in_varchar2_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := rows_in_varchar2_stack;
				rows_in_varchar2_stack := rows_in_varchar2_stack - 1;
				RETURN varchar2_stack (temp_row);
			END IF;
		ELSIF type_int = date_stg
		THEN
			IF rows_in_date_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := rows_in_date_stack;
				rows_in_date_stack := rows_in_date_stack - 1;
				RETURN date_stack (temp_row);
			END IF;
		ELSIF type_int = named_number_stg
		THEN
			IF names_in_number_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := names_in_number_stack;
				names_in_number_stack := names_in_number_stack - 1;
				RETURN named_number_stack (temp_row);
			END IF;
		ELSIF type_int = named_varchar2_stg
		THEN
			IF names_in_varchar2_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := names_in_varchar2_stack;
				names_in_varchar2_stack := names_in_varchar2_stack - 1;
				RETURN named_varchar2_stack (temp_row);
			END IF;
		ELSIF type_int = named_date_stg
		THEN
			IF names_in_date_stack = 0
			THEN
				RETURN NULL;
			ELSE
				temp_row := names_in_date_stack;
				names_in_date_stack := names_in_date_stack - 1;
				RETURN named_date_stack (temp_row);
			END IF;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE push (type_in IN VARCHAR2, row_in IN BINARY_INTEGER) 
	IS
	/*
	|| For each type of stack, push the specified row (row_in) onto
	|| appropriate stack. Increment the total number of rows in the
	|| stack.
	*/
	type_int VARCHAR2(30) := UPPER (type_in);
	BEGIN
		IF type_int = number_stg
		THEN
			rows_in_number_stack := rows_in_number_stack + 1;
			number_stack (rows_in_number_stack) := row_in;
		ELSIF type_int = varchar2_stg
		THEN
			rows_in_varchar2_stack := rows_in_varchar2_stack + 1;
			varchar2_stack (rows_in_varchar2_stack) := row_in;
		ELSIF type_int = date_stg
		THEN
			rows_in_date_stack := rows_in_date_stack + 1;
			date_stack (rows_in_date_stack) := row_in;
		ELSIF type_int = named_number_stg
		THEN
			names_in_number_stack := names_in_number_stack + 1;
			named_number_stack (names_in_number_stack) := row_in;
		ELSIF type_int = named_varchar2_stg
		THEN
			names_in_varchar2_stack := names_in_varchar2_stack + 1;
			named_varchar2_stack (names_in_varchar2_stack) := row_in;
		ELSIF type_int = named_date_stg
		THEN
			names_in_date_stack := names_in_date_stack + 1;
			named_date_stack (names_in_date_stack) := row_in;
		ELSE
			RAISE global_failure;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

-- The next_row function

	FUNCTION next_row (type_in IN VARCHAR2) RETURN BINARY_INTEGER
	IS
	/*
	|| The next_row function returns the next available row for a globals
	|| value table (as specified by the type_in). It immediately calls a
	|| local module named get_next. This program first checks to see if it 
	|| can pop a row off the associated reusable rows stack. If not, then 
it 
	|| increments the number of rows for the associated table and returns
	|| that value. Notice that the computation for next_row is very 
different
	|| for a named global (type_in LIKE 'NAMED%'). For globals-by-number, 
the
	|| next row is simply computed by adding 1 to the current row. For 
globals-
	|| by-name, the number of rows in the table is always positive, but the
	|| actual next row is both negative and is shifted into its segregated
	|| set of rows (-1 through -10000, -10001 through -20001, and less than
	|| -20000), so start_row_in must be used when computing the next_row.
	*/
		type_int VARCHAR2(30) := UPPER (type_in);
		return_value BINARY_INTEGER;
		
		PROCEDURE get_next 
			(type_in IN VARCHAR2, 
			 max_rows_inout IN OUT NUMBER,
			 new_row_out IN OUT BINARY_INTEGER, 
			 start_row_in IN BINARY_INTEGER := NULL) IS
		BEGIN
			new_row_out := pop (number_stg);
			IF new_row_out IS NULL
			THEN
				max_rows_inout := max_rows_inout + 1;
				IF type_in LIKE 'NAMED%'
				THEN
					-- Compute negative, shifted next row for named global.
					new_row_out := (-1*max_rows_inout) + start_row_in + 1;
				ELSE
					-- Just return incremented row value for globals-by-number.
					new_row_out := max_rows_inout;
				END IF;
			END IF;
		END;		
	BEGIN
		/*
		|| Main body of next_row. For each type of global, call get_next.
		|| For globals-by-name must also pass the appropriate starting row
		|| in the global values table.
		*/ 
		IF type_int = number_stg
		THEN 
			get_next (number_stg, rows_in_number_table, return_value);
		ELSIF type_int = varchar2_stg
		THEN 
			get_next (varchar2_stg, rows_in_varchar2_table, return_value);
		ELSIF type_int = date_stg
		THEN 
			get_next (date_stg, rows_in_date_table, return_value);
		ELSIF type_int = named_number_stg
		THEN 
			get_next (named_number_stg, names_in_number_table, 	
						 return_value, number_start_row);
		ELSIF type_int = named_varchar2_stg
		THEN 
			get_next (named_varchar2_stg, names_in_varchar2_table, 
						 return_value, varchar2_start_row);
		ELSIF type_int = named_date_stg
		THEN 
			get_next (named_date_stg, names_in_date_table, 
						 return_value, date_start_row);
		ELSE
			RAISE global_failure;
		END IF;
		RETURN return_value;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

-- Function returns row of name in name_table.

	FUNCTION name_row (type_in IN VARCHAR2, name_in IN VARCHAR2) 
		RETURN BINARY_INTEGER
	IS
	/*
	|| The name_row function searches through the global names and returns
	|| the row in which the data for that named global can be found.
	*/
		no_match BOOLEAN := TRUE;
		return_value BINARY_INTEGER;
		max_row BINARY_INTEGER;
		adj_row BINARY_INTEGER;
		name_int VARCHAR2(60) := UPPER (name_in);
	BEGIN
		/*
		|| First based on the datatype, set the amount by which I will 
adjust
		|| the current row to check to see if at end of current set and also
		|| set the maximum number of rows for searching.
		*/
		IF type_in = number_stg 
		THEN 
			adj_row := number_start_row;
			max_row := names_in_number_table;
		ELSIF type_in = varchar2_stg 
		THEN 
			adj_row := varchar2_start_row;
			max_row := names_in_varchar2_table;
		ELSIF type_in = date_stg     
		THEN 
			adj_row := date_start_row;
			max_row := names_in_date_table;
		END IF;
		return_value := adj_row;
		/*
		|| Loop through the names table until I find a match or have run out
		|| of rows to check.
		*/
		WHILE no_match AND ABS(return_value-adj_row)+1 <= max_row	
		LOOP
			no_match := name_table (ABS (return_value)) != name_int;
			IF no_match THEN return_value := return_value - 1; END IF;
		END LOOP;

		IF no_match OR max_row = 0
		THEN
			RETURN NULL;
		ELSE
			RETURN return_value;
		END IF;
	END;
/*
||-------------------------- Public Modules -------------------------------
-
||
|| Now we are done with the private modules. It's time to create all the 
|| overloaded procedures and functions you saw in the package 
specification.
|| You will notice that each of these modules is very concise. They are all
|| built upon the private modules or other public modules.
||
|| I will place comments only in the first of each set of overloaded 
modules,
|| since the code is the same in each one (usually with some variation, as
|| will be noted, between globals-by-number and globals-by-name).
||
||-------------------------------------------------------------------------
-
*/

-- The overloaded globals-by-number putval procedures.

	FUNCTION putval (number_in IN NUMBER) RETURN BINARY_INTEGER
	IS
	/*
	|| Get the next row from the next_row function. Assign that row's
	|| value to the table and return the row.
	*/
		new_row BINARY_INTEGER;
	BEGIN
		new_row := next_row (number_stg);
		number_table (new_row) := number_in;
		RETURN new_row;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	FUNCTION putval (varchar2_in IN VARCHAR2) RETURN BINARY_INTEGER
	IS
		new_row BINARY_INTEGER;
	BEGIN
		new_row := next_row (varchar2_stg);
		varchar2_table (new_row) := varchar2_in;
		RETURN new_row;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	FUNCTION putval (date_in IN DATE) RETURN BINARY_INTEGER
	IS
		new_row BINARY_INTEGER;
	BEGIN
		new_row := next_row (date_stg);
		date_table (new_row) := date_in;
		RETURN new_row;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

-- The overloaded globals-by-name putval procedures.

	PROCEDURE putval (name_in IN VARCHAR2, number_in IN NUMBER) 
	IS
	/*
	|| First I call the name_row function to see if this global is
	|| already stored in the names table. I do this by setting the 
	|| default value for current_row from name_row. Then in the body
	|| of putval, if current_row is NOT NULL, I simply change the
	|| corresponding value (this is "newval" for named globals). If
	|| it is NULL, then we have a new global, so I call next_row to 
	|| get the next row. I save the name to the names table (using
	|| ABS to make sure I store the name in a positive row) and then
	|| add the value to the global values table.
	*/
		current_row BINARY_INTEGER := name_row (number_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			current_row := next_row (named_number_stg);
			name_table (ABS (current_row)) := UPPER (name_in);
		END IF;
		number_table (current_row) := number_in;
	END;

	PROCEDURE putval (name_in IN VARCHAR2, varchar2_in IN VARCHAR2) 
	IS
		current_row BINARY_INTEGER := name_row (varchar2_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			current_row := next_row (named_varchar2_stg);
			name_table (ABS (current_row)) := UPPER (name_in);
		END IF;
		varchar2_table (current_row) := varchar2_in;
	END;

	PROCEDURE putval (name_in IN VARCHAR2, date_in IN DATE) 
	IS
		current_row BINARY_INTEGER := name_row (date_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			current_row := next_row (named_date_stg);
			name_table (ABS (current_row)) := UPPER (name_in);
		END IF;
		date_table (current_row) := date_in;
	END;

-- The overloaded globals-by-number getval procedures

	PROCEDURE getval (row_in IN BINARY_INTEGER, number_out OUT NUMBER)
	IS
	/*
	|| I first check to see whether the the specified row is either in
	|| the stack of reusable rows (cleared globals) or outside of the
	|| valid range of rows for this table. Notice that I only check the
	|| stack if the row is otherwise valid. I don't want to perform
	|| searches through tables unless absolutely necessary for performance
	|| reasons.
	*/
	BEGIN
		IF row_in NOT BETWEEN 1 AND rows_in_number_table
		THEN
			RAISE global_undefined;
		ELSIF in_stack (number_stg, row_in) 
		THEN
			RAISE global_undefined;
		ELSE
			number_out := number_table (row_in);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE getval 
		(row_in IN BINARY_INTEGER, varchar2_out OUT VARCHAR2)
	IS
	BEGIN
		IF row_in NOT BETWEEN 1 AND rows_in_varchar2_table
		THEN
			RAISE global_undefined;
		ELSIF in_stack (varchar2_stg, row_in) 
		THEN
			RAISE global_undefined;
		ELSE
			varchar2_out := varchar2_table (row_in);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE getval (row_in IN BINARY_INTEGER, date_out OUT DATE)
	IS
	BEGIN
		IF row_in NOT BETWEEN 1 AND rows_in_date_table
		THEN
			RAISE global_undefined;
		ELSIF in_stack (date_stg, row_in) 
		THEN
			RAISE global_undefined;
		ELSE
			date_out := date_table (row_in);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

-- The overloaded globals-by-name getval procedures

	PROCEDURE getval (name_in IN VARCHAR2, number_out OUT NUMBER)
	IS
	/*
	|| Get the row for this global name. If it returns NULL, then
	|| it is an undefined global. Otherwise, retrieve the value from 
	|| the appropriate global values table.
	*/
		current_row BINARY_INTEGER := name_row (number_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			RAISE global_undefined;
		ELSE
			number_out := number_table (current_row);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE getval (name_in IN VARCHAR2, varchar2_out OUT VARCHAR2)
	IS
		current_row BINARY_INTEGER := name_row (varchar2_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			RAISE global_undefined;
		ELSE
			varchar2_out := varchar2_table (current_row);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE getval (name_in IN VARCHAR2, date_out OUT DATE)
	IS
		current_row BINARY_INTEGER := name_row (date_stg, name_in);
	BEGIN
		IF current_row IS NULL
		THEN
			RAISE global_undefined;
		ELSE
			date_out := date_table (current_row);
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

-- The overloaded globals-by-name default value procedures.
-- Note: there are no defval procedures for globals-by-number.

	PROCEDURE defval (number_in IN NUMBER, name_in IN VARCHAR2)
	IS
	/* 
	|| Try to get the value with getval. If getval raised the package 
exception
	|| global_undefined, then call putval to define the global with the
	|| default value.
	*/
		temp_number NUMBER;
	BEGIN
		getval (name_in, temp_number);
	EXCEPTION
		WHEN global_undefined THEN putval (name_in, number_in);
	END;

	PROCEDURE defval (varchar2_in IN VARCHAR2, name_in IN VARCHAR2)
	IS
		temp_varchar2 VARCHAR2(1000);
	BEGIN
		getval (name_in, temp_varchar2);
	EXCEPTION
		WHEN global_undefined THEN putval (name_in, varchar2_in);
	END;

	PROCEDURE defval (date_in IN DATE, name_in IN VARCHAR2)
	IS
		temp_date DATE;
	BEGIN
		getval (name_in, temp_date);
	EXCEPTION
		WHEN global_undefined THEN putval (name_in, date_in);
	END;

-- The overloaded globals-by-number newval procedures
-- Note: there are no newval procedures for globals-by-name.

	PROCEDURE newval (number_in IN NUMBER, row_in IN BINARY_INTEGER)
	IS
	/*
	|| Perform a getval to see if the specified row points to a defined
	|| global. If so, write the new value into the table. If not, raise
	|| the packaged exception.
	*/
		temp_value NUMBER;
	BEGIN
		getval (row_in, temp_value);

		--If still here and not in exception section, then the row is valid.
		number_table (row_in) := number_in;

	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END newval;

	PROCEDURE newval (varchar2_in IN VARCHAR2, row_in IN BINARY_INTEGER)
	IS
		temp_value VARCHAR2(1000);
	BEGIN
		getval (row_in, temp_value);
		varchar2_table (row_in) := varchar2_in;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END newval;

	PROCEDURE newval (date_in IN DATE, row_in IN BINARY_INTEGER)
	IS
		temp_value DATE;
	BEGIN
		getval (row_in, temp_value);
		date_table (row_in) := date_in;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END newval;

/*
|| Now, the different variations of clrval: 
||
|| 1. For globals by number: For each datatype a procedure to clear a 
||		specified global by row number, and then a procedure to either 
||		clear ALL globals for that datatype or clear a specified global
||		which might have been passed as a character string (ie, '34').
||
|| 2. For globals by name: a single clrval procedure regardless of the
||		datatype since I look up the name and figure out the datatype from
||		from the row number of the name.
||
*/

	PROCEDURE clrval_number (row_in IN BINARY_INTEGER) 
	IS
	/*
	|| Specified a specific row number to clear. If it is equal to the 
	|| last row used, just decrement the number of rows in the table. That
	|| value will now be ignored. Otherwise, push the row onto the stack
	|| of reusable rows. Finally, NULL out the table entry for that row.
	*/
	BEGIN	
		IF row_in = rows_in_number_table
		THEN
			rows_in_number_table := rows_in_number_table - 1;
			number_table (row_in) := NULL;
		ELSIF row_in BETWEEN 1 AND rows_in_number_table - 1
		THEN
			push (number_stg, row_in);
			number_table (row_in) := NULL;
		ELSE
			-- Request to clear undefined global. Just ignore.
			NULL;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval_number (action_in IN VARCHAR2) 
	IS
	/*
	|| Either "ALL" or a number in quotes. If "ALL" then I set to zero
	|| the number of rows in tables and stacks and empty the global tables
	|| with an aggregate assignment from a "null" table. This is the 
closest
	|| you can come to actually erasing or deleting a table.
	*/
		empty_number_table number_tabtype;
		empty_stack stack_tabtype;
		maybe_number NUMBER;
	BEGIN	
		IF UPPER (action_in) = 'ALL'
		THEN
			
			number_table := empty_number_table;
			rows_in_number_table := 0;
			number_stack := empty_stack;
			rows_in_number_stack := 0;
			names_in_number_table := 0;
			named_number_stack := empty_stack;
			names_in_number_stack := 0;
		ELSE
			/*
			|| Maybe they passed a number in quotes. Try to convert
			|| and if successful (that is, I am around to execute next line)
			|| then call the other version of clrval.
			*/
			maybe_number := TO_NUMBER (action_in);
			clrval_number (maybe_number);
		END IF;
	EXCEPTION
		WHEN VALUE_ERROR THEN NULL;
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval_varchar2 (row_in IN BINARY_INTEGER) 
	IS
	BEGIN	
		IF row_in = rows_in_varchar2_table
		THEN
			rows_in_varchar2_table := rows_in_varchar2_table - 1;
			varchar2_table (row_in) := NULL;
		ELSIF row_in BETWEEN 1 AND rows_in_varchar2_table - 1
		THEN
			push (varchar2_stg, row_in);
			varchar2_table (row_in) := NULL;
		ELSE
			-- Request to clear undefined global. Just ignore.
			NULL;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval_varchar2 (action_in IN VARCHAR2) 
	IS
		empty_varchar2_table varchar2_tabtype;
		empty_stack stack_tabtype;
		maybe_number NUMBER;
	BEGIN	
		IF UPPER (action_in) = 'ALL'
		THEN
			varchar2_table := empty_varchar2_table;
			rows_in_varchar2_table := 0;
			varchar2_stack := empty_stack;
			rows_in_varchar2_stack := 0;
			names_in_varchar2_table := 0;
			named_varchar2_stack := empty_stack;
			names_in_varchar2_stack := 0;
		ELSE
			maybe_number := TO_NUMBER (action_in);
			clrval_varchar2 (maybe_number);
		END IF;
	EXCEPTION
		WHEN VALUE_ERROR THEN NULL;
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval_date (row_in IN BINARY_INTEGER) 
	IS
	BEGIN	
		IF row_in = rows_in_date_table
		THEN
			rows_in_date_table := rows_in_date_table - 1;
			date_table (row_in) := NULL;
		ELSIF row_in BETWEEN 1 AND rows_in_date_table - 1
		THEN
			push (date_stg, row_in);
			date_table (row_in) := NULL;
		ELSE
			-- Request to clear undefined global. Just ignore.
			NULL;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval_date (action_in IN VARCHAR2) 
	IS
		empty_date_table date_tabtype;
		empty_stack stack_tabtype;
		maybe_number NUMBER;
	BEGIN	
		IF UPPER (action_in) = 'ALL'
		THEN
			date_table := empty_date_table;
			rows_in_date_table := 0;
			date_stack := empty_stack;
			rows_in_date_stack := 0;
			names_in_date_table := 0;
			named_date_stack := empty_stack;
			names_in_date_stack := 0;
		ELSE
 			maybe_number := TO_NUMBER (action_in);
			clrval_date (maybe_number);
		END IF;
	EXCEPTION
		WHEN VALUE_ERROR THEN NULL;
		WHEN OTHERS THEN RAISE global_failure;
	END;

	PROCEDURE clrval (name_in IN VARCHAR2)
	IS
	/*
	|| The clrval procedure for globals-by-name. Since only the name is
	|| specified, I need to search in the names for numbers, varchar2s
	|| and dates until I find a match. I create a local module to 
	|| consolidate what code I can without passing a table as a parameter.
	*/
		current_row BINARY_INTEGER := NULL;

		PROCEDURE find_row (name_in IN VARCHAR2, 
								type_in IN VARCHAR2, 
								named_type_in IN VARCHAR2, 
								max_rows_inout IN OUT NUMBER,
								found_row_inout IN OUT BINARY_INTEGER)
		IS
		/*
		|| Calls name_row for the specified datatype (type_in) to see if we
		|| have a match on the name. If we do then (a) if the row is same as
		|| last row, then decrement the number of rows, otherwise (b) push
		|| this row on the appropriate resuable rows stack (driven by
		|| named_type_in). 
		*/
		BEGIN
			found_row_inout := name_row (type_in, name_in);
			IF found_row_inout IS NOT NULL
			THEN
				IF found_row_inout = max_rows_inout
				THEN
					max_rows_inout := max_rows_inout - 1;
				ELSE
					push (named_type_in, found_row_inout);
				END IF;
			END IF;
		END;

	BEGIN
		/*
		|| Main body of clrval: first check numbers, then strings, then 
dates.
		*/
		find_row (name_in, number_stg, named_number_stg, 
					 names_in_number_table, current_row);
		IF current_row IS NOT NULL
		THEN
			number_table (current_row) := NULL;
		ELSE
			-- Not a number. Maybe a character string global.
			find_row 	(name_in, varchar2_stg, named_varchar2_stg,
						 names_in_varchar2_table, current_row);
			IF current_row IS NOT NULL
			THEN
				varchar2_table (current_row) := NULL;
			ELSE
				-- Not a varchar2. Maybe a date global.
				find_row (name_in, date_stg, named_date_stg, 
							 names_in_date_table, current_row);
				IF current_row IS NOT NULL 
					THEN date_table (current_row) := NULL;
				ELSE
					-- No match (bet they waited a while to find out!). 
					-- Just ignore request to clear.
					NULL;
				END IF;
			END IF;
		END IF;
	END;

	PROCEDURE clrval_all_globals 
	IS
	/*
	|| Call the other clrval procedures to clear out everything of all
	|| the different datatypes.
	*/
	BEGIN
		clrval_number ('ALL');
		clrval_varchar2 ('ALL');
		clrval_date ('ALL');
	EXCEPTION
		WHEN OTHERS THEN RAISE global_failure;
	END;

END PS_Global;
/
show errors