Are you Oracle database 12c ready?

I had recently attended OTN Database 12c Developer Day, at Bangalore. I must say it was very informative and gave me new insights on the new database unveiled recently by Oracle Corporation.

Rather than focussing more about the event, I will focus what was being the agenda or what what I learned from this event.

I must admit I am getting old because this cloud business is too hard to understand. You will agree with me if you see advertisements of cloud-ready, but are not able to understand anything beneath it. This event gave me the initial thoughts.

Before 12c, Oracle databases supported multi-tenancy through VPD or FGAC (whatever you prefer to call it). To use this feature, your tables needed to have extra columns to identify a tenant when he is logging in to the database. So with a single database, you could (a big COULD) support multiple clients. But it was used not for this purpose (maybe oracle knew this before). Application developers took this feature to implement user security instead. Within a client if there are multiple units of business, you could hide the irrelevant information from being shown.

New Multi tenant architecture

Starting with 12c, your database is really multi-tenant ready. The happy news for application developers is that your code can be directly used AS IT IS. C in 12c stands for cloud ready. Oracle introduces concepts of Container Database and Pluggable Database.

What is shared, and what is not

When you install a database normally, whatever binary and data dictionary comes with it is now called Container database. You as application developer is not supposed to touch it. This isolation of oracle structures from user structures gives 12c database the extra mileage to support multi-tenancy.

This magic is called Pluggable databases. As soon as you install a container database, a pluggable database is also installed by default. This is called a seed database from where you can create multiple copies (tenant specific). There are new features which allows creating a database from existing pluggable databases or completely new.

As an application developer/product company you could install your application in one pluggable database. We will call this BASELINE for clarity of discussion. Once this baseline is installed you can keep it as a read only one. Whenever you need a database for a client, you simply clone the baseline and your new database for client 1 is ready. All it takes is seconds/minutes depending your dbf files size. 

If you have trouble in understanding the concept of Container and Pluggable databases, consider this example. Visualize container database as a PC. Pluggable databases are like users logging into the same system. They share a lot of common things like operating system binaries, programs installed etc. This is just and idea and not exactly the same, so bear with me for this terrible comparison.

Whenever you require upgradation/patch fix for oracle database, it is applied only in container database and automatically all pluggable databases are upgraded or patched. If some clients are adamant in not applying any patches, then move them to a different container database. (Yes this is possible !) 

It is easier to clone and plug/unplug/drop pluggable databases, new commands have been introduced. The container database is only one copy of oracle binaries which need not be duplicated in a server intended to run multiple databases for separate clients. Only the client specific copy of application logic is to be duplicated and kept separate. Thus minimizing memory and space constraints.

One important thing to remember is that to connect to pluggable databases, service_name only can be used and not SID in tnsnames.ora [This is my initial impression, need to dig deeper to find the reason]

You can create a maximum of 252 pluggable databases in one container database.

Don't think DBAs are left with no job now. They can concentrate more on database performance instead of installing new databases for new clients. (This was a boring job anyways.)

Apart from this there are new features added for application developers. You can call PL/SQL code within WITH statement in SQL itself. You can even create functions/procedures within WITH clause and execute a SQL statement. The use of it will be when in an environment if you don't have access to create procedures you can execute such statements. Not sure when the real usability will come.

All other features (519 in total), I think are just minor ones. The VARCHAR2 support of 32767 in tables is a very minor one. It treats data as CLOB internally, but is intended to be used for migrations from other databases like SQL server/MySQL.

The INTERNAL key feature may be useful one, but only for new developments. After all who will change their code to incorporate new features like this.

One more feature is READACTION. This is developed by oracle similar to what FGAC is. FGAC works on row filtering, whereas readaction works on columns. For example if we want to mask some sensitive columns based on application logic redaction can come in handy. This is additional cost option over database, and can be installed even in Oracle 11g db.

Also the EM console is called Enterprise Manager Database Express, which helps in real time monitoring of databases. 

I will share more information as and when I could write something. Till the time, happy exploring.

PDF Download not working in Interactive Report : Oracle APEX: SOLVED Finally

This was one of my biggest concerns while using Oracle APEX. I will often enable PDF download from Interactive Report setup in a page, but the download was generating only a corrupt file. My google searches also did not yield any result.

Then came the news that I have to install BI Publisher in order to use the feature. This message was floating in OTN forums related to this topic. I got disheartened learning this.

Why would Oracle provide such a tool if it can't support PDF download by default.

Then came the SOLUTION for the problem. In one of documentation available in Oracle's website I found the news I have been searching for a long time.

The APEX download (4.2.2) had been shipped with a war file fop.war. This file is available in the folder apex_4.2.2\apex\utilities\fop

