Feed aggregator

An Interesting Feature of NOT IN and Multi-Row Subqueries

Andrew Tulley - Fri, 2013-03-01 07:55

Take the following simple SQL statement:

FROM dual
(SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual);


What about in the following case?

FROM dual
(SELECT 'a' FROM dual

You might expect this to return a row as well since ‘x’ is still not in our subquery (which now returns two values: ‘x’ and NULL).

You’d be wrong, however. This query will in fact return no rows:

  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual
  6   SELECT NULL FROM dual);

no rows selected

If the subquery referenced by your NOT IN statement contains any NULL values, the NOT IN condition will evaluate to unknown.

It’s worth noting that this is not the case if you use an IN as opposed to a NOT IN:

  2   FROM dual
  3   WHERE 'a' IN
  4   (SELECT 'a' FROM dual
  5    UNION ALL
  6    SELECT NULL FROM dual);


McAfee wins best database security solution award

Slavik Markovich - Thu, 2013-02-28 16:26
It’s hard to believe that another year has passed from last RSA. But, indeed, time flies when you’re busy, I guess. So, for the second year in a row, McAfee wins the SC magazine award for best database security solution. I’m so proud!

Oracle BI Applications (OBIA) released

Oracle e-Business Suite - Wed, 2013-02-27 00:03

Good News Oracle Business Intelligence Application new version is available to download.

Highlights of the Oracle BI Applications Release:

  • New Application – Oracle Manufacturing Analytics with pre-built adapters for EBS Process Manufacturing R12.x and EBS Discrete Manufacturing R12.x and 11.5.10
  • New Application – Oracle Enterprise Asset Management Analytics with pre-built adapters for EBS R12.x, EBS 11.5.10 and IBM Maximo 7.5
  • Universal Adapter to extend the capability to other source systems
  • Certified for OBIEE 11g
  • Certified for Exalytics
  • Certified for DAC 11g including support for Exalytics / Times Ten, Patching Framework, Dual ETL Support and many other performance enhancements
  • Native support for mobile and tablet devices
  • Localized in 28 languages
  • Supported on Oracle, SQL Server, DB2 and Teradata DB
  • Rollup patch resolving 122 BI Applications bugs

Good thing so many bugs are fixed. To get solution / work around,  me and my team spent days on Severity 1 Service Requests with Oracle.

OBIA Bugs Fixed in [ID 1528774.1]

Link to Download Software and Documentations


Categories: APPS Blogs

Changing Enterprise Manager 12c Default inactive Timeout

Fuad Arshad - Tue, 2013-02-26 14:27
Oracle Enterprise defaults to a timeout of 45 minutes . Depending on the organization or security policies you might want to change that to a lesser amount of time or in my case a longer period .
To check what the timeout is set
em@emap1>./emctl get property -name oracle.sysman.eml.maxInactiveTime
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
SYSMAN password:
Value for property oracle.sysman.eml.maxInactiveTime for oms All Management Servers is null null = default of 45 minutes
After you have checked the value . you can change it
em@emap1>./emctl set property -name oracle.sysman.eml.maxInactiveTime -value 90
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
SYSMAN password:
Property oracle.sysman.eml.maxInactiveTime has been set to value 90 for all Management Servers
OMS restart is required to reflect the new property value
This will set the inactive time to 90 minutes . you will need an OMS bounce for this to take effect. Please ensure that whatever value you set meets your requirements and the security requirements of your company

TASK_GROUP_Load_PartyDimension “Duplicate Keys Found”

Oracle e-Business Suite - Mon, 2013-02-25 08:33

While Running Full load on Oracle Business Intelligence Applications, when using Source system R12 TASK_GROUP_Load_PartyDimension failed with error Log says the issue is because “duplicate keys found”

Extract from the Log



MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX
with error DataWarehouse:CREATE UNIQUE INDEX
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
EXCEPTION CLASS::: java.lang.Exception


