Add Image to Column from URL Column Value Apex

Create a new interactive report based on SQL Query. Make sure the column where image is store is populated in the column ‘PHOTO’ of the table

Go to Columns > Column Formatting and add to HTML Expression below code

<img src=”#PHOTO#” alt=”Image Not Found” height=”200″ width=”260″>

– ‘PHOTO’ is the name of the column which holds URL for the image
– ‘height’ and ‘width’ is the size to which the image will be cropped
– ‘alt’ is the text which will be displayed if the URL is not valid

For “Report with Forms on Table”

Select the Item eg : P4_PHOTO

Change type to “Display Image” and settings to “Image URL stored in Page Item Value”

Enable SSL Certificates for Oracle Apex 18.1 with Oracle Rest Data Services (ORDS) 18.1 hosted on Apache Tomcat 9

If you have a public facing APEX instance it would be mandatory to secure it with TLS 1.2 or SSL. If you want to enable https for a public facing web server, it’s always recommended to use a public certificate authority or at-least use Lets encrypt to generate certificates.Self-signed certificates are not to be put on a public expose service. Ideally, it is better to use a reverse proxy in front (like httpd or NGINX) with tomcat connecting to the DB in backend.

First step is to enable HTTPS from ORDS

——————- STEP A : Enable HTTPS for ORDS ————

Login to your ORDS on http://localhost:8080/ords

Login as “internal” workspace

Go to Manage Instance > Security

Enable HTTPS

Require HTTPS: Always
Require Outbound HTTPS : No

Apply Changes Save..Now go to Step B to enable HTTPS for Apache Tomcat

 

——————- STEP B :  Self-Signed Certificates for Tomcat which is only used on the local network  ————

Enable HTTPS for Apache Tomcat for localhost (this is only for webserver which is not facing the internet )

1. As Apache Tomcat User, generate a keystore with Java

su – tomcat

cd $HOME

pwd

— Check java version —

# which java

# java -version

# keytool -genkey -alias tomcat -keyalg RSA

Add below code to server.xml

 

<Connector SSLEnabled=”true” acceptCount=”100″ clientAuth=”false”
disableUploadTimeout=”true” enableLookups=”false” maxThreads=”25″
port=”8443″ keystoreFile=”/home/tomcat/.keystore” keystorePass=”yourpassword”
protocol=”org.apache.coyote.http11.Http11NioProtocol” scheme=”https”
secure=”true” sslProtocol=”TLS” />

 

 

Remove the HTTP connector tag from the server.xml file
Ensure ‘keystoreFile’ parameter correctly reflects where you created the key Java keystore

— Restart tomcat —

 

Access https://localhost:8443/
also http://localhost:8080/ will work

 

Configuring your app to work with SSL (Optional)
Add below code to web.xml file before web-app tag ends:

 

<security-constraint>
<web-resource-collection>
<web-resource-name>securedapp</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<user-data-constraint>
<transport-guarantee>CONFIDENTIAL</transport-guarantee>
</user-data-constraint>
</security-constraint>

 

This configuration allows you to set SSL options for all an application’s pages in one place. For example, to disable SSL for all your application’s pages, change “CONFIDENTIAL” to “NONE”.
—————– STEP C : Let’s Encrypt SSL Certificates ————

Install Let’s Encrypt from EPEL repos

# yum install certbot -y

Create a certificate

1. If using httpd (in Apex ORDS this is not required goto step 2)
# certbot certonly –webroot -w /home/whadev/public_html -d whadev.whitehat-staging.com.au

2. If using Tomcat
# certbot certonly –webroot -w /home/oracle/apache-tomcat/webapps -d whadev.whitehat-staging.com.au

-w it is the path of ‘webapps’ directory in your CATALINA_HOME directory
-d your domain

Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator webroot, Installer None
Starting new HTTPS connection (1): acme-v01.api.letsencrypt.org
Obtaining a new certificate
Performing the following challenges:
http-01 challenge for whadev.whitehat-staging.com.au
Using the webroot path /home/whadev/public_html for all unmatched domains.
Waiting for verification…
Cleaning up challenges

