Install oracle apex on pluggable database and config with ords and apache tomcat

In this step-by-step tutorial, you will learn how to install the latest Oracle APEX version 22.2 on your own local Windows PC. This local Oracle APEX development environment setup will be created using Oracle Database 21c XE, the latest Oracle Rest Data Services (ORDS 22x), Java (JDK 17x), and Apache Tomcat.

Download Oracle XE 21c

https://www.oracle.com/ie/database/te...

Download Oracle APEX

https://www.oracle.com/tools/download...

Download ORDS

https://download.oracle.com/otn_softw...

Download JAVA

https://www.oracle.com/java/technolog...

Download Tomcat

https://tomcat.apache.org/download-90...

Install Oracle APEX

-------------------------------

Go to the Apex folder Location

D:\ApexSetup\apex\apex-latest\apex

D:

sqlplus /nolog

conn sys as sysdba

show pdbs;

alter session set container = ORCLPDB;  // Always execute this command when you open a new sqlplus session

column default_tablespace format A15

column temporary_tablespace format A15

@apexins.sql SYSAUX SYSAUX TEMP /i/

@apxchpwd.sql

alter user APEX_PUBLIC_USER account unlock;

alter user APEX_PUBLIC_USER identified by s;  // Used this password in this video for all accounts 

@apex_rest_config.sql

-----------------------------

Now Install jdk 17 or later version

---------------------------------

Now go to the C Drive and Make a folder name app>then two folder ords and config>in config make

a folder ords>then make a folder logs

Then Copy the Ords zip file to the c drive ords file and then extract it.

Install ORDS

--------------------

Now go to the sqlplus

sys as sysdba

alter user sys identified by s account unlock container=all;

alter session set container = orclpdb;

alter user apex_listener identified by s account unlock;

alter user apex_public_user identified by s account unlock;

alter user apex_rest_public_user identified by s account unlock; 

----------------------------------------------------------------------------

Now Create  a ords.bat file and put the below information in the bat file

----------------------------------------------------------------------------

set ORDS_HOME=c:\app\ords set ORDS_CONFIG=c:\app\config\ords set ORDS_LOGS=%ORDS_CONFIG%\logs set DB_HOSTNAME=localhost set DB_PORT=1521 set DB_SERVICE=ORCLPDB set SYSDBA_USER=SYS c:\app\ords\bin\ords.exe ^ --config %ORDS_CONFIG% install ^ --log-folder %ORDS_LOGS% ^ --admin-user %SYSDBA_USER% ^ --db-hostname %DB_HOSTNAME% ^ --db-port %DB_PORT% ^ --db-servicename %DB_SERVICE% ^ --feature-db-api true ^ --feature-rest-enabled-sql true ^ --feature-sdw true ^ --gateway-mode proxied ^ --gateway-user APEX_PUBLIC_USER ^ --proxy-user

------------------------------------------------------------------------------

Now in the ords location C:\app\ords

Write ords.bat

Enter the Database password for sys as SYSDAB :s

Enter the Database password for ORDS_PUBLIC_USER:s

Confirm password:s

Now run this command 

setx PATH "%PATH%;c:\app\ords\bin"

set PATH="%PATH%;c:\app\ords\bin"

Now Install Apache Tomcat

Give the port 8888 and user name admin and password admin

--------------------------------------

Now go to the cmd command prompt then write

set CATALINA_HOME="c:\Program Files\Apache Software Foundation\Tomcat 9.0"

mkdir %CATALINA_HOME%\webapps\i

Now go to the Apex folder where image file located and copy the images and then go to 

c:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\i this location and keep their

Now go to the ords folder in C drive and copy ords.war file then go to the 

c:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps this location and paste here

now go to the c:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\bin

in the bin there is a file tomcat9w and double click on it and 

then click on java first java options keep it.

-Dconfig.url=c:\app\config\ords

Run Oracle APEX

-----------------------------

http://localhost:8888/ords/apex_admin