For Resolution:- Download and review the readme and pre-requisites for Patch.14085106.

Categories: APPS Blogs

Metrics for Compensation Allocation

TalentedApps - Sun, 2013-02-24 06:56

Simon would like to allocate performance bonus to his workforce who got performance rating of 5/5 and 4/5. People who got 5/5 rating will get (X) % of their base salary as performance bonus where as 4/5 will get (X-1) %. He would like to define compensation metric for the same so that he can apply it, with his decided % amounts, on his entire workforce rather than allocating to each one of them. He would also like to share it with his peers or subordinates in case they would like to use same metrics with their own numbers.

Compensation Allocation Metrics for Managers

Neither the use of organization wide published metrics is new to compensation process nor is the desire of a manager to build his own metrics for distributing compensation. Only question is whether your current (or potential) adopted compensation solution supports it or not.

As a manager, you would always like to create metrics that you can use to allocate compensation to your team as it will facilitate a smooth process and help you in well-informed decision-making. From ages, managers are using off-line tools to flag and store decision attributes which helps them in making compensation as well as other decisions. What they actually need is a system which not only allows them to build their own metrics based on various person related attributes but also allows them to share it with their peers or subordinates. Manager owned metrics supplements the organization wide metrics (aka HR established Metrics) and not really replace them.

Some managers will be happy if system supports basic attributes like performance rating, work location, Job/Grade whereas others may need more specific attributes like years in Job/Grade, compa-ratio and grade ranges. It will be beneficial to have embedded support for all the possible decision attributes as well as custom attributes (an extension to store business specific values) so that managers can build robust metrics for compensation allocations with great ease. It will result in compensation allocation process to reach the next level.

OTN Yathra - past the 1/2 way mark

Hans Forbrich - Sat, 2013-02-23 06:19
I am currently sitting in the hotel in Bangalore in India, catching my breath. We have finished 4 of the 6 cities in this tour.

I'm going to cheat tremendously and link to Lucas Jellema's blog about the tour, starting with http://technology.amis.nl/2013/01/27/otn-yathra-2013-the-six-city-oracle-tour-of-india/ as the overview.

Shamelessly linking Lucas' map of the Yathra.

I arrived in Chandigarh, Punjab on February 10, and was met by Oracle ACE and friend Aman Sharma.  The trip:  left Edmonton on Friday evening, landed London Heathrow Saturday morning, left LHR Saturday evening, arrived Delhi Sunday morning, left Delhi Sunday afternoon and into Chandigarh early evening.

On Monday and Tuesday, much to my delight, Aman showed me his home country and local community.  And I did some shopping.

Wednesday, headed back to Delhi by taxi, and prepared for the gathering and presentations.

On Friday the team gathered at the Country Inn by Carlson in Saket.  ACE Director team members are

Murali Vallath - our host and RAC ACED from India
Lucas Jellema - Development ACED, including SQL and Java
Edward Roske - BI/Hyperion ACED, presenting on Hyperion, Essbase and BI
Raj Mattamal  - Development ACED, specializing in APEX
Hans Forbrich - Infrastructure ACED, presenting on LDAP, Cloud Control and Linux

In each city we have been fortunate to have ACEs such as Aman Sharma join us to present

Saturday was the first presentation day at the FMDI (Fertilizer Marketing Development Institute) which has wonderful classroom facilities.

Sunday, a quick tour of Delhi, off to the airport to Mumbai (formerly Bombay), and settle into the Holiday Inn.

Monday, presented using the facilities of the Women's Technical Institute, and then by car off to Pune.

Since we arrived in Pune Monday evening, Tuesday was a touring day out to Mahabalshawar and the head of the Krishna river.

Wednesday we presented in Pune at the Oracle offices.

Thursday by plane to Bangalore, and

Friday presented in the Oracle offices in Bangalore.  Lucas has a fantastic half-way write-up at http://technology.amis.nl/2013/02/21/otn-yathra-2013-spreading-the-story-of-oracle-across-india-half-time/

