
Sometimes the technology used for the UI-layer might be more comfortable using a SQL-based contract with the DBMS. So instead of calling a stored procedure, we would like to submit SQL-statement. More specifically, in the example at hand, a query.
Using SQL based contract
In this post I'll demonstrate how the same page can be serviced by the DBMS via a pipelined table function. Pipelined table functions are a great enabler of pushing business logic into the DBMS tier while at the same time enabling a SQL-based contract of the UI-layer with the DBMS.
Here's how we are going to setup the contract.

We'll use the application context feature inside the DBMS to first convey to the data-tier some context of the page. This will be exactly the same context as with the procedural contract: the username that's logged in, the EAN and the book (search) string. Then we'll develop a view that employs a pipelined table function that uses this context to produce the correct set of rows that is to be returned to the UI-layer.
So our contract with the UI-developer will be:
- call a stored procedure supplying the three context variables, and then
- query the rows by doing a select * from a view (without any where-clause).

Let's develop the context procedure first. This requires:
- creating a (generic) package to populate name-value pairs inside a context
- creating a context specifically for this page and attaching it to the package
- creating a UI-interface object specifically for this page, using the package to enter the three name-value pairs into the context.
The package which has a "setter" to enter a name-value pair into a context (a.k.a. namespace):
create or replace package x_ctx asThe context that requires the package to populate it:
procedure p_set(p_namespace in varchar2
,p_attribute in varchar2
,p_value in varchar2);
end;
/
create or replace package body x_ctx as
procedure p_set(p_namespace in varchar2
,p_attribute in varchar2
,p_value in varchar2) as
begin
--
dbms_session.set_context(p_namespace,p_attribute,p_value);
--
end;
end;
/
create context bookorder_ctx using x_ctxAnd finally the UI-interface procedure:
/
create or replace procedure p_bookorder_ctxNow all that's left is to develop a view that uses a pipelined table function to retrieve the rows. A pipelined table function requires the creation of two types: a type that describes the structure of the rows that are being returned by that function, and on top of that, a table type. Here they are:
(p_username in varchar2
,p_ean in varchar2
,p_book_string in varchar2) as
begin
--
-- Put info in v$context.
--
x_ctx.p_set('BOOKORDER_CTX','USERNAME',p_username);
x_ctx.p_set('BOOKORDER_CTX','EAN',p_ean);
x_ctx.p_set('BOOKORDER_CTX','BOOK_STRING',p_book_string);
--
end;
/
create type f_bookorder_t is objectThese types are then used in the code of the pipelined table function:
(username varchar2(10)
,ean varchar2(9)
,title varchar2(95)
,amount number
,new_amount number)
/
create type f_bookorder_table_t as table of f_bookorder_t;
/
create or replace function tf_bookorderNote that I'm again hardcoding binds into the sql-text of the ref-cursor, which is of course a bad idea. The purpose here is to show the pipelined table function concept. Tim Hall has a great article here that demonstrates how the values available inside the context can be readily reused as binds, and (this is what makes it great as far as I'm concerned) at the same time preventing having to code different 'open ref-cursor for ... using ...' statements.
(p_username in varchar2
,p_ean in varchar2
,p_book_string in varchar2) return f_bookorder_table_t pipelined as
--
type refcursor is ref cursor;
c1 refcursor;
r1 f_bookorder_t;
pl_where varchar2(2000);
begin
--
if p_username is null
then
return;
elsif
p_ean is not null
then
pl_where := 'and o.username = '''||p_username||''''||'
and b.ean = '''||p_ean||'''';
elsif
p_book_string is not null
then
pl_where := 'and o.username = '''||p_username||''''||'
and upper(o.ean||'' ''||b.author||'' ''||b.title||'' / ''||b.publisher) like ''%''||'''||upper(p_book_string)||'''||''%''';
else
pl_where := 'and o.username = '''||p_username||'''';
end if;
--
open c1 for
'select f_bookorder_t
('''||p_username||'''
,b.ean
,o.ean||'' ''||b.author||'' ''||b.title||'' / ''||b.publisher
,o.amount
,to_number(null)) as bookorder
from book b
,bookorder o
where b.ean = o.ean '
||pl_where;
--
fetch c1 into r1;
--
while c1%FOUND
loop
--
pipe row(r1);
--
fetch c1 into r1;
--
end loop;
--
close c1;
--
return;
--
exception when no_data_needed
then close c1;
return;
end;
/
And finally here's the view on top of this table function:
create or replace force view v_bookorder asWe invoke the table function in the FROM clause while at the same time supplying it with the three values that have been set into the context.
select o.username
,o.ean
,o.title
,o.amount
,o.new_amount
from table(tf_bookorder(sys_context('BOOKORDER_CTX','USERNAME')
,sys_context('BOOKORDER_CTX','EAN')
,sys_context('BOOKORDER_CTX','BOOK_STRING'))) o
/
Using XML in the contract
As annouced in the previous post, we'll also quickly show a contract based on XML.

create or replace procedure get_xmlWe could of course also return a true XMLType value, by simply casting the Clob to XMLtype prior to returning it. Alternatively we could have built an XMLType value straight from the start by using all the XML function support available to us inside the DBMS.
(p_username in varchar2
,p_ean in varchar2
,p_book_string in varchar2
,p_xml out CLOB) as
--
type order_t is record
(username varchar2(10)
,ean varchar2(9)
,title varchar2(95)
,amount number
,new_amount number);
r1 order_t;
type refcursor is ref cursor;
c1 refcursor;
pl_where varchar2(2000);
begin
--
if p_username is null
then
return;
elsif
p_ean is not null
then
pl_where := 'and o.username = '''||p_username||''''||'
and o.ean = '''||p_ean||'''';
elsif
p_book_string is not null
then
pl_where := 'and o.username = '''||p_username||''''||'
and upper(o.ean||'' ''||b.author||'' ''||b.title||'' / ''||b.publisher) like ''%''||'''||upper(p_book_string)||'''||''%''';
else
pl_where := 'and o.username = '''||p_username||'''';
end if;
--
open c1 for
'select o.username
,o.ean
,substr(o.ean||'' ''||b.author||'' ''||b.title||'' / ''||b.publisher,1,95) as title
,o.amount
,to_number(null) as new_amount
from book b
,bookorder o
where b.ean = o.ean '
||pl_where;
--
fetch c1 into r1;
--
p_xml := ''||chr(10); ');
--
while c1%FOUND
loop
--
dbms_lob.append(p_xml,''||chr(10);
'||chr(10));
dbms_lob.append(p_xml,''||r1.username||' '||chr(10));
dbms_lob.append(p_xml,''||r1.ean||' '||chr(10));
dbms_lob.append(p_xml,''||r1.title||' '||chr(10));
dbms_lob.append(p_xml,''||r1.amount||' '||chr(10));
dbms_lob.append(p_xml,''||r1.new_amount||' '||chr(10));
dbms_lob.append(p_xml,'
--
fetch c1 into r1;
--
end loop;
--
close c1;
--
dbms_lob.append(p_xml,'
--
end;
/
ut 失落的世界聊天室 -
ReplyDelete小瓢蟲論壇 -
洪爺貼圖 -
免費視訊妹妹 -
金瓶梅情色網 -
0509視訊交友 -
s383視訊 -
成人貼圖站 -
日本人妻熟女自拍貼圖 -
漂亮妹妹聊天網 -
台中夜店辣妹影片 -
曼雪兒免費視訊 -
情趣視訊聊天網 -
內衣模特兒寫真 -
ut聊天室環球辣妹聊天室90691 -
色咪咪貼影片 mv -
日本avdvd介紹免費觀賞 -
2008真情寫真 -
裸露美女 -
免費視訊kk 聊天室 -
浪漫情人視訊網 -
168視訊聊天室一對一show -
真愛love聊天室 -
0204live比基尼辣妹 -
台灣檳榔西施 -
0401影音視訊網 -
6元視訊辣妹脫衣秀 -
ez網愛聊天室 -
好玩遊戲區 -
夢幻女優一區 -
台灣美眉脫衣麻將 -
080流星花園聊天室 -
性感辣妹脫衣秀 -
a383成人影音館 -
火辣美媚自拍寫真貼圖 -
情人浪漫聊天室 -
全球華人真人影音網 -
八卦山視訊影音聊天室 -
阿賓情慾小說 yam -
真人野球拳 -
Has this blog died? It describes a fascinating and useful development paradigm; I hoped for more.
ReplyDeletesPh
The blog is temporarily on hold.
ReplyDeleteI hope to resume transmissions later this year.
Toon, thank-you. I've read it all. I don't have as much experience as you, but but we arrived at fairly the same conclusions. My current development stack is now fat-thin-fat RIAs, with extensive use of entry-point stored procedures as an API layer (contract) and exception handling. I stumbled uppon your blog and it has been a reaffirmating experience. Thanks.
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | PLC SCADA DCS Training in Chennai | Best PLC Training in Chennai | Best PLC Training Institute in Chennai | PLC Training Centre in Chennai | Embedded System Training in Chennai | Embedded Training in Chennai | VLSI Training in Chennai | VLSI Training Institute in Chennai
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | PLC SCADA DCS Training in Chennai | Best PLC Training in Chennai | Best PLC Training Institute in Chennai | PLC Training Centre in Chennai | PLC SCADA Training in Chennai | Automation Training Institute in Chennai | PLC Training in Kerala
ReplyDeleteEmbedded Training in Chennai | Best Embedded Training in Chennai | Embedded System Training in Chennai | Embedded System Training Institute in Chennai | Best Embedded System Training Institute in Chennai | Embedded Course in Chennai | Embedded System Training Institutes in Chennai | Embedded System Training Center in Chennai | Best Embedded System Training in Chennai | Embedded Systems Training in Chennai | VLSI Training in Chennai | VLSI Training Institute in Chennai
ReplyDeleteVLSI Training in Chennai | Best VLSI Training in Chennai | VLSI Training Centres in Chennai | VLSI Courses in Chennai | VLSI Training Courses in Chennai | VLSI Training Institute in Chennai | VLSI Training Institutes in Chennai | Best VLSI Training Institute in Chennai
ReplyDelete