--------------------------------------If Pluggable Database is off----then

alter session set container = ORCLPDB;

------------------------------------

alter database open;

-----------------------------------------

show pdbs;

localhost

Installation and Configuration Guide ORDS 22.2

 Step 1: Go to your SQL plus    And connect the sys user

Step 2: From the location of ORDS Folder take the path 

@F:\ords\scripts\installer\ords_installer_privileges.sql  hr

Run the above script.

Now go the CMD Command Prompt and Give the locations

F:\ords\bin

and the second locations

F:\ords\bin>ords install

ORDS: Release 22.2 Production on Wed Jul 06 16:43:47 2022

Copyright (c) 2010, 2022, Oracle.

Configuration:

  /F:/ords/bin/

The configuration folder F:\ords\bin does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the type of installation

    [1] Install or upgrade ORDS in the database only

    [2] Create or update a database pool and install/upgrade ORDS in the database

    [3] Create or update a database pool only

  Choose [2]:

  Enter a number to select the database connection type to use

    [1] Basic (host name, port, service name)

    [2] TNS (TNS alias, TNS directory)

    [3] Custom database URL

  Choose [1]:

  Enter the database host name [localhost]:

  Enter the database listen port [1521]:

  Enter the database service name [orcl]:

  Provide database user name with administrator privileges.

    Enter the administrator username: sys

  Enter the database password for SYS AS SYSDBA:

Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//localhost:1521/orcl

Retrieving information.

  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]:

  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]:

  Enter a number to select additional feature(s) to enable:

    [1] Database Actions  (Enables all features)

    [2] REST Enabled SQL and Database API

    [3] REST Enabled SQL

    [4] Database API

    [5] None

  Choose [1]:

  Enter a number to configure and start ORDS in standalone mode

    [1] Configure and start ORDS in standalone mode

    [2] Skip

  Choose [1]:

  Enter a number to use HTTP or HTTPS protocol

    [1] HTTP

    [2] HTTPS

  Choose [1]:

  Enter the HTTP port [8080]: 8080

Blog link : https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.1/ordig/about-REST-configuration-files.html#GUID-006F916B-8594-4A78-B500-BB85F35C12A0

Persistent Authentication in Oracle APEX 22.1

 Step 1 : Login to Your Administrative user.

Step 2 : Then Click on Manage Instance.

Step 3 : Click on Security.

Step 4 : Allow Persistent AUTH enable to YES.

Installing Oracle Apex 21.1 with 19C Database and Configure ORDS

================================================================================================================================================

Installing Oracle Apex 21.1 as like the previous version.

1>  First download the the Oracle apex 21.1 apex software from the below link

https://www.oracle.com/tools/downloads/apex-v191-downloads.html

2>  After downloading extract the zip file

3>  Open the command prompt on windows  to change the directory

4>  Now take the location just like  

D:\apex

5> On the command prompt write the command :cd the the location 

D:\apex

6> Now run the oracle apex 21.1 all scripts from this software .So login to the sys user.

# sqlplus sys/sys as sysdba

     After login to the sys user run the script

# @apexins SYSAUX SYSAUX TEMP /i/

8> Now run the password change script for the admin user.

     Login the sys user

  sqlplus sys/sys as sysdba

@apxchpwd.sql

9> Configure Apex RESTful Services as follows: 

    Connect to Oracle Using SQL*PLUS if disconnected

# sqlplus sys/syspsw@orcl as sysdba

#@apex_rest_config.sql

9> To copy the Oracle Apex 21.1 images to the apex/images folder .Run the script.

  @apex_epg_config.sql D:\

10> Unlock the APEX_PUBLIC_USER account and specify the password.Don’t forget to connect to Oracle using SQL *PLUS not cmd if disconnected.

# ALTER USER APEX_PUBLIC_USER  IDENTIFIED BY  password ACCOUNT UNLOCK;

          

           #   alter user anonymous identified by password account unlock;

12> Set the HTTP Port if using HTTP server as follows

    Connect to oracle using SQL PLUS if disconnected

