Calling PL/SQL Stored Procedures Within Oracle APEX

There's a great deal of detail involved with developing robust, maintainable and efficient solutions by paring PL/SQL stored procs with the Application Express web based frame work. Not knowing the current skill level of the author of the OP, I assume that the most helpful explanation is a simple example developed from scratch.

Getting Started: The APEX environment has a lot of tools that can get you started. If you don't already have an Oracle database environment with APEX installed, consider signing up for a free, hosted trial account through the APEX home page on Oracle.com.

Application Design Decisions

PL/SQL stored procedures are not necessary to develop applications on Oracle APEX, however they do supply a greater amount of flexibility and customization during the development process.

This example will use the popular schema object: EMP typically available as an optional part of each Oracle database installation. In case you don't have it, here is the DDL and DML source for building the example table:

The EMP Table (A Copy Aliased as: LAB01_SAMPLE_EMP)

 CREATE TABLE  "EMP"    (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,     "ENAME" VARCHAR2(10),     "JOB" VARCHAR2(9),     "MGR" NUMBER(4,0),     "HIREDATE" DATE,     "SAL" NUMBER(7,2),     "COMM" NUMBER(7,2),     "DEPTNO" NUMBER(2,0),      PRIMARY KEY ("EMPNO") ENABLE    ) / ALTER TABLE  "EMP" ADD FOREIGN KEY ("MGR")       REFERENCES  "EMP" ("EMPNO") ENABLE /

If you would like some test data, this is what I had to work with:

1. BUILD a SQL-based DML update statement that will change the SAL (Salary) value for one employee at a time based on their ENAME.

2. WRAP the UPDATE DML statement into a reusable, compiled PL/SQL stored procedure. Include parameter arguments for the two data input values for "Name" and "Amount of Increase".

3. TEST the stored procedure and verify it works as required.

4. DESIGN and CODE an APEX Application Page which will:

   (a) Show the current contents of the employee entity table.(b) Accept input values to pass into the PL/SQL Stored Procedure.(c) Utilize native APEX Error and Success message settings to provide more feedback on the outcome of the procedure call.

5. TEST the APEX page and verify it works as specified.

6. REVIEW the discussion and conclusions section at the end for additional comments and more tips to keep you moving on your own path to developing Oracle skills.

Programming a SQL DML Process

This is the initial example created to accomplish this task.

    UPDATE LAB01_SAMPLE_EMP       SET SAL = SAL + 1250     WHERE ENAME = 'KING';

A quick revision reveals how we can parametrize the approach to make this statement

    UPDATE LAB01_SAMPLE_EMP       SET SAL = SAL + p_salary_increase     WHERE ENAME = p_ename;

If you are not sure where to go next, this is where a lesson on "best-practices" is available thanks to APEX. Navigate to the OBJECT BROWSER and CREATE a Procedure Object. The application will walk through every step to set up a PL/SQL stored proc.

The Working PL/SQL Procedure Source Code:

After walking through the wizard setup, this is the cleaned-up stored procedure. There were some additional changes after debugging some compile-time error warnings:

 create or replace procedure "PROC_UPDATE_SALARY"    (p_ename IN VARCHAR2, p_salary_increase IN VARCHAR2) is v_new_salary   lab01_sample_emp.sal%TYPE;        begin       UPDATE LAB01_SAMPLE_EMP          SET SAL = SAL + p_salary_increase        WHERE ENAME = p_ename    RETURNING SAL INTO v_new_salary; commit; dbms_output.put_line('INCREASED SALARY FOR: ' || p_ename ||     ' TO THE NEW AMOUNT OF: $ ' || to_char(v_new_salary)); end;

Best Practices, a Quick Aside and Discusssion: You just have to keep doing it... coding that is. There is just no way around it. Look for examples at work or in life and try your hand at developing schemas and designs to satisfy made-up but realistic requirements. For beginning developers, the PL/SQL stored procedure above may already show some "unfamiliar" or odd coding syntax and commands.

That is only the tip of what is possible out there. Coding style is also only a part of it as you get deeper into things, you may notice a few things:

1. ORGANIZATION is important. Learn quickly some conventions in naming and notation to use in the code. This will keep things organized and easy to find or reference elsewhere.

2. RECYCLE and REUSE means your code should be developed with reuse in mind. Common routines and processes should be bundled together to avoid redundancy.

3. ALWAYS TEST your work suggests that less frustration is found when initial, fundamental steps in your process or application have been carefully tested first before proceeding.

TESTING the Oracle PL/SQL Procedure

I used the built-in APEX scripting engine found in the SQL WORKSHOP section of the environment. Below is a screenshot of the output logs of my testing script.

Bringing it Together: Designing an APEX Application Page with a Procedure Call

Create or open up an APEX Application and start out by making a new page. There are system wizard processes that will help you get started if you haven't done this before.

Select the option to BUILD a FORM on top of a STORED PROCEDURE. There will be prompts for permission to build the page items needed for input parameters. Follow the wizard to completion to make sure all the dependent page design elements are included.

My near-finalized design is below:

There are a few extras added, such as the REPORT region to provide immediate visibility to the table and any applied changes to its data.

The Form in Action: Testing Data Input and Results

The SUCCESS alert message is a feature available for certain page elements to inform the user of any significant events conducted on the database.

Closing Comments and Discussion

The immediate answer to the question of the OP is YES, there are best practices. It is such a huge subject that the only realistic way of handling it is by walking through different examples to see the ways that these practices are "commonly" applied.

There were a few shortcuts involved in this solution (based on several assumptions) and it might be helpful to bring them up as a parting discussion on the possibility of revisiting this walk-through to make an improved, EMP-2.0 project.

• The procedure works on the EMP table based on searches by ENAME. What is the REAL key of this table? Are there any risks involved with this approach- possibly with respect to larger data sets?

• Most Oracle PL/SQL Objects used in production ready environments have some level of error trapping or exception handling through a PL/SQL EXCEPTION BLOCK. What kind of errors would you want to trap? How?

Don't underestimate the resources available within the APEX tool. There are lots of wizards that walk developers through the process of creating different, functioning modules of code. These automated guides provide solutions that can also be reverse-engineered to understand how the code was generated and also what general design approaches make compliant design patterns.