building maintainable applications in apex

Building Maintainable Applications in Apex

Jeffrey Kemp

AUSOUG Perth, November 2014

All artifacts including code are presented for illustration

purposes only. Use at your own risk. Test thoroughly in

a non-critical environment before use.

Agenda

Maintainable PL/SQL

Case Study

MVC

TAPIs & XAPIs

Starting your Next App

3 Controversial Statements

2 Interesting Quotes

1 LOTR reference

Also

Is this your application?

Lots of processes and conditions

Complex interactions

Difficult to learn and refactor

Low reuse

Challenges

Readable

Testable

Maintainability

Maintainability is in the eye of the beholder maintainer.

Maintainability

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”

- Martin Golding / John F. Woods (?)

“Always code as if the guy who ends up maintaining your code:

is reasonably smart but has not read it all;

knows about 50% of the language; and

will probably present some of your code at a future user group meeting.”

- J.K.

DRY

Consistency

Naming

Single-purpose

Assertions

One exit point

Techniques

#include "SDL.h" #define $ for(O=9 #define CX M+=(T%3+2*!(!T*t-6)) #define x ,A=4*!T,O=t,W=h=T<3?u(Q?p:D(A+3),D(A),D(A+1)[i]+D(A+2)*g+):K(t),U=V=K(a),o?U=h,W=V:V, #define C 8*-~L #define Z short #define y a(Z)Y[++O] #define B ),a--||( #define _ ),e--||( #define V(I,D,E)(O=a(I)h[r])&&!(A=(D)(V=(1[E+L]<<16)+*i)/O,A-(I)A)?1[E+L]=V-O*(*E=A):H(0) #define i(B,M)B(o){return M;} #define R(O,M,_)(S=L?a(I Z)O:O,N=L?a(I Z)O M(f=a(I Z)_):(O M(f=a(I n)_))) #define T(_)R(r[u(10,L=4,--)],=,_) #define u(a,r,T)16*i[a]+(I Z)(T i[r]) #define a(_)*(_*)& #define L(_)M(W,_,U) #define M(S,F,T)R(r[S],F,r[T]) #define A(_)(i[L=4]+=2,R(_,=,r[u(10,4,-2+)])) #define c(R,T)(1[u=19,L+T]=(N=a(R)h[r]*(R)*T)>>16,*i=N,G(F(N-(R)N))) #define h(_)(1&(L?a(Z)_:_)>>C-1) #define I unsigned #define n char #define e(_)v(F(40[L(_##=40[E]+),E]&N==S|_ N<_(int)S)) I n t,e,l[80186],*E,m,u,L,a,T,o,r[1<<21],X,*Y,b,Q,R;I Z*i,M,p,q=3;I*localtime(),f,S,kb,h,W,U,c,g,d,V,A;N,O,P=983040,j[5];SDL_Surface*k;i(F,40[E]=!!o)i(z,42[E]=!!o)i(D,r[a(I)E[259+4*o]+O])i(w,i[o]+=~(-2*47[E])*~L)i(v,G(N-S&&1&(40[z((f^=S^N)&16),E]^f>>C-1)))J(){V=61442;$;O--;)V+=40[E+O]<<D(25);}i(H,(46[u=76,J(),T(V),T(9[i]),T(M),M(P+18,=,4*o+2),R(M,=,r[4*o]),E]=0))s(o){$;O--;)40[E+O]=1&&1<<D(25)&o;}i(BP,(*i+=262*o*z(F((*E&15)>9|42[E])),*E&=15))i(SP,(w(7),R&&--1[i]&&o?R++,Q&&Q++,M--:0))DX(){$,O*=27840;O--;)O[(I*)k->pixels]=-!!(1<<7-O%8&r[O/2880*90+O%720/8+(88+952[l]/128*4+O/720%4<<13)]);SDL_Flip(k);}main(BX,nE)n**nE;{9[i=E=r+P]=P>>4;$;q;)j[--q]=*++nE?open(*nE,32898):0;read(2[a(I)*i=*j?lseek(*j,0,2)>>9:0,j],E+(M=256),P);$;Y=r+16*9[i]+M,Y-r;Q|R||kb&46[E]&&KB)--64[T=1[O=32[L=(X=*Y&7)&1,o=X/2&1,l]=0,t=(c=y)&7,a=c/8&7,Y]>>6,g=~-T?y:(n)y,d=BX=y,l],!T*t-6&&T-2?T-1?d=g:0:(d=y),Q&&Q--,R&&R--x(O=*Y,O=u=D(51),e=D(8),m=D(14)_ O=*Y/2&7,M+=(n)c*(L^(D(m)[E]|D(22)[E]|D(23)[E]^D(24)[E]))_ L=*Y&8,R(K(X)[r],=,c)_ L=e+=3,o=0,a=X x a=m _ T(X[i])_ A(X[i])_ a<2?M(U,+=1-2*a+,P+24),v(f=1),G(S+1-a==1<<C-1),u=u&4?19:57:a-6?CX+2,a-3||T(9[i]),a&2&&T(M),a&1&&M(P+18,=,U+2),R(M,=,U[r]),u=67:T(h[r])_(W=U B u=m,M-=~L,R(W[r],&,d)B 0 B L(=~)B L(=-),S=0,u=22,F(N>S)B L?c(I Z,i):c(I n,E)B/**/L?c(Z,i):c(n,E)B L?V(I Z,I,i):V(I n,I Z,E)B L?V(Z,int,i):V(n,Z,E))_++e,h=P,d=c,T=3,a=m,M--_++e,13[W=h,i]=(o|=!L)?(n)d:d,U=P+26,M-=~!o,u=17+(m=a)_(a=m B L(+=),F(N<S)B L(|=)B e(+)B e(-)B L(&=)B L(-=),F(N>S)B L(^=)B L(-),F(N>S)B L(=))_!L?L=a+=8 x L(=):!o?Q=1,R(r[p=m x V],=,h):A(h[r])_ T=a=0,t=6,g=c x M(U,=,W)_(A=h(h[r]),V=m?++M,(n)g:o?31&2[E]:1)&&(a<4?V%=a/2+C,R(A,=,h[r]):0,a&1?R(h[r],>>=,V):R(h[r],<<=,V),a>3?u=19:0,a<5?0:F(S>>V-1&1)B R(h[r],+=,A>>C-V),G(h(N)^F(N&1))B A&=(1<<V)-1,R(h[r],+=,A<<C-V),G(h(N*2)^F(h(N)))B R(h[r],+=(40[E]<<V-1)+,A>>1+C-V),G(h(N)^F(A&1<<C-V))B R(h[r],+=(40[E]<<C-V)+,A<<1+C-V),F(A&1<<V-1),G(h(N)^h(N*2))B G(h(N)^F(h(S<<V-1)))B G(h(S))B 0 B V<C||F(A),G(0),R(h[r],+=,A*=~((1<<C)-1>>V)))_(V=!!--1[a=X,i]B V&=!m[E]B V&=m[E]B 0 B V=!++1[i]),M+=V*(n)c _ M+=3-o,L?0:o?9[M=0,i]=BX:T(M),M+=o*L?(n)c:c _ M(U,&,W)_ L=e+=8,W=P,U=K(X)_!R||1[i]?M(m<2?u(8,7,):P,=,m&1?P:u(Q?p:11,6,)),m&1||w(6),m&2||SP(1):0 _!R||1[i]?M(m?P:u(Q?p:11,6,),-,u(8,7,)),43[u=92,E]=!N,F(N>S),m||w(6),SP(!N==b):0 _ o=L,A(M),m&&A(9[i]),m&2?s(A(V)):o||(4[i]+=c)_ R(U[r],=,d)_ 986[l]^=9,R(*E,=,l[m?2[i]:(n)c])_ R(l[m?2[i]:(n)c],=,*E)_ R=2,b=L,Q&&Q++_ W-U?L(^=),M(U,^=,W),L(^=):0 _ T(m[i])_ A(m[i])_ Q=2,p=m,R&&R++_ L=0,O=*E,F(D(m+=3*42[E]+6*40[E])),z(D(1+m)),N=*E=D(m-1)_ N=BP(m-1)_ 1[E]=-h(*E)_ 2[i]=-h(*i)_ 9[T(9[i]),T(M+5),i]=BX,M=c _ J(),T(V)_ s(A(V))_ J(),s((V&~m)+1[E])_ J(),1[E]=V _ L=o=1 x L(=),M(P+m,=,h+2)_++M,H(3)_ M+=2,H(c&m)_++M,m[E]&&H(4)_(c&=m)?1[E]=*E/c,N=*E%=c:H(0)_*i=N=m&E[L=0]+c*1[E]_*E=-m[E]_*E=r[u(Q?p:m,3,*E+)]_ m[E]^=1 _ E[m/2]=m&1 _ R(*E,&,c)_(a=c B write(1,E,1)B time(j+3),memcpy(r+u(8,3,),localtime(j+3),m)),a<2?*E=~lseek(O=4[E][j],a(I)5[i]<<9,0)?((I(*)())(a?write:read))(O,r+u(8,3,),*i):0:0),O=u,D(16)?v(0):D(17)&&G(F(0)),CX*D(20)+D(18)-D(19)*~!!L,D(15)?O=m=N,41[43[44[E]=h(N),E]=!N,E]=D(50):0,!++q?kb=1,*l?SDL_PumpEvents(),k=k?k:SDL_SetVideoMode(720,348,32,0),DX():k?SDL_Quit(),k=0:0:0;}i(G,48[E]=o)i(K,P+(L?2*o:2*o+o/4&7))