#sqlplus sys/syspsw@orcl as sysdba

          #EXEC DBMS_XDB.SETHTTPPORT(8181);  

Enabling Network Services for Oracle Database 12C or later version:

/* First connect as the SYS user with SYSDBA role from sqlplus not cmd */ 

sqlplus sys/syspsw@orcl as sysdba

/* Then execute the below script */

Begin

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '*',

        ace => xs$ace_type(privilege_list => xs$name_list('connect'),

                           principal_name => 'APEX_200100',

                           principal_type => xs_acl.ptype_db));

End;

/

Now your Installation and configuration are complete for Oracle Apex 20.1.You can open the Oracle Apex in a browser using the following URL:

http://localhost:8181/apex/apex_admin

=========================================================================

ORDS Configuration

What is Oracle REST Data Services(ORDS) in Oracle Apex?

Oracle REST Data Services(ORDS) in Oracle Apex

Oracle Application Express uses a simple architecture in which pages are dynamically generated using metadata which stored within the Oracle Database.

The Oracle Application Express architecture consists of a web browser, Oracle REST Data Services (the web listener), and an Oracle Database containing Oracle Application Express. The major advantage of this architecture is the separation of the mid-tier and the database tier.

The web listener, Oracle REST Data Services, functions as a communications broker between the Web browser and the Oracle Application Express objects in the Oracle database by mapping browser requests into database stored procedure calls.

After fully installed, a Uniform Resource Locator (URL) is defined for both developers and end users to access Oracle Application Express.

Users need only a Web browser and the required URL. No additional client software is required.

How to Install Oracle Apex 20.1 on windows 10 with oracle database 19c

================================================================================================================================================

Installing Oracle Apex 20.1 as like the previous version.

1>  First download the the Oracle apex 20.1 apex software from the below link

https://www.oracle.com/tools/downloads/apex-v191-downloads.html

2>  After downloading extract the zip file

3>  Open the command prompt on windows  to change the directory

4>  Now take the location just like  

D:\apex

5> On the command prompt write the command :cd the the location 

D:\apex

6> Now run the oracle apex 20.1 all scripts from this software .So login to the sys user.

# sqlplus sys/sys as sysdba

     After login to the sys user run the script

# @apexins SYSAUX SYSAUX TEMP /i/

8> Now run the password change script for the admin user.

     Login the sys user

   sqlplus sys/sys as sysdba

@apxchpwd.sql

9> Configure Apex RESTful Services as follows: 

    Connect to Oracle Using SQL*PLUS if disconnected

# sqlplus sys/syspsw@orcl as sysdba

#@apex_rest_config.sql

9> To copy the Oracle Apex 20.1 images to the apex/images folder .Run the script.

  @apex_epg_config.sql D:\

10> Unlock the APEX_PUBLIC_USER account and specify the password.Don’t forget to connect to Oracle using SQL *PLUS not cmd if disconnected.

# ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

           #    ALTER USER APEX_PUBLIC_USER IDENTIFIED BY rony;

           #   alter user anonymous identified by oracle account unlock;

12> Set the HTTP Port if using HTTP server as follows

    Connect to oracle using SQL PLUS if disconnected

#sqlplus sys/syspsw@orcl as sysdba

          #EXEC DBMS_XDB.SETHTTPPORT(8181);  

Enabling Network Services for Oracle Database 12C or later version:

/* First connect as the SYS user with SYSDBA role from sqlplus not cmd */ 

sqlplus sys/syspsw@orcl as sysdba

/* Then execute the below script */

Begin

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '*',

        ace => xs$ace_type(privilege_list => xs$name_list('connect'),

                           principal_name => 'APEX_200100',

                           principal_type => xs_acl.ptype_db));

End;

/

Now your Installation and configuration are complete for Oracle Apex 20.1.You can open the Oracle Apex in a browser using the following URL:

http://localhost:8181/apex/apex_admin

Oracle Apex beginner tutorial -11

