As an applications development professional, my career objectives are to do work that is fulfilling in the sense that it challenges my capabilities, keeps me on the cutting edge of technology, and pays me decently. I have established my career as an Oracle Applications developer over the last two decades, and I couldn’t see myself doing anything else. Having worked with a few other technologies over the years, I can confidently say Oracle is a cut above most, as far as fulfilling my career goals is concerned. The purpose of this paper was to inspire a rising generation of IT professionals by highlighting some of the application development roles that I enjoy and how they are supported by Oracle technologies.
As it is with any vocation, the tools available to get the job done are as essential as the knowledge and the experience one needs to get it done efficiently and effectively. Fundamentally, it is crucial to have a solid foundational knowledge of software and how the specific tools help in implementing a software solution. At the core of the Oracle application is a relational database where data is logically organised into tables, and the tables are interlinked via primary and foreign key relationships to create a system. There are various development roles that one can do in the world of Oracle ranging from; database development, user interface development, application interface development, cloud applications, and business intelligence development.
One of Oracle’s greatest strengths is that it comes complete with an integrated set of development tools that support any development approach, any technology platform, and any operating system. Oracle development tools range from the traditional database management tools, Java-based mid-tier development tools, to the contemporary frameworks and platforms that are driving sophisticated cloud applications and architecture. These tools are being used to build and deliver intelligent, cutting edge solutions that manage applications data hosted by Oracle databases.
Over my last two decades in the industry, I have seen the evolvement of Oracle technologies together with the tools available for applications development, and I have understood how crucial it is for one to keep one’s skills updated, to stay relevant in the industry. What has been important for me is the fact that once I got the core technical development skills, it then became easier to upgrade as long as I understood the trending technologies and how Oracle incorporates them into their products.
As an Oracle applications developer, my job involves developing and maintaining routines that create and maintain the data in the database, and to present the data in such a way that it is operational for its intended purposes. I have developed extensions to Oracle’s out of the box applications functionality, bespoke applications, and interfaces that link Oracle applications to other external applications. Oracle also provides technologies and tools such as SOA that make interconnections with applications based on any technology seamless. Different skills and technologies are required to create and maintain a real-life application end to end and the beauty of Oracle is that it comes integrated with proprietary tools relevant for each stage of an application’s life cycle.
The diagram below is a simple illustration of the architecture of Oracle-based system applications. It represents the database tier, mid-tier and the interface tier where information flows between an Oracle database and the end users, other applications, or other databases.
There is development work to be done at each tier of the application architecture represented above, and Oracle provides the necessary tools and technologies to develop a complete system. One may not necessarily need to be an expert in all the technologies, but it would be essential to understand how they interact with each other to build a seamless system. Oracle tools even cater for non-technical tasks which are equally essential in software development or in maintaining production systems. The following are some of the roles that I get involved in and how Oracle tools support meeting the task objectives.
Requirements analysis and solution design are crucial steps for any software solution as these ensure that the product to be developed meets its functional or non-functional requirements. Data modelling is a technique that is used in database applications to specify the business requirements that would be later translated into software requirements for coding purposes. Data modelling can start during the business analysis phase as a communication tool in the exploration of system requirements with stakeholders. At this phase, the data model is done at a high level as appropriate to visualise requirements and explore domain concepts.
For purposes of system design, a data model represents the data structures that hold application data and how they are interrelated to create a system. Data modelling at this stage is especially important for purposes of making sure the data requirements of a system are accurately interpreted, and the attributes of each data entity are entirely specified. A developer’s data model is a refinement of the business analysis data model, with details of the actual data to be captured by the system which is later translated into a database.
Oracle SQL Developer, comes integrated with Data Modeler, a graphical tool for creating data models that enhances productivity and simplifies database creation. Data Modeler also generates the SQL code that is required to create the database tables represented by the data model. Database developers can use this tool to contextualise application data requirement and design the required database. More importantly, Data Modeler can import from, and export to a variety of sources and targets, provides a variety of formatting options, and validates models through a predefined set of design rules.
Database Development and Management
All operations on the data in an Oracle database application are performed using Structured Query Language (SQL) statements. From the creation of the database objects, you use data definition language (DDL) code, to data management using data manipulation language (DML) queries. SQL is the ANSI standard language for any relational database. Where DDL statements enable you to build and change the structure of the database, DML statements enable you to query and change the contents.
Oracle has its own version of SQL, Oracle SQL, which is an implementation of the ANSI standard that extends beyond the standard SQL features. Oracle SQL has evolved with the development of Oracle database technology, providing features to make use of cutting-edge database technology. It also incorporates the use of functions that would only be possible with a procedural language, for example, the decode construct simulates IF THEN ELSE logic. It goes without saying that to take full advantage of Oracle SQL features, one needs to know the features that are available for their database version and this information is generally available with the database version release note.
Oracle cursors is another powerful feature of Oracle SQL that allows users to manipulate multiple rows in a row by row manner. With cursors, Oracle creates a memory area, known as the context area, for processing SQL statements. A cursor can be explicitly named and defined for DML queries that return multiple rows or implicitly defined for single-row queries. The context area contains all the information needed for processing the statement; for example, the number of rows retrieved, etc.
In real life operations, it is common that the details of the SQL statement to be executed are not known until run time, for example where the exact details are driven by user input or the processing being done by a program. Oracle SQL supports the construction and execution of dynamic queries. This feature allows developers to develop flexible queries and cursors whose full context is determined dynamically at run time.
Virtual private database (VPD) is a powerful Oracle database security feature that enforces data security at the table row and column level. VPD is useful when security protections such as privileges and roles are not sufficiently fine-tuned. For example, you can allow all users to access the same employee’s table but create a security policy to restrict access to employees in the same department as the user. Essentially, the database adds a dynamic WHERE clause to a SQL statement issued against the table, view or synonym to which an Oracle VPD security policy was applied. The WHERE clause allows only users whose credentials pass the security policy to access the protected data.
Query Optimizer is another feature of Oracle SQL that is really useful, especially for performance management. All Oracle SQL statements use the optimizer to determine the most efficient means for accessing the specified data. The optimizer uses database statistics gathered for the system and the available access paths to determine the most cost-efficient execution plan. Oracle SQL developers can influence the default execution plan generated by the optimizer via the use of hints, changing the optimizer goal, or refreshing database statistics.
SQL is a non-procedural language, which specifies what data to be operated on but cannot handle logical operations or how the operations are to be carried out. This leaves SQL as inadequate when it comes to developing applications that implement real-life business logic. This calls for the need for procedural program languages that implement control structures, such as loops or conditional branches, that are not available in SQL and are used to create procedures and functions.
With Oracle, procedures and functions can be developed and stored within the database. This is known as server-side programming, and the principal advantage of this approach is that the stored procedures, functions, and triggers can be invoked from anywhere. Also, server-side subprograms increase scalability by centralising application processing on the server; this enables clients to reuse code. In addition, as subprograms already reside within the database, there is reduced context switching and network traffic; therefore, calls are quick and efficient.
Oracle comes with its own proprietary procedural language, PL/SQL, to create server-side programs that are stored on the database in compiled form, making them more readily available to execute at run time. PL/SQL is the procedural extension to SQL which comes integrated with the database, supporting all Oracle SQL statements, functions, and data types. Oracle Database also provides support for developing, compiling, storing, and deploying Java programs. Java stored programs run on the database and are independent of programs that run in the middle tier. They interface with SQL using a similar execution model to PL/SQL. This integration with such powerful application development tools gives Oracle a competitive edge over other relational databases.
Most of my work as an applications developer is maintaining Oracle applications that are already in use. Maintenance work varies from creating extensions to existing functionality, modifying existing functionality to meet changing business requirements, fixing bugs, and performance tuning of slow running programs. Performance tuning is always challenging as performance problems can be a result of several issues from poor program design to insufficient system resources. Even with the use of the most sophisticated development tools, if system resources are exhausted or poorly managed, system performance deteriorates. In such scenarios, the problem can only be rectified by increasing the required resources. Otherwise, performance tuning the underlying programs and queries would be required.
Oracle SQL developer comes with session and SQL monitoring tools that can help identify system performance, culprits, to be targeted for fixing. The execution plan that shows the path that is chosen by the Optimiser also helps to identify areas of concern within an SQL query. This information can be used to tune the SQL for better performance and can also inform whether database constructs such as indexes are required to speed up execution.
It is one thing knowing what needs to be done in applications development, but having the tools to get the job done more efficiently is very satisfying. Working with Oracle technologies is a continuous learning and development journey. There is a lot to learn, with each task accomplished. Inarguably, in order to succeed as an IT professional one needs to have good foundational knowledge, and to develop a systematic approach to problem-solving, which comes with experience. It is also equally important to know the tools available and to make the most use of the features presented by the tools. Oracle has developed a set of development tools that are geared to make applications development more efficient. Oracle technologies have also matured such that there are a mature knowledge base and online user communities where answers to most technical questions can be found. There is a lot that one can do as a technical career within the Oracle sphere. As an Oracle Applications technical professional, I can confidently say that I am satisfied with my career choice.