Raj and Edward left today to return home.

Tomorrow Lucas and I will head to Hyderabad and continue this tour.
Categories: DBA Blogs

Run Scripts in SQL Developer

Chet Justice - Thu, 2013-02-21 19:53
I finally decided to save a script that cleans out a couple of tables for me.

Now I have a script, how do I run it in SQL Dev? In SQL*Plus, I would run it like @clean_tables. Two things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress.

I wanted to be able to run my script in a SQL Developer worksheet. How?


Error starting at line 38 in command:
Error report:
Unable to open file: "clean_tables.sql"

Twitter. Jeff Smith hangs out there, a lot. He supposedly has a real job as the Senior Assistant Principal Skinner Product Dude for SQL Developer at Oracle. Crazy title, I know. Back to Twitter.

@oraclenerd working directory would be directory of parent file

— Jeff Smith (@thatjeffsmith) February 22, 2013

Since he lives there (Twitter) (and I'm glad he does), I got an immediate response. Yay for Jeff.

Wait, what? Parent file? WTF are you talking about?

(I then remove the snark and try to put more details)

(oh, and I don't like that I can't just embed a single tweet...sorry, their fault, not mine)

@thatjeffsmith "@ test(.sql)" I just want to know where to put test.sql.

— oraclenerd (@oraclenerd) February 22, 2013
Two seconds later:

@oraclenerd i think this, Tools > Preferences > Database > Worksheet > Select default path to look for scripts. Please test :)

— Jeff Smith (@thatjeffsmith) February 22, 2013
Tested, and it works. Yay for me. Yay for Jeff.

In case it isn't obvious, I'm being sarcastic. Jeff is a fantastic advocate for SQL Developer. Yes, he gets paid to do it, but he goes above and beyond on a daily basis. Oracle is lucky to have him.
Categories: BI & Warehousing

Update on InteliVideo

Bradley Brown - Thu, 2013-02-21 18:57

Pivot UpdateIt's been a while since I wrote a blog entry.  I guess we've been heads down working for a while now.  I'm finally coming up for some air!  When we started InteliVideo, the focus was on providing a marketplace for people who wanted to sell their video content.  After talking to 100s of people with video content, many of which sell millions of units a year, it became clear that we needed to create a number of pricing (and service) options for our customers.  The marketplace is "high end" from a cost (to our customers) perspective.  We used to get a lot of questions about how we are different or better than iTunes.  We'll be able to answer that some day since we'll do a better job of promoting our customer's content than iTunes, but for now we're going to pivot a bit.  Since we don't really have a marketplace in place yet, we decided to focus on providing authenticated access to video content.  This could include putting a paywall in front of videos, but could also mean that our customers can simply tell us who can access a video, how long they can access it for, etc.  This could be done through our API or via an email to us (that will be read and processed automatically).  So the "slight pivot" is moving from the marketplace mentality to paywall / authenticated video viewing.  There are plenty of places that can host your videos, but providing a paywall (or authentication) in front of your video - not so much!

We developed a short video explaining what we do for our customers.

I would love to hear your feedback on this video.

When I started my consulting business, TUSC, in 1988, I wondered about how to price our consulting services.  A good friend of mine, Steve Silver recommended we ask our customers what we should charge.  This seemed counter-intuitive to me.  Would customers be honest with pricing our value?  The answer was yes - they were VERY honest about what they could pay for our world class Oracle consultants.

At InteliVideo we did a lot of research on the market and have now arrived at a pricing model.  We'll disclose this soon - when we release our new website.  It was exciting to work through this, to run it by potential customers for feedback and now to close on this chapter.

As you can see, we'll have features that are available by classes of customers.  Our monthly bundles provide HUGE value to our customers.
New sales site
In the process of revising our message, we revised our website accordingly.  Less focus on the marketplace and more focus on the value we provide to our clients.  The feedback on the site has been good!  Our primary customers are currently those companies who have historically been in the DVD replication business.  These folks have witnessed a change in their business over the last 5 years.  Many people have been quick to point out that if they receive a DVD they don't even have a DVD player anymore.  We're providing the video on demand platform for these companies.  You would never know that InteliVideo is the platform behind their offerings!  We're a complete white label solution for them!

