Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 12, 2004 - 3:11 pm UTC

Last updated: July 23, 2010 - 9:50 am UTC

Version: 9.1.1

Viewed 10K+ times! This question is

You Asked

Tom,
I just wanted to ask a question and get your opinion or some links to some good sources for information that you may know of. The company I am working for we have an application where 90% of the business logic is in the database and the rest is in the client which is a VB6 front end. I have been trying to research better ways that we could have built this application.

I have read some techniques from Steven Feurestein on encapsulating the data access within your stored procedures which seems like a good idea. I have seen some of his examples and they mostly deal with one table. ex. one package would have functions for update, delete, insert, select for that one table. What if you have to join some tables for a select, what would be the best approach in encapsulating that?

We can read peoples opinions about keeping things in the database or putting them in java objects from now till hell freezes over and no one will ever agree completely. what have you seen or use for data access from java, when you do have all your logic and data access in the database. There are so many persistence frameworks out there now that seem to want to map an object with a table but not necessarily a stored procedure (unless I haven't look hard enough into some frameworks). Basically in a nutshell what examples of java architectures can you give when most of the code is in the database and the front end would be either JSPs or a swing client.

Thanks for your time and i've learned alot from your books.

and Tom said...

I do not believe in the "TAPI" approach (table API).


I believe in the API approach. For example, for asktom:

ask_tom@ASKUS> desc WWC_ASK_API

FUNCTION ACCEPTING_NEW_QUESTIONS RETURNS BOOLEAN
PROCEDURE ADD_BAD_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN
PROCEDURE ADMIN_UPDATE_QUESTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN
P_SUBJECT VARCHAR2 IN
P_ANSWER VARCHAR2 IN
P_STATUS VARCHAR2 IN
PROCEDURE ASSIGN_TO_FAQ
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
P_FAQ_LIST VARCHAR2 IN
FUNCTION FETCH_ACTIVITY_COOKIE_VAL RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_VAL_NAME VARCHAR2 IN
FUNCTION FETCH_ALL_VALUES_CHAR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN DEFAULT
P_DISPLAYID VARCHAR2 IN DEFAULT
FUNCTION FETCH_ASKCOOKIE_VAL RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_VAL_NAME VARCHAR2 IN
PROCEDURE FETCH_QUESTION_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
FUNCTION FETCH_VALUE_CHAR RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN DEFAULT
P_DISPLAYID VARCHAR2 IN DEFAULT
P_COLUMN_NAME VARCHAR2 IN
FUNCTION FETCH_VALUE_DATE RETURNS DATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN DEFAULT
P_DISPLAYID VARCHAR2 IN DEFAULT
P_COLUMN_NAME VARCHAR2 IN
FUNCTION FETCH_VALUE_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN DEFAULT
P_DISPLAYID VARCHAR2 IN DEFAULT
P_COLUMN_NAME VARCHAR2 IN
FUNCTION GET_PREFERENCE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_PREFERENCE VARCHAR2 IN DEFAULT
P_USER VARCHAR2 IN DEFAULT
FUNCTION IS_ADMINISTRATOR RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_USERID VARCHAR2 IN
PROCEDURE NOTIFY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN
P_STATUS VARCHAR2 IN
P_UPDATE_LINK VARCHAR2 IN
P_REVIEW_LINK VARCHAR2 IN
PROCEDURE NOTIFY_OF_FOLLOWUP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
P_REVIEW_LINK VARCHAR2 IN
P_ADMIN_NOTIFY VARCHAR2 IN
FUNCTION PARSE_SEARCH_STRING RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SEARCH_STR VARCHAR2 IN
FUNCTION QUESTION_COUNT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CODE VARCHAR2 IN
PROCEDURE READ_ACTIVITY_COOKIE
PROCEDURE READ_ASKCOOKIE
PROCEDURE REFRESH_TOP_QUESTIONS
PROCEDURE REMOVE_BAD_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN
FUNCTION SAVE_QUESTION RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN
P_FNAME VARCHAR2 IN
P_LNAME VARCHAR2 IN
P_LOCATION VARCHAR2 IN
P_JOB_TITLE VARCHAR2 IN
P_ORG VARCHAR2 IN
P_CATEGORY VARCHAR2 IN
P_VERSION VARCHAR2 IN
P_YEARS VARCHAR2 IN
P_SUBJECT VARCHAR2 IN
P_NOTIFY VARCHAR2 IN
P_QUESTION VARCHAR2 IN
PROCEDURE SAVE_REVIEW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
P_RATING VARCHAR2 IN
P_TITLE VARCHAR2 IN DEFAULT
P_NAME VARCHAR2 IN DEFAULT
P_LOCATION VARCHAR2 IN DEFAULT
P_REVIEW VARCHAR2 IN DEFAULT
P_FROM_SUBMITTER VARCHAR2 IN DEFAULT
P_EMAIL VARCHAR2 IN DEFAULT
PROCEDURE SAVE_REVIEW_FOLLOWUP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN/OUT
P_DISPLAYID VARCHAR2 IN
P_REVIEWID VARCHAR2 IN
P_COMMENTS VARCHAR2 IN
PROCEDURE SET_PREFERENCE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_PREFERENCE VARCHAR2 IN DEFAULT
P_VALUE VARCHAR2 IN DEFAULT
P_USER VARCHAR2 IN DEFAULT
P_FORCE_UPPER BOOLEAN IN DEFAULT
PROCEDURE UPDATE_ADMINISTRATORS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ADMINISTRATOR_LIST VARCHAR2 IN
PROCEDURE UPDATE_APPLICATIONS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_APPLICATION_LIST VARCHAR2 IN
P_ENABLED_APPLICATION VARCHAR2 IN DEFAULT
PROCEDURE UPDATE_CATEGORIES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CATEGORY_LIST VARCHAR2 IN
P_APPLICATION_CODE VARCHAR2 IN
PROCEDURE UPDATE_FAQ_CATEGORIES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_FAQ_LIST VARCHAR2 IN
PROCEDURE UPDATE_QUESTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN
P_EMAIL VARCHAR2 IN
P_FNAME VARCHAR2 IN
P_LNAME VARCHAR2 IN
P_LOCATION VARCHAR2 IN
P_JOB_TITLE VARCHAR2 IN
P_ORG VARCHAR2 IN
P_CATEGORY VARCHAR2 IN
P_VERSION VARCHAR2 IN
P_YEARS VARCHAR2 IN
P_SUBJECT VARCHAR2 IN
P_QUESTION VARCHAR2 IN
FUNCTION URLENCODE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STR VARCHAR2 IN
FUNCTION WORD_WRAP RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STRING VARCHAR2 IN
P_LEN NUMBER IN DEFAULT
PROCEDURE WORD_WRAP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STRING VARCHAR2 IN
P_LEN NUMBER IN DEFAULT
PROCEDURE WRITE_ACCESS_LOG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_DISPLAYID VARCHAR2 IN
P_EMAIL VARCHAR2 IN
PROCEDURE WRITE_ACTIVITY_COOKIE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_COOKIE_ID VARCHAR2 IN DEFAULT
PROCEDURE WRITE_ACTIVITY_LOG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_COOKIE_ID VARCHAR2 IN
P_PAGE_ID VARCHAR2 IN
P_SESSION_ID VARCHAR2 IN
PROCEDURE WRITE_ASKCOOKIE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN DEFAULT
P_FNAME VARCHAR2 IN DEFAULT
P_LNAME VARCHAR2 IN DEFAULT
P_LOCATION VARCHAR2 IN DEFAULT
P_JOB_TITLE VARCHAR2 IN DEFAULT
P_ORG VARCHAR2 IN DEFAULT
P_YEARS VARCHAR2 IN DEFAULT
PROCEDURE WRITE_SEARCH_LOG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CRITERIA VARCHAR2 IN
P_EMAIL VARCHAR2 IN


If I needed to return result sets -- there would be ref cursors and such in there as well. My concept is "build the data api in the database, you call the data api".

I don't have to know that SAVE_QUESTION writes to table T1, updates T2, queues an email, inserts into AUDIT_TRAIL, etc etc etc etc etc. I (as the coder) just know "call save_question when you have interfaced with a user and collected the data for a question"

The cool thing here -- this API has been layered by different UI technologies over time. Never changed the API for the UI, have changed the IMPLEMENTATION of the API for all UI's simultanenously cause we added a new feature or something.



So, TAPI's -- no, i don't believe in them.

API's -- all about API's. The applications don't do table level stuff, the apps don't even really *know* about tables.


I have a schema.

On top of schema we have an API that does the data stuff.

In the application we call this API but have our own application logic as well (only application logic is in the application, data logic is -- well, right where it belongs -- next to the data, waiting for the 'next great programming paradigm to come along')


The fact that my UI is in HTML/DB isn't really relevant. You could pretty much see how you would use this package from Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, a mobile phone, <whatever the heck you want>.

The programmers can persist whatever data they want at the java level (remember, the data they 'persist' -- what is that data? is it a question/answer on asktom??? no. Oh wait, THEY don't actually persist anything (of actual use), the database does. they (application instances) are transient.

Do they really persist a "human being" in an HR application?
Do they really persist an "account" in a banking application?

I'm trying to figure out "what it is they actually 'persist'" since an application view of data is very myoptic -- far far far too narrow to be of general purpose use. Think of a banking application. You build an application for a teller, you model "well teller sees CUSTOMER, CUSTOMER has ACCOUNTS, ACCOUNTS have TRANSACTIONS, CUSTOMER also has ADDRESSES (home, business, etc). There is our object we need to persist". But wait -- aren't there a bunch of people in the back room trying to figure out things like:

o where do our customers live?
o how many transactions did we do today?
o what is the sum of the balances in the accounts?

if you have a "Customer" view with accounts/transactions and addresses -- you have the wrong perspective of the data.



well, this is starting to ramble on isn't it.


Applications -- a dime a dozen. They come, they go, they have programming paradigm of the week.

Data -- priceless. Most of it has been here for a long long long time. Most all of it will be here after we are dead.



Rating

  (49 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andy, October 12, 2004 - 5:49 pm UTC

This is, by far, the best description I've seen, and best example of how and why to build your data rules next to (and within) your database.

Place for TAPI

Dushan, October 13, 2004 - 3:19 am UTC

Hi Tom,

I comletely agree that there is not good idea to give TAPI (1. package for table) to java programmer and say - "Here are TAPI's for tables and build a business process using TAPI's only". It is really better to build API in PL/SQL and tell them "Here is API, call it and do not care what happens inside.".
Nevertheless, I do believe that there is still a place for TAPI. We are using ORACLE Designer and generate TAPI for things like journalling, validations, derivations etc. The TAPI package contains procedures like:
ins
upd
del
lck
insert_jn
slct
cascade_update
cascade_delete
Validate_arc
Validate_domain
validate_foreign_keys_ins
validate_foreign_keys_upd
validate_foreign_keys_del
domain_cascade_delete
upd_denorm2
upd_oper_denorm2

... plus triggers
(We do not use all of them)

TAPI gives us more control over table and let us automate lots of things on table level. Nevertheless, I say again, we do write API's to cover the whole process on database level.

Tom Kyte
October 13, 2004 - 8:20 am UTC

I hate TAPI's.

most of those 'tapi' names above look like simple 'APIs'

but validate fkeys -- all should be (have to be) RI in the database.
cascade delete -- that is a database feature as well.
cascade update -- well, won't even go there.

for for simple ins,upd,del,lck -- it is just as easy to code.....

Nope, I'm not into the TAPI approach. never saw the benefit

RE:place for TAPI

Dushan, October 13, 2004 - 9:54 am UTC

We use some usefull features like journalling, population of ID's by sequences and some denormalizing like running total. RI is on database, of course.
This stuff is generated by Designer, we found it reliable - so why not to use it? After generation we do not touch it, we simply install it and it works.
I am lazy programmer, I am not going to write e.g. journalling code for tons of tables.


Jeff, October 19, 2004 - 3:46 pm UTC

Tom,
So if lets say i had a web page that was to manage users, for example. and we were to do inserts, updates, deletes, maybe some other things. whether these actions depended on 1 or more tables we'd just create a package called users and have procedures for insert , update, delete etc. So our packages would depend on how we access the data and what we want to do with it, correct.

how would you structure a package(s) if say you had an app that involved alot of drop downs on the screen. basically you are selecting codes, descriptions from many different tables. would you put all of these into one package or would you use the select proc out of the users package or client package, etc.

basically we have an application that has alot of control type data, that can be created, update, deleted. And then this data is used in drop downs and selection lists all over the app. it wasn't done the best way at the beginning and now i am coming up with ways to get all the sql in the database so we can maintain it better.

thanks for your time.

Tom Kyte
October 19, 2004 - 4:21 pm UTC

drop down lists would either be

a) a view (for each list).
b) a stored procedure returning a ref cursor.

for each list, using static sql.


follow up

Jeff, October 20, 2004 - 4:04 pm UTC

Tom,
maybe i wasn't too clear. how would you structure your packages if putting drop down sql into stored procedures. would you put it in one package?

Tom Kyte
October 20, 2004 - 5:13 pm UTC

i group things that logically belong together.

do they LOGICALLY belong together? are they used together? are they related to eachother.



And another thing...

mike, October 21, 2004 - 5:19 am UTC

If there are 2 things I hate, that I see all the time it is TAPI's (or API's that are so restrictive you always end up calling the same bunch) and pointless / misguided de-normalisation. Given that "Databases are born to query" as someone once said on this site ( I just clocked my dodgy laptop at 6 times), I still see buggy applications where a denormalised value is not correctly sync'd with the master value.

