Tuesday, November 12, 2019

ā·pěks 10 Years Later



Exactly 10 years ago today, I wrote a succinct blog post with the intent of clarifying how to properly pronounce and abbreviate Oracle APEX.  I decided to use the phonetic spelling, ā'pěks, to avoid all ambiguity with the pronunciation.  Was I successful?

  • I still encounter many people who spell this Apex (and not the correct APEX)
  • I routinely hear people pronounce this as ah·pěks or ap·ěks (and not the correct ā'pěks)

Obviously, we still have a ways to go.  However, this hasn't been a complete loss.  With many thanks to the global APEX community, this simple phonetic spelling has resulted in:
  • stickers
  • hoodies
  • umbrellas
  • 3D-printed keychains
  • cutting boards
  • bottle openers
  • coffee mugs
  • challenge coins
  • T-shirts
  • fan shop
...and more.  And did I say stickers?

What I especially love is that all of this was created by the Oracle APEX community.  Instead of Oracle providing merchandise and branding for Oracle APEX, the community embraced this and ran with it themselves.  This has been wonderfully organic and authentic, and completely community-driven.

Going forward, if you come across someone who misspells or mispronounces Oracle APEX, please feel free to direct them to this blog post.  It is:

Oracle APEX

and it's pronounced ā·pěks.

Saturday, November 02, 2019

My Personal Thanks to the Chicago Police Department - the First Real Proving Ground for Oracle APEX


In 2001, the Chicago Police Department took a chance on APEX.  And with all thanks to them for the opportunity they provided us, Oracle APEX is what it is today.  We owe them a big debt of gratitude.  Let me explain.

As many people know, the genesis of Oracle APEX was an internal development project that began in 1999, to build a Web-based HTML calendar for use by Oracle employees.  My manager, Mike Hichwa, was the inventor of Oracle Web DB.  And when faced with the assignment of creating a new HTML calendar application for the company, the choices were a) WebDB, b) a lovingly hand-crafted PL/SQL application from scratch, or c) a yet-to-be-created application metadata framework (using Mike's lessons learned from WebDB).  We went with the latter, and Mike began the creation of the APEX framework while I developed a calendar application which was "programmed" to use this new framework.  Mocked and doubted by many at Oracle, we went live with the first production application in 3 months, rolled out to thousands of employees.  Having Tom Kyte to help us was instrumental in our success.

Over the next 18 months, we evolved this framework and created a number of other internal applications.  We thought we were ready to offer this framework for customers to use.  But one of the best things happened for APEX at that time.  When Larry Ellison was visiting New York City, Mike traveled to meet with him and brief him on the state of the framework, as well as Mike's aspirations to offer this framework as another tool from Oracle.  The advice offered by Larry to Mike - prove the framework with 30 real-world customers before you consider taking this live.  Invaluable guidance.

In 2001, Mike and I had an internal meeting in Chicago with Oracle Consulting.  The back-end information system for the Chicago Police Department (CPD), the Criminal History Record Information System (CHRIS), was written in Oracle Forms.  It had been developed over many years, and was a joint effort between Oracle Consulting and the Chicago Police Department.  The purpose of this meeting, at the time, was to discuss possible alternatives to the next state of CHRIS.  This meeting was ultimately precipitated by the estimated hardware requirements to run the next version of Oracle Forms.  They had estimated that the backend database server requirements alone would require 4 very large and very expensive new Sun Enterprise 10000 servers.  This was a lot of money to be spent on hardware with effectively no net gain in functionality for their end users.  We proposed APEX ("Flows", at the time), and they went with it.

Over a period of more than a year, a number of today's APEX product development team members worked directly, onsite, with Oracle Consulting and Chicago Police Department to move the functionality of their Oracle Forms applications to APEX.  It wasn't a 1-to-1 mapping, and it required a level of application and UI redesign.  But we were able to capitalize on the existing data structures and business logic, already present in the database.  The Oracle Forms applications and APEX apps were able to easily co-exist, because they were built on the same foundation.  There were also new systems developed as part of this effort, named CLEAR.  You can still read about CLEAR from this article from 2004.

This entire exercise was hugely beneficial to us.  We thought we were ready to go to market.  But once we dug into the requirements of a large-scale enterprise system like CHRIS, it uncovered many significant gaps in the functionality of the APEX framework.  Fortunately, we owned the framework and were able to simultaneously fill those functional gaps in APEX.  As a simple example, at the time there was no way to manage vectors of information in APEX session state.  This real-world requirement resulted in today's APEX collections.  When you own the framework and you are concurrently building the app, you can do anything!

Scalability was another concern.  While the original calendar application we wrote for Oracle had more than 25,000 users, let's face it - the use of a calendar is occasional throughout the day.  Contrast this with CHRIS, which had more than 10,000 total users, the vast majority who would interact with CHRIS frequently throughout the day.  The heavy concurrent usage of these applications provided us numerous opportunities to tune and optimize the APEX execution engine.  And talk about mission-critical applications - "business" slows to a crawl if you can't look up information about a person or log evidence.  And when business slows to a crawl, public safety is jeopardized.

Fast forward to 2019, and here we are with a large global community of hundreds of thousands of developers.  There are dedicated conferences, stickers, bloggers, videos, meetup groups, awards, books, podcasts, webinars, hosting providers, cloud services, partners & consulting companies, and thousands upon thousands of real-world successes from around the globe.  Much of our success can be traced to this proving ground, which was afforded us by the Chicago Police Department.

The purpose of this blog post is simple - I wish to offer my personal, sincere thanks to the Chicago Police Department for the gamble they took on us.  There was no true guarantee that APEX was going to exist beyond a "skunkworks" project, but they still forged ahead, given some assurances from Oracle and the alternatives.  They banked on us and they won.  Their real-world use cases stretched us and the technology in ways we had never imagined.  We learned so many valuable lessons during this project, and all of it resulted in a much more scalable, hardened, proven system by the time APEX was first offered as an Oracle Database feature in 2004.  We will forever be grateful to them.

For the record, these internal systems still run on Oracle APEX today, and are used by thousands of Chicago Police Department employees every day.  Now that is longevity, and a great investment.  Amidst today's rapid technology churn, this remains an extraordinary success story.


Patch by City of Chicago - http://www.publicsafetypatches.org/IL/Police/, Public Domain, Link

Friday, June 28, 2019

Should the Oracle APEX Community Care About Autonomous Database?



This past week, Oracle announced the availability of Oracle APEX, SQL Developer Web and Oracle REST Data Services on Oracle Autonomous Database.  If you're in the APEX community, should you care?  I say "absolutely yes!", but not for the reasons you might suspect.

Autonomous Database is strategic to Oracle.  Just read the transcript from the recent Oracle quarterly earnings conference call and it will be obvious to you.  Autonomous is an advancement in technology that has significant investment from Oracle and very real benefits for customers.  It's a clear market differentiator - I do truly believe this, it's not merely my marketing spin.  And now, with the addition of Oracle APEX & SQL Developer Web & Oracle REST Data Services, I think this combination of technologies provides even more capabilities to this platform and even greater differentiation.  What other service provides elastic, autonomous capabilities, application design and proven low code application development, out-of-the-box?  Did I mention that this also happens to include the world's most popular database, Oracle Database?

The benefits of low code application development are real.  And Low Code + Autonomous Database is the ideal combination.  Low code is about reducing costs, delivering faster, with greater consistency, and being usable by a broader range of skill sets.  Some of the benefits of Autonomous Database are equivalent - less cost, instant availability, usable by others who may not be world-class experts.  It has been a long multi-year confluence of events that has brought us together here.

The APEX community is the envy of others at Oracle.  Even people who aren't APEX fans recognize the APEX community's passion.  But where did this come from?  Do people really get excited about a tool?  No.  They get excited about what they can do with a tool - how it helps them deliver a solution, and be successful.  A carpenter doesn't get passionate about his dual-slide compound miter saw because it's a cool tool.  He gets satisfaction about what he can actually do with that tool versus a hand saw.  When you get a pay raise or praise or a promotion because of what you've been able to deliver with APEX and ORDS and Oracle Database, that's a reason to get excited!  And I think that is ultimately the real story behind the enviable, tangible energy in the APEX community.  Countless people have had many great successes with this combination of technologies, and success begets success.

Let's say you're in the APEX community, you saw this announcement about APEX on Autonomous, but you're not interested in cloud.  Or, as Andre de Souza so eloquently stated on Twitter, "I know it’s big news, just does not affect 99,9% of current #orclapex developers I’m guessing."  Should you care?  I say yes, and here's why.  The great APEX community that I mention above, which has been so successful with APEX & ORDS & Oracle Database over the years, has become very large across the globe, and with not a lot of help from Oracle.  Make no mistake - Oracle does invest in APEX, millions of dollars every year.  But I still come across Oracle Database customers who have simply never heard of APEX.  This is because there has not been much promotion from Oracle marketing or public relations or even sales.  All of this is about to change.  Why?  Because APEX is on Autonomous Database, and Autonomous Database is strategic to Oracle.  You will probably see more communication and discussion from Oracle about APEX than probably the last 20 years combined.  Low code resonates with customers, APEX is proven, and everyone has application development needs.

How does this benefit someone in the APEX community?  Simple:

  1. Awareness and interest will rise by people who have never heard about APEX before, both existing on-premises customers and net new customers.
  2. There will be greater demand for APEX and database development talent.  If you have experience with APEX, with a proven track record of delivering solutions with APEX, you're a very attractive person.  Perhaps the rate you charge has now gotten a bit higher.  You'll certainly gain upward mobility.
  3. You'll no longer have to introduce someone to APEX for the very first time, or counter the claim that "it's not strategic."
  4. As our friends from Explorer UK say, with APEX, they "develop cloud ready applications".  And you've been doing this for years.  Don't be afraid to make this claim.  When and if you're ready for cloud, you're already out of the gate.  The same APEX apps you developed on-premises run and look and feel exactly the same in the cloud.  Who has been developing cloud-ready apps for years?  You!

So.  Even if you're not into "cloud" but into APEX, this announcement and these capabilities on Autonomous Database has material impact on you and everyone else in the APEX community.  Your skills and experience will become more valued, and we should expect the market and interest and demand to grow.

Everything is not perfect, and we on the APEX team still have a lot of very hard work ahead of us.  But these are exciting times and it's what we've labored on for the past 20 years, to get to this point.  For those who have been with the APEX community for so many years, congratulations!  You've bet on the right horse.  Just fasten your seat belt.

Saturday, February 23, 2019

APEX World 2019: Het gaat weer los!



The very first and still the largest Oracle APEX-focused conference in the world, APEX World 2019, is happening again this year on March 25/26 2019 in Rotterdam, Netherlands.  Incredibly, it's the 10th year for this conference, and like the global Oracle APEX community, it's still growing!

If you've never been to a user group conference, then you should consider attending one this year, and APEX World is an excellent choice.  There is something very organic and authentic about user group conferences.  It is a collection of enthusiasts, professionals, students, partners, business leaders, citizen developers, and full-stack developers, all there for a common purpose.  The APEX community is unique, certainly at Oracle, and probably within the industry too.  You will find this common spirit of sharing and camaraderie ever-present at APEX World.  It helps that the Dutch are naturally kind people, too (and very direct). ;)

This year at APEX World, there are a number of important additions, including:

  • A business seminar "The Future of APEX"
  • A separate track with real customers presenting their real success stories.  This is ideal for someone who is curious about APEX and the large variety of problems being solved with APEX today
  • Numerous deep-dive sessions for those who are experienced APEX developers
  • A special student track
  • Workshops for students from academies/ universities so they can build their first Low Code Oracle APEX app

There is honestly something for everyone.  And the vast majority of sessions will be in English, so there's no reason not to attend.  Did I mention that the Dutch are super nice?

I am honored to attend APEX World 2019, and I will be there, along with product development team superstars Hilary Farrell, Shakeeb Rahman and Jason Straub.  Personally, I relish the opportunity to engage with our many customers and partners, understand what you're doing, understand what your pain points are, and get your advice how we can help you in the future.  The APEX team is invested in your success.  We look forward to seeing you there!



Saturday, December 15, 2018

The job where I learned the most valuable lessons was...

...McDonald's!  Prior to Oracle, I worked for a number of different companies: in IT at a bank and in product development at a couple other large companies.  But the most valuable lessons I ever learned and what has helped me the most in my entire career has been the education I received in in my late teens at McDonald's.

Often times, when people reflect on who shaped them the most in life, it's usually a coach or a high school teacher or a university professor.  In my case, it was Louis J. Stallman, - the general manager of a McDonald's franchise in Wadsworth, Ohio, where I worked at in high school and in the summer breaks between my years at university.

I'm second-generation American, the product of Polish and German immigrants.  I'm the youngest of 7 children (my Mom was also also the 7th child in her family).  My parents grew up during the Depression and passed their life lessons onto us - waste nothing, speak when you're spoken to, and always work & do your best.  My Dad was in the US Army Air Corps and flew on B-29s during World War II.  He was the disciplinarian.  At the time, my friends thought that I grew up in an overly strict household, but I can say today with certainty that this disciplined household provided complete structure.  There was a lucid difference between doing something right and doing something wrong.

When I started at McDonald's in high school, I wasn't sure what to expect.  It probably was a bit more rigid than what most kids were used to, but having grown up in a disciplined family, it was pretty easy to adapt to.  I certainly wasn't perfect - I was the typical crass teenager who thought he knew everything.  But it was a very structured environment and there was always a right and a wrong way to do something.

The general manager of this McDonald's was Louis J. Stallman.  While he was a kind man, he was also a bit of a disciplinarian.  There was no slouching about.  This is where I learned the infamous phrase "if you have time to lean, you have time to clean".  I made a whopping $3.35 an hour and we were put to task and earned every cent of it.  I worked a number of years there, in the summers, over holidays, in some cases even on holidays.  I had the good fortune to interact with a lot of people, some nice, some not so nice.  Lou Stallman coached everyone who interacted with a customer, and when you would fail or omit something, he was always there to remind you.  What I learned from Lou Stallman and what I've carried forward into my professional life:

  1. Greet the customer with a smile, always.
  2. When talking to a customer, look at them directly in the eye.
  3. Talk clearly and repeat back to the customer what they told you.
  4. Treat the customer (and really everyone) with respect and dignity.
  5. Genuinely thank the customer and wish them farewell.

That's it.  Seems obvious, right?  It is, but I see countless people forego these very basic courtesies when interacting with any customer.

I'll greet anyone at any time.  It's the kind thing to do.  Where I live in the USA (Ohio), it's generally considered rude not to greet someone, to put on a smile and say "good morning" while waiting for an elevator or say "hello" while passing them on the street.  I always enjoy going to Oracle HQ in California and greet everyone I pass on the sidewalk - they look at me like I'm from Mars.

In a disconnected, smartphone-crazy world, I find people are a bit afraid to engage someone and look them in the eye while talking to them or listening to them.  There couldn't be a better way to say "you have my full attention" than looking at them directly, without distractions.

When listening to a customer, pay careful attention to what they're asking, and repeat back to them what you think they said.  Maybe they're complaining and need to vent some steam.  Maybe what they really want isn't accurately expressed in the words they're using, so repeat back to them what you think they want.  That's the perfect time to ensure you have a mutual understanding.  And people like to know that someone has truly listened to them.

The customer (and really everyone) should always, always, always be treated with respect and dignity.  At all times.  The customer may not always be right, but they are deserving of respect and dignity.

Always thank the customer.  Always.  You may have killed yourself for your customer and you might think that they owe you something.  But still thank them.  They're the reason why you're able to feed your family.  Competition is everywhere, and an easy way to stand out is through a personal connection.  Something is wrong if you can't extend a simple courtesy to your customer, thanking them for their business.

There you have it - the simple lessons I learned from McDonald's general manager Louis J. Stallman.  They're not technical and they're not difficult to grasp.  But they have broad applicability to almost any job in any field anywhere in the world.  They have served me well and I encourage you to try them yourself.

Friday, November 09, 2018

How do I add an image to a page in Oracle APEX?

Preface:  Many blog posts about Oracle APEX seem to cover a new feature or something rather obtuse & complex.  The sophisticated blog posts have little relevance to someone who is completely new to APEX and Oracle Database.  Having worked with APEX since 1999, it's always difficult for me to put myself in the shoes of a brand new student of APEX.  When I interact with people new to APEX, I try to look at APEX through their eyes, and listen to what they find confusing.  Sometimes, it's completely obvious to me and, at the same time, completely vexing to them.  Thus, this will begin a series of blog posts how to perform very common tasks in APEX.  It will be boring for the experienced user, but hopefully helpful to the APEX developer in training.  And separately, we will also strive to make APEX simpler and easier to understand, so that blog posts like this one become unnecessary in the future.



Gerald Venzl, a highly respected Oracle Database product manager, was recently participating in a hackathon with a team of people, and they were using Oracle APEX as part of their solution.  They produced a QR code for the URL to their app, they saved this QR code image to a local file, and they wanted to include the image on a page in their APEX application.  As Gerald stated, it took more than 30 minutes for this learned and competent group of people to figure out how to do this.  This is not a criticism of Gerald and his colleagues, it's a criticism of APEX.  Gerald and his team were expecting a simple upload to a page item of type Image and they would be done, right?  Well, not so simple.

This blog post is not intended to cover the case where you have images in BLOB columns in a database table (we'll cover that in the future), or the case where the image can already be referenced via a URL.  I am presenting the simple case of you having an image on the file system on your local computer, and you want to reference it in your APEX page.

From a high-level, the steps are:
  1. Upload the file as a static application file.
  2. Using the HTML IMG tag, reference the static application file in your application.


Now, for the more detailed explanation.

Before you can reference an image in your APEX application, it has to be in a location which can be "served" by your Web server.  With APEX, you have access to the back-end Oracle Database, but you don't have access to the Web server file system.  Fortunately, there is an easy way to upload the image into the Oracle Database and have it served by your Web server when used within an APEX application.  These are called Static Application Files.

The steps are really quite simple:
  1. Edit your application in the Application Builder
  2. Click Shared Components
  3. Click Static Application Files
  4. Click Upload File
  5. Choose the file from your local computer
  6. Click the Upload button




In the picture above, this is the list of Static Application Files in the APEX Application Builder, after uploading file Three_Stooges.jpg.  Under the Reference column, there is the string #APP_IMAGES#Three_Stooges.jpg.  This is how you can reference the file when used in an HTML context in your APEX application.  This reference means nothing when used outside of an APEX application.  When someone is running your application, the APEX engine will replace the substitution string #APP_IMAGES# with an absolute URL reference to an embedded REST endpoint which will return the stored image.

To reference this image in your application, you just need to include a reference to it from an HTML IMG tag on your APEX page.  This image reference would have to be entered into the attributes of your APEX application wherever it's appropriate to embed HTML.  This includes the headers and footers of page regions, templates, and even the Source attribute of certain region types.  An easy way to get started is to reference an image in a region of type Static Content.

As an example, edit a page in Page Designer.  In the gallery at the bottom of the page, drag a region of type Static Content onto your page.  In the Source attribute of this region, enter the HTML string to reference the image:

<img src="#APP_IMAGES#Three_Stooges.jpg">

Obviously, reference your file and not the name of the file in my example (Three_Stooges.jpg).  Save your page and run.  That's all there is to it!






One last point.  If you do have access to the web server file system, and you wish to include static file references in your application, then definitely put them on your web server file system and simply serve them from there.  It will always be more efficient to have these served by a web server instead of having to fetch them out of the database.

Sunday, August 19, 2018

Automating service creation in Oracle APEX

Oracle Academy is a division in Oracle whose mission is to advance "computer science education globally to drive knowledge, innovation, skills development, and diversity in technology fields."  The programs that Oracle Academy offers are free to accredited secondary schools, technical/vocational schools, and two and four-year colleges and universities.

Oracle Application Express (APEX) has been hosted by Oracle Academy and used for many years to facilitate the delivery of curriculum for database design and programming, SQL, PL/SQL and even APEX.  To facilitate their business requirements, they had custom extensions written (in part, by our team) into the core APEX product.  But this type of solution becomes difficult to maintain, because every release of APEX requires migration and rewrite of these custom extensions.

A number of months ago, I met with the Oracle Academy team to try and encourage them to move the service creation of APEX workspaces into their own custom interfaces.  I told them that everything is provided to easily and programmatically create APEX workspaces, create database users (schemas), create administrators and developers within each workspace, and even pre-load APEX applications and custom database objects in each workspace, at the time of service creation.

Naturally, they asked for an example script to accomplish all of these tasks, which I authored and shared with them.  Because this type of logic is very relevant for many other purposes, e.g., testing, continuous integration, DevOps, I wish to share these same annotated scripts here.  If you are someone who has been using APEX for 10 years, you won't learn anything new here.  But for those just getting started with APEX, I hope you find it fruitful.

Starting with an empty Oracle database, the requirements are to develop a single script which will:

  1. Create a tablespace (storage) for each workspace
  2. Create and associate multiple database users (schemas) with each workspace
  3. Create an APEX workspace
  4. Create an administrator and developer account in each workspace, restricting the access of each developer to only one of the schemas mapped to the workspace.
  5. Create custom database objects in each schema
And here we go...
set define '^' verify on
set concat on
set concat .

Rem
Rem    Title:  Demo_of_Provisioning.sql
Rem
Rem    Description:  This script will demonstrate use of all of the APIs and SQL statements necessary
Rem                  to create tablespaces, data files, database users, and APEX workspaces.  From this
Rem                  example, it is assumed that the Academy team will be able to develop their own custom
Rem                  provisioning process and no longer rely upon custom extensions to the 
Rem                  Oracle Application Express software.
Rem
Rem    Notes:  It is assumed that this script is run as user SYSTEM.
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jkallman    08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Provisioning_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1

timing start "Create demonstration tablespaces, schemas and workspaces"


--
-- Predefine the path used for tablespace datafile creation.  If you're using Oracle Managed Files
-- or grid infrastructure, then this isn't necessary and you'll remove the 'datafile' portion of 
-- the CREATE TABLESPACE statements
--
define DATAFILE_PATH='/u01/app/oracle/oradata/'


--
-- Step 1:  Create the Tablespaces
--
-- Since Oracle Academy will want to group multiple database users/workspaces into a single tablespace, we'll need to
-- first create the tablespaces, and then the database users in step 2
--
create tablespace OACADEMY_DATA1 datafile '^DATAFILE_PATH.iacademy_01.dbf' 
 size 50M autoextend on next 50M maxsize 2G extent management local 
 autoallocate segment space management auto;

create tablespace OACADEMY_DATA2 datafile '^DATAFILE_PATH.iacademy_02.dbf' 
 size 50M autoextend on next 50M maxsize 2G extent management local 
 autoallocate segment space management auto;



--
-- Step 2:  Create the Database Users (Schemas)
--
-- It's up to you how you wish to group database users/schemas into different tablespaces.  
-- You can do it by total number or free space or ratio of users to tablespaces.  It's all up to you.
--
begin
 -- DB users in DATA1 tablespace
    execute immediate 'create user OACADEMY_DB10 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

    execute immediate 'create user OACADEMY_DB11 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

 -- DB users in DATA2 tablespace
    execute immediate 'create user OACADEMY_DB20 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

    execute immediate 'create user OACADEMY_DB21 identified by "' || sys.dbms_random.string('X',30) || 
                   '" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

end;
/



--
-- Step 3:  Create the APEX Workspaces
--
-- Create the APEX workspaces and associate a default schema with each
--
begin
    apex_instance_admin.add_workspace(
     p_workspace_id   => null,
     p_workspace      => 'OACADEMY1',
     p_primary_schema => 'OACADEMY_DB10');
end;
/

begin
    apex_instance_admin.add_workspace(
     p_workspace_id   => null,
     p_workspace      => 'OACADEMY2',
     p_primary_schema => 'OACADEMY_DB20');
end;
/


--
-- Step 4:  Add additional schemas to the existing workspaces
--
--
begin
    apex_instance_admin.add_schema(
     p_workspace      => 'OACADEMY1',
     p_schema         => 'OACADEMY_DB11');
end;
/

begin
    apex_instance_admin.add_schema(
     p_workspace      => 'OACADEMY2',
     p_schema         => 'OACADEMY_DB21');
end;
/


--
-- Show a quick summary of the workspaces and schemas
--
column workspace_name format a50
column schema         format a40
select workspace_name, schema from apex_workspace_schemas;



--
-- Step 5:  Create an administrator account and a developer account in each worskpace
--
--
begin
    -- We must set the APEX workspace security group ID in our session before we can call create_user
    apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY1'));

    apex_util.create_user( 
        p_user_name               => 'BOB',
        p_email_address           => 'bob@bob.com',
        p_default_schema          => 'OACADEMY_DB10',
        p_allow_access_to_schemas => 'OACADEMY_DB10',
        p_web_password            => 'change_me',
        p_developer_privs         => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- workspace administrator

        apex_util.create_user( 
        p_user_name               => 'JUNE',
        p_email_address           => 'june@june.com',
        p_default_schema          => 'OACADEMY_DB11',
        p_allow_access_to_schemas => 'OACADEMY_DB11',
        p_web_password            => 'change_me',
        p_developer_privs         => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- developer

    commit;
end;
/

begin
    -- We must set the APEX workspace security group ID in our session before we can call create_user
    apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY2'));
    
    apex_util.create_user( 
        p_user_name               => 'ALICE',
        p_email_address           => 'alice@alice.com',
        p_default_schema          => 'OACADEMY_DB20',
        p_allow_access_to_schemas => 'OACADEMY_DB20',
        p_web_password            => 'change_me',
        p_developer_privs         => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- workspace administrator

    apex_util.create_user( 
        p_user_name               => 'AUGUST',
        p_email_address           => 'august@august.com',
        p_default_schema          => 'OACADEMY_DB21',
        p_allow_access_to_schemas => 'OACADEMY_DB21',
        p_web_password            => 'change_me',
        p_developer_privs         => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- developer

    commit;
end;
/

-- 
-- Show a quick summary of the APEX users
--
column workspace_name           format a30
column user_name                format a20
column email                    format a25
column is_admin                 format a10
column is_application_developer format a10
select workspace_name, user_name, email, is_admin, is_application_developer from apex_workspace_apex_users order by 1,2;


--
-- Install custom database objects in each schema by simply running one more SQL scripts.  Note that the
-- script below is something that you author and maintain.  It will do all DML and DDL you have in it,
-- and we simply iterate through the schemas and run the script each time.
-- 
alter session set current_schema = OACADEMY_DB10;
@custom.sql

alter session set current_schema = OACADEMY_DB11;
@custom.sql

alter session set current_schema = OACADEMY_DB20;
@custom.sql

alter session set current_schema = OACADEMY_DB21;
@custom.sql

timing stop
spool off


And to complete the lifecycle, I also authored a simple SQL script which will cleanup everything created above - removing the APEX workspaces, database users and tablespaces.
set define '^' verify on
set concat on
set concat .

Rem
Rem    Title:  Demo_of_Cleanup.sql
Rem
Rem    Description:  This script will cleanup all objects created by script Demo_of_Provisioning.sql
Rem
Rem    Notes:  It is assumed that this script is run as user SYSTEM.
Rem
Rem            **** THIS SCRIPT IS DESTRUCTIVE **** - It will drop tablespaces, data files, workspaces, schemas, etc.
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jkallman    08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Cleanup_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1


timing start "Remove all workspaces, schemas and tablespaces"

--
-- Step 1:  Remove the APEX Workspaces
--
--
begin
    apex_instance_admin.remove_workspace(
        p_workspace      => 'OACADEMY1' );
end;
/

begin
    apex_instance_admin.remove_workspace(
        p_workspace      => 'OACADEMY2' );
end;
/



--
-- Step 2:  Drop the database users
--
--
drop user OACADEMY_DB10 cascade;
drop user OACADEMY_DB11 cascade;
drop user OACADEMY_DB20 cascade;
drop user OACADEMY_DB21 cascade;



--
-- Step 3:  Drop the tablespaces 
--
--
drop tablespace oacademy_data1 including contents and datafiles;
drop tablespace oacademy_data2 including contents and datafiles;

timing stop
spool off