Before rows:

============

<style>

table.apexir_WORKSHEET_CUSTOM { border: none !important; -moz-box-shadow: none; box-shadow: none; -webkit-box-shadow: none; }

.apexir_WORKSHEET_DATA td {border-bottom: none !important;}

table.reportDetail td {

        padding: 2px 4px !important;

border: none !important;

font: 11px/16px Arial, sans-serif;

}

table.reportDetail td.separator {

background: #F0F0F0 !important;

padding: 0 !important;

height: 1px !important;

padding: 0;

line-height: 2px !important;

overflow: hidden;

}

table.reportDetail td h1 {margin: 0 !important}

table.reportDetail td img {margin-top: 8px; border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;}

</style>

<table class="reportDetail">

For each row:

==============

<tr>

<td rowspan="5" valign="top"><img width="75" height="75" src="#DETAIL_IMG_NO_STYLE#" alt="#PRODUCT_NAME#"></td>

<td colspan="6"><h1><a href="#ICON_LINK#"><strong>#PRODUCT_NAME#</strong></a></h1></td>

</tr>

<tr>

<td><strong>#CATEGORY_LABEL#:</strong></td><td>#CATEGORY#</td>

<td><strong>#PRODUCT_AVAIL_LABEL#:</strong></td><td>#PRODUCT_AVAIL#</td>

<td><strong>#LAST_DATE_SOLD_LABEL#:</strong></td><td>#LAST_DATE_SOLD#</td>

</tr>

<tr>

<td align="left"><strong>#PRODUCT_DESCRIPTION_LABEL#:</strong></td><td colspan="5" >#PRODUCT_DESCRIPTION#</td>

</tr>

<tr>

<td style="padding-bottom: 0px;"><strong>#LIST_PRICE_LABEL#</strong></td>

<td style="padding-bottom: 0px;"><strong>#UNITS_LABEL#</strong></td>

<td style="padding-bottom: 0px;"><strong>#SALES_LABEL#</strong></td>

<td style="padding-bottom: 0px;"><strong>#CUSTOMERS_LABEL#</strong></td>

</tr>

<tr>

<td style="padding-top: 0px;">#LIST_PRICE#</td>

<td style="padding-top: 0px;">#UNITS#</td>

<td style="padding-top: 0px;">#SALES#</td>

<td style="padding-top: 0px;">#CUSTOMERS#</td>

</tr>

<tr><td colspan="7" class="separator">&nbsp;</td></tr>

After rows:

===========

<tr><td colspan="7" class="separator"></td></tr>

</table>

oracle apex beginner tutorial -10 (Create Product Forms and Interactive Report-1)

Report code:

========

/* Formatted on 2020/06/17 21:34 (Formatter Plus v4.8.8) */

SELECT p.product_id, p.product_name, p.product_description, p.CATEGORY,

       DECODE (p.product_avail, 'Y', 'Yes', 'N', 'No') product_avail,

       p.list_price, (SELECT SUM (quantity)

                        FROM demo_order_items

                       WHERE product_id = p.product_id) units,

       (SELECT SUM (quantity * p.list_price)

          FROM demo_order_items

         WHERE product_id = p.product_id) sales,

       (SELECT   COUNT (o.customer_id)

            FROM demo_orders o, demo_order_items t

           WHERE o.order_id = t.order_id

             AND t.product_id = p.product_id

        GROUP BY p.product_id) customers,

       (SELECT MAX (o.order_timestamp) od

          FROM demo_orders o, demo_order_items i

         WHERE o.order_id = i.order_id

           AND i.product_id = p.product_id) last_date_sold,

       p.product_id img,

       

       

       apex_util.prepare_url

                       (p_url         =>    'f?p='

                                         || :app_id

                                         || ':6:'

                                         || :app_session

                                         || '::::P6_PRODUCT_ID,P6_BRANCH:'

                                         || p.product_id

                                         || ','

                                         || 3,

                        p_dialog      => 'null'

                       ) icon_link,

       DECODE

          (NVL (DBMS_LOB.getlength (p.product_image), 0),

           0, NULL,

              '<img alt="'

           || apex_escape.html_attribute (p.product_name)

           || '" title="'

           || apex_escape.html_attribute (p.product_name)

           || '" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '

           || 'src="'

           || apex_util.get_blob_file_src ('P6_PRODUCT_IMAGE', p.product_id)

           || '" height="75" width="75" />'

          ) detail_img,

       DECODE

          (NVL (DBMS_LOB.getlength (p.product_image), 0),

           0, NULL,

           apex_util.get_blob_file_src ('P6_PRODUCT_IMAGE', p.product_id)

          ) detail_img_no_style,

       tags

  FROM demo_product_info p