SELECT a FROM t;

instead of

OPEN CURSOR FOR 'select a from t';

Quality PL/SQL

max_lines_per_transaction CONSTANT NUMBER := 1000;

IF line_count > max_lines_per_transaction THEN

instead of

IF line_count > 1000 THEN

Quality PL/SQL

TRANSACTION_PKG.submit (tran_id => r.tran_id ,source_id => NULL ,description => r.description ,approved_by => r.updated_by); instead of TRANSACTION_PKG.submit (r.tran_id, NULL, r.description, r.updated_by);

Quality PL/SQL

Controversial Statement #1

p_this

l_is

v_an

g_awful

cl_way

lol_to

bbq_write

$!#*_code

v_no v_more v_hungarian, v_please!

FUNCTION get_name (event_id IN events.event_id%TYPE) RETURN events.event_name%TYPE IS event_name events.event_name%TYPE; BEGIN IF event_id IS NOT NULL THEN SELECT e.event_name INTO event_name FROM events e WHERE e.event_id = get_name.event_id; END IF; RETURN event_name; END get_name;

Aliases

table alias function alias

Be consistent.

More importantly…

FUNCTION get_name (event_id IN events.event_id%TYPE) RETURN events.event_name%TYPE IS event_name events.event_name%TYPE; BEGIN IF event_id IS NOT NULL THEN SELECT e.event_name INTO event_name FROM events e WHERE e.event_id = get_name.event_id; END IF; RETURN event_name; END get_name;

