Oracle PL/SQL Programming Oracle PL/SQL and APEX Best Practices For PL/SQL Development in Oracle Application Express Steven Feuerstein [email protected] PL/SQL Evangelist Quest Software Oracle PL/SQL Programming Resources for PL/SQL Developers www.plsqlchallenge.com Daily PL/SQL quiz, weekly SQL, APEX and logic quizzes. Prizes! www.plsqlchannel.com 27+ hours of detailed video training on Oracle PL/SQL www.stevenfeuerstein.com Sign up for monthly PL/SQL newsletter www.toadworld.com/SF Quest Software-sponsored portal for PL/SQL developers; download powerpoints and demo.zip Copyright 2011 Feuerstein and Associates Page 2 Oracle PL/SQL Programming Best practices all happen in here: • A little obsessive... • A little compulsive... • Is probably an OK thing for a developer. Copyright 2011 Feuerstein and Associates Page 3 Oracle PL/SQL Programming The Big Picture on NOT Best Practices Copyright 2011 Feuerstein and Associates Page 4 Oracle PL/SQL Programming PL/SQL and APEX: the best and worst of friends • Point and click interface to define your application – Way better than writing it all in code, right? • One language (PL/SQL) for backend and frontend development. – Of course, it sure helps to know Javascript, jQuery, HTML, CSS, etc. • But how well do APEX and PL/SQL play (together)? Copyright 2011 Feuerstein and Associates Page 5 Oracle PL/SQL Programming Some Issues We Face • Repetition and hard-coding – APEX doesn't make it particularly easy to reuse application elements, especially code. • When code is written inside that oh-so-cool UI environment, we tend to pay less attention to it. – Irritating interruption from the usual point-and-click. – Less than minimal editing capabilities. • The custom-written source code in your APEX application will present the biggest challenge in terms of debugging, maintenance, support. Copyright 2011 Feuerstein and Associates Page 6 Oracle PL/SQL Programming What's an APEX developer to do? • Set clear rules on... – Where code goes, and – Where and how SQL statements are written. • Don't repeat anything – hide everything. • Soft-code as much as you can. – Fully leverage APEX's close connection to Oracle tables. • Prepare for (assume) the worst. Copyright 2011 Feuerstein and Associates Page 7 Oracle PL/SQL Programming Set Some Clear Rules 1. If the PL/SQL code does not contain references to UI elements, move to packages. 2. If the PL/SQL code contains UI elements, move as much code as possible to packages. – Leave behind a minimal "footprint" in APEX code. 3. The only SQL you should write inside APEX are queries to populate tables. – And even then you should query from views. – No updates, inserts, deletes - unless generated and performed automatically by APEX. Copyright 2011 Feuerstein and Associates Page 8 Oracle PL/SQL Programming Don't repeat anything – hide everything. • This is Golden Rule of Programming. – Repetition = hard coding = maintenance nightmare. – You end up with brittle, hard-to-change apps. • Instead, aim for a Single Point of Definition (SPOD) for every aspect of your application. – That way, when you have to change it, you change it in one place • The hard part is recognizing all the sorts of hard-codings that can occur. Copyright 2011 Feuerstein and Associates Page 9 Oracle PL/SQL Programming Common Hard Codings in APEX • Literals that are defined in PL/SQL as constants, but cannot be referenced in queries. • WHERE clauses, validations, conditions • Entire pages of similar functionality. • Be ruthless and disciplined. – Single, shared pages are more complex, but in the long run easier to maintain. Page 651 Copyright 2011 Feuerstein and Associates Page 10