IMPORTANT NOTES:
– Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/whadev.whitehat-staging.com.au/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/whadev.whitehat-staging.com.au/privkey.pem
Your cert will expire on 2018-10-07. To obtain a new or tweaked
version of this certificate in the future, simply run certbot
again. To non-interactively renew *all* of your certificates, run
“certbot renew”
– If you like Certbot, please consider supporting our work by:

Donating to ISRG / Let’s Encrypt: https://letsencrypt.org/donate
Donating to EFF: https://eff.org/donate-le

# cd /etc/letsencrypt/live/whadev.whitehat-staging.com.au

Generate a PFX file, with certificates already issued by certbot:

# openssl pkcs12 -export -out bundle.pfx -inkey privkey.pem -in cert.pem -certfile chain.pem -password pass:yourpassword

Add in server.xml below connector tag and remove old Connector tag which was defined in Step B

 

<Connector
protocol=”org.apache.coyote.http11.Http11NioProtocol”
port=”443″ maxThreads=”200″
scheme=”https” secure=”true” SSLEnabled=”true”
keystoreType=”PKCS12″ keystoreFile=”/home/oracle/apache-tomcat/bundle.pfx” keystorePass=”yourpassword”
clientAuth=”false” sslProtocol=”TLS”/>

 

Restart Tomcat

 

 

Upgrade Oracle Apex from 5.1.x to 18.1

Oracle has released the latest version of Apex and keeping in line with their naming convention Apex has jumped from version 5.1.4 to 18.1 . The new version of Apex has many exciting new features, you can read about them all here

Setup :
Apex Version : Oracle Apex 5.1.4
Webserver : ORDS Running on Apache Tomcat 9
DB Version : Oracle 12.2 with January 2018 Patchset

 

1. Download Apex 18.1 from Oracle Website
Link: http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

 

2. Rename the old apex directory in ORACLE_HOME

cd $ORACLE_HOME
mv apex/ apex_old/

 

3. Go to the new Apex Directory and run the Upgrade

cd apex/

SQL> @apexins.sql apex apex temp /i/

apex – tablespace for apex
apex – tablespace for apex files
temp – temporary tablespace

After a while you will get the below message if you had an earlier version of Oracle Apex

Session altered.

timing for: Phase 1 (Installation)
Elapsed: 00:04:59.21
Phase 2 (Upgrade)

Session altered.

 

— Now beginning upgrade. This will take several minutes.——-‘)

timing for: Phase 2 (Upgrade)
Elapsed: 00:06:10.38
Phase 3 (Switch)
…Upgrading DBMS_REGISTRY

PL/SQL procedure successfully completed.

 

Session altered.

Thank you for installing Oracle Application Express 18.1.0.00.45

Oracle Application Express is installed in the APEX_180100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:58.70
timing for: Complete Installation
Elapsed: 00:11:10.83

PL/SQL procedure successfully completed.

 

4. Load Images Directory for Apex.

@apex_epg_config.sql ORACLE_HOME

@apex_epg_config.sql /u01/app/oracle/product/12.2.0/dbhome_1

 

5. Copy images from Apex image directory and Restart your Webserver, in my case I have Tomcat, else you will get below error after you access your Web URL for Apex

“There is a problem with your environment because the Application Express files are not up-to-date! The files for version 5.1.4.00.08 have been loaded, but 18.1.0.00.45 is expected. Please verify that you have copied the images directory to your application server as instructed in the Installation Guide.”

As tomcat user

cd /u01/app/oracle/product/12.2.0/dbhome_1/apex/images/

cp -R * $CATALINA_HOME/webapps/i

sh $CATALINA_HOME/bin/shutdown.sh

sh $CATALINA_HOME/bin/startup.sh

