printing from apex using pl/sql

As part of our exercise to convert an excel spreadsheet to APEX, we ran into some simple logic that required more logic and less database select statements. The question comes up how to do an if – then – else statement in APEX. In this example we are trying to advise between using compute on demand or dedicated compute resources. With dedicated compute resources, we can purchase 50 OCPUs as an aggregate. With dedicated we can go as low as 1 OCPU. If we request 14 systems with 4 OCPUs it might be better to request 50 OCPUs.

A typical question form would look like the following image allowing us to ask processor shape as well as quantity. If the total mount exceeds, 50 processors, we output a message suggesting dedicated compute rather than compute on demand.

To get this message on the screen, we first had to pull in the questions that we ask using variables. In this example, we read in the UNMETERED_COMPUTE_SHAPE which is a pick list that allows you to select (1, 2, 4, 8, or 16) OCPU shapes. You can also type in a quantity number into UNMETERED_COMPUTE_QUANTITY. The product of these two values allows us to suggest dedicated or compute on demand for economic reasons.

To execute pl/sql commands, we have to change the content type. To create this area we first create a sub-region. We change the name of the sub-region to represent the question that we are trying to answer. For this example we use the title “Compute on Demand vs Dedicated Compute” as the sub-region header. We then change the type to “PL/SQL Dynamic Content”. Under this we can then enter our dynamic code. The sub-region looks like

If you click on the expand button it opens a full editor allowing you to edit the code. In our example we are going to read the variables :UNMETERED_COMPUTE_SHAPE and :UNMETERED_COMPUTE_QUANTITY. Notice the colon in front of these names. This is how we treat the values as variables read from APEX. The code is very simple. It starts with a begin statement followed by an if statement. The if statements looks to see if we are allocating more than 50 processors. We then output a statement suggesting dedicated or compute on demand using the htp.p function call. This call prints what is passed to it to the screen. The code should look like

Overall, this is a simple way of outputting code that requires control flow. In the previous example we used a select statement to output calculations. In this example we are outputting different sections and different recommendations based on our selections. We could also set variables that would expose or hide different sub-regions below this section. This is done by setting :OUTPUT_VARIABLE = desired_value. If we set the value inside the pl/sql code loop, we can hide or expose sections as we did in a previous blog by setting a value from a pull down menu.

The code used to output the recommendation is as follows

