Setting And Retrieving CLOB values in APEX with JavaScript

September 1, 2015 at 12:36 pm | Posted in Oracle Developement | Leave a comment
Tags: , , , ,

Here is a very helpful blog post by Andy Tulley on how to set/get CLOB values from an APEX collection.

APEX 5 Login Screen

July 23, 2015 at 11:48 am | Posted in Oracle Developement | 4 Comments
Tags: , , , ,

The login screens of some of the APEX 5 sample applications have a nice look and include a check mark when you enter text into the username/password fields. This article will demonstrate how to create your own login page to look like that of some of the sample applications.

Edit your Login page (101):

  • Change login region template to Login.
  • Changes to Username and Password fields:
    • Change Template to Hidden.
    • Change Value Placehoder to Username / Password respectively.
    • Change Value Required to Yes.
    • Change CSS classes:
      • Username: icon-login-username
      • Password: icon-login-password
    • Change Post Text for username and password to
      <span class=”t-Login-iconValidation a-Icon icon-check”></span>.
  • Change the Login button Button Position to Next.

Save the changes and run the page. The result should look like this:


To can add one of the application icons from the the packaged applications select on of the icons my previous blog post and make the following changes:

Navigate to Edit Application Definition -> User Interface, and edit the interface for your application.

Edit User Interface

Under Cascading Style Sheets add the this CSS file: #IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css

Add CSS file

On the Login page (101) click the Log In region and add the name of the icon class to Icon CSS Classes:


Save and run the log in page. It should look something like this:


Where is my schema?

July 17, 2015 at 5:17 pm | Posted in Oracle Developement | Leave a comment
Tags: , , ,

I just came accross a little nuisance: During attempt to load some spreadsheet data into a new table using the APEX Data Load wizard in the SQL Workshop, I saw that the Schema select list was empty:

The schema select list is empty.

The schema select list is empty.

The issue was that the parsing schema was granted CREATE ANY TABLE. However for this list to be populated, the parsing schema needs the CREATE TABLE privilege.

APEX 5: Convert Skillbuilder’s Modal to Universal Theme

June 1, 2015 at 10:52 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 5 Comments
Tags: , , , , , ,

If you have implemented the Skillbuilder’s Modal Page Plugin in an application of a previous version of APEX, and want to move to the APEX 5 Universal Theme, you will need to make some modifications. If you continue in an older theme, the plugin should continue to work as normal.

Since the APEX 5 Universal Theme has a modal page type, the plugin is no longer needed. The steps below allow you to convert the Skillbuilder’s Modal Plugin to the Universal Theme modal page. The steps outline the changes needed to make to the application, the modal page, and the parent page (the one that calls the modal page).


Change theme to Universal Theme.

  1. Go to Shared Components -> Themes.
  2. Click Create Theme.
  3. Select  From the Repository, then click Next.
  4. Choose User Interface Desktop, then click Next.
  5. Select Theme Type Standard Themes, Theme Universal Theme, click Next.
  6. Click Create.
  7. After theme is created select Switch Theme.
  8. Select the current theme in the top select list and the Universal Theme in the bottom select list.
  9. Depending on your application select a Reset Grid option. Click Next.
  10. On the Verify Compatibility page, make the necessary adjustments for your page and click Next.
  11. Click Switch Theme.

The Universal Theme should now be the current theme of your application.

Modal Page:

  1. Page Attributes
    1. Change page mode to Modal Dialog.
    2. Change Dialog Template to Modal Dialog.
      Modal Page Attributes
  2. Cancel Button
    1. Change Action to Defined By Dynamic Action.
    2. Add a dynamic action on click of the cancel button, with a true action of Cancel Dialog.
      Cancel Button DA
  3. Page Processes and Branches
    1. Add a new page process of type Close Dialog, to fire after any database activity/row processing is done. Include a success message if desired.
      Close Dialog Process
    2. Remove any branches from the modal page. They are no longer needed.