Clean code

FUNCTION get_name (event_id IN events.event_id%TYPE) RETURN events.event_name%TYPE IS event_name events.event_name%TYPE; BEGIN IF event_id IS NOT NULL THEN SELECT events.event_name INTO event_name FROM events WHERE events.event_id = get.event_id; END IF; RETURN event_name; END get;

“Ugly” code?

Fun for future maintainers PROCEDURE rtrv_evnamev1 (p_no NUMBER ,p_nm OUT VARCHAR2 ,p_dt OUT DATE) IS BEGIN --IF p_id < -100 THEN -- g_nm := 'Invalid'; --END IF; FOR r IN (SELECT event_name, start_date FROM events WHERE event_id = p_no ) LOOP p_nm := r.event_name; p_dt := r.start_date; END LOOP; UPDATE events SET start_date = TRUNC(start_date) WHERE event_id = p_no; END;

GENERIC_PKG.get_event (event_id => nv('P1_EVENT_ID'));

GENERIC_PKG.get_member (member_id => nv('P1_MEMBER_ID'));

EVENT_PKG.get (event_id => nv('P1_EVENT_ID'));

MEMBER_PKG.get (member_id => nv('P1_MEMBER_ID'));

Package names as context

Large government department

New Apex 4.2 app

Co-hosted with eBus database instance

Integrate with Oracle Financials and OBIEE

Interfaces with other transactional systems

In-house experience - PL/SQL

Project Background

Simplified

MVC Architecture

Process: load

Get PK value

Call TAPI to query record

Set session state for each column

load

Validation

Get values from session state into record

Pass record to TAPI to validate

Return error message

validate

process page request

Get v('REQUEST')

Get values from session state into record

Pass record to TAPI to insert, update or delete

process

Parameters?

v()

APEX_UTIL.set_session_state()

(commit issue)

