Extreme Decoupling in SQL – Select and DML against a table that is not a table at all

One of the key objectives – though not ulterior motives – in Service Oriented Architecture is decoupling. By decoupling currently mutually dependent agents, we introduce more opportunity for flexibility, no-impact optimization, reuse and interoperability. This does not require Web Services and the world of WS* to realize. Even with fairly simple database means, we can establish some nice decoupling. Right here in SQL.

This article introduces a rather thorough case of decoupling. An example of executing a SQL Query that does not hit a table. The query addresses a View and returns data, without ever touching a table. The View supports DML and will reflect the results of the DML in subsequent queries. However, no table is used at all. The View is a complete decoupling mechanism that absorbs the query, turns it into a PL/SQL call and returns the results from PL/SQL. Through an Instead Of trigger, it does the same thing for DML. So when you are looking for decoupling – and you ought to be – look no further than to this simple, straightfoward View example.

The View in question is called NUMBERS. It has a single column – NUM – and will return integers between 1 and 10. However, it would be easy to extend that range and add more functionality to the NUMBERS view.

A typical query against the view looks like:

select num

from   numbers

where num < 5

As stated before, the view is not associated with a table. At least not a real, persistent database table with actual rows in it. The view is defined as follows:

create or replace

view numbers ("NUM")

as

select column_value

from   table(p.numbers)

The package p uses the collection type number_tbl that is defined with this statement:

create or replace

type number_tbl as table of number

The package has the following specification:

create or replace
package p
is

function numbers
return number_tbl;

procedure insert_number
(p_num in number);

procedure delete_number
(p_num in number);

end;

The body of the package has initialization code to set up the collection of numbers that is the foundation of the NUMBERS view. The numbers function does nothing but return that collection:

create or replace
package body p
is

g_numbers number_tbl;

function numbers
return number_tbl
is
begin
  return g_numbers;
end numbers;

begin
  g_numbers := number_tbl(1,2,3,4,5,6,7,8,9,10);
end;

in addition to function numbers that is used in the view definition, it also has procedures insert_number and delete_number. These two are invoked from Instead Of Triggers that are defined for this view. Any DML operation – well, update should be covered too – against the view NUMBERS is diverted to an Instead Of trigger that invokes a procedure in said package to take care of handling the DML operation.

The instead of triggers are defined as follows:

CREATE OR REPLACE
TRIGGER HANDLE_INSERT_NUMBERS_TRG
INSTEAD OF
  INSERT ON numbers
FOR EACH row
BEGIN
  p.insert_number( :new.num);
END;

for insert and

CREATE OR REPLACE
TRIGGER HANDLE_DELETE_NUMBERS_TRG
INSTEAD OF
  DELETE ON numbers
FOR EACH row
BEGIN
 p.delete_number( :new.num);
END;

for delete.

The two procedures in package p look like this:

procedure insert_number(p_num in number)
is
begin
  g_numbers.extend;
  g_numbers( g_numbers.last):= p_num;
end insert_number;

procedure delete_number
(p_num in number)
is
  i integer;
begin
  i := g_numbers.FIRST;
  WHILE i IS NOT NULL LOOP
    if g_numbers(i) = p_num
    then
      g_numbers.delete(i);
      exit;
    end if;
    i := g_numbers.NEXT(i);
  END LOOP;
end delete_number;

With the number_tbl, the package p and view numbers in place, we can execute SELECT and DML operations like

select num from numbers;
insert into numbers (num) values (11);
delete numbers where num between 2 and 5;
select num from numbers where mod(num,2) = 0;

Note: DML against NUMBERS does not truly participate in transactions; upon a rollback of the transaction that wrought the changes in NUMBERS, no mechanism exists to inform package p of that rollback. Also: NUMBERS is session bound. Each database session has its own in memory (UGA) copy of package p and the data returned by it.

Accessing the exact query against the view

One final touch: suppose our package p did not merely return numbers 1..10, but instead results to be gathered by invoking external services – like string translations to be requested via Google Translation Service or some other result that is to be produced on the spot instead of upon initialization of the package – it would not do to first query all potential results and only then filter, like we might risk doing with a query like this:

select translation

from   table(p.translations)

where  source = 'en'

and    target = 'es'

and    string = 'hello'

If inside the translations function, we would not know the source, target and string, and leave it to the SQL engine to perform the filtering against all the results returned by the translations function, we could not create a sensible implementation: which translations to compose and return to have then filtered? To be on the safe side, we might have to have every dictionary in every language translated by the translation service – into every other language – and that would not help performance.

However, if, inside the translations function, we would have access to the WHERE clause of the query, we would know exactly which source and target language to cater for and what the string is that required translation. With a little trick, this can be done: learn about the query being executed and applying that knowledge inside the translations function.

After some investigation into dynamic V$ views – normally primarily the domain of the DBA – I have created the following PL/SQL function:

create or replace
function get_sql(p_query in varchar2)
return sys_refcursor
is
  l_query sys_refcursor ;
begin
  open l_query for
      select vsq.sql_text
      from v$sql vsq
           join
           v$open_cursor vo
           on (vsq.address = vo.address)
     where vo.sid = sys_context('USERENV','SID')
     and   upper(vsq.sql_text) like '%'||upper(p_query)||'%'
     and   instr(vsq.sql_text, 'VSQ.SQL_TEXT') =0
     order
     by    LAST_SQL_ACTIVE_TIME desc
     ;
  return l_query;
end;

This function finds the current open SQL cursors for the session that invokes the function. They are ordered from most recently executed to least recently executed. The invoker can pass a string that must occur somewhere in the sql_text, to zoom in on the relevant SQL statement.

Execute access to this function should be granted to the schema in which the View with Package are created.

If for example in the numbers function in our package p we would want to know about the query that is currently accessing the function, we can include a call to get_sql and find out about for example the where-clause that is applied to the results returned by numbers:

function numbers
return number_tbl
is
   l_sql varchar2(5000);
   l_sql_query sys_refcursor:= sys.get_sql(user);
begin
  loop
    fetch l_sql_query into l_sql;
    exit when l_sql_query%notfound;
    -- l_sql now contains the query executed against the NUMBERS view
	-- this  is the time to make use of whatever the WHERE clause contains
	exit;
  end loop;
  close l_sql_query;
  return g_numbers;
end numbers;

If a user were now to execute a query like:

select num

from   numbers

where num < 5

then inside the numbers function we would know about the where clause. If the creation of the results returned by the function was somehow expensive, knowledge about the where clause would allow the function to only produce a limited result set.

Resources

download source code for this article: extremeDecoupling_iotPLSQLCollection.txt