Get Image attributes from BLOB

Q: How can I obtain image attributes such as height, width, format from BLOB column?
A: Using Oracle Multimedia ORDImage object type it is possible to get such attributes.

Consider the following example:
DECLARE
  lv_blob                  BLOB;
  unused_attributes        CLOB;
  img_mimetype             VARCHAR2(32);
  img_width                INTEGER;
  img_height               INTEGER;
  img_contentlength        INTEGER;
  unused_fileformat        VARCHAR2(32);
  unused_contentformat     VARCHAR2(32);
  unused_compressionformat VARCHAR2(32);
BEGIN
  SELECT blob_content
  INTO lv_blob
  FROM mytable;
  ordsys.ordimage.getproperties(lv_blob,
                                unused_attributes,
                                img_mimetype,
                                img_width,
                                img_height,
                                unused_fileformat,
                                unused_compressionformat,
                                unused_contentformat,
                                img_contentlength);
  dbms_output.put_line('Width=' || img_width || ' Height=' || img_height);
END;

You may encounter exceptions while calling this procedure if the image format is not proper. So always wrap this procedure call within an EXCEPTION block and determine the error.

For a list of exceptions which you can handle, check out the documentation.

Learning Git

Now-a-days if you search for any source code, this word is always there. "GIT". What is so special about it?

Well check out the Wikipedia page to know about its history. I would like to borrow one sentence from Wikipedia though:

Git was initially designed and developed by Linus Torvalds for Linux kernel development in 2005

The light bulbs glowed for the first time. I always fancied Linux and its kernel development, though I knew nothing about it. But this was the first time something near to the Linux kernel was available to me, and which could help me manage my source code. Although the learning was mandated for official reasons, I now fancy Git in "all and everything" which I code.

Having said that what is the starting point? I implemented Git in my local development first. My aim was to have Git take backup of everything in my development folder. The starting point was this Git book.

I summarize the tasks I was upto:

1. Create a fresh project folder
2. Copy all my required and necessary folders and files
3. Back them up to a shared folder (server)
4. Work independently of my local machine

Save with PacktPub

Packt Publishing is celebrating 10 years of its presence by a unique promotional way. 

To celebrate this huge milestone, from June 26th Packt is offering all of its eBooks and Videos at just $10 each for 10 days – this promotion covers every title and customers can stock up on as many copies as they like until July 5th. #Packt10Dollar

Hurry up, and buy your stocks before the offer ends, click here: http://bit.ly/1rhKWwS

ORA-00900: invalid SQL statement tips

I was getting this curious error lately in a development package. I could not resolve what could be the reason for this for sometime.

ORA-00900: invalid SQL statement

I got some head on from this website, but it did not help my case as both of the cases mentioned by him was not valid for me. I was not using any dblink or neither because of Y2K error.

I started to investigate upon it and with the help of my colleague, I spotted the error. I was using EXECUTE IMMEDIATE, which was not tested had a code oversight.

I was executing a procedure within EXECUTE IMMEDIATE but without an anonymous block.

EXECUTE IMMEDIATE 'package.procedure';

The above was my statement, and with the oversight it took about 30 minutes of frustration to fix this error:

EXECUTE IMMEDIATE 'begin package.procdure; end;';

The above code executed properly. The oversight I had was to miss the semi-colon which is not required when executing a SQL statement.

Thanks to +Ajish Kumar who helped me in resolving the situation.

Custom CSV Download button for Interactive Report

Its a fact, users are lazy. They want to avoid multiple clicks of button. This time it is to avoid clicking on Actions Menu > Download > Click to download CSV. The requirement was to click one button, download the CSV.

Oracle Apex allows this using a simple technique. I was searching many blogs, OTN etc where people were asking you to create a new region, then change the settings of the region. Why should I manage two copies of same region?