New "Player"
We reworked our "player" in the browser to simply that for our customer's customers:

We list all of a customers videos (that they have purchased) on the right.  The top left of the page provides branding for our customers.  Below the list of videos we created an "upsell" section for our customers to advertise other things that might be of interest to their customers.  At the bottom of the page, we list all of our players (beyond the browser).

Clean, simple, easy to use!
PartnersWe've also added a couple of world class partners/founders to the business.  I'm going to assume the CEO role.  Monty Sooter, former CIO at Corporate Express (a huge customer for TUSC) and former COO for ClickBank is our President/COO.  John Hayward has joined us as our CTO.  I'm very excited to have these guys on my executive team!

Whole new design in actionI mentioned in a previous blog post that I was big on Crowdsourcing logos, website development, etc.  The last round of UI work was done through CrowdSpring (and others through 99Designs).  I really enjoy that process and the end result.  This provided us with an excellent revision in our site's look and feel.  We're in the process of using Mike Stemple to design a whole new look and feel throughout the site.  Mike's an amazing guy and so much fun to work with!  We're also working on our site flow.  It's time!

We have clients, who will upload their videos, categorize them, monitor sales and how often they are watched, and the like.  This is our "white" site.

We also have customers, who will actually be our customer's customer - i.e. they will buy a video.  This is our "dark" site, which is supposed to look and feel more like a TV.  This site will have its own set of features and benefits such as being able to watch your video on any platform.

We are working heavily on our admin side of our "white" site too.  This will provide our clients with the analytics they need, but just as important is the uploading, categorizing, etc. that I mentioned above.

A big part of our focus has been that we protect our client's content or intellectual property.  In other words, we don't simply email a customer an mp4 file and let them figure out how to play it.  We also don't email the mp4 to protect the content creator's content / IP.  In other words, that would make it very easy for them to upload it to YouTube and make it public content.

