UTC timestamps for Salesforce from Oracle

Database Management and Performance
| May 2, 2013
I came across the requirement the other day to update Salesforce every 5 minutes with data from Oracle. 

 The data in Oracle was a simple table with few columns A,B,C  and a timestamp column T indicating the last modified date/time of the record. 

To my surprise whenever I (more...)

SQL Access to Salesforce data

Database Management and Performance
| Feb 14, 2013
In this post I will talk about an ODBC/JDBC driver solution I discovered lately which enables you to Access your Salesforce data using the standard SQL query language.

The company which provides these drivers is called Progress|DataDirect 

Their JDBC/ODBC Salesforce Connect XE drivers, acts as translators between SQL and SOQL (The Salesforce proprietary query language). So you can write your joins, use expressions like SUBSTRING on your Salesforce data as if the data was in a relational database.

I found the concept quite interesting.  If you already know SQL -and there are many people which do- you can just (more...)

Send tweets from Oracle with OAuth

Database Management and Performance
| Feb 7, 2013
Twitter is a great real time social platform. Timelines and hashtags are a great way to communicate to an audience of subscribers relevant information. 

There are lots of websites and applications which use twitter to provide content to their users. There are programming languages, Java, PHP, Python which have build Twitter API libraries to quickly send and receive content from Twitter but I haven't come accross a fully fledged library in PL/SQL, which would enable you to send a Tweet from PL/SQL using Twitter's latest oAuth security protocol. 

There are blogs out there which show you how to (more...)

Is DELETE necessary?

Database Management and Performance
| Jan 26, 2013
A  delete is a very destructive operation for a database. An operation which requires a lot of  thinking and preparation and most of the time is irrevocable. You can get in a lot of trouble if you delete the wrong set of rows and lose all that very important data!

Maybe the DELETE command should be banned from SQL. It was probably invented when the disks were tiny and it might not be suitable for our times, especially nowadays when we talk about things like BIG DATA.

Why do we have to delete anyway? We should invalidate and age out (more...)

Salesforce basic data model

Database Management and Performance
| Jan 6, 2013
All applications store data. Data is stored in database tables - or objects in case of Salesforce.  In this post I will try to describe in simple terms how to read and Entity Relationship model or sometimes called a Data Model. I give reference to further reading at the end of the post.

Data is related with other data based on relations and associations. For example, as in Salesfoce's data model One Account can have Many Contacts. In the model these relationships/associations are indicated by drawing lines from one object to the others as seen in the diagram below, where (more...)

Oracle Apex 4.2 JQM Smartphone UI

Database Management and Performance
| Nov 10, 2012
Put a bit of a swing in your Oracle Apex mobile apps by using the JQM smartphone UI application interface now available in Oracle Apex 4.2.

The interface enables you to put page transitions to your apps, define JQuery lists and much more. Here is the option:



I have been playing around creating mobile apps in Oracle Apex for a while now trying various ways such as adding Javascript to HTML content regions, running javascript from PL/SQL, you name it. The JQM smartphone UI is a great improvement. See for yourself. Below is the JQM smartphone version of my (more...)

Mobile apps with Oracle Apex

Database Management and Performance
| Aug 11, 2012
I have been toying with the idea of using both Oracle Apex with JQuery Mobile as an application development platform for a while now. Why not? You get a great database and a Mobile UI design framework to built your mobile apps just using your browser!

I managed to create few apps on this platform. This post is about what tools I used when I was developing the apps.

Overview of using Oracle Apex and JQuery Mobile

This one was easy, just go to http://apex.oracle.com and register for a workspace. The workspace gives you an Oracle Workspace Schema, (more...)

Big Data

Database Management and Performance
| Mar 4, 2012

The other day I watched the Oracle Big Data forum. Now available here. A half-day event with various speakers on the subject of BigData, including Tom Kyte , a mentor who I admire!

In the forum they have gone over Oracle's approach to Big Data and allow me to summarise it below:
  1. Acquire - Collect Big Data, identify it, where is it? Then store it in Oracle NoSQL - a value-pair database

  2. Organise - Stage Big Data in a transient elastic database. Using Oracle Data Integrator and the Oracle Hadoop connector, reduce and distil it.

  3. Analyse - Start Analytics on (more...)

All Watched Over by Machines of Loving Grace

Database Management and Performance
| Feb 27, 2012
All Watched Over by Machines of Loving Grace


I like to think (and
the sooner the better!)
of a cybernetic meadow
where mammals and computers
live together in mutually
programming harmony
like pure water
touching clear sky.

I like to think
(right now please!)
of a cybernetic forest
filled with pines and electronics
where deer stroll peacefully
past computers
as if they were flowers
with spinning blossoms.

I like to think
(it has to be!)
of a cybernetic ecology
where we are free of our labors
and joined back to nature,
returned to our mammal
brothers and sisters,
and all watched over
by machines of loving grace.



By Richard Brautigan





Oracle Apex Mobile App

Database Management and Performance
| Feb 2, 2012
I have built this Mobile Oracle Apex app on a hosting provider showing temperatures on Greek islands. I used jQuery libraries and Oracle Apex Templates. This is a learning prototype.

Features include:

1.Hourly calls to Yahoo Weather API
2.Static Google map API call to show the island on the map
3.More than 1 year daily temperature records to compare with current temperature, per island o. Google charts API
4.Learn the locations of 100+ Greek islands on the map

I am thinking of enriching the database behind this app with other relevant info.

To reach the app (more...)

Salesforce to Oracle Real Time Integration