The solution is simple. While creating a button, specify the option "Page in this application". The page number should be same as the one where the Interactive report is created. (Probably this is one of the reasons why there is a limit of 1 Interactive report in a page). In the Request text field, mention the "CSV" option. That is all you need.

Create a button with the Action when button clicked property as above

Interactive Reports - Configure header and footers

Version: Oracle Application Express 4 and above.

Recently I had been asked by my colleague to how to configure header and footer for PDF printing of Interactive reports in Oracle Apex. We have been using Apex 4.2.5, but this tip will work for versions above 4.

If PDF download is not working in interactive reports in your setup, refer this article to configure PDF printing.

By default if you are downloading an interactive report in Oracle Apex, it will only download the data inside the report. It will not print anything which is available in Region header/footer. It is available as a separate option which will help configure attributes while printing.

Navigate to your page in developer view, and click on Interactive Report link for the region.

Click the Interactive Report link

Click on the tab Print Attributes.

Click the tab, Print Attributes

In this section you will be able to add/modify the following settings:

• Print Server Override: If you have created a custom report with XSL-FO templates, and you need to execute this report, the configuration has to be done here.
• Paper Size, Orientation of the report
• Font settings for printing the headings, data in the report
• Page Header & Footer

Print Attributes settings screen

Please note that this section "Print Attributes" will only be used while downloading the report in PDF, and not in CSV/HTML download in Interactive Reports.

For more information refer Oracle Application Express Builder's User Guide.

International day against DRM

Digital Rights Management (DRM) is a technique used by manufacturers or publishers to control use of their products after sale. If you buy a book, you are not allowed to copy the contents (even take a copy of a page you feel is important). Today on 6th May lot of organizations are raising a voice against the DRM.

As individuals it is our aim to support this voice, as we will be benefited. Raise your voice, support the supporters.

https://www.defectivebydesign.org/node/2312

Packt Publishing is also celebrating this day by its offers. They are celebrating International Day against DRM on 6th May 2014 by offering all its DRM-free content at $10 for 24 hours only on May 6th – that’s all 2000+ eBooks and Videos at www.packtpub.com.

Book Review: Getting Started with Oracle VM VirtualBox

Getting Started with
Oracle VM VirtualBox

This book gives a general introduction to Oracle VM VirtualBox software, which is used in virtualization. Using this software you will be able to run operating systems which are different from the one installed in your hard disk. It can be installed on your Mac, Linux, or even Windows machines. It adds the capability to run multiple operating system as a virtual machine. For example once you have logged into your Linux/Ubuntu machine, you can start a Virtual machine for Windows.

This book covers a great deal about how to start using with VirtualBox, which is an open source software licensed under GPL (GNU General Public License) version 2. Learn more about this software from its website.

The book also covers various details like installation (which is anyway not a daunting task you might think, but there are people who might need this) in various operating systems. It then talks about how to create virtual machines, cloning, restoring in a step by step fashion. You will never get lost in this way.