One player that we were missing was a player for the laptop or desktop computer.  Sure, you could stream the content and watch it on your browser, but you couldn't download it and watch it on an airplane (i.e. disconnected from the internet)...and you had all of the baggage of streaming too.
Laptop/desktop client
Our first attempt at a laptop/desktop player was with HTML5.  We spent December realizing this was a futile effort.  HTML5 has a lot of "disconnected" or "offline" functionality built into it.  However, HTML5 is SUPER buggy and you can't download large files (which video files are large), so we spent January creating our laptop/desktop client in Java.  With Oracle's Java FX we were able to create a client that runs on Windows, Mac, Linux AND we were able to create installers for all 3 environments / operating systems.  Our laptop/desktop player looks a LOT like the browser-based functionality...but allows you to download any of your videos.  It also manages subscriptions (i.e. you can't play a video when your subscription ends).

Android, iOS and RokuWe've had players for Android, iOS (iPhone and iPad) and Roku for a while now.  However, our iOS player didn't allow you to download your content to the device.  We reworked that application, so it now provides that functionality.  At some point we'll develop players for all of the smart TVs that are out there.  Our Android app works on the Google TV, but there are Yahoo, Samsung, etc. smart TVs out there.

SubdomainsOne feature that I love that we added is the "subdomain" functionality.  This allows our customers to establish their own subdomain within InteliVideo.  We can create an ENTIRE video site for them here or we can automatically display all of the videos in their sales library.  For example:


This allows our customers to create their own branded marketplace in seconds!

I'm always interested in your feedback, ideas, comments, suggestions!  Every day is a learning opportunity.

(no IT for once) This drummer is mad!

Francois Degrelle - Thu, 2013-02-21 13:52
Hello there, If you like funny people, have a look at this drummer . He's (Steve Moore) just crazy on his set ! He made my day Have a good time, Francois

Fusion Applications Transactional Business Intelligance (OTBI) and Descriptive Flexfield

Oracle e-Business Suite - Thu, 2013-02-21 07:46

Fusion Applications OTBI and Descriptive Flexfield

In Oracle Transactional Business Intelligence for Oracle Fusion applications a very good feature is to use DFF to make any analysis. It used to be very painful for any organization to make analysis based on DFF using BIAPPS. Now it’s just matter of few clicks and you are done.

What Oracle says “If a descriptive Flexfield is enabled for Oracle Business Intelligence, the Manage Descriptive Flexfields task displays a BI Enabled checkbox for each global, context, and context-sensitive segment. Select a segment’s BI Enabled checkbox to specify that the segment is available for use in Oracle Business Intelligence.

a list of all the predefined “BI Enabled”  DFFs available in Oracle Metalink Note Fusion Applications Business Intelligence – List of Descriptive Flex Field Mappings [ID 1509316.1]

Categories: APPS Blogs

NZOUG 2013 Conference: Agenda online - register while Earlybird Pricing still here!

Gareth Roberts - Thu, 2013-02-21 05:29
The New Zealand Oracle Users Group - NZOUG Conference 2013 is open for registrations and Earlybird pricing is still available! The agenda is now available.

NZOUG 2013 is being held in Wellington at the iconic Te Papa Museum on the beautiful waterfront on the 18th and 19th of March, with an additional Workshop Day on the 20th March.

The conference will see a wealth of brilliant speakers from around the world, including Tom Kyte, Graham Wood, Andrew Holdsworth, John Schiff and Nadia Bendjedou and of course top speakers from Middle Earth (NZ) as well. Tom, Andrew and Graham will be presenting their Real World Performance show, which will be a highlight of the conference.

Topics being covered cross a wide range of Oracle solutions, with tracks for Development, DBA, Cloud Computing, Oracle E-Business Suite, a full dedicated JD Edwards Day, Fusion Applications, EPM/BI, Middleware, Management, Infrastructure, Security, Hardware and Professional Development.

I'll be co-presenting one paper on utilizing the Oracle E-Business Suite Integrated SOA Gateway, tips, tricks and a demo, not too much this time around so that I can head along to as many of the other presentations as I can. Drop me an email if you want to catch up at the conference!

So, if you're around New Zealand in mid-March, perhaps following on from the NZ - England Cricket Test, and if you like getting out and networking often with fun consequences, then make sure you come to the New Zealand Oracle event of the year, not to be missed!

Catch ya!


This is a post from Gareth's blog at http://garethroberts.blogspot.com


Updated February 2013 Critical Patch Update for Java SE Released

Oracle Security Team - Tue, 2013-02-19 15:01

Hi, this is Eric Maurice.

Oracle today released the updated February 2013 Critical Patch Update for Java SE.  As discussed in a previous blog entry, the purpose of this update is to deliver 5 additional fixes which could not be included when Oracle accelerated the release of the Critical Patch Update by publishing it on February 1st instead of February 19th.  Note that since Critical Patch Updates for Java SE are cumulative, this Critical Patch Update release also includes all previously-released Java SE security fixes.  

All but one of the vulnerabilities fixed today apply to client deployment of Java.  This means that these 4 vulnerabilities can be exploited through Java Web Start applications on desktops and Java applets in Internet browsers.  Three of these vulnerabilities received a CVSS Base Score of 10.0.  As I stated before, Oracle reports the most severe CVSS Base Score, and these CVSS 10.0s assume that the user running the malicious Java Applet or Java Web Start application has administrator privileges (as is typical on Windows XP). However, when the user does not run with administrator privileges (as is typical on Solaris and Linux), the corresponding CVSS impact scores for Confidentiality, Integrity, and Availability are "Partial" instead of "Complete", typically lowering the CVSS Base Score to 7.5 denoting that the compromise does not extend to the underlying Operating System. 

The last security fix added by this updated Critical Patch Update release applies to server deployments of the Java Secure Socket Extension (JSSE).  This fix is for a vulnerability commonly referred as the “Lucky Thirteen” vulnerability in SSL/TLS (CVE-2013-0169).  This vulnerability has received a CVSS Base Score of 4.3.

Due to the severity of the vulnerabilities fixed in this Critical Patch Update, Oracle recommends that these fixes be applied as soon as possible.  IT professionals should refer to the advisory located at http://www.oracle.com/technetwork/topics/security/javacpufeb2013update-1905892.html and desktop users can install this new version from java.com or through the Java autoupdate.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Finally, note that Oracle’s intent is to continue to accelerate the release of Java fixes, particularly to help address the security worthiness of the Java Runtime Environment (JRE) in desktop browsers.   As a result, we will be issuing a Critical Patch Update for Java SE on April 16, 2013 at the same time as the normally scheduled Critical Patch Update for all non-Java products.  The next scheduled release dates for the Critical Patch Update for Java SE are therefore: April 16, 2013; June 18, 2013; October 15, 2013; and January 14, 2014. 


For More Information:

The Advisory for the updated February 2013 Critical Patch Update for Java SE is located at http://www.oracle.com/technetwork/topics/security/javacpufeb2013update-1905892.html

The advisory for the February 2013 Critical Patch Update is located at http://www.oracle.com/technetwork/topics/security/javacpufeb2013-1841061.html

More information about Oracle Software Security Assurance is located at http://www.oracle.com/us/support/assurance/index.html

How to get the latest document by date/time field?

Tugdual Grall - Mon, 2013-02-18 22:32
I read this question on Twitter, let me answer the question in this short article. First of all you need to be sure your documents have an attribute that contains a date ;), something like : To get the "latest hired employee" you need to create a view, and emit the hire date as key. The important part is to check that this date is emitted in a format that is sorted properly, for example an Tugdual Grallhttps://plus.google.com/103667961621022786141noreply@blogger.com2