If the error persists, clear all browser cache ,cookies and restart your browser

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=158503212359153&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=2342618.1&_afrWindowMode=0&_adf.ctrl-state=beqcsqsc3_4

 

And Oracle Apex is upgraded to the latest version 18.1.0.00.45

Screen Shot 2018-05-28 at 11.17.12 AM

 

 

Install Oracle Apex 5.1.x, 18.1 on ORDS 3,17.4, 18.1

Oracle Apex is a modern framework to quickly develop and deploy web apps sitting on top of Oracle Database. Apex can be run directly from the database engine itself using a component called Mod PL/SQL. It is one of the easiest way to run Apex, but it is not a production ready setup. In a production scenario your database server should not be internet facing. The ideal design is a reverse proxy server facing the internet and a webserver sitting behind it and the DB server behind a firewall isolated from any public network.

Oracle has a modern gateway to do this, it is called ORDS( previously called Apex Listener). It stands for Oracle Rest Data Services. Not only it can help you host your apex instance in a Java web container like Tomcat, Glassfish, Weblogic but also it gives your developer access to build enterprise scale data access API’s. It can help you build your front-end in the modern JavaScript instead of the traditional programming languages. You can read about it more here : https://blogs.oracle.com/newgendbaccess/why-use-rest-and-ords-to-transform-your-oracle-database-into-a-restful-api-service

architecture big

Now to setup ORDS to host your Apex instance first we need to make sure you have 3 components

1. Oracle APEX itself, though from ORDS 17.1 it is not compulsory to have Apex installed, but i always prefer having it. Download the latest version from here : http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2. A Servlet web container like Tomcat, Glassfish or Weblogic. I prefer to use Tomcat since it is the easiest to setup, is open source and is quite lightweight. Apache tomcat can be downloaded from here  : https://tomcat.apache.org/download-90.cgi

3. ORDS, this is the most important component in this setup. Oracle has recently changed it’s numbering scheme. So it jumped from ORDS 3 to ORDS 17.4 and ORDS 18.1. It is now done using YY.MM naming convention. It can be downloaded from here : http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

Let’s  Start with Installing Apache tomcat first on you server

                             1.  INSTALL APACHE TOMCAT

1. Create a user tomcat on the linux server which will be your webserver

useradd tomcat

2. Untar the file in the home directory or create a separate directory. To have a well defined structure i always prefer to have a directory structure like /u01/tomcat

tar xvf apache-tomcat-9.0.8.tar.gz

This will create the directory ‘apache-tomcat-9.0.8’ inside

go to the .bash_profile of tomcat user and add below for the home directory

PATH=$PATH:$HOME/bin:$CATALINA_HOME/bin
CATALINA_HOME=/u01/tomcat/apache-tomcat-9.0.8
export CATALINA_HOME
export PATH

. .bash_profile

3. Start Stop Apache Tomcat 9 snd enable Gui Access

sh $CATALINA_HOME/bin/shutdown.sh

sh $CATALINA_HOME/bin/startup.sh

You can now access the console of tomcat using http://localhost:8080/

To enable access to manager app and manager app gui. Add below to lines to file tomcat-users.xml

vi $CATALINA_HOME/conf/tomcat-users.xml

<role rolename=”manager-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”manager-gui”/>
<role rolename=”admin-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”admin-gui”/>

Save and restart Tomcat

                        2.   INSTALL ORACLE APEX 5.1.x, 18.1

This installation has to be done on the DB Server. Copy your Apex zip file to Oracle Home Directory. Remove the existing Apex directory and unzip the fil

1.Install Apex Binary and Create Tablespace and Schema

cd $ORACLE_HOME

rm -rf apex/

unzip apex_5_0_1_en.zip

cd $ORACLE_HOME/apex

sqlplus “/as sysdba”

SQL> CREATE TABLESPACE APEX  DATAFILE ‘/u01/oradata/orcl/datafiles/apex01.dbf’ SIZE 1G AUTOEXTEND ON NEXT 10M;