"One version of the Truth" seems to disappear in the face of nothing more than worries that a join might be done "often" and so denormalising it is bound to have a positive impact.

ARRGHH!!

On the TAPI side the application I work on was written by two groups, one side has code that causes me to chain smoke a pack of fags before even firing up the IDE, the other I use on a daily basis, but I haven't looked inside the packages for maybe a year.

Mike

<Off to improve progress into said packet>

.

dxl, October 28, 2004 - 7:58 am UTC

Just wondering in your response about how you do things in the AskTom api your package looked like:

ask_tom@ASKUS> desc WWC_ASK_API

FUNCTION ACCEPTING_NEW_QUESTIONS RETURNS BOOLEAN
PROCEDURE ADD_BAD_EMAIL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_EMAIL VARCHAR2 IN
PROCEDURE ADMIN_UPDATE_QUESTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ID VARCHAR2 IN
P_SUBJECT VARCHAR2 IN
P_ANSWER VARCHAR2 IN
P_STATUS VARCHAR2 IN

..
.. etc

Not that I know what your code looks like but in the whole package you don't seem to have any parameter that are not VARCHAR2. Even P_ID which i'm guessing is really an integer it is a declared as varchar2, why is that? Are you converting them within the api? for what reason?
I thought you would encounter conversion problems if that is the case?

