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.
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.
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?
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!
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.
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
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?
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.
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.
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
.}
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
.}
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
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.
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 ?
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 ?
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.
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.
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??
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?
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?
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
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...
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...
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?
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?
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
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