Apex Logic

logic PROCEDURE set_session_state

(p_name IN VARCHAR2, p_value IN VARCHAR2) AS

BEGIN

IF v(p_name) = p_value OR (v(p_name) IS NULL AND p_value IS NULL) THEN

UPDATE apex_session_data SET item_value = p_value WHERE session_id = nv('SESSION') AND app_id = nv('APP_ID')

AND item_name = p_name;

COMMIT;

END IF;

END set_session_state; DISCLAIMER: this is fictional code!!! WARNING: DO NOT copy & paste!!!

If the value has changed…

update it…

then commit.

PROCEDURE sv (p_name IN VARCHAR2 ,p_value IN VARCHAR2 := NULL) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN APEX_UTIL.set_session_state (p_name => p_name ,p_value => p_value); COMMIT; END sv;

Wrapper for set_session_state

Process a page request PROCEDURE p9_process IS

rv VOLUNTEERS$TAPI.rvtype;

r VOLUNTEERS$TAPI.rowtype;

BEGIN

CASE

WHEN APEX_APPLICATION.g_request IN ('CREATE','SAVE') THEN

rv := p9_get;

IF rv.vol_id IS NULL THEN

r := VOLUNTEERS$TAPI.ins (rv => rv);

success('Record created.');

ELSE

r := VOLUNTEERS$TAPI.upd (rv => rv);

success('Record updated.');

END IF;

p9_set(r);

WHEN APEX_APPLICATION.g_request = 'DELETE' THEN

VOLUNTEERS$TAPI.del

(vol_id => nv('P9_VOL_ID'));

success('Record deleted.');

clear_page_cache;

ELSE

NULL;

END CASE;

END p9_process;

PROCEDURE success (msg IN VARCHAR2) IS BEGIN IF APEX_APPLICATION.g_print_success_message IS NOT NULL THEN APEX_APPLICATION.g_print_success_message := APEX_APPLICATION.g_print_success_message || '<br>'; END IF; APEX_APPLICATION.g_print_success_message := APEX_APPLICATION.g_print_success_message || msg; END success;

Set success message

PROCEDURE clear_page_cache IS

BEGIN

APEX_UTIL.clear_page_cache(APEX_APPLICATION.g_flow_id);

END clear_page_cache;

clear_page_cache

It’s ok to call v().

A lot.


get_row FUNCTION p9_get RETURN VOLUNTEERS$TAPI.rvtype IS rv VOLUNTEERS$TAPI.rvtype; BEGIN rv.vol_id := nv('P9_VOL_ID'); rv.given_name := v('P9_GIVEN_NAME'); rv.surname := v('P9_SURNAME'); rv.date_of_birth := v('P9_DATE_OF_BIRTH'); rv.address_line := v('P9_ADDRESS_LINE'); rv.suburb := v('P9_SUBURB'); rv.postcode := v('P9_POSTCODE'); rv.state := v('P9_STATE'); rv.home_phone := v('P9_HOME_PHONE'); rv.mobile_phone := v('P9_MOBILE_PHONE'); rv.email_address := v('P9_EMAIL_ADDRESS'); rv.version_id := nv('P9_VERSION_ID'); RETURN rv; END p9_get;

set row PROCEDURE p9_set (r IN VOLUNTEERS$TAPI.rowtype) IS BEGIN sv('P9_VOL_ID', r.vol_id); sv('P9_GIVEN_NAME', r.given_name); sv('P9_SURNAME', r.surname); sd('P9_DATE_OF_BIRTH', r.date_of_birth); sv('P9_ADDRESS_LINE', r.address_line); sv('P9_STATE', r.state); sv('P9_SUBURB', r.suburb); sv('P9_POSTCODE', r.postcode); sv('P9_HOME_PHONE', r.home_phone); sv('P9_MOBILE_PHONE', r.mobile_phone); sv('P9_EMAIL_ADDRESS', r.email_address); sv('P9_VERSION_ID', r.version_id); END p9_set;

PKG.proc;

PL/SQL in Apex

SELECT t.col_a ,t.col_b ,t.col_c FROM my_table t; Move joins, select expressions, etc. to a view

except Apex-specific stuff like generated APEX_ITEMs

Always alias everything

SQL in Apex

Templates, Regions, Items, Reports, etc.

Conditions (simple)

Branches

Authorisation Schemes

Build Options

