How to build an Oracle database application
How to build an Oracle database application
Quick but not dirty; with or without a front-end built with Oracle APEX or a Node.js framework like React, Angular or Vue.
About the Book
This book presents you with a method to develop robust Oracle database applications where the focus is on software architecture, development team and process. And automation of course, hence quick but not dirty.
Table of Contents
-
- Foreword
-
Preface
- Why this book and why now?
- Who is this book for?
- How to read this book?
- Typographic conventions
- Please help improve this book!
- About the author
-
I Designing the build plan
-
1 Introduction
- 1.1 What’s in a name?
- 1.2 Philosophy
- 1.3 Standing on the shoulders of giants
-
1.4 Tools, techniques and best practices
- Oracle Database and Oracle APEX
- Oracle SQL Developer Data Modeler
- Version control
- Maven
- Flyway
- Oracle SQL Developer
- utPLSQL
- Perl
- Ant
- DevOps
- 1.5 Introducing Paulissoft Application Tools for Oracle (PATO)
- 1.6 Conclusion
-
2 Start me up
- 2.1 Introduction
-
2.2 Setting up the environment
- Database & APEX
- Maven
- Git
- Oracle SQL Developer
- Oracle SQLcl
- Oracle SQL Developer Data Modeler
- Perl
-
2.3 Database configuration
- Virtual machine settings
- SQL*Net
- Creating the proxy user
- Creating the PATO schema
- Installing database objects
- Installing the PATO APEX application
- Upload spreadsheets
-
3 Build plan essentials
- 3.1 Introduction
- 3.2 Scope of a build plan
-
3.3 Software architecture
- Three-tier architecture
- Database structure
- Data model
- Business rules
- Logging
- Error handling
- Globalization
- 3.4 The development team
-
3.5 Development process
- DevOps
- Agile, DevOps and CI/CD
- What version?
- Parallel development
- Deployments
- Documentation
- 3.6 Development tools
-
1 Introduction
-
II Implementing the build plan
-
4 Automation tooling
-
4.1 Maven
- Build Tasks & Profiles
- Environment properties
- Sources
- POMs
- Application consisting of several software projects
- 4.2 Jenkins
-
4.1 Maven
-
5 Database migrations
- 5.1 Why migrations?
-
5.2 How does Flyway work?
- Callbacks
- Incremental migrations
- Repeatable migrations
- 5.3 DML
- 5.4 Preferred order of migrations
- 5.5 Why not Liquibase?
-
5.6 Project folder layout
- Directory apex
- Directory conf
- Directory db
- Mapping database structure to folder layout
-
6 Oracle SQL Developer Data Modeler
- 6.1 Best user experience on Windows
-
6.2 Data Modeling related concepts
- Business Rules
- Surrogate keys
- Super and subtypes
- Many-to-many relations
-
6.3 Best practices
- Configuration settings
- Use the PATO Custom Library and Custom Transformations Scripts
- Data Modeler Configuration & Version Control
-
6.4 Tasks
- Design the logical and relational model
- Check the Design Rules
- Generate DDL
- Create incremental migration scripts
-
7 Mapping architecture to code
- 7.1 Business rules
-
7.2 Business rule mapping
- Static Data Constraint Rules
- Dynamic Data Constraint Rules
- Change Event Rules with or without DML
- Authorization Rules
-
7.3 Alternatives for checking complex business rules
- Standard views with a check option
- Refresh fast on statement
- De-normalization
- Conclusion
- 7.4 Database object naming conventions
-
7.5 Accessing database schema objects
- DATA
- EXT
- API
- UI
-
7.6 Database Security
- Set-up context and privileges
- Add the policy
- Enable the policy
- Create a predicate function
- Create a procedure to set the context
- Set the context in the front-end
-
8 Testing
- 8.1 V-model of software development
-
8.2 utPLSQL
- Introduction
- Best practices
- Examples
- Hands-on experience
-
9 Debugging
- 9.1 Why DBUG?
-
9.2 History of DBUG
- DBUG C library
- DBUG PL/SQL library
- 9.3 Later enhancements
- 9.4 Installation
-
10 Error handling
-
10.1 An APEX error handling solution
- Oracle constraint violations
- A reserved PATO exception
- No translation found
-
10.2 How to raise a custom error?
- DATA_API_PKG.RAISE_ERROR
- DATA_API_PKG.RAISE_ERROR_OVERLAP
- 10.3 APEX messages
- 10.4 How to store APEX messages?
- 10.5 Conclusion
-
10.1 An APEX error handling solution
-
11 Code quality
-
11.1 Standard Oracle functionality
- PL/SQL warnings
- PL/Scope
- 11.2 PATO code quality check
-
11.1 Standard Oracle functionality
-
12 Advanced Topics
-
12.1 Collaboration
- APEX
- Database
- 12.2 Multiple APEX application systems
-
12.3 Uploading spreadsheet documents
- Using it in another application system
- 12.4 Translating APEX applications
-
12.1 Collaboration
-
13 Project set-up
- 13.1 Folder layout
- 13.2 Root folder
- 13.3 Maven folder
-
13.4 Apex folder
- Folder <app_X>/src/export
- Folder <app_X>/src/translate
-
13.5 Configuration folder
- File env.properties
- File flyway-app.conf
- File apex.properties
- File db.properties
- File flyway-db.conf
-
13.6 Database folder
- Folder <schema_X>/src/admin
- Folder <schema_X>/src/dml
- Folder <schema_X>/src/full
- Folder <schema_X>/src/incr
- 13.7 Conclusion
-
14 Continuous Deployment
-
14.1 Requirements
- Automated
- Flexible
- Complete
- 14.2 Install Jenkins
-
14.3 Configure Jenkins
- Manage Plugins
- Configure System
- Manage Credentials
- Manage Nodes and Clouds
- 14.4 Test the GitHub SSH connection
-
14.5 Run the Jenkins deployment job
- Jenkins Templating Engine
- Create the pipeline job
- Build the pipeline job
- 14.6 Conclusion
-
14.1 Requirements
-
4 Automation tooling
-
III Testing the build plan
-
15 Example project 1, Sample Database Application
-
15.1 Setting up the project structure
- Install the APEX Sample Database Application
- Cloning PATO
- Create an examples project
- The root POM
- The database POM
- The APEX POM
-
15.2 Generate scripts
- Database
- APEX
-
15.3 Re-install the application
- Database
- APEX
- 15.4 Test the installation scripts
- 15.5 Conclusion
-
15.1 Setting up the project structure
-
16 Example project 2, Incident Tracking
- 16.1 Install the APEX Sample Database Application
- 16.2 Modify the project structure
-
16.3 Modify the Sample Database Application POMs
-
Create a
db/pom.xml
-
Change the
db/sample-database-application/pom.xml
-
Create a
db/incident-tracking/pom.xml
-
Create an
apex/pom.xml
-
Change the
apex/sample-database-application/pom.xml
-
Create an
apex/incident-tracking/pom.xml
-
Create a
-
16.4 Generate scripts
- Database
- Load the data
- APEX
- 16.5 Conclusion
-
17 Example projects, Continuous Deployment
- 17.1 Set up the project structure
- 17.2 Setting up Jenkins
- 17.3 Conclusion
- Afterword
-
15 Example project 1, Sample Database Application
-
Appendices
- Appendix A - Custom transformations
-
Appendix B - Jenkins installation
-
Installation of Jenkins as a service on Linux
- General
- Install Jenkins
- Installation of Jenkins with Docker Compose
-
Post install actions
- Install plugins automatically
- Install plugins manually
- Admin user
- Final steps
-
Installation of Jenkins as a service on Linux
- Appendix C - PATO and Transferware repositories
- Index
The Leanpub 60 Day 100% Happiness Guarantee
Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.
Now, this is technically risky for us, since you'll have the book or course files either way. But we're so confident in our products and services, and in our authors and readers, that we're happy to offer a full money back guarantee for everything we sell.
You can only find out how good something is by trying it, and because of our 100% money back guarantee there's literally no risk to do so!
So, there's no reason not to click the Add to Cart button, is there?
See full terms...
Earn $8 on a $10 Purchase, and $16 on a $20 Purchase
We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.
(Yes, some authors have already earned much more than that on Leanpub.)
In fact, authors have earnedover $13 millionwriting, publishing and selling on Leanpub.
Learn more about writing on Leanpub
Free Updates. DRM Free.
If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).
Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.
Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.
Learn more about Leanpub's ebook formats and where to read them