Changing your PS Database Platform: Cutover

Brent Martin - Sun, 2013-02-17 00:33

So far I've written about how you might approach the plan, design, build and test phases of a PeopleSoft replatforming project.  This time around I'd like to spend some time on the Cutover.

You’ll probably want to do at least 4 mock cutovers. One to build the initial development environment on the new hardware.  One to start System Test. One to start User Acceptance Testing, and a “dress rehearsal” to prove out your cutover plan/strategy. 

Start the cutover plan when you do your first migration. Capture tasks and timing. And continue to refine it with each additional mock cutover.

For the 3rd mock cutover, include items in the cutover plan for communication, external systems that will need to be modified and moved in parallel, shutdown sequence for batch, expected timeline, contact lists, etc.  By now your communication plan should be fairly explicit and there should be no surprises from the extended IT team or the business as to what will happen and when.

One to two weeks prior to cutover, execute a “dress rehearsal” where you actually move your production database in as realistic of a fashion as possible.  Validate your final timings and make sure nothing was missed.

 Two words about cutover communications:  They’re important.  You need to keep all of your stakeholders informed of where you are in the cutover, raise any issues quickly, and insure all of the hand offs are executed cleanly with no loss of time.  Identify a single point of contact (or contacts if you’ll be running cutover around the clock) who can get status from the team members without bugging them too much and prepare regular communications to the interested stakeholders.   

 In addition, you’ll probably want to maintain two open conference call bridge lines:  One for executive/stakeholder updates, and another to allow your technical teams to quickly collaborate on handoffs or issues that arise.

 A good cutover plan will include a final “Go/No-Go” decision point prior to starting any cutover activities.  If you have no “Severity 1” or “Showstopper” issues the cutover should proceed on schedule.

 Now the cutover plan becomes the script for everything over the next hours and days.  A common scenario follows:  Users close out transactions.  Batch schedule is stopped in a controlled manner. Final interface files are sent.  Validation reports are run that users will use to validate the system when it comes back up.  Finally user accounts are disabled, the application is stopped, and the DBA team (who is hopefully caught up on sleep) takes over.

 Now the DBA team executes the data migration using whatever tool you decided on.  Row count reports and other validation will be executed when it’s complete and the PeopleTools upgrade will start on the database.  This can be the longest part of the process.  Then all of your customizations are migrated in, the application is configured and a non-destructive technical checkout is conducted.

 It’s typical at this point to allow a limited number of users log in and enter and process real production transactions. This allows any problems to be identified and resolved before the system is turned over to the larger user population.

 Finally we’re ready to go.  Get your project sponsors and executives on the phone for a final Go/No-Go decision.   Once you get the green light, unlock all of the users and start your batch schedule back up in a controlled manner.  Congratulations!  This is a big accomplishment!!

