Advanced Oracle SQL ... in several nutshells

The advanced sql in Oracle can be divided in several categories:

<ol>
<li>Hierarchical sql<br />This is all about the connect by-statement</li>
</ol>

1. Hierarchical sql
   This is all about the connect by-statement
2. Partitioned joins
3. Subquery factoring
   This is all about the with-statement
4. Recursive sql
5. Aggregate sql
   This is all about extensions to the group by-statement: grouping sets, rollup and cube, but also the pivot-statement
6. Analytical or windowing sql
7. Sql modelling

How to install seperate APEX bugfix patches

Don't have an Oracle Linux Developer-Day VM up and running yet? Check this post first...

What you need in order to be able to apply bugfix patches

After installing the latest version or update patch, new patch sets may be released that do not increase the version number. They are, so to speak, bugfixes.
If you have access to My Oracle Support or when you otherwise have a - trustworthy! - bugfix patch set at your disposal via something or someone, you can apply it to your current APEX installation. 

Often the process is broadly the same as it is in case of a version update. Place the zip file in the shared folder on the host.

To avoid any conversion issues between the Windows host and the Linux guest, extract the file on the Linux guest. Create a new subdirectory in /home/oracle called "INSTALL" and copy the file from ./HOST into it. Then you can unpack it there.

Each patch directory contains a file README.txt - ALWAYS READ THIS. It states which version (4.2.2.00.11) the patch is suitable for. Which bugs are fixed by this patch. And it contains an installation manual.

Installation scenarios

Roughly speaking, there are two installation scenarios: either you should copy some files, or you have to run a sql script.

Often it's stated that all databases and application servers must stop, and then only the database must be started again. 
In the Developer-Day VM that usually comes down to just stopping the APEX listener, performing the installation actions and then restart the listener again.

Everything you need in order to be able to perform these kind of installations, can be found in the earlier post about upgrading the APEX installation to a new version.

Determine installed bugfix patches

The only problem is that from within the APEX environment, you can not determine which patches are currently installed or not (not as far as I know ...)


I 'solved' this, by defining a login message for the APEX instance (via INTERNAL ADMIN under Manage Instance > Message Log) using the info from the README-files:

How to apply patchset to upgrade an existing APEX installation

Don't have an Oracle Linux Developer-Day VM up and running yet? Check this post first...

What you need in order to be able to upgrade

If you have access to My Oracle Support or when you otherwise have a - trustworthy! - patch set for a new APEX version at your disposal via something or someone, you can upgrade a current APEX installation instead of adding or replacing it by a new one.

This example will demonstrate an upgrade of the version that's bundled with the Pre-Build Developer VM for Virtualbox version 4.2.0.00.27 to 4.2.2.00.11 using Patch# 16277995. Place the zip file in the shared folder on the host.

Open a terminal window. First stop the APEX listener:

To avoid any conversion issues between the Windows host and the Linux guest, extract the file on the Linux guest. Create a new subdirectory in /home/oracle called "INSTALL" and copy the file from ./HOST into it. Then you can unpack it there:

This should result in:

Set the current directory in the terminal screen to the patch directory:



And start the patch via sqlplus:



When the update is finished, you will end up at the SQL prompt. Enter the exit command to exit sqlplus:

Seperate the APEX images directory

First time? Then first get the APEX images directory from the APEX listener configuration directory by creating a new directory /home/oracle/apeximages. You can use a file manager/browser for this.

Then copy the contents of the images directory from the patch into the apeximages directory:

Reconfigure the APEX Listener

Finally, the APEX listener must be reconfigured. So, first remove the current configuration:



And then start the configuration using succesively :

Start APEX listener running in background

The listener is now running in standalone mode in the terminal screen. Once that window closes, the listener will no longer be running. So it must start up in a different way.

Stop it first with Ctrl-C in the terminal window and start it with the existing startup script in /home/oracle:

When you're back at the prompt, you can close the terminal screen. Navigate your browser to the APEX installation and check the new version via the browser:

Upgrading the APEX Listener in an Oracle Dev-Day Virtualbox VM

Don't have an Oracle Linux Developer-Day VM up and running yet? Check this post first...

Download APEX Listener

The Oracle Dev-Day pre-build VM for Virtualbox has the APEX Listener version 2.0.0.331.16.54 installed by default. You can check the currently used version. Just log in to a workspace and then go to Administration / About:

Download latest version here:

    http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html

Unzip the file in the shared folder on the host.

Stop APEX Listener: return to the terminal screen or open it again and make sure you are logged in as root and type the following command:

Afterward, go back to your own user, via "exit":

Seperate the APEX images directory