just curious!

Tom Kyte
October 28, 2004 - 12:29 pm UTC

they come in from the web -- all of them.

say we used number.

So, say in the "p_id" field -- someone types in ABC instead of 123. Now, mod_plsql tries to call our function and binds 'ABC' to a number -- immediate failure, you get a "404-not found" from the webserver (or an error page, depending on the setup but 404-not found by default).

Thats not good.

So, pass it as a string, convert it yourself -- you can catch the error and report back something a tad more "useful" in some cases.....


so, all of my web facing packages pretty much take "strings" yes.

TAPIs and change management

Todor, December 13, 2004 - 10:40 am UTC

They say using TAPIs it is easier to manage changes in the data model. Say you want to add new column, drop or rename a column etc. - TAPIs make it faster.

Tom Kyte
December 13, 2004 - 1:40 pm UTC

no they don't.

all of the calls to the TAPI have to change as well if you add/drop and probably even rename (why bother renaming X to Y if the parameter is still called X...)


they make these operations no easier than if people just coded inserts directly.

API and implicit cursor

James, February 27, 2005 - 8:19 pm UTC

Hi Tom,

Based on the example of a html select dialogue:

Say we have the data stored in a table:

create table category_options(
display_field varchar2(20),
value_field varchar2(20)
);

insert into category_options values('Option 1','1');
insert into category_options values('Option 2','2');
insert into category_options values('Option 3','3');

How should this be best expose this via an API?

The most performant way to present this data is via an implicit cursor and mod_plsql:

create or replace procedure display1
begin

htp.p('<select>');
for x in ( select * from category_options )
loop
htp.p('<option value="'||x.value_field||'>'||x.display_field||'</option>');
end loop;

htp.p('<select>');

end display1;

But what if I want to uncouple the presentation from the data access?

I should create an API function that returns a ref cursor:

create or replace function category_api return rc
is
begin

open rc for
select value_field,display_field
from category_options;

return rc;

end category_api;

--this procedure could be java, plsql etc
--but will use plsql for convienince:

create or replace procedure display2
is

type rc_type is ref cursor;
rc rc_type;

option_val varchar2(100);
option_dis varchar2(100);

begin

rc := category_api;

htp.p('<select>');

loop

fetch rc into option_val,option_dis;
exit when rc%notfound;

htp.p('<option value="'||x.value_field||'>'||x.display_field||'</option>');

end loop;

close rc;
htp.p('</select>');

end display2;

So these examples seem to be saying to me:

Whilst implicit cursors are best, when it comes to designing API's for data access ref cursors should be used for flexibility.

This seems to be what you are saying in this thread but in another thread :
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10369303150185 <code>

The followup entitled "Cursor Farm October 08, 2003" Neil suggested:
"separate all my cursors from my business logic by putting them into packages."

In your reply, you didn't particularly like this approach, but I am wondering if you approve of it more these days as your WWC_ASK_API seems to be built this way?

Does this sound like a good rule:

If data access is required by a presentation layer it should be via a neatly defined API residing as pl/sql stored procedures in the database returning a ref cursor, a record or a single value.


Regards,
James


API Again

James, March 02, 2005 - 12:29 am UTC

Since this thread is disucssing API's I thought I'd provide an exmaple of how we currently provide an API and how I want to refactor the API.

We have a "core" package which generates dynamic html for a given page.
This core package has an init procedure which does most of the actual data access.
The core package generally represents a webpage.

We have a set of public package procedures which reformats the output slighty
--change graphics
--change links
--text

This is used for "skinning" websites that have the same functionally but a different look. To add a new skin we simply add a new public procedure to the package and set some global variables - we have got into the habit of calling this a "constructor".


----------------------------------------
--example core package with 2 constructors

create or replace package core_display
is

procedure display_oracle;
procedure display_asktom;

end;
/

create or replace package body core_display
is

--global graphics variables
heading_gfx varchar2(100);

--global ui procedures
my_link_url varchar2(100);

--global data access
the_date varchar2(100);

--data access, parsing, logging etc
procedure init
is
begin

select to_char(sysdate,'DD-MON-YYYY')
into the_date
from dual;

end init;

procedure display
is
begin