Keep in Apex

Pros

Fast development

Smaller apex app

Dependency analysis

Procedural control

Refactoring

Modularity

Code re-use

Customisation

Version control

Misspelled/missing item names

Mitigation: isolate all apex code in one package

Enforce naming conventions – e.g. P1_COLUMN_NAME

Apex Advisor doesn’t check database package code

Performance?

Cons

All v() calls at start of proc, once per item

All sv() calls at end of proc

Dynamic Actions calling PL/SQL – use parameters

Replace PL/SQL with Javascript (where possible)

- maintainable?

Performance

Validate - only record-level validation

Cross-record validation – db constraints or XAPI

Capture DUP_KEY_ON_VALUE and ORA-02292 for unique and referential constraints

RAISE APPLICATION_ERROR (-20000, 'User-friendly error message');

Error Handling

Process: load & process

Encapsulate all DML for a table

Row-level validation

Detect lost updates

Don’t write first cut by hand – generate them all

(first cut doesn’t have to be 100% perfect)

TAPIs

Record types rowtype, arraytype, validation record

Functions/Procedures ins / upd / del / merge / get as needed

bulk_ins / bulk_upd / bulk_merge / get_all as needed

Constants for enumerations

All parameters are VARCHAR2 or TAPI record/array types

TAPI contents

Downside: Updateable SQL Reports not easy

Mitigation: exception to the rule?

TAPI cons

TAPI example (variation 1) PACKAGE XXSPS_PAYMENT_GATEWAYS$TAPI AS

SUBTYPE rowtype IS xxsps_payment_gateways%ROWTYPE;

TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER;

FUNCTION val

(pg_name IN VARCHAR2

,pg_notes IN VARCHAR2

,pg_start_date IN VARCHAR2 := NULL

,pg_end_date IN VARCHAR2 := NULL

) RETURN VARCHAR2;

FUNCTION ins

(pg_name IN VARCHAR2




) RETURN rowtype;

FUNCTION upd (pg_id IN NUMBER ,pg_name IN VARCHAR2 ,pg_notes IN VARCHAR2 ,pg_start_date IN VARCHAR2 := NULL ,pg_end_date IN VARCHAR2 := NULL ,version_id IN NUMBER ) RETURN rowtype; PROCEDURE del (pg_id IN NUMBER ,version_id IN NUMBER); FUNCTION get (pg_id IN NUMBER) RETURN rowtype; FUNCTION get_id (pg_name IN VARCHAR2) RETURN NUMBER; FUNCTION get_next (yr_year IN NUMBER := NULL) RETURN NUMBER; FUNCTION is_valid (pg_id IN NUMBER) RETURN BOOLEAN; END XXSPS_PAYMENT_GATEWAYS$TAPI;

TAPI example (variation 2) PACKAGE XXSPS_PAYMENT_GATEWAYS$TAPI AS

SUBTYPE rowtype IS xxsps_payment_gateways%ROWTYPE;

TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER; TYPE rvtype IS RECORD (pg_id NUMBER ,pg_name VARCHAR2(4000) ,pg_notes VARCHAR2(4000) ,pg_start_date VARCHAR2(4000) ,pg_end_date VARCHAR2(4000) ,version_id NUMBER);

FUNCTION val (rv IN rvtype) RETURN VARCHAR2;

FUNCTION ins (rv IN rvtype) RETURN rowtype;

FUNCTION upd (rv IN rvtype) RETURN rowtype;

PROCEDURE del (rv IN rvtype);

FUNCTION get (pg_id IN NUMBER) RETURN rowtype;

FUNCTION get_id (rv IN rvtype) RETURN NUMBER;

FUNCTION get_next (yr_year IN NUMBER := NULL) RETURN NUMBER;

FUNCTION is_valid (pg_id IN NUMBER) RETURN BOOLEAN;

END XXSPS_PAYMENT_GATEWAYS$TAPI;

FUNCTION get (pg_id IN NUMBER) RETURN rowtype IS r rowtype; BEGIN IF pg_id IS NOT NULL THEN SELECT x.* INTO r FROM xxsps_payment_gateways x WHERE x.pg_id = get.pg_id; END IF; RETURN r; END get;

TAPI get

