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);