--display image with link
htp.p('

<a href="'||my_link_url||'">
<img src='||heading_gfx||'>
</a>

');

end display;

procedure display_oracle
is
begin

--set presentation variables
my_link_url := '</code> http://www.oracle.com';
 heading_gfx := '
http://www.oracle.com/admin/images/ocom/oralogo_small.gif';

 --initialize
 init;

 --display content
 display;

end display_oracle;

procedure display_asktom
is
begin

 --set presentation variables
 my_link_url := '
http://asktom.oracle.com/';
 heading_gfx := '
http://asktom.oracle.com/i/asktom/asktom2.gif';

 --initialize
 init;

 --display content
 display;

end display_asktom;


end core_display;

/

-----------------------

Now that we have set up the "core" package we use this as an API for the website.

create or replace procedure asktom_app
is
begin

htp.p('This is the template header<br>');
htp.p('This is the template navigation<br>');
htp.p('Time to do something tricky - lets call a core package<br>');

core_display.display_asktom;

htp.p('<br><br>Display footer');

end asktom_app;
/

------------------------------

This application framework has started to fray due to the number of "core" packages combined with the number of skins we have. ie it takes a long time to add a new "constructor" too 50 packages. And when it comes to these packages being used by another UI - it is impossible due to the embdedded htp calls.

So in developing a new framework out main goals are: 
* de-couple data access from webserving whilst not scraping all our existing pl/sql.
* create an API that another UI can access
* reduce maintenance time when adding another skin

To do this I propose this framework.

1. Create a core data-access API. 
2. Based on this data-access API create a set of Oracle Object types to expose this data *and* create member procedures that generate basic user interface (in html, xml, whatever)

--data access api
function dispaly_api
return varchar2
is

the_date varchar2(100);

begin

 select to_char(sysdate,'DD-MON-YYYY') 
 into the_date
 from dual;

  return the_date;

end dispaly_api;

This could be the end of the story but I am reluctant to rely on application developers to get the complexity of using this function and I want to provide them with some more help. I'll create another level of API for them...

create or replace type display_t as object (

--object graphics variables
heading_gfx    varchar2(100),

--object ui procedures
my_link_url    varchar2(100),

--object data access
the_date    varchar2(100),

member function display return varchar2,

constructor function display_t return self as result

);

/


create or replace type body display_t as

 member function display return varchar2
 is
 begin

  return 
  '

    <a href="'||my_link_url||'">
      <img src='||heading_gfx||'>
    </a>
   ';

 end display;

constructor function display_t return 
self as result

is
begin
    --data access to set attributes
     select to_char(sysdate,'DD-MON-YYYY') 
     into self.the_date
     from dual;
    
    return;

end;

end;

/


Now using this objects the asktom application becomes

create or replace procedure asktom1_app
is

dt display_t := display_t();

begin

dt.my_link_url := '
http://asktom.oracle.com/';
dt.heading_gfx := '
http://asktom.oracle.com/i/asktom/asktom2.gif'; <code>

htp.p('This is the template header<br>');
htp.p('This is the template navigation<br>');
htp.p('Time to do something tricky - lets call a core package<br>');

htp.p(dt.display);

htp.p('<br><br>Display footer');

end asktom1_app;

/

So this approach would seem to address my goals.
1. de-couple data access from webserving whilst not scraping all our existing pl/sql.
Moving data access to a separate API and presentation to an object is less rewriting the code base and more refactoring it.

2. create an API that another UI can access
Application development can either use the raw data API or the more sophisticated application api.

3. reduce maintenance time when adding another skin
What we have tried to achieve using packages, Oracle Object Types do 100 times better - plus all the other OO features make this a better approach. When we add another skin, the application objects do not have to touched.

Regards,
James



Tom Kyte
March 02, 2005 - 7:07 am UTC

I'd use htmldb with it's templates and builtin navigation/reporting/pagination/graphing/display/state management/etc features to make application development really fast and easy to maintain.


that is, when I moved to htmldb from "just code", I erased a good 50-75% of my code, leaving just the transactional API's. I can change the look and feel of asktom at the push of a button, on the fly. I can create a new look and feel in any html design tool and apply it as a template.


I can reuse my API's with any programming paradigm that comes along and needs it.

Naming convention behind wwc, wwv, etc.

mobra, March 02, 2005 - 9:18 am UTC

You mention that you have a package called WWC_ASK_API. I've also seen the prefix WWC, WWV, etc. in the Oracle Portal API.

What do these prefixes/abbreviations mean? Are they documented anywhere?


Tom Kyte
March 02, 2005 - 11:01 am UTC

Tyler here. While I can't remember the exact names for these abreviations, they are simply 3 character prefixes to make sure object names from different products do not collide. I believe WWV was from the WebView days and WWC was from the Web Calendar (now replaced by CollabSuite). WWV is still used by HTML DB, but there are HTMLDB_ synonyms for all of the publicly available API's. These are documented in the HTML DB documentation and in the Database "PL/SQL Supplied Packages Doc". wwc_ask_api is not publicly distributed and is exclusive to asktom.oracle.com.

Hope this helps,
Tyler

Different Looks, Same Site

Tyler D. Muth, March 02, 2005 - 11:08 am UTC

In response to "API Again" from James of Australia:

You could achieve this with nothing more than CSS. If you look at the following site:
</code> http://www.csszengarden.com/ <code>
It's the same HTML, but as you click different links on the site, it changes the CSS and consequently the WHOLE UI of the site. I reccomend this to people using HTML DB all the time, but it will work with any technology, asuming you code your UI using CSS (the direction the whole internet is heading).

Thanks,
Tyler

Some Clarifications

A reader, August 03, 2006 - 6:30 am UTC

In your “SAVE_QUESTION” and “UPDATE_QUESTION” PROCEDURES there are almost same parameters. I have some guestion:
1-When user changes any one thing for example Last_Name then you get changed value for Last_Name and same values for other parameters as these were sent from db for updating (UPDATE_QUESTION) .?
2-At which point you get which values has changed on front end or in procedure (I means you compare old and new values at front end or in procedure).?
3- Every time you execute update for all the columns even if one column value changed?
4-If there is a master detail Insertion/Updating GUI what you prefer for detail table.
(a) Call detail table insertion procedure in loop from front end.
(b) Get values of detail table in a array/string and parse it.? If possible give example.


Tom Kyte
August 03, 2006 - 9:40 am UTC

1,2,3- yes. indexes are only touched if the values change - we are going to update the row anyhow - it is much easier to update all of the columns rather than have many permutations of sql updates to get just the "right columns". I'll live with the marginal increase in undo/redo in order to have a single update (shared pool) that uses binds.

4 - it is called an array. why would you parse structured information?



layered API's

Mark Wooldridge, August 03, 2006 - 6:07 pm UTC

What do you think of layering the API to support modularity/readiblity/security/testing?

For example I normally have a business function (BF) API that contains the functional procedures (add question, update question, etc). The business function API's utilize a lower level table API that maintains the simple insert/update/delete, most of the parameters are %ROWTYPE.

I have also utilize a support package to help get data in the proper format (e.g. you have complex object types and need to get the data into a %ROWTYPE).

This allows me to keep all the related basic functions together and reuse them (e.g. person_tbl_pkg might contain all the insert/update/delete procedures for all the person tables, marriage license, birth certificate, etc.)

I also utilize a package layer on top of the business functions to add security and utilize roles better. For example you have a business function package that adds/update/deletes a user. Not everyone can add/delete a user. I create seperate packages one that has a pass through to the update procedure in the user_bf_pkg. This way I can have a role end_user with execute on user_bl1_pkg and the admin_role can have execute on the user_bf_pkg. The user_bl1_pkg can have added logic to ensure the user is only trying to update their own record.

Tom Kyte
August 03, 2006 - 6:51 pm UTC

sounds like a sound approach to what I've been calling a "transactional API".

I'm totally into modular programming.

Re:Some Clarifications

A reader, August 04, 2006 - 3:39 am UTC

Thanks a lot for your quick response. Here some clarification is required:

2-At which point you get which values has changed on front end or in procedure (GUI will decide if record has changed or not)?

4-"Get values of detail table in a array/string and parse it." Actually I was trying to say that if we get values in string by appending a special character for next occurrence of record we will parse it. For example we are updating salary and job of emp table for a department.
IP_Deptno varchar2 (value=20)

For updating emp_no7369 & 7388 salary and job we will send data as this and parse the string.

IP_EMP varchar2{value (format empno»sal»job»empno»sal job»………….)= 7369»1000» ANALYST» 7788»3500» CLERK………….}


Tom Kyte
August 04, 2006 - 8:01 am UTC

2) in a stateless environment - where you do not have a transaction spanning multiple pages - you must use optimistic locking. We lock and check immiediately prior to doing the update.

