Archive

Archive for the ‘Oracle Application Express (APEX)’ Category

Upgrade Oracle Application Express to 4.1

September 9th, 2011 Jasdeep Singh No comments

In this post, we are upgrading our install of Application express 4.0 to 4.1 using Embedded PL/SQL Gateway on Windows. As per Oracle APEX documentation, any version of APEX starting from 1.5.x can be upgraded to 4.1. This version of Oracle Application Express requires an Oracle database that is release 10.2.0.4 or later or Oracle Database 10g Express Edition.

The install/upgrade file for APEX can be downloaded from APEX page. Download the file apex_4.1_en.zip.

Step 1: Unzip the downloaded zip file.

Step 2: Open command line editor and change working directory to the unzipped folder name “apex”.

Step 3: Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.

C:\apex>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 13 22:58:16 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL>

Step 4: Run apexins.sql passing the following four arguments in the order shown:

@apexins tablespace_apex tablespace_files tablespace_temp images

Where:

  • 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.
  • images is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/.

SQL> @apexins USERS USERS TEMP /i/

Step 5: The script will install new accounts and objects to support 4.1.

Step 6: The install script creates a log file in the APEX folder with a file that contains a timestamp.

Step 7: The upgrade will carry over the admin password from the previous release. In case, you need to reset the password, run the apxchpwd.sql script.

Step 8: Finally, copy the new images to the existing apex images folder. The new images are in a sub folder called “images” in the unzipped folder. Copy the images to the images folder in the existing application express home.

xcopy /E /I ORACLE_HOME\apex\images ORACLE_HTTPSERVER_HOME\ohs\images

If you are using OracleXE then the resources are stored directly in the database. Use the following script to point to the images directory:

SQL> @apxldimg.sql c:

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:/apex/images'

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

timing for: Load Images
Elapsed: 00:01:05.44

Directory dropped.

Step 9: Open a browser window and log into APEX.

APEX Release 4.1

APEX Release 4.1

For more details and other instructions refer to Oracle Application Express Installation Guide.

Oracle Application Express 4.0 Early Adopter

December 22nd, 2009 Jasdeep Singh No comments

APEX 4.0 EA version is now out. Everyone who has been waiting to get their hands on the new release can sign up at: http://tryapexnow.com/. Click on the Sign Up button.

tryapexnow.com Early Adopter Sign Up Page

tryapexnow.com Early Adopter Sign Up Page

I first launched the application in IE8 (browser) but realized that some of the buttons were not showing up. Oracle also posts a warning, “Internet Explorer is not currently supported with the hosted APEX 4.0 Early Adopter. The preferred browser for this evaluation is Mozilla Firefox. Internet Explorer will be fully supported at a later date (before Production).”

Application Express 4.0 EA Home Page

Application Express 4.0 EA Home Page

I first wanted to try the new application type – “websheet”. But as I clicked on the option, I got an error: “Websheets are not enabled at this time”. Looking at some of the messages, it looks like Oracle will have an EA Phase II.

The interface does look good. I will be reviewing the new features and posting my feedback/thoughts here in future posts.

Oracle APEX – Reset Internal Password for Oracle XE on Linux

August 19th, 2009 Jasdeep Singh No comments

Last week I had installed Oracle XE on our Linux DEV box. I wanted to make some changes to the instance settings and realized I had misplaced the password for Internal workspace account. After doing some research, I found that Oracle provides us a “password change” script with APEX source code. We can run this script using the SYS account and it will reset the password for internal account. Here are the steps and an example:

1) Log into sqlplus using the SYS account.

2) Execute apxxepwd.sql script in the APEX source code directory with the new password.

3) Log back into Application Express INTERNAL workspace using the new password.

Here is an example:

[home@localhost]# cd apex
[home@localhost apex]# sqlplus sys/syspassword
SQL> @apxxepwd.sql password123
Session altered.
...changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.
SQL> exit

The code in apxxepwd.sql looks like this:

Rem Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved.
Rem
Rem NAME
Rem apxxepwd.sql
Rem
Rem DESCRIPTION
Rem Changes the password for the INTERNAL ADMIN user
Rem
Rem NOTES
Rem Assumes the SYS user is connected.
Rem
Rem REQUIRENTS
Rem - Oracle 10g
Rem
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jstraub 08/01/2006 - Created
Rem jkallman 09/29/2006 - Adjusted current_schema to FLOWS_030000
Rem jkallman 08/02/2007 - Change FLOWS_030000 references to FLOWS_030100
Rem
set define '&'
set verify off
alter session set current_schema = FLOWS_030100;
prompt ...changing password for ADMIN
begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := 'ADMIN';
wwv_flow_security.g_import_in_progress := true;
for c1 in (select user_id
from wwv_flow_fnd_user
where security_group_id = wwv_flow_security.g_security_group_id
and user_name = wwv_flow_security.g_user) loop
wwv_flow_fnd_user_api.edit_fnd_user(
p_user_id => c1.user_id,
p_user_name => wwv_flow_security.g_user,
p_web_password => '&1',
p_new_password => '&1');
end loop;
wwv_flow_security.g_import_in_progress := false;
end;
/
commit;