The book then talks about installing Oracle Enterprise Linux (which is Oracle's version of Linux) on VirtualBox, and then about various networking and storage options available in VirtualBox and how they work. If you are an application developer there is nothing to get excited about, but you can know what are the different options available. If you are a networking person/DBA who is looking to tune the availability of your virtual machines this is for you.

Overall its a decent book on VirtualBox, and can be used as a quick reference guide either while setting up for the first time or when you are stuck with "How to do this", or "What is wrong with my set up" kind of situation.

You can buy this book from various on-line stores, the publishers often reward your purchases with discounts. You can buy the e-book format or physical copy of the book from Packt Publishing website.

Oracle Query to get table size across schemas

I googled for this and got this from some website. Do not remember the website from where I got this query. Thanks for the original author. Pasting the query for my personal records:

SELECT owner,
       table_name,
       trunc(SUM(bytes) / 1024 / 1024) meg
FROM   (SELECT segment_name table_name,
               owner,
               bytes
        FROM   dba_segments
        WHERE  segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name,
               i.owner,
               s.bytes
        FROM   dba_indexes  i,
               dba_segments s
        WHERE  s.segment_name = i.index_name
        AND    s.owner = i.owner
        AND    s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.segment_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name,
               l.owner,
               s.bytes
        FROM   dba_lobs     l,
               dba_segments s
        WHERE  s.segment_name = l.index_name
        AND    s.owner = l.owner
        AND    s.segment_type = 'LOBINDEX')
WHERE  owner NOT IN ('SYS', 'SYSTEM')
GROUP  BY table_name,
          owner
HAVING SUM(bytes) / 1024 / 1024 > 30 /* Ignore tables lower than 30 MB */
ORDER  BY SUM(bytes) DESC

jQuery tabs within Oracle APEX

This tip was posted in Oracle Apex Knowledge Group by Richard Martens. Its my sincere pleasure in thanking the author for this simple tip.

The original blog is available here. I am posting the steps first, and a slight deviation which makes the setup one time only through this article.

The idea is to create a region first and all sub-regions as tabs. If this design suits your application then its simple as copy-paste.Create a template

1. Go to Shared Components 
2. Navigate to Templates
3. Press the Create Button
4. Choose Region
5. Choose From Scratch
6. Provide the name jQuery Tabs
7. Choose Custom 1 for template class
8. Press Create button
9. Once the template is created, find the jQuery tabs from the list and click to edit.
10. Paste both the following codes in Definition Section:

1. <div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>
   #BODY##SUB_REGION_HEADERS##SUB_REGIONS#
   <div style="clear:both;"></div>
   </div>
   <link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.tabs.css" type="text/css" />
   <script src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.tabs.min.js" type="text/javascript"></script>

2. <script type="text/javascript">
    apex.jQuery(function() {
    apex.jQuery("##REGION_STATIC_ID#").tabs();
    });
   </script>

11. Paste the following in Sub Regions > Header Template

1. <ul style="height: auto;">#ENTRIES#</ul>

12. Paste the following in Sub Regions > Header Entry Template

1. <li><a style="overflow:auto;" href="##REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION_TITLE#</a></li>

13. Paste the following in Sub Regions > Template

1. <div id="#REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION#</div>

The template is now ready. Now you are ready to create regions in your page. While creating the Parent region, choose the template as jQuery Tabs.

If you are wondering what is the improvisation I have done, its present in Step 10.2

In Richard Martens blog the step 10.2 is mentioned to be done for every page. I have modified it to be included in the page template itself. A very small and negligible improvisation. 

Oracle APEX - Remove Show All from region selector

Oracle Application Express (APEX) offers a lot of regions to be displayed in a single page without cluttering the screen display. One such way of making a page to display lot of contents is using the Region Display Selector.

In this tip however I am not going to show how to display the "Region Display Selector" but an inherent problem with this control.

I have a screenshot of this control in action. I have Five regions in this sample page.

For each region this "Display Selector creates a link in its control. As soon as you click a particular region, only that specific region will be displayed. The remaining will be hidden.

The "drawback" is that there is no option to hide the first option "Show All". Initially when the page loads the "Show All" will be selected and all regions will be displayed.

I think this should be a great feature which should be available in-built within Apex. But as of 4.2.3 version which I am using, this feature is not available. But its not a big deal to make it hidden. As mentioned in this blog, it only requires 3 lines of jQuery code to be added. I am making this post as a reference to myself based on the blog entry mentioned.

Do the following to make this change in your page:
Step 1: Edit Page and create a Dynamic Action

Step 2: Select the Page Load event, and Execute Javascript code while creating the dynamic action.

The magical 3 line of jQuery Code is below:

$('.apex-rds li:first-child').remove();

$('.apex-rds li:first-child').addClass('apex-rds-first');

$('.apex-rds li:first-child > a').trigger('click');

That is all required to be done for making the change. The Show All will be hidden and only the first region will be made visible.

Thanks to Nick Buytaert for this tweak.