Nice way to bring some coolness to Oracle statistics

Slavik Markovich - Fri, 2013-02-15 20:12
Turns out that Tanel has an artist hidden deep down inside!


Slavik Markovich - Fri, 2013-02-15 19:51
These are some amazing statistics…

SQL Access to Salesforce data

Kubilay Çilkara - Thu, 2013-02-14 13:13
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 query data in any Salesforce standard objects like Account, Contact and custom objects with the use of a SQL Client tool.

For my post I used SQuireL SQL Client and the JDBC driver. You can easily point your own SQL Client tool to these drivers too.  ODBC and JDBC are data access standards many tools comply with.

To get started

1. Download Progress|DataDirect Salesforce.com JDBC driver the file will be called something like this PROGRESS_DATADIRECT_CONNECT_JDBC_5.1.0.jar  ( I have downloaded the 15 day trial version)

2. Install the driver as per instructions found here and more generic info like User Guide is here

3. Configure SQuireL SQL Client to use the driver (to install SQuireL go here)

Once you have downloaded the driver and you have installed it. Start the SQuireL Client tool and register the Salsforce.com JDBC driver with SQuireL like this:

Go to Drivers tab on the right and click the + sign.

    Add a driver by using the plus sign (+) and fill in the driver details like below. You can find out the Class Name and the Website URLs, Extra Class Payt for the Progress|DataDirect Salesforce.com JDBC drivers here. You can find out more about connection properties here DataDirect Connect Series for JDBC User's Guide

      Once you have configured the driver is time to add an Alias SQuireL connection to your Salesforce Org. I used my developer org below. Just follow the wizard to add the alias. It will ask you for the driver to use. Choose the Salesforce driver you have crated above. 

      Once you have created the alias (connection) is time to test your alias and connect to Salesforce with the new JDBC driver. Provide your Salesforce credentials like this:

      Once you connect then you can issue a SQL join and write any SQL statement to your Salesforce Org as below.  Below I am writing a SQL join between Account with Contact Salesforce standard objects.

      What the driver really does is to translate your SQL to SOQL.

      To read more about the JDBC and other drivers go to the company site Datadirect.com

      There is potential here, imagine all the data profiling, data quality operations and data integrations, you can do 'in place' in SaaS and Cloud systems with SQL, without having to move the data around a lot. 

      More resources

      More blogs and resources on SQL Access to Salesforce can be found below. There is a very interesting blog post which shows you how you can access the Salesforce data from within Oracle directly here too.

      Categories: DBA Blogs

      Introduction to Collated Views with Couchbase 2.0

      Tugdual Grall - Wed, 2013-02-13 05:38
      Most of the applications have to deal with "master/detail" type of data: breweries and beer department and employees invoices and items  ... This is necessary for example to create application view like the following: With Couchbase, and many of the document oriented databases you have different ways to deal with this, you can: Create a single document for each master and embed all the Tugdual Grallhttps://plus.google.com/103667961621022786141noreply@blogger.com0


      Subscribe to Oracle FAQ aggregator