First time? Then first get the APEX images directory from the listener configuration directory: Copy the contents of directory /home/oracle/apex/images to a new directory /home/oracle/apeximages. You can use a file manager/browser for this, or do it via the terminal window using this command:

In the listener directory rename the current APEX listener to apex_200.war and from /home/oracle/HOST copy the new apex.war to the listener directory:

Then remove the directory /home/oracle/apex using the following command:

Note: If you want to delete only the contents of a directory, use: rm -rf /home/oracle/apex/*

Next, set the configuration directory of the new listener. Otherwise, it will default to /tmp/apex. First change the current directory to /home/oracle/listener:

Note: Here the absolute path was provided, starting from the root "/". But that is not necessary. You can also use a relative approach, reasoning from your current directory. With ". /" you refer to the current directory and ".. /" refers to the parent directory.

Next, enter the following command:

Now the new APEX listener can be configured using:

Now log in to a workspace and then go to Administration/About to check the new version:

Start APEX listener running in background

The listener is now running in standalone mode in the terminal screen. Once that window closes, the listener will no longer be running. So it must start up in a different way.
Stop it first with Ctrl-C in the terminal window and start it with the existing startup script in /home/oracle:

When you're back at the prompt, you can close the terminal screen. Navigate your browser to the APEX installation again - it should still work!

Install and configure "Oracle Database Application Development"-prebuild Virtual Machine in Virtual Box

Download Oracle VirtualBox

Depending on whether you want to install on Linux or Windows choose the correct version:

    http://www.oracle.com/technetwork/server-storage/virtualbox/downloads/index.html

Download both Oracle VM VirtualBox and the corresponding version of Oracle VM VirtualBox Extension Pack.

For now, I will presume a Windows installation.

  

Download Page for Oracle Database Application Development Virtual Machine:

    http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

The download link for that file:

    http://download.oracle.com/otn/other/virtualbox/dd/Oracle_Developer_Day.ova

Note: Before you can download this file, you must first register with an OTN (Oracle Technical Network) account. If you do not have one, then you can register one for free at this login screen:

Then the download starts - may take some time because the file is over 4GB in size J

Start the installation of VirtualBox by running the downloaded VirtualBox-4.2.6-82870-win.exe and follow the instructions. When the installation has finished, start VirtualBox (through Windows Start menu).

Set the location for VMs: Choose File>Preferences:

Add Extension Pack 

On the same window – choose Extensions>Add package and indicate the previously downloaded Oracle VM VirtualBox Extension Pack:

Importing the Oracle_Developer_Day.ova into VirtualBox 

In VirtualBox choose File>Import Appliance and then Open appliance… next locate the file "Oracle_Developer_Day.ova" and choose Next> to start the import.
Always check the option (Reinitialize the MAC address) on the bottom left. Additionally, you can keep the standard namings, or change them to fit your own needs:
Standard:

Customized:

Finally, choose Import.

When the import is complete, select the VM (if not already the case), go to Shared folders…

and choose Add shared folder:

Select a directory that can serve as a conduit for files to the VM:

  
Check Auto-mount. The name as shown in the VM, can be altered:

Now you can start the VM, for example via the green arrow ...

When starting the VM, you can login with username and password "oracle".

Installing Virtualbox Linux Guest Additions 

Choose Devices>Install Guest Additions:

A new icon shows up on the desktop (the browse window that was opened, can be closed):

In the terminal window that was opened automatically at startup, or in one reopened via one of the two marked icons...

…enter the following commands, using the password 'oracle':

Now the volume VBOXADDITIONS can be unmounted. First, change the current directory…

 …and then eject:

In the future you may want to check the current version of the installed Guest Additions using:

Automatically mount the shared folder at startup 

First step is to create a folder in Linux that can serve as the mountpoint for the shared folder on the host.
Open a file manager – there are two ways to do that. The first one starts in your home-dir – in this case /home/oracle:

=>

The second one opens a more verbose filemanager:

 =>

Choose File>Create folder and name the new folder ‘HOST’:

Return to the terminal-window or open a new one and make sure to sign in as root and enter the following command:

 (Ignore the warning.) In the text file that opens, add the following line and save it again:

 Then select System> Shutdown> Restart and log back in with user oracle. Start the file browser and navigate to the HOST folder. If you put some files in the SHARED folder on the host OS, then those files should now be displayed:

Creating a snapshot of the VM-state in order to be able to easily return to this state 

Go to the VirtualBox Manager screen and choose the Snaphots button. (Note: You do not have to close or stop the VM in order to take a snapshot.) You will then see the list of snapshots and the current state. Currently there are no snapshots. Choose Take Snapshot in the toolbar:

Resulting in: