Today's WTF moment is a bit of a monster, so I will try to explain what it does, and you can skim the actual code (this is probably a good idea) and still appreciate it.
Imagine that you have some static reference tables, let's say ORDER_STATUSES and LOCATIONS (amongst others), and you decide it would be a good idea to build an interface so that any application code can simply retrieve, say, the default order status or the head office location.
Being a bit clever, you notice that all the tables have the same structure, consisting of an ID and a description, and it occurs to you that if you UNIONed them all together, with the table name as a "type" key, you would have that generic lookup table you always wanted, along with a generic cursor to query it. (It avoids having one cursor per table, after all, so there is an efficiency gain already, right?) So you write this:
/* -------------------------------------------------------------------------------------
Name: xxxx
Author xxxx
Description: Script to create the types functions dynamically during schema creation
Input parameters: None
----------------------------------------------------------------------------------------*/
set serveroutput on size 1000000
variable v_destination_dir varchar2(256);
variable v_errorfilename varchar2(256);
variable v_datafile_name varchar2(256);
BEGIN
:v_destination_dir := '&1';
:v_errorfilename := 'type_model_functions.err';
:v_datafile_name := 'type_model_functions.lst';
END;
/
DECLARE
CURSOR c_tables IS
SELECT DISTINCT table_name
FROM types_functions
ORDER BY table_name;
CURSOR c_types IS
SELECT table_name, function_name, REPLACE(type_value,'''','''''') type_value
FROM types_functions
WHERE function_name IS NOT NULL
ORDER BY table_name,function_name;
v_prev_rec number;
file_handle UTL_FILE.FILE_TYPE :=
UTL_FILE.FOPEN (:v_destination_dir, :v_datafile_name, 'W');
BEGIN
UTL_FILE.PUT_LINE(file_handle, 'set define off');
UTL_FILE.PUT_LINE
(file_handle, '/*----------------------------------------------------------------');
UTL_FILE.PUT_LINE
(file_handle, ' Name: type_model_functions.lst');
UTL_FILE.PUT_LINE
(file_handle, ' Description: Functions to return the id from the types tables');
UTL_FILE.PUT_LINE
(file_handle, ' (Package Specifications)');
UTL_FILE.PUT_LINE
(file_handle, ' Input parameters: None');
UTL_FILE.PUT_LINE
(file_handle, ' Author: xxxx');
UTL_FILE.PUT_LINE
(file_handle, chr(10));
UTL_FILE.PUT_LINE
(file_handle, '-----------------------------------------------------------------*/');
UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'CREATE OR REPLACE VIEW types_view AS ');
v_prev_rec := 0;
FOR r_tables IN c_tables LOOP
IF v_prev_rec <> 0 THEN
UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'UNION');
END IF;
UTL_FILE.PUT_LINE
( file_handle
, chr(9)|| 'SELECT typeid, typelabel, ''' ||
r_tables.table_name||''' tablename from '||r_tables.table_name );
v_prev_rec := c_tables%rowcount;
END LOOP;
UTL_FILE.PUT_LINE(file_handle, '/');
UTL_FILE.PUT_LINE(file_handle, 'show err');
UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types_view ;');
UTL_FILE.PUT_LINE(file_handle, 'create public synonym types_view for types_view ;');
UTL_FILE.PUT_LINE(file_handle, 'grant select on types_view to public;');
UTL_FILE.PUT_LINE(file_handle, 'CREATE OR REPLACE PACKAGE types AS ');
FOR r_types IN c_types LOOP
UTL_FILE.PUT_LINE
( file_handle
, chr(9) ||
'-- Function to return the typeid for ' ||
r_types.type_value || ' ' || r_types.table_name );
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| 'FUNCTION '||r_types.function_name||' RETURN NUMBER;');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| 'PRAGMA RESTRICT_REFERENCES ('||r_types.function_name||', WNDS);');
END LOOP;
UTL_FILE.PUT_LINE(file_handle, 'END types;');
UTL_FILE.PUT_LINE(file_handle, '/');
UTL_FILE.PUT_LINE(file_handle, 'show err');
UTL_FILE.PUT_LINE(file_handle, chr(10));
UTL_FILE.PUT_LINE
(file_handle, 'CREATE OR REPLACE PACKAGE BODY types AS ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)||'CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)||'SELECT typeid from types_view ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)||'WHERE typelabel = p_typelabel and tablename = p_tablename ;');
FOR r_types IN c_types LOOP
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| 'FUNCTION '|| r_types.function_name||' RETURN NUMBER IS ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'v_typeid'||chr(9)||'NUMBER; ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| 'BEGIN');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'OPEN c_types_view (''' ||
r_types.type_value||''','''||r_types.table_name||''');');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'FETCH c_types_view INTO v_typeid;');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'IF c_types_view%NOTFOUND THEN ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| chr(9)|| 'RAISE no_data_found; ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'END IF; ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'CLOSE c_types_view;');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'RETURN v_typeid; ');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| 'EXCEPTION');
UTL_FILE.PUT_LINE
(file_handle, chr(9)|| chr(9)|| 'WHEN OTHERS THEN');
UTL_FILE.PUT_LINE
( file_handle
, chr(9)|| chr(9)|| chr(9) ||
'RAISE_APPLICATION_ERROR(-20001, ''Function types.' ||
r_types.function_name || ': '''||'||sqlerrm );' );
UTL_FILE.PUT_LINE(file_handle, chr(9)|| 'END '|| r_types.function_name|| ';');
END LOOP;
UTL_FILE.PUT_LINE(file_handle, 'END types;');
UTL_FILE.PUT_LINE(file_handle, '/');
UTL_FILE.PUT_LINE(file_handle, 'show err');
UTL_FILE.PUT_LINE(file_handle, 'drop public synonym types;');
UTL_FILE.PUT_LINE(file_handle, 'create public synonym types for types;');
UTL_FILE.PUT_LINE(file_handle, 'grant execute on types to public;');
UTL_FILE.PUT_LINE(file_handle, 'exit');
UTL_FILE.FCLOSE (file_handle);
END;
/
@&1/type_model_functions.lst
exit
Now, given a set of values in TYPES_VALUES like this:
TABLE_NAME FUNCTION_NAME TYPE_VALUE
--------------- ---------------------------- ---------------
order_statuses get_default_order_status Default
order_statuses get_order_cancelled_status Cancelled
order_statuses get_order_dispatched_status Dispatched
order_statuses get_order_closed Closed
locations get_head_office_location Head Office
locations get_accounts_office_location Accounts
the script generates something like this:
set define off
/*----------------------------------------------------------------------------
Name: type_model_functions.lst
Description: Packaged functions to return the typeid from the types tables
Input parameters: None
Author: xxxx
-----------------------------------------------------------------------------*/
CREATE OR REPLACE VIEW types_view AS
SELECT typeid, typelabel, 'locations' tablename from locations
UNION
SELECT typeid, typelabel, 'order_statuses' tablename from order_statuses
/
show err
drop public synonym types_view ;
create public synonym types_view for types_view ;
grant select on types_view to public;
CREATE OR REPLACE PACKAGE types AS
-- Function to return the typeid for Accounts locations
FUNCTION get_accounts_office_location RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_accounts_office_location, WNDS);
-- Function to return the typeid for Head Office locations
FUNCTION get_head_office_location RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_head_office_location, WNDS);
-- Function to return the typeid for Default order_statuses
FUNCTION get_default_order_status RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_default_order_status, WNDS);
-- Function to return the typeid for Cancelled order_statuses
FUNCTION get_order_cancelled_status RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_order_cancelled_status, WNDS);
-- Function to return the typeid for Closed order_statuses
FUNCTION get_order_closed RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_order_closed, WNDS);
-- Function to return the typeid for Dispatched order_statuses
FUNCTION get_order_dispatched_status RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (get_order_dispatched_status, WNDS);
END types;
/
show err
CREATE OR REPLACE PACKAGE BODY types AS
CURSOR c_types_view(p_typelabel varchar2, p_tablename varchar2) IS
SELECT typeid from types_view
WHERE typelabel = p_typelabel and tablename = p_tablename ;
FUNCTION get_accounts_office_location RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Accounts','locations');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_accounts_office_location: '||sqlerrm );
END get_accounts_office_location;
FUNCTION get_head_office_location RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Head Office','locations');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_head_office_location: '||sqlerrm );
END get_head_office_location;
FUNCTION get_default_order_status RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Default','order_statuses');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_default_order_status: '||sqlerrm );
END get_default_order_status;
FUNCTION get_order_cancelled_status RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Cancelled','order_statuses');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_order_cancelled_status: '||sqlerrm );
END get_order_cancelled_status;
FUNCTION get_order_closed RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Closed','order_statuses');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_order_closed: '||sqlerrm );
END get_order_closed;
FUNCTION get_order_dispatched_status RETURN NUMBER IS
v_typeid NUMBER;
BEGIN
OPEN c_types_view ('Dispatched','order_statuses');
FETCH c_types_view INTO v_typeid;
IF c_types_view%NOTFOUND THEN
RAISE no_data_found;
END IF;
CLOSE c_types_view;
RETURN v_typeid;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
( -20001, 'Function types.get_order_dispatched_status: '||sqlerrm );
END get_order_dispatched_status;
END types;
/
show err
drop public synonym types;
create public synonym types for types;
grant execute on types to public;
exitNow all you have to do is ensure that there are values in the LOCATIONS table with typelabels 'Head Office', 'Accounts' and so on, and whenever you run the script it will create or replace a whole new package implementing whatever you put in TYPES_FUNCTIONS, and application code can simply invoke types.get_accounts_office_location().
Now, I wonder whether there is a way to generate the TYPES_FUNCTIONS table...