Database Management and Performance
| Dec 16, 2011
In this blog post I will show you how to make Web Service like calls from Salesforce to Oracle, using Informatica Cloud Endpoint URL in real time.

In Winter 12 release of Informatica Cloud we will have the ability to make Salesforce outbound message calls to an Informatica Cloud task and enable real-time integration.

The idea is simple

Use Salesforce Workflow Rule (a database trigger) and make an outbound message call whenever a new Account Record is edited or created.

Step by step instructions

1) You will create a vanilla Data Synchronisation task, I called mine 'test ws', (more...)

Update data in the same Salesforce Object with a ‘Self-join’ Informatica Cloud Task

Database Management and Performance
| Sep 1, 2011

When you use Informatica Cloud you usually use it to do Migration, Integration and Synchronisation. That's about what it is used for, right?

It is all about moving data from point A to point B or  syncing data between A and B. That is what  Informatica Cloud is built for, to move/sync data from A to B and while moving it, maybe transform it as well. A is the 'source' and  B is the 'target'. Correct! A can be an Oracle or ERP database and B can be a Salesforce Org. Classical use case of Informatica Cloud.

Well, not (more...)

Copy your Salesforce org into Oracle with CopyForce

Database Management and Performance
| Aug 18, 2011

We found this really cool piece of code called CopyForce in Google code the other day which lets you: 

Copy your Salesforce org objects (tables, indexes) into Oracle or any other RDBMS, yes you can!

The code I believe is freely available. But not to lose time I used the GUI which is available with trial a license of 30 days from CAPSTORM. In this post I want to quickly show you how you can copy your whole Salesforce org dev or prd with its data into Oracle, then do whatever you like with it.

Installing CopyForce with CAPSTORM (more...)

Salesforce Dev 401 Exam study material

Database Management and Performance
| Jul 15, 2011
Just a quick post to share with you my past Salesforce Dev401 Exam study materials. I have assembled this table  of 87 questions when I was studying for the exam back in January 2011. 

These are not actual Exam questions, don't be fooled!

This is study stuff that I have compiled reading the study guides and the available training materials I found online. It did help me pass the exam. The quiz like questions I compiled helped me a lot to understand concepts and limits.

I have also tried to categorise the questions & quizzes by the Salesforce Dev401 (more...)

Salesforce API call from Oracle APEX

Database Management and Performance
| Apr 2, 2011
This post is about integrating Salesforce force.com with Oracle using the Salesforce API.

I will try to show you how you can write a PL/SQL procedure to quickly send an SQL INSERT you do in an Oracle table to a Salesforce custom object as well. That is, how you can INSERT to a Salesforce object simultaneously when you insert to an Oracle table. I will use Oracle APEX, an Oracle RAD (Rapid Application Development) tool, as the Oracle database. 

You can achieve this by using the Oracle APEX package APEX_WEB_SERVICE.MAKE_REQUEST and send SOAP envelopes to the Salesforce (more...)

Salesforce workflow

Database Management and Performance
| Jan 29, 2011
What is Salesforce Workflow?

Workflow in force.com, is business logic and in this post I will try to explain it.

In force.com, you can create custom objects and then you can create relationships on your custom objects according to the application data model you have designed. Very good, but if you don't have workflow, your force.com app will be nothing more than a lousy database. A mere database to enter and search records. What is salesforce anyway? Isn't it a data-centric, based around a database application building platform? Another database front end?

Not, just that! Force.com (more...)

Salesforce Security

Database Management and Performance
| Dec 21, 2010
Salesforce security? Cloud security? Multi tenancy? I can't believe myself I set off writing a blog entry maybe on the hottest discussion topic in the industry nowadays. No, no, not at all, not yet, not yet.

I will rather try to quickly outline for the benefit of the interested reader how security is applied within a force.com Org. That is, security which enables or prevents certain users in the Org seeing the other user's records/data and how users are prevented from seeing certain fields and objects in the Org. So in-house Salesforce 'data' security I should say, not inter-cloud (more...)

Oracle Apex 4.0 UKOUG SIG in London

Database Management and Performance
| Nov 4, 2010
I attended the UKOUG SIG on Apex 4.0 in London yesterday and learned lots of new things. Thanks to presenters like John Scott from SumNeva and Hillary Farrell from Oracle. Looking forward to receive the seminar notes.

Briefly the things to take home from this SIG meeting for me were:
  • Oracle Apex plugins are great. Great presentation from John Scott on this.
  • In Oracle Apex release 4.0.2, there will be new themes which will render nicely, almost like native apps, on iPhones and iPads.
  • Lots of tips and tricks on how to scale and tune Oracle Apex (more...)

Oracle 11g Streams Synchronous Capture

Database Management and Performance
| Oct 22, 2010
Have you ever wanted just to maintain a table in two different databases? In two different schemas? Something like replicating, keeping a copy of a table in sync in another database, not in real-time, but almost real time? There is an easy way, use Oracle Streams Synchronous Capture!

Many times in development I get this request, where a developer in a team will come with a request like "Can we get a copy of that table in our schema in some sort of sync please?". Alternative ways of providing the developer with this table would be backup/restore, data-pump, both (more...)

Web services

Database Management and Performance
| Sep 11, 2010
I have been going around websites/wikis to find a definition for, what is a webservice? Especially I was interested to find out the kind of web services, which of course, have to do with databases, Oracle in particular. I spend the whole day looking at certain websites and here is my DIY one-liner definition:

Web services is all about give and take data over HTTP. When you give data you are a 'provider' of a web service and when you take data you are a 'consumer' of a web service.

Oracle 11g is doing both.

You can turn your (more...)