Example of Recursion in PL-SQL

CREATE OR REPLACE PACKAGE my_pkg 
IS 
  FUNCTION get_groups (username VARCHAR2)
    RETURN VARCHAR2; 

END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg 
AS
  counter INTEGER; --global counter
  
  
  
  FUNCTION get_ldap_groups(username VARCHAR2)
    RETURN VARCHAR2; --forward declaration
    
  FUNCTION get_groups (username VARCHAR2)
    RETURN VARCHAR2
  IS
  BEGIN
    counter := 0;
    RETURN get_ldap_groups(username);
  END get_groups;
  
  FUNCTION get_ldap_groups(username VARCHAR2)
    RETURN VARCHAR2
  IS
      l_counter INTEGER; 
      l_groups_in VARCHAR2(4000);
      l_groups_out VARCHAR2(4000);
  BEGIN
    counter := counter + 1;
    l_counter := counter;
    if counter < 10 THEN
      l_groups_out := l_counter||username||get_ldap_groups(username);
    END IF;
    RETURN l_groups_out;
  END get_ldap_groups;
  
END my_pkg;
/

What's Happening in Healthcare Analytics?

1) Reports bound directly to tables
2) Persistent belief that the warehouse can be purchased, downloaded and installed
3) Generic db accounts with shared passwords and far reaching access
4) Tendency to pull all data into DW at the finest possible grain instead of getting clear requirements.
5) Business logic (joins, filters, definitions, security) built at report level, over and over.
6) Duplicate infrastructure. Tables, reports, BI tools, development efforts
7) Subject areas defined around business org chart, or vendor modules, not significant business nouns (eg. patient, provider, encounter, etc).
8) Persistent belief that the new version will be the last, perfect iteration.
9) Highly technical work performed by untrained or nontechnical staff
10) Persistent belief that data warehousing, BI and analytics is easy and can be done quickly by a few people.
11) Disinterested stakeholders underestimate how much work their role requires in the development process.
12) Fine grained data built into BI due to superfluous pursuit of perfection.
13) Über models designed but not implemented because legacy structures cannot be decommissioned.
14) Arguments about facts and dimensions, surrogate keys, etc. paralyze development effort and lead to inconsistent design.
15) Lack of end to end linage and impact reporting of ETL system due to metadata neglect or poor tool interoperability.
16) No data dictionary on DW. Attempts to build one bog down in ontological abstractions.
17) Database optimizer neutered because joins implemented in BI/reporting layer.
18) System complexity due to poorly integrated systems, understaffed administration, duplicate tools.
19) Customer facing report aesthetics trump hidden, complicated infrastructure needs.
20) Entire EMR history loaded every night to ensure all data correct in futile pursuit of perfection.
21) Kimball dimension "types" are unnecessary and his architecture is poorly framed and inconsistent, yet remains a dimly understood gold standard.
22) DW maintainance practice relies on drop and full refesh of tables. DW not able to reliably use surrogate keys. Source table consolidation impossible.

Design vs Flexibility

Just discussing today the merits of a design guide we have with one of our developers. The developer claims that a design guide should be flexible. This is a standard complaint, but as I thought about it, it occurred to me that design and flexibility are at odds. What is flexible is not designed, and what is designed is not flexible. The art of design is the art of expressing inflexibility. Design is the imposition of constraints.

While this may seem to be a tautology, it was met with outrage. Perhaps it needs a brief explanation, because we have all heard about flexible designs, or designing for flexibility.

Looking into the details of a flexible design, the elements that remain flexible are not specified in the design. For example, consider a house design that shows a square floorplan 10' by 10' and the remainder to be flexible. Clearly the inflexible elements belong to the design, and the rest does not. The same trivial example covers the idea of "designing for flexibility."

In other words, to make an existing design more flexible, it must require the un-designing of some or all of the original. Making flexible is not designing, it is un-designing. Flexibility cannot be the ultimate aim of a design. In other words, to say that a flexible design is better than an inflexible one is a contradiction in terms.

Welcome

Hello world.