SQL> @apexins.sql APEX APEX TEMP /i/

APEX – Tablespace to hold Apex Schema and its associated files

TEMP –  Temporary Tablespace

/i/ – It is  your image directory

Change your Admin password

SQL> @apxchpwd.sql

Create APEX_LISTENER and  APEX_REST_PUBLIC_USER

SQL> @apex_rest_config.sql

If you want to run the PL/SQL gateway continue to the next step else you can skip this and go to Step 3. directly

SQL> @apex_epg_config.sql /u01/app/oracle/product/12.2.0/dbhome_1

‘/u01/app/oracle/product/12.2.0/dbhome_1’ is the home directory of Oracle Database

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Check the HTTP Port,

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

If this is 0 and you need to to install Apex with ORDS then goto Step 3. else continue and get the port

SQL> EXEC DBMS_XDB.sethttpport(8080);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

8080

The URL for Apex application now should be accessible from Web Browser http://localhost:8080/apex/apex_admin

              3. ORACLE REST DATA SERVICES (ORDS) CONFIGURATION & INSTALLATION

After downloading the ORDS to the server, go to the server where you installed Tomcat earlier

1.Unzip the ords zip file

cd /u01

mkdir -p /u01/ords

mkdir -p /u01/ords/conf

unzip ords.18.1.1.95.1251.zip

2. Login to DB Server SQL*PLUS and unlock the Apex  listener usernames and password to ensure ORDS installation goes smoothly

SQL> ALTER USER SYS IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL> ALTER USER APEX_LISTENER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL>ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

If last SQL command fails, it is because there is no ORDS schema yet.

3. Configure ORDS for Database connectivity. Go back to WebServer and there will be a ords.war file in /u01/ords

Edit the /u01/ords/params/ords_params.properties and set the parameters of your DB server and instance. Make sure there is network connectivity between DB Server and Web Server

cd /u01/ords

java -jar ords.war configdir /u01/ords/conf

java -jar ords.war

Don’t select Standalone option in the last, just press 2 and exit. Standlone server is a quick Jetty webserver build in ORDS. But its not the best production setup, hosting on Apache Tomcat is better.

mkdir $CATALINA_HOME/webapps/i/

Copy the image directory from the Apex installation in DB Server to Tomcat image directory.

cp -R $ORACLE_HOME/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ords.war file from /u01/ords to Webapps directory of tomcat

cd /u01/ords
cp ords.war $CATALINA_HOME/webapps/

ORDS should now be accessible on URL http://localhost:8080/ords

Remember to remove the port 8080 configuration from Embedded PL/SQL gateway, if you did Step 2. and are moving to ORDS from PL/SQL Gateway

SQL> EXEC DBMS_XDB.sethttpport(0);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

0

And try URL again : http://localhost:8080/ords

Error 1 :

Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured

Check APEX_PUBLIC_USER account and unlock it, stop and start Apache tomcat and try again. Or maybe an incorrect APEX_PUBLIC_USER password was entered during ORDS configuration. reconfigure the ords.war using below command:

java -jar ords.war setup

And copy new war file to webapps directory again and access URL again

http://localhost:8080/ords

Reference : Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured (Doc ID 2048493.1)

Error 2:

Server isn’t redirecting oracle apex ords on Firefox

This page isn’t working
<hostname> redirected you too many times.

Try clearing the cookies and try again. Most likely close the browser windows and open and again and the error will go away

http://localhost:8080/ords

Reference: Problem Accessing APEX Application Using ORDS 3.0.9.348.07.16 (Doc ID 2280694.1)

Summary :

1.Steps to do when installing only PL/SQL gateway

1 > 2

2. Steps when upgrading from PL/SQL Gateway to ORDS

1 > 3

3. Steps when doing fresh new install of ORDS

1 > 2 > 3

Quick and Easy Integration with Twitter with Apex 18.1

Quick Integration with Twitter in Oracle Apex 18.1

