Conditional Column Formatting in APEX

Tyler Muth's Blog

I wanted to share a little trick I’ve used in APEX for a while now to conditionally format report columns based on their values. I’m sure there are plenty of alternatives to this trick, including the 4 built-in conditional alternatives for a row when using a named column template.

At a high level, this technique uses the following components:

  1. A hidden column in the query that returns the formatting attributes for a column. I’m going to return the color or padding-left in my examples later in this post.
  2. Edit the Report Attributes > Column Attributes > Column Formatting > HTML Expression of a visible column and use the hidden column to change it’s formatting. This is the same section you apply a date or number format.

Example 1 – Color Code Salary


Report Attributes > Column Attributes for “SAL” > Column Formatting > HTML Expression


Example 2 –…

View original post 17 more words


Sending Mail with Multiple Attachment In PL/Sql

Aykut Akın's Blog

Oracle provides you two different packages for sending mail. Utl_Smtp package firstly introduced in Oracle 8i and Utl_Mail package firstly introduced in Oracle 10g. In my post, I will give example with Utl_Smtp package.

First of all you need to understand ‘MIME Type’ concept for my example. I will explain it briefly, but you can find more detail in here. We will use MIME standart for creating multiple part e-mail.

We will use common header MIME informations like To, Date, From etc. to standartize our email content. Boundry feature will be used for creating multi attachment mail. Every attachment will be divided by a boundry and every attachment will have its own content type and name. With content type parameter, you can specify text/plain, text/html etc. types. If your content type is set to text/html, you can use html tags like table, tr, br etc. and with this way…

View original post 547 more words

/opt/freeware/lib/libintl.a( Error while installing RPM on AIX 7.1

While installing some RPM’s on AIX we got below dependency error.  Now without resolving this error you cannot even proceed further.

[octst3:root:/aixbackup:]rpm -ivh rsync-3.1.2-2.aix6.1.ppc.rpm
exec(): 0509-036 Cannot load program /usr/opt/freeware/bin/rpm because of the following errors:
0509-150 Dependent module /opt/freeware/lib/libintl.a( could not be loaded.
0509-152 Member is not found in archive

After scourging through the internet and spending half the day, our system admin almost gave up on it. And in come’s the DBA, as they say smart work is better than hard work. Instead of re-inventing the wheel i asked him for a server where the RPM installation works. I logged into the server, copied the file to the same path in the server where it isn’t installing and it worked like a charm. Now i cannot guarantee this method will work 100% with all enviroments but just a refresh of the library from another source worked for us.

[ocptst3:root:/aixbackup:]rpm -vh rsync-3.1.2-2.aix6.1.ppc.rpm
rpm: –hash (-h) may only be specified during package installation and erasure
[ocprd3:root:/aixbackup:]rpm -ivh rsync-3.1.2-2.aix6.1.ppc.rpm
Preparing… ################################# [100%]
Updating / installing…
1:rsync-3.1.2-2 ################################# [100%]

I’m attaching the library for the purpose of any one who wants to do this on AIX 7.1 on Power7 CPU architecture.


Using Ansible for executing Oracle DBA tasks.

Frits Hoogland Weblog

This post looks like I am jumping on the bandwagon of IT orchestration like a lot of people are doing. Maybe I should say ‘except for (die hard) Oracle DBA’s’. Or maybe not, it up to you to decide.

Most people who are interested in IT in general will have noticed IT orchestration has gotten attention, especially in the form of Puppet and/or Chef. I _think_ IT orchestration has gotten important with the rise of “web scale” (scaling up and down applications by adding virtual machines to horizontal scale resource intensive tasks), in order to provision/configure the newly added machines without manual intervention, and people start picking it up now to use it for more tasks than provisioning of virtual machines for web applications.

I am surprised by that. I am not surprised that people want boring tasks like making settings in configuration files and restarting daemons, installing software…

View original post 2,564 more words

Read XLXS Files from APEX 5.0 and Higher – One (or Two) Ways


In my work as a consultant I often have the task of getting data from MS Excel spreadsheets into tables in the database.

If this is a one-time load, the process is simple – use either SQL Developer or, easier yet, use the Data Load Workshop in APEX:  SQL Workshop –> Utilities –> Data Workshop.

If the requirement is for a repeated load or many files, I need to set up some automated process for reading in the data.  I do not want to ask my users to open the XLSX files, save the data to CSV, then upload the CSV files.  That makes for unhappy users, at best.

I am sure there are several options for load XSLX files into Oracle data tables.  I am going to focus on a two I have used (and continue to use) successfully across several clients, across many projects.  And another option that…

View original post 1,036 more words

READ_XLSX_for CLOBs ~ Extending Anton’s AS_READ_XLSX for CLOBs


I had a need to read a series of MS Excel XLSX files from a file server over a period of time – usually chunks of 20-50 files, several times a year. That’s too many files to make an upload interface practical (without annoying the users), and the files come as is from an external agency so there is no control over the contents. That is, this is NOT a case of “save to CSV format” – the requirement is to read MS Excel .XLSX format files as they come.

I was happy to use Anton Scheffer’s AS_READ_XLSX code .. but wary of the VARCHAR2(4000) column limit. Sure enough, a few files in, I hit that limit.

What to do? Modify – adjust – Anton’s code to work with CLOBS, of course. The CLOB-ready package is here, PACKAGE_READ_XLSX_CLOB.

So now I have an AS_READ_XLSX_CLOB package that will read the…

View original post 338 more words

Killing Sessions

Neil Chandler's DB Blog

You have a session which is out of control. You decide to kill it. What options are available to you as a DBA?

Assuming you don’t have access to the O/S to be able to use O/S commands to kill the session (using kill -9 in Unix/Linux or orakill in windows to kill the thread within the process – “orakill sid thread”), you probably turn to:

alter system kill session 'sid, serial#, @inst_id' immediate;

(note the undocumented @inst_id, which can be used to identify specific RAC instances, rather than the instance you are connected to) Note that immediate does not change the processing, but just returns control to the command line immediately.

You can generate this command using

select 'alter system kill session ''' || sid || ', ' || serial# || ', @' || inst_id || ''';' from gv$session;

However, despite the name, this does not actually kill the…

View original post 266 more words