Oracle APEX – Create an Excel (XML) download in APEX and retain formatting (without using BI Publisher)

August 4th, 2009 Jasdeep Singh Comments off

For one of our clients we were tasked with maintaining the look and feel of their existing Excel sheets that they used to manage budget data in the replacement system using APEX. The business users over time had put in a lot of effort into the look and feel of the Excel sheets to meet management appeal. They had formatted columns, headings, total lines and  conditional formatting (such as different background colors for different conditions) within the spreadsheet cells.

For the project to be considered  a success we needed to retain the look and feel of the reports  when we recreated them in APEX.  Within APEX we created a custom HTML and CSS in the HTML region to be assigned to each report.  We also planned to enable the option via a property setting to allow users to download a reports using “Report Export”  (enable CSV output in APEX).

After the first set of reports were converted to APEX and went into production, the users came back to us with a new request. They like the APEX reports but they also wanted to retain the look of the downloads to be carried over into Excel via the the download option. By default the APEX reports do not retain the formats when downloaded in CSV. Otherwise the business users would be forced into spending a lot of time reformatting the CSV outputs.  Hence, below I will show you how to  replace the CSV download with an Excel download that keeps the existing formats and colors.

Note: BI Publisher allows you to control the look and feel of an excel sheet format for downloads, but using BI Publisher for this project was not an option.

To allow the business users to have the Excel downloads and keep the APEX report look and feel (line formats, color and conditional format etc) we came up with one favorable solution – XML spreadsheet file type option (option in Excel which is a save as option) and XML tags called by PLSQL.  Using XML spreadsheets we can open any Excel file and save it in a XML format (see image below). Unlike the CSV option where all the formatting is lost because its a text file type, the XML spreadsheet file type  will retain formats and colors.

Download APEX report into Excel

Download APEX report into Excel

We can also open the file with any editor and review report output. Take note of the XML.

To implement the stated solution, we created a “Download” button on the report screens in APEX. When user clicked the download button, it calls our stored procedure that generates the XML output for the report.

Here are all the steps and requirements for the Excel (XML Spreadsheet) download. To implement this example use the Orders Sample Application schema (demo application that comes with APEX). For the report data build I used SQL query statements referencing the tables DEMO_CUSTOMERS, DEMO_ORDERS and DEMO_USERS in  orders schema. I used the already existing “My Orders” report in the Sample Application to keep simple.  The steps involved to implement this example are split into 4 separate tasks:
We can also open the file with any editor and review report output. Take note of the XML.
To implement the stated solution, we created a “Download” button on the report screens in APEX. When user clicked the download button, it calls our stored procedure that generates the XML output for the report.
Here are all the steps and requirements for the Excel (XML Spreadsheet) download. To implement this example use the Orders Sample Application schema (demo application that comes with APEX). For the report data build I used SQL query statements referencing the tables DEMO_CUSTOMERS, DEMO_ORDERS and DEMO_USERS in orders schema. I used the already existing “My Orders” report in the Sample Application to keep simple. The steps involved to implement this example are split into 4 separate tasks:

Oracle APEX – Controlling the Width of “Select Lists” in APEX forms.

July 3rd, 2009 Jasdeep Singh No comments

For one of our clients, we developed an APEX application (APEX 3.2.1) and during a screen review they were particularly concerned with the look of the entry fields that had disproportionate lengths. As a best practice, its important for an application to have a uniform look to be aesthetically pleasing.  The application required a form with multiple select drop down lists and by default “select lists”  in APEX display different widths based on the existing data value lengths. During QA the users were not pleased with the look of the select lists and requested to see uniformed columns. We decided to make the width of the “select lists” to match the width of the longest select list value in the form.

Image of Form before the Select Lists are sized to Match

Image of Form before the Select Lists are sized to match

Image of Form after the Select Lists are sized to match

Image of Form after the Select Lists are sized to match

Within APEX forms, we can control the width of the items by using the “Width” attribute for an item.   Unfortunately setting this attribute does not work for a “Select List” item. My solution was to use inline style (CSS attribute) for controlling the select list width. To apply the style to control the width, edit the Select List item (Page Properties -> Edit Page Item -> Element Properties) and update the “HTML Form Element Attributes” setting to the required width. See example image below. (Example: style=”width:200px”).
Apply Style in HTML Form Element Attributes

Apply Style in HTML Form Element Attributes

I applied the same width to all the select lists on the form.  Now, when we run the application, take note of the uniform ”Select Lists” on the form as shown below.
Select Lists in the Form have the same Width

Select Lists in the Form have the same Width

The key here for such a simple task, was using CSS styles.