https://blogs.oracle.com/apex/quick-and-easy-twitter-api-with-apex-181

 

Install and Configure Oracle Application Express Apex 5.0, 4.2.6, 4.2.x with Embedded PL/SQL Gateway

1. Download Software as Zip file from OTN
http://www.oracle.com/technetwork/developer-
tools/apex/downloads/index.html

2.Unzip apex_5.0_en.zip Software to ORACLE_HOME directory,
it will create a directory called Apex

3. Login to SQLPLUS as SYSDBA in database where you will
create the Apex instance. Create a tablespace called
“apex” for the Apex 5 installation

$ cd $ORACLE_HOME/apex

SELECT dbms_xdb.gethttpport FROM dual;

EXEC DBMS_XDB.SETHTTPPORT(0);

4. — Full development environment. Run apexins.sql

@apexins.sql tablespace_apex tablespace_files
tablespace_temp images

tablespace_apex is the name of the tablespace for the
Oracle Application Express application user.

tablespace_files is the name of the tablespace for the
Oracle Application Express files user.

tablespace_temp is the name of the temporary tablespace or
tablespace group.

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apexins.sql apex apex temp /i/

images is the virtual directory for Oracle Application
Express images. To support future Oracle Application
Express upgrades, define the virtual image directory as
/i/.

@apxldimg.sql APEX_HOME

APEX_HOME is the directory you specified when unzipping
the file, in our case it is the ORACLE_HOME directory

SQL> @apxldimg.sql /u02/app/oracle/product/11.2.0/dbhome_1

5. Run apxchpwd.sql to create Admin account and Reset the
password —
@apxchpwd.sql

Alter/Unlock the anonymous account


alter user anonymous account unlock;

If you get this error ORA-20001: Password validation
failed.

Then there is a bug in Apex 5 for the password validation

–Execute Below Query and Generate DATA as INSERT
statements–

select t.name, t.value--, t.pref_desc
from apex_040100.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

— Insert Data into APEX_050000 schema and Check again the
values —

select t.name, t.value--, t.pref_desc
from apex_050000.wwv_flow_platform_prefs t
where t.name in ('ACCOUNT_LIFETIME_DAYS'
,'PASSWORD_ALPHA_CHARACTERS'
,'PASSWORD_PUNCTUATION_CHARACTERS'
,'STRONG_SITE_ADMIN_PASSWORD'
,'USERNAME_VALIDATION')

—————————————————-
Configuring the PL/SQL Embedded Gateway
—————————————————-

6. Running the apex_epg_config.sql Configuration Script to
the base directory where Apex software was unzipped. In
our case it is the DB home directory

@apex_epg_config.sql
/u02/app/oracle/product/11.2.0/dbhome_1

7. Conifuringthe Oracle XML DB Protocol Server Port

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

EXEC DBMS_XDB.SETHTTPPORT(8080);

8. Enabling Network Services in Oracle Database 11g or
Later

By default, the ability to interact with network services
is disabled in Oracle Database 11g Release 1 or 2 or
later. Therefore, if you are running Oracle Application
Express with Oracle Database 11g Release 1 or 2 or later,
you must use the new DBMS_NETWORK_ACL_ADMIN package to
grant connect privileges to any host for the APEX_050000
database user. Failing to grant these privileges

Grant connect privileges to any host for the APEX_050000
database user

— for Apex 5 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_050000
-- the "connect" privilege if APEX_050000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_050000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_050000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

— For Apex 4 —

DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200
-- does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'APEX_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Now access the URL for Apex, becuase we configured the
Embedded PL/SQL Gateway, go to:

http://hostname:port/apex/apex_admin

In case you get below error then check if port 8080 is
taken by some other service on the server, change the port
to a new one and restart the database.

“APEX.ERROR.ERROR_PAGE.UNHANDLED_ERROR”

EXEC DBMS_XDB.SETHTTPPORT(8898);

alter system register;

srvctl stop database -d dbname

srvctl start database -d dbname