Parent Page

  1. Remove Dynamic Actions
    If you followed my previous post, the parent page will have three dynamic actions to handle the Skillbuilder’s Modal page: One that fires on the Create button, one that fires on the report’s edit link, and one that handles the Auto Close of the modal in order to refresh the report on the parent page.
    All dynamic actions that have to do with the plugin will need to be removed.
  2. Create Button
    1. The create button that opens the modal to a new, empty record. Change  the Behavior -> Action to Redirect to Page in this Application. Reference the modal page number and also clear the cache for that page. In this example the modal page number is 2.
      Create Button
  3. Edit Links
    In this example there is a report on the EMP table. Each row of the report contains an edit link which opens the modal page (2) to allow editing of the data. In my previous blog post I explained the changes you have to make in order to make the Skillbuilder’s Modal to pop-up. We now need to undo these changes and use the new APEX 5 methodology. In this example, the edit link exists on the EMPNO column.

    1. Remove the Link Attributes from the column link.
    2. Change the Link Target to navigate to the modal page (2) and set the modal page’s primary key (EMPNO).
      Column Link
  4. Dynamic Actions to handle report refresh on modal close.
    Create a dynamic action that fires on the new Dialog Closed event. This action needs to be attached to the report region.

    1. Right-click on the report region and select Create Dynamic Action from the context menu.
      Close Dialog DA
    2. Add a true action to refresh the report.
      Refresh Report DA
  5. Dynamic Action to handle the success message on modal close.
    To do this we need some JavaScript to capture the success message, and create a new div to display the message on the parent page.

    1. Right-click on the report region and select Create Dynamic Action from the context menu.
      DA for Success Message
    2. Create a true action of type JavaScript to display the success message. Add the following code to the true action:
      //Capture success message
      var lSuccessMsg;
      if ( ) {
        lSuccessMsg =;
      } else {
        /* Fallback for 5.0.0 */
        lSuccessMsg = unescape(;
        //Remove checksum
        lSuccessMsg = lSuccessMsg.substr(0,lSuccessMsg.indexOf('/'));
      //Construct HTML to emulate standard success message alert
      var successHTML;
      successHTML = '<div class="t-Body-alert" id="customSuccessMessage">';
      successHTML += ' <div class="t-Alert t-Alert--defaultIcons t-Alert--success t-Alert--horizontal t-Alert--page t-Alert--colorBG is-visible" id="t_Alert_Success" role="alert">';
      successHTML += ' <div class="t-Alert-wrap">';
      successHTML += ' <div class="t-Alert-icon">';
      successHTML += ' <span class="t-Icon"></span>';
      successHTML += ' </div>';
      successHTML += ' <div class="t-Alert-content">';
      successHTML += ' <div class="t-Alert-header">';
      successHTML += ' <h2 class="t-Alert-title">'+lSuccessMsg+'</h2>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += ' <div class="t-Alert-buttons">';
      successHTML += ' <a href="javascript:void(0);" onclick="$(\'#customSuccessMessage\').remove();" class="t-Button t-Button--noUI t-Button--icon t-Button--closeAlert" type="button" title="Close">';
      successHTML += ' <span class="t-Icon icon-close"></span>';
      successHTML += ' </a>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += ' </div>';
      successHTML += '</div>';
      // Display Success Message

All the pieces should now be in place to have the Universal Theme modal windows behave like the Skillbuilder’s Modal Plugin.




Quick & Dirty: Instal htop on Redhead Enterprise Linux 7 (RHEL7)

April 23, 2015 at 1:42 pm | Posted in Oracle Developement | Leave a comment
Tags: , , , , , , , ,

I typically prefer htop to the regular top command. Here are the steps I took to install htop on the Oracle Developer Day VM:

  1. Change to tmp direcoty
    cd /tmp
  2. Download epel repository:
  3. Install epel repository:
    sudo yum install epel-release-7-5.noarch.rpm
  4. Install htop:
    sudo yum install htop

Enjoy the enhanced process viewer



Quick & Dirty: Create a pluggable database in Oracle 12c

April 22, 2015 at 8:30 am | Posted in Oracle Developement | Leave a comment
Tags: , , , , ,
  1. Log in as sys
  2. Change to CDB$ROOT and verify container:
    SQL> alter session set container = cdb$root;
    Session altered.
    SQL> show con_name
  3. Create plugable database from seed (this command also adds the new pdb2 directory):
    create pluggable database pdb2 admin user pdb2_admin 
    identified by secret_password
    file_name_convert =(
  4. At this point the database is created and mounted. Open new database:
    SQL> alter pluggable database pdb2 open read write;
  5. List pluggable databases:
    alter session set container=CDB$ROOT;
    col pdb_name for a30
    col status for a10
    col status for a10
    select a.pdb_name,a.status,b.open_mode
      from dba_pdbs a, v$pdbs b
     where a.pdb_name =
     order by 1
    ------------------------------ ---------- ----------
    PDB2			       NORMAL	  READ WRITE
  6. Create a new tnsnames.ora entry for the database:
    PDB2 =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
            (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = pdb2)
  7. Log in as pdb2_admin:
    sqlplus pdb2_admin/secret_password@pdb2
  8. Log in as sys to pdb2:
    1. sqlplus sys@pdb2 as sysdba;


    2. sqlplus sys as sysdba;
      alter session set container = pdb2;

The new database will have three tablespaces;

SQL> select tablespace_name,contents from dba_tablespaces;

 ------------------------------ ---------
 SYSTEM                         PERMANENT
 SYSAUX                         PERMANENT
 TEMP                           TEMPORARY

For more information see the Oracle-Base article.

Chase that cat! Enable scp on the Oracle Developer Day VM

April 16, 2015 at 10:23 am | Posted in Oracle Developement | Leave a comment
Tags: , , , , , , , ,

I just installed the Oracle Developer Day – Database VirtualBox Appliance and noticed I wasn’t able to scp from my host to the VM. Thanks to Mauro Pagano, who suggested removing the banner from the login script, I was able to fix it.

To remove the banner, edit the /home/oracle/.bashrc file and comment out the following line

cat /home/oracle/Desktop/readme.txt

This prevents the banner to show up, and allows scp to work.

Random Data Generator

April 9, 2014 at 2:54 pm | Posted in Oracle Developement | 1 Comment
Tags: , , , ,

Today I came across this awesome website which will automatically generate data for you, and spit it out in different formats. These include XML, CSV, Excel, and more. But the coolest format of course is Oracle SQL. The website will generate the table creation script and the insert statements for your data:

 id number primary key,
 name varchar2(255) default NULL,
 city varchar2(255),
 age varchar2(50) default NULL,
 zip varchar2(10) default NULL,

INSERT INTO myTable (name,city,age,zip) VALUES ('Ian Parks','New Orleans',15,'8587');
INSERT INTO myTable (name,city,age,zip) VALUES ('Silas Blackburn','Dunbar',40,'97129');
INSERT INTO myTable (name,city,age,zip) VALUES ('Kenyon Hammond','Varsenare',60,'6744');

Note: You may have to work with the quotes.

How cool is that?

I’ve previously blogged about another way of generating random data, so take a look at

Generating Data: Increase a number by a random percentage

Meet “edb360”: a free tool that provides a 360-degree view of an Oracle database

February 20, 2014 at 9:50 am | Posted in Oracle Developement, Oracle Performance, Oracle Tools, PL/SQL | Leave a comment
Tags: , , , ,

Meet "edb360": a free tool that provides a 360-degree view of an Oracle database.

Excel Style Row Numbering in SQL

February 3, 2014 at 12:59 pm | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Tools | Leave a comment
Tags: , , , , , , , , ,

Here is a trick to change your rownumbering (or any other sequential numbering) to the way Excel names their columns. The first 26 rows will be named A – Z, the next 26 AA – AZ, then BA – BZ, etc..

SELECT decode( ceil( ( ROWNUM / 26 ) - 1)
             , 0, ''
             , chr( ceil( (ROWNUM/26)-1 ) + 64)
     || chr(64 + decode( MOD(ROWNUM, 26)
                       , 0, 26
                       , MOD(ROWNUM, 26)
            ) col2
 FROM dual
CONNECT BY LEVEL <= 702; --limits the result to 702 rows 

Row	Label
1	A
2	B
3	C
4	D
5	E
28	AA
29	AB
30	AC
31	AD
32	AE
698	ZV
699	ZW
700	ZX
701	ZY
702	ZZ

Note that this will be good for 702 rows (nbr_of_letters * (nbr_of_letters+1)). After that you’d have to concatenate a third character, or write a function.

Next Page »

Entries and comments feeds.