4) i know what you meant and I don't know why you would munge up all of the data only to have to un-munge it. keep it structured, use arrays. Don't use strings that need parsing. Use FORMAL NAMED parameters.

Pascal, August 04, 2006 - 3:55 am UTC

Hi Mark Wooldridge from Warrenton, VA ,

Your layered Logic as you described above sounds very
interesting. Could you please post some code snippets
to show how this was done.

Thanks in advance,

Pascal.

Update for Pascal

Mark Wooldridge, August 08, 2006 - 1:55 pm UTC

Pascal,

pl/sql psuedo code showing the layer PL/SQL API approach I utilize.

in the following example:
po_header_otyp is an sql object that looks much the same as the po_header%rowtype
po_detail_otyp is an sql object that looks much the same as the po_detail%rowtype
po_detail_rtyp is table of po_detail_otyp
po_otyp is object
(header po_header_otyp
details po_detail_ttyp);


/**
* table package, basic insert, update, delete for each table
* also including support for inserting multiple records through table of objects
*/
package po_tbl_pkg

procedure i_po_header(p_rec in out po_header%rowtype) is
--this is where I wish oracle had a 'overidding' syntax for record inserts
--(e.g. insert into po_header values p_rec overriding po_header_id with po_header_is_s.nextval)

select po_header_id_s.nextval
into p_rec.po_id
from dual;

insert
into po_header
values p_rec;

procedure u_po_header(p_rec in out po_header%rowtype);

procedure i_po_detail(p_rec in out po_detail%rowtype);

procedure i_po_detail(p_po_id in po_header.po_id%type,
p_obj in po_detail_ttyp) is

begin
insert
into po_detail
select p_po_id,
po_detail_id_s.nextval,
qty,
....
from table(p_obj);

end i_po_detail;

procedure u_po_detail(p_rec in out po_detail%rowtype);

end po_tbl_pkg;

/**
* support package, convert data from an object to a record and a record to object
* related lookup table get functions, etc.
*/
package po_sp_pkg

function po_header_o2r(p_obj in po_header_otyp)
return po_header%rowtype is

v_rec po_header%rowtype;

begin
v_rec.po_id := p_obj.po_id;
.....

end po_header_o2r;

function po_detail_o2r(p_obj in po_detail_otyp)
return po_detail%rowtype;

end po_sp_pkg;


/**
* business functions package, these are the core controllers of the tbl and sp packages
*/
package po_bf_pkg

procedure insert_po(p_obj in po_object_typ) is

begin
po_tbl_pkg.i_po_header(v_po_id, po_sp_pkg.po_header_o2r(p_obj.header));
po_tbl_pkg.i_po_detail(v_po_id, po_obj.details);

end insert_po;


approve_po(po_num in varchar2) is

begin
mark the po approved;
audit what is needed;
end;


end po_bf_pkg;

/**
* business logic/security controls
* this package allows us to repackage the functions and use roles in the database to protect
* specific functions
* they may also allow checking for different needs based on the state
* (e.g. a user may update an po that is not approved, but once approved only and po_admin
* may be allowed to update the po)
*/
package po_admin_pkg is

approve_po(po_num in varchar2);
update_po....
cancel_po....

end po_admin_pkg;


package po_user_pkg is

procedure insert_po is
simply call po_bf_pkg.insert_po(...);
end insert_po;

procedure update_po is
checks to see the po is still in the draft state or not approved....
simply call po_bf_pkg.update_po(...);
end update_po

end po_user_pkg;

end;
/

/**
* po_if_package - this supports repacking things for interfaces
* you may have a business function package that includes many things in the spec
* but for business reasons you only want to publish some of the procedures using
* Oracle's JPublisher
*/
package po_if_pkg

insert_po(p_obj po_otyp) is
simply call the po_bf_pkg.insert_po(...);

end insert_po;

end po_if_pkg;

now we can use roles like po_user_role which has execute on the po_user_pkg
and the po_admin_role which has execute on the po_admin_pkg
you could even combine some of the logic in one package and check for existence of role but I like this
declarative approach.


when developing web applications using PSP's and not APEX I also have an htp package that the PSP's use
to help generate the dynamic HTML for the PSP.

The layers also isolate some of the development and end user of the application. For the most part the
Business Logic (BL) Layer only executes the Business Function (BF) layer and Support (SP) layer.
The Interface (IF) layer utilizes the BL and SP. BF utilizes the TBL and SP.
From a security point the layers are isoloted from the user user roles. You can also add session context to help the security.

The best benefit the whole
thing can be tested from and sql prompt. You can put any application interface (APEX, PSP, JSP, etc..)
in front.
the BL and SP layer.


Pascal if you have any more questions just ask. Hope this demonstrates how my layered approach works.


Re: Clarification

A reader, August 09, 2006 - 12:58 am UTC

Can you please give example how we we do it by array in procedure?

4-"Get values of detail table in a array/string and parse it." Actually I was
trying to say that if we get values in string by appending a special character
for next occurrence of record we will parse it. For example we are updating
salary and job of emp table for a department.
IP_Deptno varchar2 (value=20)