The installation instructions mentioned that I need to deploy this war file in OC4J but which requires  Oracle Containers for J2EE (10.1.3.2 or above) [I don't have a clue what this is]. My DBA advised me that our version of Oracle Containers for J2EE was 10.1.2.3 (oh not again).

I forced my DBA to deploy this war file in our development environment, and I had to do a simple settings in Admin Console of Oracle Apex. Voila the PDF download is now working.

I am summarizing the steps for noble souls who is faced with this situation:

1. Deploy fop.war in your Apache/Application Server environment using your EM Console.
• Login to EM console
• Click on Home tab and navigate to Applications tab
• Click on Deploy War file tab
• Click Browse button and provide the path for fop.war file
• For Application Name pass value  “fop”
• For Map to URL pass value “/fop”
• Click on Deploy button.
2. Login to Apex Admin Console
3. Navigate to Manage Instance, Instance Settings.
4. Click Report Printing
5. Specify the following:
1. Report Server: Oracle BI Publisher
2. Print Server Protocol: HTTP/HTTPS (based on your setup)
3. Print Server Host Address: Ip address of your application server
4. Print Server Port: Port of Application server (forms) -DO NOT USE APEX's PORT if this is different from Oracle Forms/Reports if installed.
5. Print Server Script: /fop/apex_fop.jsp

More information can be found in this document which saved my situation.

Reference: PDF Printing in Application Express using FOP

Oracle Apex - Position IR Control Panel items in one line

This mini tip is to help those who does not know CSS much and those who know CSS but do not know where to apply code in Oracle Apex.

In Interactive reports by default the control panel items (filters/report titles) etc will be displayed one line below the other. If you have ten filters applied on an IR/Interactive Report then the panel when expanded occupies 10 lines in the report just like the one below:

This mini tip will enable you to change this like the one below:

If you have a Page 0/Global Page created within your application do not follow step 1.

Step 1: Create a Global Page/Page 0 in your application.
Step 2: Create a HTML region with template "No template" in this page.
Step 3: Add the following CSS inside this region source and save.

<style>
  table#apexir_CONTROL_PANEL_COMPLETE tr{float:left;padding-right:15px;}
</style>

This is all you have to do to change Interactive report's control panels visibility option in your entire application. The magic is not done by the little piece of code you have added now, but the way in which Global Page/Page 0 acts within an application.

All piece of code inside this special page will be executed whenever a page within the application is invoked. Kudos to the Oracle Apex designers who have made this available to us.

The second part of magic is the little piece of code you have added. Inside Oracle Apex, you can have only 1 interactive report in a page. All these reports control panels will be created with a table with id apexir_CONTROL_PANEL_COMPLETE. By specifying that rows are displayed in a single line you are utilizing the space available to the users.

Oracle APEX: Interactive Report "no data found" while adding attachments link

You must follow the exact steps to add link to attachments in BLOB column in Interactive Report/IR in Oracle APEX:

1. Table must have primary key (I could not make this work with ROWID in View), and it must be queried in the select statement for IR.
2. In query one column must fetch size of attachment. You may use dbms_lob.getlength(blobcolumn) function to fetch the size.
3. In the size column added provide format mast as BLOB and in Apex 4.2.2 "BLOB Column Attributes" section will come.
4. In "BLOB Column Attributes" you must specify at least two columns, BLOB column in "Column" and "Primary Key column 1". If you have composite primary key, you may specify "Primary Key column 2".
5. If you are saving the file name in table, you may also identify the column name in "Filename Column" field.

If you are getting "no data found" error in the region of Interactive report, do make sure that the first point mentioned above is adhered to. It is a must. Just learned this today, sharing with you all.

Sample Application - Table Tennis - Oracle Apex

For educational purposes I had created a sample application. This application is intended to be used within an organization where a table tennis tournament is taking place.

The requirements for the application were:

1. There should be provision to create Team and Players (Only doubles tournament was conducted).
2. Team & Players should be entered in Master-Detail format.
3. Matches & Results data should be entered as a tabular format.
4. Match Calendar should be shown.
5. Reports like:
1. Points tally
2. Top 4 teams (who will qualify for semi-finals)

The completed application can be downloaded from this link. The E-R diagram for the application (generated out from SQL Developer can be found below).

To see more information on generating E-R diagram from SQL developer click here.

Creating E-R Diagram from SQL Developer

This is one of the most coolest feature I like about SQL Developer. I am using version 3.1.07 and this feature is available in this version.

SQL Developer requires just the DDL scripts that I use for installation for the product and it generates the E-R diagram within seconds.

Where is my Page Zero? - Oracle Apex

Till Apex 4.1 while creating a page you had an option for creating "Page Zero". This page type is a special master page to your application. If you add any element to this page, automatically the element will be displayed in all pages within your application.

LOVs based on fields in page - Oracle Apex

I was reading about features in Oracle APEX 4.1 in Patrick Wolf's Blog, and I wanted to try it out to see how easy or difficult it is to implement a Select list which dynamically changes based on a different value in the page.

I created a sample application with three tables:

1. Country_Master - Stores country id and name
2. State_Master - Stores country id, state id, and name
3. Company_Master - references country id and state id to store company record.

My intention was to implement country select list and state select list in company master page. As soon as I select a country states belonging to the country should be refreshed in state select list.

The APEX feature used: "Cascading LOV Parent Item(s)"

Oracle APEX Tutorial - 1

Oracle's APEX is a RAD (rapid application development) tool which requires only a web browser like Google Chrome/Mozilla Firefox to develop and deliver "New Age" web based applications.

Oracle allows a cloud based website apex.oracle.com to help ourselves familiarise this tool. As of this tutorial the version of APEX in the cloud is 4.2.2. In this cloud website one can apply for a free account.

I am going to publish a series of videos helping you to get started with Oracle Apex. In this first of series I will be creating a free account with the cloud based website. Let's see how you can create your account:

A simple introduction to Oracle APEX can be seen here.

E-Book: Cloud Architecture for Dummies & Cloud Considerations & Connectivity

Download your free copy of E-Book Cloud Architecture for Dummies & Cloud Considerations & Connectivity brought to you by Oracle.

Click here to download your copy.

This is an Oracle Special edition. Grab your copy before the offer is taken out.

Oracle APEX - ORA-44004: invalid qualified SQL name

I was creating a custom function (for demo purposes) and was stumbled with this error.

ORA-44004: invalid qualified SQL name

I was referring to my code in APEX 4.0 which was calling the procedure as return f_validate; I was trying to do the same in APEX 4.2.1 also. But I was continuously bugged by this or some other error.

ORA-31011: XML parsing failed while calling srw.run_report

ORA-31011: XML parsing failed while calling srw.run_report from database

Below is a sample report calling block I copied from Oracle Forums. There was a mistake in the block which was causing the error ORA-31011: XML parsing failed. The solution is simple. the GATEWAY parameter had a 'question mark' at the end of the link.

If you see the output generated by srw.start_debugging, the URL framing would be listed there:
http://192.168.50.53:7778/reports/rwservlet?&SERVER=rep_dev&report=rep_name&userid=user/pass@sid&destype=file&desformat=pdf&destination=/u01/&distribute=yes

If you notice closely there is a & added before SERVER parameter. This is due to the presence of the ? SRW.ADD_PARAMETER of GATEWAY.

If you remove the ? from the last of link the report will be invoked.

DECLARE
v_paramlist srw_paramlist;
v_jobident srw.job_ident;
v_status srw.status_record; 
BEGIN
srw.start_debugging;
v_paramlist := srw_paramlist(srw_parameter('', ''));
srw.add_parameter(v_paramlist, 'GATEWAY', 'http://192.168.50.53:7778/reports/rwservlet?');
srw.add_parameter(v_paramlist, 'SERVER', 'rep_dev');
srw.add_parameter(v_paramlist, 'REPORT', 'rep_name'); --path to where report is
srw.add_parameter(v_paramlist, 'USERID', 'user/pass@sid');
srw.add_parameter(v_paramlist, 'DESTYPE', 'FILE');
srw.add_parameter(v_paramlist, 'DESFORMAT', 'PDF');
--srw.add_parameter(v_paramlist, 'DESTINATION', '/u01/');--path to where xml file
srw.add_parameter(v_paramlist, 'DESTINATION', '/u01/distribution.xml');--path to where xml file
srw.add_parameter(v_paramlist, 'DISTRIBUTE' , 'YES');
v_jobident := srw.run_report(v_paramlist);
v_status := srw.report_status(v_jobident);
srw.stop_debugging;
END;

Uploading documents - table name configuration

Oracle's Web Toolkit (HTP/HTF) supports creating webpages with full control. I have been using them for my official project and we have built a framework associated with a style sheet we have created.

Among other things it supports uploading files by user. Using the HTML INPUT type="file" we can position the file selection dialog.

The form needs to be open with multipart/form-data.

For the first time if you are using this functionality you may have to configure the table name where Oracle populates the file in a BLOB column. The configuration is part of DAD, placed in Database server within /dbfolder/Apache/modplsql/conf/dads.conf file. "dbfolder" is where you have installed the database.

In the Location tag specify a table for the attribute
     PlsqlDocumentTablename MYDOCUMENTS

A sample document table can consist of the following:

CREATE TABLE [table_name] (
  NAME VARCHAR2(256) UNIQUE NOT NULL,
  MIME_TYPE VARCHAR2(128),
  DOC_SIZE NUMBER,
  DAD_CHARSET VARCHAR2(128),
  LAST_UPDATED DATE,
  CONTENT_TYPE VARCHAR2(128),
  [content_column_name] [content_column_type]
  [ , [content_column_name] [content_column_type]]
);

Oracle then automatically uploads your document to this table.

Check mod_plsql User's Guide for more information on this. This is a mini-tip which I want to share for those who may be looking for the information.