How to use Carousel Container or Image slider

Code:
====
<image src="#APP_IMAGES#Oracle Apex Beginner Tutorial - 7.png"
width=1050px
Height=350px></image>

How to create and link interactive grid report with modal form

Trigger:
=====
create or replace TRIGGER  "DEMO_CUSTOMERS_BIU"
  before insert or update ON demo_customers FOR EACH ROW
DECLARE
  cust_id number;
BEGIN
  if inserting then
    if :new.customer_id is null then
      select demo_cust_seq.nextval
        into cust_id
        from dual;
      :new.customer_id := cust_id;
    end if;
END;
/


Interactive Report Query:
==================
select customer_id,cust_last_name ||','||cust_first_name customer_name,
cust_street_address1|||decode(cust_street_address2,null,null,','||cust_street_address2)
customer_address,
cust_city,cust_state,cust_postal_code
from demo_customers

How To Upload and show Image Files on Oracle APEX

Table Code:
========
create table image_table(
p_id number,
p_name varchar2(20),
p_pic blob,
primary key(p_id));

Sequence Code:
===========
create sequence hr_image_seq
start with 100
increment by 1

Report Query:
==========

select p_id,p_name,
decode (nvl(dbms_lob.getlength('p_pic'),0),0,null,
'<img src=" ' ||apex_util.get_blob_file_src('P4_P_PIC',p_id) || ' " width="100"/>') "image"
from image_table

Page item source database column setting dangerous

Page items based on a database column where the attribute Source Used is set to Only when current value in session state is null will raise an error when the page item gets rendered. Using this setting for a database column is very dangerous and can result in accidentally overwriting data when viewing and saving multiple records. Always set the Source Used attribute to Always, replacing any existing value in session state.

Oracle Apex Beginner Tutorial - 4 (Create list entry for sales Application)

Parent_list_entry       image/class       list_entry_label           target_type                             page
========================================================================
no_parent_list          choose_image_any  Orders               Page in this application                   4
no_parent_list          choose_image_any  Reports              Page in this application                 26
no_parent_list          choose_image_any  Administration       no target                                    -
========================================================================
setup                   select an image   Manage Customers      Page in this application                    2
setup                   select an image   Manage Products         page in this application                     3 
setup                   select an image   Reset Password            page in this application                   65 
Reports               select an image   Graphical Reports        page in this application                   26
Reports               select an image   Advance Reports          page in this application                   26
Administration   select an image   Users Group                  page in this application                   62
Administration   select an image    Users                            page in this application                   63
========================================================================
Parent_list_entry                list_entry_label                         target_type                                      page
========================================================================
Graphical Reports              Customers Orders                     page in this application                  17
Graphical Reports              Sales by category/products       page in this application                  16
Graphical Reports              Sales by category/month           page in this application                   5
Graphical Reports              Order calendar                           page in this application                  10
Graphical Reports              Customer map                            page in this application                  15
Graphical Reports              Product Order Tree                    page in this application                  19
Advance Reports                Monthly Review Report            page in this application                   0
Advance Reports                Customer Invoice                      page in this application                  50