For updating emp_no7369 & 7388 salary and job we will send data as this and
parse the string.

IP_EMP varchar2{value (format empno»sal»job»empno»sal job»………….)= 7369»1000»
ANALYST» 7788»3500» CLERK………….}



Tom Kyte
August 09, 2006 - 10:40 am UTC

arrays - very natural thing in programming languages? Not sure what you mean.

If you have STRUCTURED INFORMATION
that you glom together in string
to parse back out

THEN
you must have structured information
which you can process in an array.


I don't get why you would use strings like that. Programming languages have this concept of "formal named parameters complete with datatypes and type checking"


for updating emp_no 7369 and 7388 I would either

a) call a procedure twice, once with 7369's information passed as formal named parameters:

procedure update_emp( p_empno in number, p_sal in number, ..... )


b) using plsql index by tables or sql collection types, call a procedure once with arrays of information:

procedure update_emps( p_empno in table_of_number,
p_sal in table_of_number,
p_ename in table_of_varchar2,



(where table_of_whatever are types you've defined of course)

Pascal, August 09, 2006 - 3:33 pm UTC

Hi Mark ,

Thank you very much for demonstrating your layered architectured PL/SQL Table API approach.

I will take a look at the pseudo code and try to implement this on my Tables.

I will try to find some way of automating this, such as, by writing a sqlplus script which would generate the pl/sql Packages by taking Table-Name as input.

I think Tom has some examples somewhere on this site, on how
to use sql to generate Table Api.



Pascal...table API generator

Mark Wooldridge, August 09, 2006 - 7:06 pm UTC

I put together one that gets it most of the way. Not sure how I can get it to you. Not sure how Tom would feel about me uploading code to his site.

Here is the spec...

create or replace package cg_pkg as

/**
* generate table api for table and support tables in tab_arr
*/
procedure gen_tbl_api(p_table in varchar2,
p_prefix in varchar2 default null,
p_tab_arr in cg_util_pkg.tab_ttyp default cg_util_pkg.tab_ttyp());

/**
* generate the business logic api for table
*/
procedure gen_bl_api(p_table in varchar2,
p_prefix in varchar2 default null);

/**
* generate object type for a table
*/
procedure gen_tbl_obj(p_prefix in varchar2 default null,
p_tab_arr in cg_util_pkg.tab_ttyp default cg_util_pkg.tab_ttyp());


/**
* generate object type move to record and record move to object (name_r2o and name_o2r)
*/
procedure gen_obj_rec_move(p_table in varchar2,
p_object in varchar2,
p_procedure in boolean default true,
p_table_varname in varchar2 default null,
p_object_varname in varchar2 default null,
p_use_null_constructor in boolean default true,
p_use_only_varchar2 in boolean default false,
p_spec in boolean default false);

end cg_pkg;
/

show errors

Tom Kyte
August 09, 2006 - 7:52 pm UTC

as long as it is 32k or less... post away.

or, you can both email me (thomas.kyte@oracle.com) and say "please introduce me" and i'll get your emails to each other.

Zdenek Vovesny, August 10, 2006 - 5:06 am UTC

Mark, I find your generator very useful, can you please email me the code too ? Tom, I'm mailing you, can you please give my email address to Mark ?

Thank you both very much

API and TAPI

Matt, August 10, 2006 - 9:03 am UTC

Very interesting article.

In my experience I have found it beneficial to have both TAPI and API.

The TAPI just does what it says on the tin, basic CRUD, but also with any bulk insert functionality in there too.

But NO COMMITS.

The API calls a TAPI, or Series of TAPI's as it needs to and commits at the end of the process.

ie.

INVOICE_PKG calls
TAPI_INVOICE_HEADER.CREATE();
TAPI_INVOICE_LINE.CREATE();
commit;

Advantage of this method is that if another API comes along and needs to create an invoice header it also has access to the TAPI.

Plus we retain the benefits of seeing the actual business processes (flow) in the API package.

Just my pennies worth.

Oops

Skim Reader!, August 10, 2006 - 9:06 am UTC

Sorry didn't see Mark Woolridge eg. above.

Works well!!

Regarding Tom's and Mark's API approaches...

Gerardo, August 10, 2006 - 10:02 am UTC

Regarding Tom's and Mark's API approaches...
suppose you have in your business logic packages procedures to insert, update and select on table A.
We have an admin_user(owner of table A and packages) and a regular_user (only updates). Furthermore, regular_user is the user we define in the application server data source, I mean, in a big user application approach, he is who connects to the database. With secure application roles, I should give execute privileges on the bl package to regular_user.
Then regular_user would have permission over insert procedure also, so I'd have to add some logic to prevent it.
My point is, if procedures are standalone, you can grant privileges separately to different roles, and manage
the business logic in a better way, but if they are all inside a package you cannot.
How do you handle this situation ?

Thanks.

Tom Kyte
August 10, 2006 - 10:45 am UTC

two packages, I see no issues with regards to that.

i just sent an Email to Tom

Pascal, August 10, 2006 - 10:14 am UTC

Hi Mark,

i have just sent an Email to Tom regarding your Table Api Generator Packages.Could you please email the Packages to Tom or post them here if it is less than 32k like Tom said.

Looking forward to receiving the Packages.

Thank you very much,

Pascal

By the way, i am also planning to add some other General Purpose Functions, such as :

--checks for all Mandatory(Not Null) Table Columns and
--gives appropriate Error message:
validate_mandatory(p_rec in out table%rowtype )






Clarification regarding api approach

Gerardo, August 10, 2006 - 11:44 am UTC

> two packages, I see no issues with regards to that.

Tom,
Correct me if i am wrong, but if I create a new package for this purpose, i'll have to clone the update procedure inside the new package, then my application will be harder to maintain, because the same procedures will be in different packages..
Could you elaborate your answer ?

Tom Kyte
August 10, 2006 - 12:57 pm UTC

why would you have to do that?

you have a package with the "table api"
you have a package with the "business api"

you grant on the business api
the business api can access the table api just fine.

(think about it - if it could be accomplish with a gazillon standalone procedures.....)

Clarification regarding api approach

Gerardo, August 10, 2006 - 1:39 pm UTC

Tom,
sorry, may be I was not clear enough.
I am against "table apis" too, so your answer confused me a a little and leads me to my first question again. If I have a package with a table API (insert, update, and delete procedures for the table), and a user should have only update privileges, I cannot grant him to execute the api package, because the package contains an insert procedure.
Then you said, "no issues with two packages". So I understood that a second package will contain just the update procedure, and I should grant this new package to the user/role. That's why I think we would end with duplicate code and a higher maintenance cost...

Am i right ?

Tom Kyte
August 10, 2006 - 11:20 pm UTC

users should not have access to the insert/update/delete api

the users would have access to the BUSINESS, transactional api

the business transactional api would have access to the update/insert/whatever routines.

Clarification

Gerardo, August 11, 2006 - 7:50 am UTC

Perfectly clear.
Thanks a lot.

How about deployment

Henrik, September 04, 2006 - 8:30 am UTC

While I agree with most (if not all) the arguments in favor of using non-table api's, I have not been able to find any opinions about deployment.

Currently using TAPI, old software can generally run on new database versions, and new software can run on older software versions.
Customer would upgrade software to get bug-fixes. To get new features, they must opgrade both software and database.

How do the old and new versions coexists?
(To deploy new versions on a branch by branch basis,
To check if the old version had the same bug/behavior)

How do you handle distribution of new versions when using stored procedures?
(Right now distribution of client software is easy, but distribution of new db versions hard, because they are run by our dba's, which must check if there were any problems during the upgrade)

I would appreciate it if anyone, which has experience using non-tapi's in a similar environment could write a few lines about how they handle these kind of things.

Tom Kyte
September 04, 2006 - 9:11 am UTC

I'm confused - you seem to be writing a "3rd party application", something your customers buy and install.

Then you say things like:

version 3.a of my application is supported on version X.Y of oracle, if you are not on X.y and would like to use 3.a, we must get you to that release.

Let me be specific

Henrik, September 04, 2006 - 11:00 am UTC

Sorry, that was our usual lingo, which is diffent than yours.

By database version, I ment schema version, not Oracle version.

Say, we when add a column C8 to table T, we change schema version from say 3.2 to 3.3.
Our software will not try to use T.C8 until the schema version is 3.3 or above.
That way, the new software version can contain bugfixes, and new features. The bugfixes will work on both schema version 3.2 and 3.3, but the new feature, which requires column T.C8 will not be available until the schema is changed.

Tom Kyte
September 04, 2006 - 2:27 pm UTC

ahh, I'll postpone saying anything for two months (check out www.oracle.com for what large event comes in 2 months...)

Tantalizing hint

Mobra, September 05, 2006 - 8:19 am UTC

Big event in 2 months? Oracle OpenWorld...

I wouldn't be too surprised if Oracle 11 was announced during OpenWorld this year...

... and are you hinting that the next release of Oracle will have some kind of built-in support for multiple versions of the same schema... ?

That would be really cool, but could also get very confusing, I guess... ;-)



Schema Evolution??

venky, September 05, 2006 - 1:15 pm UTC

I suspect Tom is referring to Schema Evolution - care to comment, Tom??

Tom Kyte
September 05, 2006 - 5:28 pm UTC

no...

No what?

S, September 06, 2006 - 3:46 pm UTC

No, you don't want to comment or no, you're not referring to Schema Evolution or no to both of them?

Tom Kyte
September 06, 2006 - 4:12 pm UTC

no comment right now.

Tom, let's discuss Schema Evolution 'offline'

Robert, September 06, 2006 - 4:32 pm UTC

Tom,

I understand you cannot discuss anything about Schema Evolution 'officially'.... so how about let's switch over to your blog site and discuss it there... 'offline' :)

Robert.

It's been a couple months

Adam, November 02, 2006 - 8:47 am UTC

OK Tom, maybe I missed something... what came out of Oracle OpenWorld about Schema Evolution? Shall I hold my breath a bit longer?

Tom Kyte
November 02, 2006 - 9:07 am UTC

</code> http://asktom.oracle.com/Misc/podcast-and-quick-video.html <code>

listen to my podcast, I talked about it there.

A reader, February 14, 2007 - 10:48 am UTC

Can you please suggest some podcasts on oracle sql and pl/sql?

Update API with null values

Pete Y, July 08, 2008 - 11:22 am UTC

Tom,

When using an Update API, if you only need to update a subset of all the columns the API supports how would you code the API to ensure the remaining columns aren't set to null?

To clarify, only the subset of columns to be updated would have values in the API call; the remaining columns would be null in the API call but may not be on the database and we don't want to lose these non-null values.

Cheers,

Pete
Tom Kyte
July 08, 2008 - 4:30 pm UTC

well, it would make for an API that cannot be used to set columns to NULL wouldn't it...

(I hate update api's by the way, they should be an transactional api that represents a business problem to be solved - if your procedure simply "does an update", what is the point?!?)

set c1 = decode( :c1, null, c1, :c1 ), .....

that'll set c1 to itself (which does nothing to indexes by the way, it won't do nasty things to indexes at all) if :c1 is null - else it sets c1 to the bind variable value of :c1

but again, you'd be not able to set c1 to null..... ever

Update API with null values

Pete Y, July 09, 2008 - 4:32 am UTC

Thanks for quick reply.

The point of a low-level Update API is that we can log any change to the data and notify interested parties accordingly. I agree that there should be a business layer above this but by encapsulating the SQL Update in a PL/SQL program we can do this plus any other low-level validation that could be necessary. Oracle use this approach throughout their own E-Business Suite Applications.

I see that we have a few options:
1. Do not allow NULL values to be set in the Update API. I'd probably use NVL rather than DECODE as it's just simpler. However, this introduces a problem as in some cases we'll need to set a column to null (for example, when a user types a value into the wrong column - "Expiration Date" instead of "Maturity Date" or someting like that).
2. Dictate that any call to the Update API should pass in the entire row i.e. it should select the row, modify the columns it needs to change and present the entire row to the Update API. This is fine for a User Interface but doesn't sit so well in automated/batch processing on subsets of the table columns.
3. Parameterise the Update API with an "Nulls Allowed" parameter that gives it two modes of operation, one which allows Nulls and the other that does not.

Your thoughts please...
Tom Kyte
July 09, 2008 - 10:34 am UTC

... The point of a low-level Update API is that we can log any change to the data
and notify interested parties accordingly. ...

that would be the point of a business transaction actually. I cannot think of a case where anyone would want to be notified of a change to a TABLE in some random way (many transactions can hit it, you are typically only interested in ONE or FEW of them). If you needed it for all of them - well, that might - just might - be a use of a trigger.

I don't really care what you've seen other applications do, I don't blindly follow herds. I've never seen the point of these "low level api's". They turn the database into a silly VSAM keyed read file is what they do. slow by slow is all they can do, so it is all you do, so it is all slow.

And you see - in number 2 you just made my point. I would erase any batch program that used a row by row api. That isn't batch, that is bad.

My thoughts remain consistent. I hate this api, I don't see the use of it, especially if forced to use it in a batch - which should be doing SETS.

Stop thinking of the database as a bunch of rows, it is data - use sets, think sets, stop thinking row by row.

Update API with null values

Pete Y, July 11, 2008 - 5:57 am UTC

I'm not suggesting you blindly follow herds but you are a technical guru within Oracle Corporation and Oracle Corporation do this within their Applications. Therefore an Oracle technical guru decided this was the best technical strategy for one of Oracle's key suites of Applications.

We do nearly all our processing in SETS. It just so happens there's a requirement to do some row-based processing on some important business data. That may surprise you but I can assure you it's a requirement.

If I were to say to the business that we can only do SET based processing and therefore fail an entire UPDATE statement when one row fails to UPDATE I'd be gently walked off site...


Tom Kyte
July 11, 2008 - 8:23 am UTC

Oracle application developers are as capable as any Non-Oracle application developer. Read that as you may - it is a two way 'compliment'

Your logic does not work. You are basically saying "anyone that works at Oracle is a technical guru of Oracle and makes the best decisions".


If you do nearly all of your processing in SETS, your original arguments do not hold up (your list of desirable attributes, they are not in place if you don't use the API hence the auditing and everything else is destroyed, the premise for using them disappears). And in order to do row based processing, you do not need, want, require, desire this slow by slow API. You are already doing things slow, why make it slower?


You are missing the point here, you can do row by row - and you certainly do not need this API to do it. And if you provide this API - what typically happens (about 100% of the time) is the set based stuff is ignored by the developers who get their marching orders to use the API.

Nulls and Update APIs

nameless, July 14, 2008 - 11:37 am UTC

The way that's usually handled is to do something a bit like Indicator Variables in Pro*C. So, the construct would be ...

set col = decode(:ind_var,1,:var, col)

But that would be only if I were to use an Update API.

Excellent explanation

Srimal Ekkadu, July 14, 2008 - 4:06 pm UTC

This is a great response and a review for the question. I have learnt quite a bit from reading your responses to various questions on this subject in this thread...

Thank you

Business Logic

Curious George, July 15, 2008 - 8:00 am UTC

If I place my business logic in the Stored Procedures what is point in buying any of the expensive Application servers? And what is the point of having a n-tier architecture? Wouldn't company's save money on the middle ware purchase if they don't have to buy them?

Tom Kyte
July 15, 2008 - 9:52 am UTC

application servers provide a layer, the layer that interacts with human beings and does stuff. Something has to figure out - based on what end users did - what to do. They provide this interaction.


In the olden days we had databases (like we do now)
we had our transactions (we put them into something like CICS)
we had our application layer (ISPF panels)

today we have databases
we have our transactions (I prefer to put them into stored procedures for infinite reuse since....)
we have our application layer (which changes from day to day, week to week)

Business Logic

Curious George, July 15, 2008 - 11:03 am UTC

Does this mean we need the application servers to render the application content? shouldn't the company just install a web server to do this and save the expense of buying the application server and the expensive license?
Tom Kyte
July 15, 2008 - 8:06 pm UTC

depends on what you are doing doesn't it.

rhetorical question.

User interfaces can be quite complex, horribly so. State management (if needed), complex branching logic, validation of data - interacting with the user, implementing a shopping cart for example.

UI's are not "easy"


I myself find I can accomplish most of what I need personally with APEX ( http://apex.oracle.com/ ), yes.

Set-based updates do not mean all or nothing

Salman Syed, July 15, 2008 - 11:47 am UTC

Pete Y,

When performing set-based updates/inserts/deletes, you can still log out the rows that error out using the log errors into clause. I have been using it for a while now and it works like a charm.


re: need of application servers

Stew Ashton, July 29, 2008 - 7:37 am UTC


1. Web Servers serve only static content such as html files. Applications need to serve dynamic content, which changes based on user interaction. This can be done by adding modules (such as PHP or MOD_PLSQL) to the Web Server, or by using an Application Server.

2. The Application Server is a separate process that sticks around, so it can handle state such as user sessions, and it can manage reusable resources such as connection pools.

3. Application Servers are meant to contain not only "presentation logic" (UIs), but also "application logic" (business rules, calls to different services, transactions) and "data access logic" (object/relational mapping, SQL, prepared statement caches, connection pools).

What Tom calls the "application layer", the Application Server folks would call the "presentation layer".

4. I follow Tom's "Business API" approach, so I wouldn't ask the Application Server to do everything, but I still use it. I agree that for standalone, data-centric applications PHP or APEX are realistic alternatives.

Mark's Table API.

Ram, April 10, 2010 - 10:03 pm UTC

Tom,

I was going thru this post and was interested in Mark Wooldrige's Table API...could you please email me the API code or introduce me to Mark...

Thanks,
Ram

A reader, July 23, 2010 - 8:27 am UTC

Tom,

Is there anyway I can still get Mark Wooldrige's Table API...could you please email me the API code or introduce me to Mark...

Thanks,
Ram


Tom Kyte
July 23, 2010 - 9:50 am UTC

sorry, I never had it and do not have his contact information.

try psp.web to develope full functional web app using pl/sql

kaven276, April 17, 2012 - 4:19 am UTC

All logic including db manipulation and http/html process can be integrated togother, beyond oracle's ancient psp, I invent psp.web,
see https://github.com/kaven276/psp.web

PSP.WEB use PL/SQL programing language to do web development, It's a language of DB stored procedure, that is different from most of the other web developing languages and platforms such as J2EE .Net, PHP, RUBY. With PSP.WEB platform, PL/SQL can use PSP.WEB APIs to gain http request info(request line parts, http header info, form submit, fileupload, request body and etc...), do nature data processing (insert,update,delete,select and PL/SQL code), and print page (header info and page body) to http response. It's the most proper way to develop database(oracle) based web applications.

PL/SQL has natural advantages, using PL/SQL for developing web site/application is seductive. But naturally, PL/SQL have no touch with http protocol, If we provide a http listener (such as nodeJS based) that can communicate with oracle PL/SQL, we can invent a whole new stored-procedure based web platform that is the most integrated and convenient platform taking both application layer and database layer together. That is just the way PSP.WEB do. Now PL/SQL leverage it's power to web development in it's most. All the sections below will tell you the unique features of PSP.WEB that other web-dev techs do not reach.

PSP.WEB realize the "Database centric architecture"

kaven276, April 17, 2012 - 4:23 am UTC

<a href=" https://github.com/kaven276/psp.web" >PSP.WEB</a>
at " https://github.com/kaven276/psp.web"
will realize the "Database centric architecture" that the topic discuss.

<br/>
Please try, Now it support oracle 11g only, and it will support 10g later.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library