Rapid Web Deployment Tool

I normally post to this blog things relevant to Databases in general and Oracle in particular but once in a while it is good to go a little off topic.

In addition to Oracle Apex, which most of Oracle DBA’s have some sort of an idea about. There is a tool (or rather framework) which I prefer more to apex, it is called wavemaker. Developing and deploying web applications is even quicker in Wavemaker than Oracle apex. And it is kind of more open (since it is not tied to an Oracle database) tool.

Do have a look. It is fairly simple to use and the learning curve is flatter than Oracle Apex.

Wavemaker

Regards

SSHDBA

Install Oracle Apex in 11gR2

Source –>  http://oraexplorer.com/2007/11/oracle-apex-in-11g-installation/

Today I installed Oracle 11g (11.1.0.6) on my machine. I did not realize that Oracle APEX is a part of the standard database components.

So after the 11g installation, I just follow simple steps (shown later below) for the post-installation. In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.
To configure the embedded PL/SQL gateway:
1. Go to the $ORACLE_HOME/apex directory.
2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.
SYS AS SYSDBA@db11r1> @apxconf

PORT
———-8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []admin_password
Enter a port for the XDB HTTP listener [      8080]
…changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

…changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.
3. Unlock the ANONYMOUS account.
SYS AS SYSDBA@db11r1> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.
4. Enable Oracle XML DB HTTP server
SYS AS SYSDBA@db11r1> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SYS AS SYSDBA@db11r1> COMMIT;

Commit complete.
5. We’re now ready to access APEX.
http://host:port/apex
http://host:port/apex/apex_admin — for admin page
Port in this case is 8080 which is the default.

Note that the format of URL is a little bit different from when using HTTP server with mod_plsql –
http://host:port/pls/apex
http://host:port/pls/apex/apex_admin — for admin page
Also the SQL Developer 1.1.3 is included under “sqldeveloper” directory of ORACLE HOME. So just double-click at sqldeveloper.exe to launch application.

After you have finished the procedure.Login with Admin account and Create a Workspace
http://localhost:8080/apex/apex_admin
After creating a workspace, login to workspace with below url
http://localhost:8080/apex
Now go ahead and Build your Application

Oracle APEX : Source tables for forms and tabular forms must have a primary key.

While creating a’ Report and Form’ Application in Oracle APEX if your using a table which has no primary key then you might get an error.  I would not recommend to do the below procedure in a production environment. It is better to create a new table with a primary key column  and copy the existing tables data into it.

But if you want to modify the existing table by adding a new column for primary key then follow the below method.

Suppose your table is called “your_table”

— Add the Column which will be Primary Key

ALTER TABLE your_table ADD (pk_id NUMBER);

— Create a Sequence

CREATE SEQUENCE  your_table_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

— Create a Trigger to update the PK_ID column with next value in sequence

create or replace TRIGGER  your_table_trg
before insert on your_table
for each row
begin
if :NEW.”ID” is null then
select your_table_seq.nextval into :NEW.”ID” from dual;
end if;
end;
/

— Fill the Sequences in Primary Key Column with Sequence

update your_table set pk_id = your_table_seq.nextval;

commit;

— Add Primary Key

ALTER TABLE your_table ADD CONSTRAINT your_table_pk PRIMARY KEY (pk_id);

———————- EXAMPLE ———————–

ALTER TABLE cardholder_bkp ADD (pk_id NUMBER);

CREATE SEQUENCE  cardholder_bkp_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

create or replace TRIGGER  cardholder_bkp_trg
before insert on cardholder_bkp
for each row
begin
if :NEW.”ID” is null then
select cardholder_bkp_seq.nextval into :NEW.”ID” from dual;
end if;
end;
/

update cardholder_bkp set pk_id = cardholder_bkp_seq.nextval;

commit;

ALTER TABLE cardholder_bkp ADD CONSTRAINT cardholder_bkp_pk PRIMARY KEY (pk_id);