FUNCTION get (pg_id IN NUMBER) RETURN rowtype IS r rowtype; BEGIN UTIL_PKG.log_start($$PLSQL_UNIT, 'get'); IF pg_id IS NOT NULL THEN SELECT x.* INTO r FROM xxsps_payment_gateways x WHERE x.pg_id = get.pg_id; END IF; UTIL_PKG.log_end(r.pg_id || ',' || r.pg_name); RETURN r; EXCEPTION WHEN NO_DATA_FOUND THEN UTIL_PKG.log_end('NO_DATA_FOUND'); RAISE; WHEN UTIL_PKG.application_error THEN UTIL_PKG.log_end('application_error'); RAISE; WHEN OTHERS THEN UTIL_PKG.raise_error(SQLERRM); END get;

Unexpurgated Version

TAPI ins FUNCTION ins (pg_name IN VARCHAR2 ,pg_notes IN VARCHAR2 ,pg_start_date IN VARCHAR2 := NULL ,pg_end_date IN VARCHAR2 := NULL ) RETURN rowtype IS r rowtype; error_msg VARCHAR2(32767); BEGIN error_msg := val (pg_name => pg_name ,pg_notes => pg_notes ,pg_start_date => pg_start_date ,pg_end_date => pg_end_date); IF error_msg IS NOT NULL THEN UTIL_PKG.raise_error(error_msg); END IF;

INSERT INTO xxsps_payment_gateways

(pg_id

,pg_name

,pg_notes

,pg_start_date

,pg_end_date)

VALUES(XXSPS_PG_ID_SEQ.NEXTVAL

,ins.pg_name

,ins.pg_notes

,TO_DATE(ins.pg_start_date,UTIL_PKG.DATE_FORMAT)

,TO_DATE(ins.pg_end_date,UTIL_PKG.DATE_FORMAT))

RETURNING

pg_id

,...

INTO r.pg_id

,r....;

RETURN r;

END ins;