htp.p('You might consider dedicated compute since you have '
|| ' OCPUs which is greater than the smallest dedicated compute of 50 OCPUs');
htp.p('Compute on Demand for a total of '
end if;

converting excel to apex

I am trying to go through the exercise of converting an excel spreadsheet into apex and have stumbled across a few interesting tricks and tidbits.

One thing that I have noted is that stuff done in a spreadsheet can be automated via navigation menus in apex. I talk about this in another blog on how to create a navigation system based on parts of a service that you want to get you to the calculation that you need. This is much better if you don’t really know what you want and need to be lead through a menu system to help you decide on the service that you are looking for.

To create a calculator for metered and un-metered services in a spreadsheet requires two workbooks. You can tab between the two and enter data into each spreadsheet. If something like a pricelist is entered into a unique spreadsheet, static references and dynamic calculations can be easily. For example, we can create a workbook for archive – metered storage services and a workbook for archive – unmetered services which will be blank since this is not a service that is offered. If we create a third workbook called pricelist, we can enter the pricing for archive services into the pricelist spreadsheet and reference it from the other sheets. For archive cloud services you need to answer four basic questions; how many months, how much you will start archiving, how much you will end up with, and how much do we expect to read back during that period. We should see the following as questions

How Many Months?cell F6Initial Storage Capacitycell F7Final Storage CapacityCell F8Retrieval FactorCell F9

The cost will be calculated as

Storage Capacity((F8+F7+((F8-F7)/F6))*F6/2*price_of_archive_per_month)/F6((F8+F7+((F8-F7)/F6))*F6/2*price_of_archive_per_month)Retrieval Cost(((F8+F7+((F8-F7)/F6)/2)*(F9/100))*price_of_archive_retrieval/F6(((F8+F7+((F8-F7)/F6)/2)*(F9/100))*price_of_archive_retrievalOutbound Data Transfersumifs(table lookup, table lookup, …)sumifs(table lookup, table lookup,…*F6

In Apex, this is done a little differently with a sequence of select statements and formatting statements to get the right answer

'   sub-part: ' || PRICELIST.PART_NUMBER ||
' - Archive Storage Capacity           ' as Description,
'   sub-part: ' || PRICELIST.PART_NUMBER ||
' - Archive Retrieval           ' as Description,
'   sub-part: ' || PRICELIST.PART_NUMBER ||
' - Archive Deletes           ' as Description,
'   sub-part: ' || PRICELIST.PART_NUMBER ||
' - Archive Small Files           ' as Description,
to_char(:SMALL_ARCHIVE, '$999,990') as PRICE
'   sub-part: ' || PRICELIST.PART_NUMBER ||
' - Outbound Data Transfer           ' as Description,
where PRICELIST.PART_NUMBER = '123456'
'   Total:' as Description,
to_char(sum(price), '$999,990') as Price
from (
where pricelist.part_number = 'B82623'
where pricelist.part_number = 'B82624'
where pricelist.part_number = 'B82629'
where pricelist.part_number = '123456'

The variables :INITIAL_ARCHIVE replaces F7, :FINAL_ARCHIVE replaces F8, and :RETRIEVE_ARCHIVE replaces F9. Rather than referring to the pricelist spreadsheet, we enter the pricing information into a database and do a select statement with the part_number being the key for the lookup. This allows for a much more dynamic pricebook and allows us to update and add items without risk of breaking the spreadsheet linkages. We can also use REST apis to create and update pricing using an outside program to keep our price calculator up to date and current. Using a spreadsheet allows users to have out of date versions and there really is not any way of communicating to users who have downloaded the spreadsheet that there are updates unless we are all using the same document control system.

Note that we can do running totals by doing a sum from a select … union statement. This allows us to compare two different services like Amazon Glacier and Oracle Archive easily on the same page. The only thing that we need to add is the cost of Glacier in the database and generate the select statements for each of the Glacier components. We can do this and use a REST api service nightly or weekly to verify the pricing of the services to keep the information up to date.

The select statements that we are use are relatively simple. The difficult part is the calculation and formatting out the output. For the bulk of the select statements we are passing in variables entered into a form and adding or multiplying values to get quantities of objects that cost money. We then look up the price from the database and print out dollar or quantity amounts of what needs to be ordered. The total calculation is probably the most complex because it uses a sum statement that takes the results of a grouping of select statements and reformats it into a dollar or quantity amount.

An example of the interfaces would look like

a traditional spreadsheet

and in Application Express 5.0

pulling X-Auth-Token from login

I am a big scripting guy. I believe in automating as much as possible and having a program do as much as possible and me typing as little as possible. I find it easier to use command lines than drag and drop interfaces. I have been struggling with how to script the REST apis for Oracle Cloud Services and wanted to get some feedback on different ways of doing this. I wanted to script creation of a database for some workshops that I typically give. The first step is creating the storage containers for the database backup.

Realize that the blogging software that is used does not allow me to type in “c url” without the space. If you see “c url” somewhere in this text, take out the space.

Most of the information that I got is from an online tutorial around creating storage containers. I basically boiled this information down and customized it a little to script everything.

First, authentication can be obfuscated by hiding the username and password in environment variables. I typically use a Mac so everything works well in a Terminal Window. On Windows 7 I use CygWin-64 which includes Unix like commands that are good for scripting. The firs tsetp is to hide the username, identity domain, and password in environment variables.

  • export OPASS=password
  • export OUID=username
  • export ODOMAIN=identity_domain

In my case, the identity domain is metcsgse00026. The username is cloud.admin. The password is given to me when I log into the system corresponding to this identity domain. What I would type in is

  • export OPASS=password
  • export OUID=cloud.admin
  • export ODOMAIN=metcsgse00026

The first step required is authentication. You need to log into the cloud service using the RESTapi to generate an X-Auth-Token. This is done with a GET command using the “c url” command.

c url -v -X GET -H “X-Storage-User: Storage-$ODOMAIN:$OUID” -H “X-Storage-Pass: $OPASS” https://$

Note the -v is for verbose and displays everything. If you drop the -v you don’t get back the return headers. Passing the -i might be a better option since the -v echos the user password and the -i only replies back with the tokens that you are interested in.

c url -i -X GET -H “X-Storage-User: Storage-$ODOMAIN:$OUID” -H “X-Storage-Pass: $OPASS” https://$

In our example, this returned

HTTP/1.1 200 OK

date: 1458658839620

X-Auth-Token: AUTH_tkf4e26780c9e6b1d171f3dbeafa194cac

X-Storage-Token: AUTH_tkf4e26780c9e6b1d171f3dbeafa194cac


Content-Length: 0

Server: Oracle-Storage-Cloud-Service

When you take this output and try to strip the X-Auth-Token from the header you get a strange output and need to add -is to the command to suppress timing of the outputs.

% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 –:–:– –:–:– –:–:– 0

If you add the grep “X-Auth-Token followed by awk ‘{print $2]’ you get back just the AUTH_string which is what we are looking for.

c url -is -X GET -H “X-Storage-User: Storage-metcsgse00026:cloud.admin” -H “X-Storage-Pass: $OPASS” | grep -s “X-Auth-Token” | awk ‘{print $2}’


accessing oracle cloud storage from command line

Note for the purposes of this blog entry, the world “c url” should be interpreted as one word and not two. Unfortunately, the blog editing software that we have prohibits this work and kicks the blog entry out if it is placed in the blog without the space. Can everyone say a collective “Good Grief” and move on. Unfortunately, you will need to delete the space to make everything work properly.

Now that we have the cost and use out of the way, let’s talk about how to
consume these services. Unfortunately, consuming raw blocks, either tape
or spinning disk, is difficult in the cloud. Amazon offers you an S3
interface and exposes the cloud services as an iSCSi interface through a
downloadable object or via REST api services. Azure offers something
similar with REST api services but offers SMB downloadable objects to
access the cloud storage. Oracle offers REST api services but offers NFS
downloadable objects to access the cloud storage. Let’s look at three
different ways of consuming the Oracle Cloud services.

The first way is to use the rest API. You can consume the services by
accessing the client libraries using Postman from Chrome or RESTClient
from Firefox. You can also access the service from the c url command line.

c url -v -X GET -H “X-Storage-User: Storage-metcsgse00026:cloud.admin”
-H “X-Storage-Pass: $OPASS”

In this example we are connecting to the identity domain metcsgse00026.
The username that we are using is cloud.admin. We store the password in an
environment variable OPASS and pull in the password when we execute the
c url command. On Linux or a Mac, this is done from the pre-installed c url
command. On Windows we had to install cygwin-64 to get the c url command
working. When we execute this c url command we get back and AUTH header
that can be passed in to the cloud service to create and consume storage
services. In our example above we received back X-Auth-Token:
AUTH_tk928cf3e4d59ddaa1c0a02a66e8078008 which is valid for 30 minutes. The
next step would be to create a storage container

c url -v -s -X PUT -H “X-Auth-Token:

This will create myFirstContainer and allow us to store data either with
more REST api commands or tools like CloudBerry or NFS. More information
about how to use the REST api services can be found in an
online tutorial

The second way of accessing the storage services is through a program tool
that takes file requests on Windows and translates them to REST api
commands on the cloud storage. CloudBerry has an explorer that allows us
to do this. The user interface looks like and
is setup with the File -> Edit or New Accounts menu item. You need to fill
out the access to look like . Note
that the username is a combination of the identity domain (metcsgse00026)
and the username (cloud.admin). We could do something similar with PostMan
or RESTClient extensions to browsers. Internet Explorer does not have plug
ins that allow for REST api calls.

The third, and final way to access the storage services is through NFS.
Unfortunately, Windows does not offer NFS client software on desktop
machines so it is a little difficult to show this as a consumable service.
Mac and Linux offer these services as mounting an nfs server as a network
mount. Oracle currently does not offer SMB file shares to their cloud
services but it is on the roadmap in the future. We will not dive deep
into the Oracle Storage Cloud Appliance in this blog because it gets a
little complex with setting up a VM and installing the appliance software.
The documentation for this serviceM is a good place to

In summary, there are a variety of ways to consume storage services from
Oracle. They are typically program interfaces and not file interfaces. The
service is cost advantageous when compared to purchasing spinning disks
from companies like Oracle, NetApp, or EMC. Using the storage appliance
gets rid of the latency issues that you typically face and difficulty in
accessing data from a user perspective. Overall, this service provides
higher reliability than on-premise storage, lower cost, and less
administration overhead.

accessing cloud storage

Oracle cloud storage is not the first product that performs basic block storage in the cloud. The name is a little confusing as well. When you think of cloud storage, the first thing that you think of is dropbox,, google docs, or some other file storage service. Oracle Cloud Storage is a different kind of storage. This storage is more like Amazon S3 storage and less like file storage in that it provides the storage foundation for other services like compute, backup, or database. If you are looking for file storage you need to look Document Cloud Storage Services which is more tied to processes and less tied to raw cloud storage.
In this blog we will look at different ways of attaching to block storage in the cloud and look at the different ways of creating and consuming services. To start off with, there are two ways to consume storage in the Oracle Cloud, metered and un-metered. Metered is charged on a per-hourly/monthly basis and you pay for what you consume. If you plan on starting with 1 TB and growing to 120 TB over a 12 month period, you will pay on average for 60 TB over the year. If you consume this same service as an un-metered service you will pay for 120 TB of storage for 12 months since you eventually cross the 1 TB boundary some time over the year. With the metered services you also pay for the data that you pull back across the internet to your computer or data center but not the initial load of data to the Oracle Cloud. This differs from Amazon and other cloud services that charge both for upload and download of data. If you consume the resources in the Oracle Cloud by other cloud services like compute or database in the same data center, there is no charge for reading the data from the cloud storage. For example, if I use a backup software package to copy operating system or database backups to the Oracle Cloud Storage and restore these services into compute servers in the Oracle Cloud, there is no charge for restoring the data to the compute or database servers.

To calculate the cost of cloud storage from Oracle, look at the pricing information on the cloud web page. for metered pricing and for un-metered pricing.

If we do a quick calculation of the pricing for our example previously where we start with 1 TB and grow to 120 TB over a year we can see the price difference between the two solutions but also note how much reading back will eventually cost. This is something that Amazon hides when you purchase their services because you get charged for the upload and the download. for un-metered pricing and for metered pricing.
Looking at this example we see that 120 TB of storage
will cost us $43K per year with un-metered services but $36K per year for
metered services assuming a 20% reading of the data once it is uploaded.
If the read back number doubles, so does the cost and the price jumps to
$50K. If we compare this cost to a $3K-$4K/TB cost of on-site storage, we
are looking at $360K-$480K plus $40K-$50K in annual maintenance. It turns
out it is significantly cheaper to grow storage into the cloud rather than
purchasing a rack of disks and running them in your own data center.

The second way to consume storage cloud services is by using tape in the
cloud rather than spinning disk in the cloud. Spinning disk on average
costs $30/TB/month whereas tape averages $1/TB/month. Tape is not offered
in an un-metered service so you do need to look at how much you read back
because there is a charge of $5/TB to read the data back. This compares to
$7/TB/month with Amazon plus the $5/TB upload and download charges.