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;
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.
download source code for this article: extremeDecoupling_iotPLSQLCollection.txt
- Table FUNctions: select a graph in SQL
- View to select CG$ERRORS from (easing RuleFrame pain using a Table Function)
- SELECT * FROM RSS_FEED – querying feeds in SQL using Table Functions and XML parsing
- Event based Decoupling in PL/SQL – your first Advanced Queue adventure
- Courtesy of Tom Kyte: Generating rows in SQL with the CUBE statement – no dummy table or table function required
- The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
- On the integrity of data in Java applications – presentation from JFall 2013
- Enriching XMLType data using relational data – XQuery and fn:collection in action
- Java 8 – Collection enhancements leveraging Lambda Expressions – or: How Java emulates SQL
- Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
- Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
- Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
- Oracle Database 12c: joining and outer joining with collections
- Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack
- Oracle Database 12c: In Line PL/SQL Functions in SQL queries