In the below components setting:
1.First go to the Share components>Navigation >List
2.then click on the create icon to create a new list
3.Select From Scratch on the source wizard
4.Give a name and type is static
5.click next
6.Enter the following value in the list.after creating first five then click on the new created list and click on right side of the wizard create and create new to finish the others list.
If you any query please comments bellow 

List Entry Label                              Target Page Id                image/Class

================                   ===============               ===========

Customers Orders                                   17                            choose any image

Sales by Category and Product               16                            choose any image

Sales by Category / Month                        5                           choose any image

Orders Calendar                                      10                            choose any image

Customer Map                                        15                             choose any image

Product Order Tree                                 19                            choose any image

Gantt Chart                                             20                            choose any image

Box Plot                                                  21                            choose any image

Pyramid Chart                                         22                            choose any image

List View (Mobile)                                 23                            choose any image

Column Toggle Report(Mobile)            24                            choose any image

Reflow Report                                       25                            choose any image

Monthly Review Report                         0                             choose any image

Customer Invoice                                  50                            choose any image

oracle apex beginner tutorial - 3 || How to Create Dashboard in Oracle Apex

Code:
===
Select to_char(o.ORDER_TIMESTAMP,'Mon dd,yyyy') OrderDay,
sum(o.ORDER_TOTAL) sales
from demo_orders o
group by to_char(o.ORDER_TIMESTAMP,'Mon dd,yyyy'),order_timestamp
order by 2 desc nulls last
fetch first 5 rows only;

oracle apex beginner tutorial -1 (how to create a free workspace)

Requesting a Free workspace 

Go to (apex.oracle.com)

Click get started for free button.

On the get started page click Request a free workspace.

Identification:

============

First name: your first name

Last name : your last name

Email : your email address

Workspace :  your workspace name

Survey :

======

Now select the survey options.

Justifications:

===========

You can write here like “for practice purpose”.

Agreement:

=========

Read and accept agreement terms.

Confirmation: click on the submit request button for confirmation.

After all you will get an email in your mail address. Click on the create workspace button in your mail to complete the process.

When you click then the link will take away to website.

After a little bit later you will get a message “workspace created Successfully”.

Click to continue sing in.

Change your password.

Remember your credentials.

Thanks for Watching ,SUBSCRIBE now.

oracle apex beginner tutorial - 2 (How to create an Application in Oracle Apex)

@First sigh in to your oracle apex development environment.That workspace you created immediately.
if you miss that dont worry link is in the description box.
@Now click on the App builder button.
@In the App builder page click on the Create icon Button.
@Click on the first New Application button.
@after displaying the application page at the top of this page you will see a link View Blueprint.
actually its the current application's defination releave in JASON format.
if you want the same page huge number then
copy the JASON code and paste multiple time.
#application name:Sales Application
#Appearence:click on the icon,now select your theme style and navigation.
#Choose new icon:from here you can choose your desired icon.
Pages:

by default your application home page with couple of pages such as (Login and Global pages))
If you click on add page icon then you can added new pages.
Features:
=========
here if you click check all then all this feature will add to your application
this feature is optional.
i will take here one page About page.

Setting section:
================
In the setting section,accept all the default values.Here .
the Application ID is unique numeric identifier,
which is generated automatically to identify your application.
schema name:where your database object's (table,view,procedure,function etc)are included.
ORACLE APEX PROVIDES A NUMBER OF PREDEFINED AUTHENTICATION MECHANISMS,INCLUDING A BUILT IN
AUTHENTICATION FRAMEWORK AND AN EXTENSIBLE CUSTOM FRAMEWORK.IN THE DEFAULT APPLICATION EXPRESS ACCOUNTS AUTHENTICATION
SCHEME USERS ARE MANAGED AND MAINTAINED IN THE ORACLE APEX WORKSPACE.
IN AN ANOTHER TUTORIAL I WILL SHOW HOW TO REPLACE THIS DEFAULT SCHEME WITH A CUSTOM AUTHENTICATION
SCHEME TO CONTROL USER ACCESS TO THIS APPLICATION.
now click the create application button to create the application.