This Tutorial Explains Oracle Database Application Development, Oracle SQL, PL/SQL & Oracle APEX Along With their Features, Benefits and Example Programs:
In the previous tutorial of Data Warehouse Guide, of Complete ORACLE Series, we have learned about Oracle data Warehouse with benefits, architecture, risks, and comparison with the OLTP system.
As we are moving towards digitization, industries are now focused on building web applications to support commercialization. These applications are mostly database-driven which provides real-time information to users in a dynamic way.
To handle this vital data, Oracle has come up with certain database development techniques that can make web development easy and secure.
Table of Contents:
Oracle Database Application Development
In this article, we will understand the features offered by Oracle Database that facilitate application development.
Oracle SQL
SQL stands for Structured Query Language. This is a set-based language supported by most relational databases like Oracle, MS SQL Server, MySQL, etc. This language is purposefully designed to perform data management within a database. Using this language, users can perform different data operations like data retrieval, data deletion, data manipulation, etc.
To perform certain tasks, it acts as an interface to the database, and statements become the instructions sent to DB. These statements provide users with the capability to control data within a database.
Users can access databases indirectly too using different Oracle provided tools or through program applications, provided access permissions are there. But these applications or tools must use SQL statements only at the back end while putting user requests to the database.
SQL Operations
With the help of this language, the user can perform a variety of operations as enlisted below:
#1) Data Querying Operation
- Using the SELECT statement, the user can query any table within a database to retrieve data.
#2) Data Sorting Operation
- ORDER BY statement helps users sort the query results in ascending or descending order.
#3) Data Manipulation Operations
- INSERT statement allows users to insert any new data into a database table.
- UPDATE statement allows users to update existing data within a database table.
- DELETE statement helps the user in deleting the rows from a table.
#4) Data Defining Operations
- CREATE TABLE statement allows users to create new tables within a database.
- ALTER TABLE statement lets the user modify the structure of an existing table.
- DROP TABLE statement allows users to delete tables themselves from the database.
#5) Access Control of Database and Database Objects
- GRANT statement helps administrators in giving the privileges on DB objects to end-users.
- REVOKE command helps in removing the above-granted access on DB objects.
As SQL is capable of managing data, learning this language is not only necessary for database developers but it supports other job profiles as well like, database administrators, quality analysts, architects, and subscribers.
To support SQL users, Oracle has developed several tools that can facilitate the SQL development like SQL*Plus, SQL Developer Oracle JDeveloper, Oracle HTML DB, etc.
There are various extensions to SQL language available like Java, PL/SQL, etc. which have added the procedural and object-oriented programmability concepts to SQL.
In the last four decades, there had been a lot of advancement in the field of data processing and many new techniques have been introduced in the market. SQL still managed to survive during this period and eventually evolved as improved technology.
Now, why SQL is so compelling? Why is it still most preferable and one of the successful data processing technology?
The enduring success of SQL is the outcome of the factors enlisted below:
#1) Robust Framework
Data is being collected for analysis purposes and this analysis can be framed around a dataset or multiple data rows rather than a single row of data. Certain rules, known as Relational Algebra, have been defined for governing the query construction and execution.
It consists of 4 operators:
- Projection: Data within a relational database gets stored in the table in the form of rows and columns. Projections are the first items identified during query execution. They are the selected columns within a table for which a query has been designed. Projections are mentioned in the first part of the SQL query i.e. SELECT statement.
- Filter: After identifying the projections within the query framework, the next step would be to identify the rows that are relevant to the query. Filters are mentioned within the WHERE clause of the query, and they will identify the rows to be included in the results.
- Join: Most of the operations require multiple datasets to be queried and so joins become a necessity. Joins can be performed on two or more datasets by combining required fields in a query based on logical relationships. Various types of joins like INNER JOIN, OUTER JOIN, SELF JOIN, and CARTESIAN PRODUCT are available.
- Aggregate: Data Aggregation is one of the most frequent activities performed during data analysis as summarized data is always required to make a business decision. Hence, data aggregation can be performed using various functions like SUM, COUNT, AVG, MIN, etc.
#2) Transparent Optimization
Oracle database has a rich set of techniques for SQL optimization. SQL Query Optimizer determines the most effective and efficient method for statement execution by generating the best execution plan.
#3) Throughout Evolution
SQL stayed relevant in the market over the decades due to continuous evolvement in various areas. It was throughout enhanced with the features which are mentioned below.
- Enhanced with new data processing techniques.
- Enhanced with new mathematical calculations.
- Incorporated the ability to map code with upcoming data types.
- Enhanced to support data sources like XML and JSON docs, Hive tables, HDFS files, image style (BLOB & CLOB), and spatial objects, etc.
#4) Standards-based Language
In1986, SQL language became standard of ANSI and from then onwards it managed to evolve and emerge with many new versions through all those years. Standardization helped SQL in,
- Maintaining application portability across various databases without major code modifications.
- Ensuring backward code compatibility and continuity. SQL code that was written years ago still manages to execute today as well without much code changes.
Oracle PL/SQL
As we have read earlier, SQL is one of the set-oriented languages developed to access data stored in a relational database. Any applications built on the top of Oracle Database would require SQL statements only, to be executed to access the database contents.
But being a non-procedural language, SQL is not sufficient to implement an end to end business logic for any application and hence, PL/SQL is introduced.
Recommended Reading =>> PL SQL Tutorials
In PL/SQL, PL stands for Procedural Language while SQL stands for Structured Query Language. PL/SQL is an extension of SQL language supporting functionalities like decision making, different iterations & other procedural features in which SQL lacks.
It combines SQL language with non-native procedural commands like (IF Conditional Statements, Assignments, Loops, etc.) and hence, enhance the capabilities of SQL.
Using PL/SQL language for application development is beneficial in the below ways:
- Better integration with SQL: PL/SQL is well integrated with SQL and supports both Dynamic and Static SQL.
- Enhanced Performance: Unlike SQL, where the statements get executed one by one, PL/SQL sends the complete block of statements at once for execution which reduces the network traffic and hence enhances the performance.
- Saves Development Time: Due to the availability of useful PL/SQL features like exceptional handling, data hiding, object-oriented data types & encapsulation, developers can save a lot of time which they needed for designing & debugging the code. Also, it offers access to system defined packages that can be used by application developers.
- Portability: Applications designed using PL/SQL language are completely portable on any operating system.
- Security: PL/SQL provides a high level of security to its applications.
Basic PL/SQL Program Units
PL/SQL is a unit of multiple SQL statements placed together in a block and executed as one. These program units can be compiled by Oracle Database Server and are saved within the database.
A PL/SQL program has been categorized into 3 sections:
- Declaration: This section holds the declarative statements that declare code elements like variables, constants, etc. which can be used within the code block. This section is optional.
- Executable: It holds the code statements that will run, whenever the program is executed. This section is mandatory for a PL/SQL program.
- Exceptional Handling: This section holds the exceptional scenarios raised during program execution using “catch” or “trap” statements. This section is optional.
Considering the above sections, a PL/SQL program is identified by four keywords DECLARE, BEGIN, EXCEPTION & END.
PL/SQL block basic syntax:
DECLARE <declaration section> <optional> BEGIN <executable code> <mandatory> EXCEPTION <exception handling> <optional> END;
Let’s see various examples to get a clearer picture.
#1) EXECUTABLE ONLY
The below program has executable section only and DBMS_OUTPUT.PUT_LINE procedure is called to display text on the output screen.
BEGIN
DBMS_OUTPUT.put_line ('Hello!');
END;
#2) DECLARE & EXECUTABLE
Here, the program has a declaration section where a variable of type VARCHAR2 (50) is declared to hold the string “Hello!”.
DECLARE text VARCHAR2 (50):= 'Hello!'; BEGIN DBMS_OUTPUT.put_line (text); END;
#3) DECLARE, EXECUTABLE & EXCEPTIONAL HANDLING
This program has all sections i.e. DECLARE, EXECUTABLE, and EXCEPTION HANDLING.
DECLARE text VARCHAR2 (50):= 'Hello'; BEGIN DBMS_OUTPUT.put_line (text); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); END;
This is how PL/SQL programs can be constructed with different sections.
Once constructed, these programs are supposed to be stored in the database so that they can be called whenever required. But the way above code blocks are written without any reference names is not an organized one, hence we can call them Anonymous code blocks.
Using anonymous blocks doesn’t fulfill the purpose of building large and complex applications as it would end up with a lot of code complexity and maintainability.
Here comes the concept of Named blocks, which is also known as Subprograms. Subprograms can have unique reference names with which they can be stored in a database. A call can be made to these programs using their reference names.
The structure of a named block would be the same as that of an anonymous block except it will not start with the “DECLARE” keyword, rather “CREATE” keyword. “CREATE” keyword instructs the compiler to create and save the code block as a database object which can be called later.
Named blocks can be of 2 types:
- Functions.
- Stored Procedures.
A function is defined as a named block, also known as subprogram or subroutine. The purpose of using functions is to perform computation and it will always return a value.
We can create a function as below:
CREATE [OR REPLACE] FUNCTION function_name [(parameter [,parameter]) ] RETURN return_data_type IS|AS --declaration statements BEGIN -- executable statements Return return_variable; EXCEPTION -- exception-handling statements END;
Stored Procedures are also named blocks written to perform a task. They are different from functions in the way that stored procedures can’t be used or called with SQL statements while functions can be used. Also, procedures can return more than one value while functions are bound to return a single value.
We can create Stored Procedures as below:
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter [,parameter]) ] IS --declaration statements BEGIN --executable statements EXCEPTION --exception handling statements END;
Oracle Application Express (APEX)
APEX is an application development platform designed by Oracle that enables worldwide industries to deal with their business problems. It is a tool that allows organizations to showcase their data over an extensible platform through their web applications.
A variety of apps can be build using APEX right from a simple app of “accessing a spreadsheet” to critical apps with the availability of 24*7. Through this platform, Oracle has focused on accommodating developers with a variety of development options and leave users with an exceptional experience.
Oracle APEX tool is enriched with a lot of features and functionalities, and it has gained success over multiple facets like user interface, data, security, monitoring, etc. It provides flexibility to the users by not being any complex tool. To work on Oracle APEX, the developer doesn’t need to be a technical expert as APEX comes with a lot of help and guides.
The features are mentioned below:
#1) User-friendly Interface
Oracle APEX is a non-complicated and easy to use tool with a user-friendly interface known as Universal Theme. This theme enables developers to build self-responsive and interactive web applications as it is very rich and supportive in terms of guiding the user through the process of developing applications. A developer doesn’t need to be an expert in any coding languages like CSS, HTML, or Javascript.
#2) Data-Driven
This tool is very powerful and supports a variety of application building processes that can be consumed by industries to present their data in the required manner. It is a data-driven tool that holds the capability of processing and manipulating data efficiently. It empowers each user to customize their reports in their unique ways using the Interactive Report component.
#3) Security
Security is one of the crucial requirements of every industry these days. Oracle has designed APEX as capable of delivering highly secured applications by strictly following the security standards.
#4) Portability
Oracle APEX is a portable tool and the user can utilize this feature as per their business requirement. They can deploy the tool anywhere and everywhere in this world, even on the Cloud, provided there is an Oracle database deployed.
#5) Monitoring
Oracle APEX is featured with monitoring and logging capabilities to identify any performance problems, application issues, or hacking attempts.
#6) Globalization
Oracle APEX has been designed in such a way that it can support various languages and related environments and hence can be used by developers across different parts of the world to develop their applications.
APEX Architecture
Oracle APEX has a powerful architecture which mainly consists of 3 tiers.
- Web Browser.
- Mid-Tier: ORDS (Oracle Rest Data Services).
- Database Tier: Oracle Database (APEX).
[image source]
Let’s go through the request flow to understand, how it works.
Here, a web request will be generated at the browser level and then passed to Oracle Rest Data Services i.e. ORDS. Further, ORDS passes that request to Oracle APEX within the database. Now, APEX will process the request and pass the response back to ORDS, which will pass it back to the browser. This is how the request flows within APEX architecture.
Oracle APEX has offered a wide range of solutions that helped developers solve their real-time business issues. It also allows developers to use third-party libraries which are an extension to APEX applications. These extensions will be the enhancements to the applications making it more privileged and powerful.
Along with multiple benefits, this tool has few limitations as well. These are listed below:
- Oracle APEX can be installed using Oracle owned tools only and can be deployed along with Oracle database.
- Not many web hosts allow hosting of Oracle APEX, hence APEX is left with limited hosting choices.
- Version control is not there and different application components can be accessed and edited using web interface only.
Conclusion
In this article, different development options offered by Oracle like SQL, PL/SQL & Oracle APEX has been introduced in a simpler way along with their features, benefits, and limitations. I hope, it gave you clarity on the usage of database development tools and languages.