FUNCTION val (pg_name IN VARCHAR2 ,pg_notes IN VARCHAR2 ,pg_start_date IN VARCHAR2 := NULL ,pg_end_date IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS buf VARCHAR2(32767); BEGIN UTIL_PKG.val_not_null (val => pg_name, label => 'Gateway Name', buf => buf); UTIL_PKG.val_max_len (val => pg_name, len => 30, label => 'Gateway Name', buf => buf); UTIL_PKG.val_max_len (val => pg_notes, len => 300, label => 'Notes', buf => buf); UTIL_PKG.val_date_range (start_date => pg_start_date ,end_date => pg_end_date ,label => 'Gateway' ,buf => buf); RETURN buf; END val;

TAPI val

TAPI upd (page 1 of 2)

FUNCTION upd

(pg_id IN NUMBER

,pg_name IN VARCHAR2




,version_id IN NUMBER

) RETURN rowtype IS

r rowtype;

error_msg VARCHAR2(32767);

BEGIN

UTIL_PKG.assert(pg_id IS NOT NULL ,'pg_id cannot be NULL');

UTIL_PKG.assert(version_id IS NOT NULL ,'version_id cannot be NULL');

error_msg := val

(pg_name => pg_name

,pg_notes => pg_notes

,pg_start_date => pg_start_date

,pg_end_date => pg_end_date);

IF error_msg IS NOT NULL THEN

UTIL_PKG.raise_error(error_msg);

END IF;

TAPI upd (page 2 of 2)

UPDATE xxsps_payment_gateways x

SET x.pg_name = upd.pg_name

,x.pg_notes = upd.pg_notes

,x.pg_start_date = TO_DATE(upd.pg_start_date ,UTIL_PKG.DATE_FORMAT)

,x.pg_end_date = TO_DATE(upd.pg_end_date ,UTIL_PKG.DATE_FORMAT)

WHERE x.pg_id = upd.pg_id

AND x.version_id = upd.version_id

RETURNING

pg_id

,...

INTO r.pg_id

,r....;

IF SQL%NOTFOUND THEN

RAISE UTIL_PKG.lost_update;

END IF;

RETURN r;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

UTIL_PKG.raise_dup_val_on_index;

WHEN UTIL_PKG.lost_update THEN

lost_upd (pg_id => pg_id);

END upd;

PROCEDURE lost_upd (pg_id IN NUMBER) IS db_last_updated_by xxsps_payment_gateways.last_updated_by%TYPE; db_last_updated_date xxsps_payment_gateways.last_updated_date%TYPE; BEGIN SELECT x.last_updated_by ,x.last_updated_date INTO lost_upd.db_last_updated_by ,lost_upd.db_last_updated_date FROM xxsps_payment_gateways x WHERE x.pg_id = lost_upd.pg_id; UTIL_PKG.raise_lost_update_error (last_updated_by => db_last_updated_by ,last_updated_date => db_last_updated_date); EXCEPTION WHEN NO_DATA_FOUND THEN UTIL_PKG.raise_error('LOST_UPDATE_DELETED'); END lost_upd;

Lost update handler

PROCEDURE del

(pg_id IN NUMBER

,version_id IN NUMBER) IS

r rowtype;


BEGIN

UTIL_PKG.assert(pg_id IS NOT NULL ,'pg_id cannot be NULL');

UTIL_PKG.assert(version_id IS NOT NULL ,'version_id cannot be NULL');

r := get(pg_id => pg_id);

-- delete child records

XXSPS_PAYMENT_GATEWAY_YEA$TAPI.del_pg (pgy_pg_id => r.pg_id);

TAPI del DELETE xxsps_payment_gateways x

WHERE x.pg_id = del.pg_id

AND x.version_id = del.version_id;

IF SQL%NOTFOUND THEN


END IF;

EXCEPTION

WHEN UTIL_PKG.ref_constraint_violation THEN

UTIL_PKG.raise_del_ref_con_violation;


lost_upd (pg_id => pg_id);

END del;

PROCEDURE bulk_ins (arr IN arraytype) IS

i BINARY_INTEGER;


BEGIN

i := arr.FIRST;

LOOP

EXIT WHEN i IS NULL;

error_msg := val

(ftl_ft_id => arr(i).ftl_ft_id

,ftl_line_no => arr(i).ftl_line_no

,ftl_line_type => arr(i).ftl_line_type

,ftl_line_description => arr(i).ftl_line_description

,ftl_line_amount => arr(i).ftl_line_amount

,ftl_sch_code => arr(i).ftl_sch_code);



END IF;

i := arr.NEXT(i);

END LOOP;

TAPI bulk_ins FORALL i IN INDICES OF arr

INSERT INTO xxsps_funding_trans_lines

(ftl_ft_id

,ftl_line_no

,ftl_line_type

,ftl_line_description

,ftl_line_amount

,ftl_sch_code)

VALUES (arr(i).ftl_ft_id

,arr(i).ftl_line_no

,arr(i).ftl_line_type

,arr(i).ftl_line_description

,arr(i).ftl_line_amount

,arr(i).ftl_sch_code);

EXCEPTION


XXSPS_ERROR.raise_dup_val_on_index;

END bulk_ins;

PROCEDURE bulk_upd

(rowids IN rowidarray

,arr IN arraytype) IS

i BINARY_INTEGER;


lost_upd_index NUMBER;

BEGIN

i := arr.FIRST;

LOOP

EXIT WHEN i IS NULL;

error_msg := val

(ftl_ft_id => arr(i).ftl_ft_id

,ftl_line_no => arr(i).ftl_line_no

,ftl_line_type => arr(i).ftl_line_type

,ftl_line_description => arr(i).ftl_line_description

,ftl_line_amount => arr(i).ftl_line_amount

,ftl_sch_code => arr(i).ftl_sch_code

,ftl_hold_payment_ind => arr(i).ftl_hold_payment_ind);



END IF;

i := arr.NEXT(i);

END LOOP;

TAPI bulk_upd FORALL i IN INDICES OF arr

UPDATE xxsps_funding_trans_lines

SET ftl_line_type = arr(i).ftl_line_type

,ftl_line_description = arr(i).ftl_line_description

,ftl_line_amount = arr(i).ftl_line_amount

,ftl_sch_code = arr(i).ftl_sch_code

,ftl_hold_payment_ind = arr(i).ftl_hold_payment_ind

WHERE ROWID = rowids(i)

AND version_id = arr(i).version_id;

FOR i IN 1..arr.COUNT LOOP

IF SQL%BULK_ROWCOUNT(i) = 0 THEN

lost_upd_index := i;


END IF;

END LOOP;

EXCEPTION


UTIL_PKG.raise_dup_val_on_index;


lost_upd (p_rowid => rowids(lost_upd_index));

END bulk_upd;

TAPI_PACKAGE_SPEC CONSTANT VARCHAR2(32767) := q'[ CREATE OR REPLACE PACKAGE #TAPI# AS SUBTYPE rowtype IS #table#%ROWTYPE; TYPE arraytype IS TABLE OF rowtype INDEX BY BINARY_INTEGER; TYPE rvtype IS RECORD (#REC_COLS#); FUNCTION val (rv IN rvtype) RETURN VARCHAR2; FUNCTION ins (rv IN rvtype) RETURN rowtype; FUNCTION upd (rv IN rvtype) RETURN rowtype; PROCEDURE del (rv IN rvtype); FUNCTION get (rv IN rvtype) RETURN rowtype; END #TAPI#; ]';

TAPI Generator

FUNCTION cols (table_name IN VARCHAR2 ,template IN VARCHAR2 ,sep IN VARCHAR2 ) RETURN VARCHAR2 IS buf VARCHAR2(32767); BEGIN FOR r IN ( SELECT c.column_name FROM user_tab_cols c WHERE c.table_name = UPPER(cols.table_name) AND c.virtual_column = 'NO' AND c.hidden_column = 'NO' ORDER BY c.column_id ) LOOP IF buf IS NOT NULL THEN buf := buf || CHR(12) || sep; END IF; buf := buf || REPLACE(template, '#col#', LOWER(r.column_name)); END LOOP; RETURN buf; END cols;

TAPI Generator

PROCEDURE create_tapi (table_name IN VARCHAR2) IS

buf CLOB;

BEGIN

buf := REPLACE(TAPI_PACKAGE_SPEC, '#TAPI#', SUBSTR(table_name,25) || '$TAPI');

buf := REPLACE(TAPI_PACKAGE_SPEC, '#table#', table_name);

buf := REPLACE(TAPI_PACKAGE_SPEC, '#REC_COLS#'

,cols(table_name => table_name

,template => '#col# VARCHAR2(4000)'

,sep => ','));

EXECUTE IMMEDIATE buf;

-- and then similar for TAPI_PACKAGE_BODY (left as exercise for reader)

END create_tapi;

TAPI Generator

Transaction-level validation

Special cross-table validation

Not always necessary

Easy to retrofit later

Code re-use made easy

XAPIs

SUBTYPE rowtype IS xxsps_interaction_vw%ROWTYPE;

PROCEDURE submit_transaction

(hdr IN rowtype

,lines IN XXSPS_FUNDING_TRANS_LINES$TAPI.arraytype ) IS

tran XXSPS_TRANSACTION_PKG.rowtype;

ftls XXSPS_FUNDING_TRANS_LINES$TAPI.arraytype

BEGIN

val (hdr => hdr ,lines => lines);

tran := XXSPS_TRANSACTION_PKG.get_new (tt_id => hdr.tt_related_tt_id);

XAPI example tran := XXSPS_TRANSACTION_PKG.ins

(ft_si_id => hdr.si_id

,ft_tt_id => hdr.tt_related_tt_id

,ft_description => tran.ft_description

,ft_yr_year => hdr.si_yr_year

,ft_pg_id => NVL(hdr.si_pg_id, tran.ft_pg_id)

,ft_salary_amount => tran.ft_salary_amount

,ft_cash_amount => tran.ft_cash_amount);

-- copy the new ID to the child rows

FOR i IN 1..lines.COUNT LOOP

ftls(i) := lines(i);

ftls(i).ftl_ft_id := tran.ft_id;

END LOOP;

XXSPS_FUNDING_TRANS_LINES$TAPI.bulk_ins(arr => ftls);

END submit_interaction;

Steven Feuerstein: “Don’t Repeat SQL Statements”

Call TAPI functions that return record arrays, ref cursors or use a table function

Conversely: Tuning a complex, general-purpose query is more difficult than tuning a complex, single-purpose query.

What about queries?

Database Architecture

Debug logging & error handling

Deployment package for DDL

Unit testing framework

Code quality standards

Getting Started

Simple – one primary UI (Apex)


Apex

data

integration inter- face

schemas interfaces

Shared – multiple UIs


Apex Apex

data x x

integration inter- face

schemas interfaces

YAGNI.


YAGNI?

“Ref Codes” Table

Translation Table

System Parameters

Be Consistent

Consider Your Successors

Takeaway

Thank you jeffkemponoracle.com

building maintainable applications in apex

Software