Copy your Salesforce org into Oracle with CopyForce

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 GUI.

1. Download the CopyForceOrace.zip file from the above site.

2. Extract the zip file to a directory of your choice

/home/crazyman/copystorm

3. Change directory permissions chmod +x on shell script CopyStorm.sh

4. Start the GUI by running

./CopyStorm.sh

5. Start the CAPSTORM GUI, accept EULA.

6. Remember this is a 30 day trial only.

Once the GUI is up you just have to provide your Salesforce and Oracle connection credentials. Before that I logged to Oracle and created a dummy schema called ORA_SFDC and in this schema the CAPSTORM programme will copy the whole Salesforce developer org objects.

Once you have configured and tested both connections, then you can start the copy process.

Once the process finishes lets login to Oracle and see how many objects were created in this blank ORA_SFDC schema. So I can see by running an Oracle dictionary view SQL that I get in my Schema only tables, views and LOBs.

SQL>

select object_type, count(*)
from user_objects
group by object_type
order by 2 desc

OBJECT_TYPE         COUNT(*)               
------------------- ---------------------- 
TABLE               161                   
INDEX               161                   
LOB                 89                     

That's it, that's what you get in Oracle from a vanilla Salesforce developer Org and a few custom objects that I had in this particular developer org. 161 tables per org only, not bad huh!

Now just imagine what you can do with this! 

The other good thing you get with CopyForce is since now you have copied your Salesforceorg  schema and your Salesforce data into Oracle, you can now use SQL to your heart's content and massage this data as you wish! That is, gone are the days where you had to dump Salesforce data to Excel and change it manually before you put  it back into Salesforce. Now you can change your Salesforce data in an Oracle database with all the sophistication ofSQL and put it back to Salesforce. 

I think CopyForce has lots of future, and really would be a good choice for one-off data integration, data beautification projects. 

I wonder what is in those LOBs ? I bet is the Apex code....

Salesforce Dev 401 Exam study material

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 study guide syllabus.

Here is the link to the questions: http://apex-outsource.com/pls/apex/f?p=309:Dev401


Hope it helps!

Salesforce API call from Oracle APEX

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 WSDL, without the HTTPS option, as I couldn't get that part working with my FREE Salesforce Developer account and my hosted Oracle Apex instance.

The idea is simple, you will create the PL/SQL procedure in Oracle, run it and insert data into an Oracle table and a Salesforce custom object simultaneously via SOAP envelope. The SOAP elements will be sent to Salesforce from the PL/SQL block using the APEX_WEB_SERVICE.MAKE_REQUEST package. The data flow direction is from Oracle Apex to Salesforce. To do this, you will create two SOAP envelopes. 

1. One to get authenticated and login to Salesforce, once you login you can get the Salesforce sesionID.

2. And two to send the INSERT (Create in Salesforce parleur) data DML. 

Here is the code you run in Oracle, you don't have to do anything in Salesforce, all you need in Salesforce is to have a custom object to insert the data into, your credentials and obviously your XML envelopes should be written accordingly based on the Salesforce WSDL which you will generate. I have the following code as a PL/SQL anonymous block in an Oracle Apex page process and does the job for me. Just to save you time, you need an Oracle Apex instance with enabled external network calls, unfortunately apex.oracle.com will not let you do that, that was when I checked it last time, you can always get a low cost fully external network enabled Oracle Apex instance from hosting companies, the one I use is Enciva.com

Enough said, here is the code make sure you pass in your Salesforce credentials with the security token.

DECLARE
l_envelope CLOB;
l_envelope_create CLOB;
l_xml XMLTYPE;
l_xml_create XMLTYPE;
v_session_id varchar2(1000);
BEGIN
wwv_flow_api.set_security_group_id;
--
--
-- ENVELOPE LOGIN
l_envelope :='<?xml version="1.0" encoding="utf-8"?>
   <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://
www.w3.org/2001/XMLSchema">
      <soap:Body>
         <login xmlns="urn:enterprise.soap.sforce.com">
            <username>YOUR@SALESFORCEUSERNAME</username>
            <password>YOURPASSWORDANDSECURITYTOKEN</password>
         </login>
      </soap:Body>
   </soap:Envelope>';
 
-- make request
l_xml := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/login',
p_envelope => l_envelope
);
--
-- ENVELOPE CREATE
-- get session id
-- You will need the salesforce session id , I know this is an ugly way of getting it, write a better one then, I just wanted to get it working

v_session_id := replace(replace(
substr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),instr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),'<sessionId>'))
,'<sessionId>',''),'<','');
-- debug
dbms_output.put_line('session_id is: '||v_session_id);
 
l_envelope_create :='<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <soapenv:Header> 
         <ns1:SessionHeader soapenv:mustUnderstand="0" xmlns:ns1="urn:enterprise.soap.sforce.com">  
            <ns2:sessionId xmlns:ns2="urn:enterprise.soap.sforce.com">'||v_session_id||'</ns2:sessionId> 
         </ns1:SessionHeader>
      </soapenv:Header>
      <soapenv:Body> 
         <create xmlns="urn:enterprise.soap.sforce.com">  
            <sObjects xsi:type="ns3:linkstream__c" xmlns:ns3="urn:sobject.enterprise.soap.sforce.com">
               <ns3:wbid__c>'||:P3_WBID ||'</ns3:wbid__c>   
               <ns3:Link__c>'||:P3_LINK ||'</ns3:Link__c>   
               <ns3:Description__c>'||:P3_DESCRIPTION ||'</ns3:Description__c>   
               <ns3:Display_link__c>'||UPPER(:P3_DISPLAY_LINK) ||'</ns3:Display_link__c>   
            </sObjects>  
         </create>
      </soapenv:Body>
   </soapenv:Envelope> ';

dbms_output.put_line(l_envelope_create);

l_xml_create := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/create',
p_envelope => l_envelope_create
);
dbms_output.put_line(substr(l_xml_create.getClobVal(),1,25500));

END;

As you can see the code executes in one PL/SQL block. You can call the procedure via an Insert Trigger on the Oracle table, or as a 'Page processing' process in Oracle Apex. It ads a link to my force.com sites custom object from my Oracle Apex app. I display this mock force.com sites on the left column in this blog, under 'My force.com apps' section.

Needless to say, there are maybe dozens of easier ways of doing the same thing between Oracle and Salesforce with tools such as CastIron, Talend, Informatica or Apatar. Some are even free. But hey, this is using no tool but just HTTP!

Salesforce workflow

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 workflow will bring this database into life. Instead of being a stagnant repository of passive and past data, it will become a forward thinking and forward acting alive database. Maybe the thinking bit was a bit of an exaggeration but nevertheless, deductive databases is what we want, isn't it? Maybe one day...

What the force.com workflow engine does is things like: automatically send email alerts, assign tasks, or update field values based on rules that we define. That is it's job, and that is exactly what it does. The force.com workflow engine, based on rules we define, can:

• Assign a 'Task' to a force.com user
• Send an email alert to 'anyone' (a valid email will suffice).
• Update the field of a particular record on an object
• Send an outbound message to an external system, things like webservice call, SOAP etc.

When does the workflow action fire?

So a workflow can do all these things. That is great! But when? Based on what? It is nice to have the ability to send emails and update fields, but what determines or induces these actions? Well the answer is, The Workflow Rule.

The workflow rule is the main container where all these events are included and are programmed to execute. They execute only when certain workflow 'evaluation criteria' and workflow 'rule criteria' are met.

That is, the workflow rule is basically watching a custom object (it can only watch over one custom object at a time) and when certain evaluation criteria on the watched custom object and rule criteria (a condition) are met, bang!... Then it activates (fires) the workflow actions and does all the things the workflow can do, like send emails, field updates etc. It sounds so much like a database trigger, hmm... doesn't it?

The 'evaluation criteria' tells the workflow rule when to watch the custom object.

The 'rule criteria' tells the workflow rule when to fire the events and based on what condition.

The 'evaluation criteria' and 'rule criteria' together must be met successfully for the workflow rule to fire.

Now, for the workflow rule 'evaluation criteria' we have predetermined choices and these are:

1. When a record is created, or when a record is edited and did not previously meet the rule criteria (this again and again repeatedly triggers the rule every
time a record is saved only until the rule's criteria are met, then it stops, sort of once only if you can say, subsequent edits are ignored)
2. Only when a record is created (this ignores updates to existing records)
3. Every time a record is created or edited (which repeatedly triggers the rule every time the record is saved, it does this only when the rule criteria is met as well).

These are the only times that we can choose for a workflow to be evaluated. Remember evaluation is not enough, the 'rule criteria' has to be met as well for the workflow actions to fire!

For setting the 'rule criteria' we have 2 options. We can use a filter like setup, a condition on the watched/associated custom object, i.e when the Status field of the custom object equals to Open(Status=Open) then fire the workflow or when a Formula Expression evaluates to true.

So to summarise, we can send emails, update fields and do outbound web service calls from force.com using workflow. This is easy to setup, is based on an object and the evaluation and rule criteria must be satisfied for the workflow to fire. Well quite handy piece of work from salesforce, so you don't need to write your own workflows any more! The mantra 'No Software' used in Salesforce's adverts suits this well.

What are you waiting for? Use your Free developer.force.com account login and go to Setup > Create > Workflow & Approvals > Workflow Rules and start practising.

Salesforce Security

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 security.

Were you ever interested? We know Oracle and other databases use words like 'permissions' to describe data access. We know applications use words like 'authorisation' or 'authentication' to describe security. Were you ever interested what and how security is accomplished in Salesforce and force.com? If you are, keep on reading, else give up now.

Data Security in Salesforce is tricky, different and not something which is similar to anything else. There aren't words like 'authorisation' and 'privileges' in the force.com world. Once a Salesforce or force.com user logs in, 'data access' security kicks in. Somebody can only login if he/she is a salesforce user.  I use Force.com and Salesforce interchangeably here, it is the same platform at the end of the day. And the security in this platform described in descending order from big data to small data access is something like this:

1. Object Level Security: This type of security prevents a user form deleting, updating, seeing or creating any instance of the object (record in the table or if you like more old fashioned, tuple in the relation).

2. Field Level Security: In this type of security a user is prevented from seeing, editing and/ or deleting the value for a particular field of an object.

I know you already started visualising a database table. Object, what object?



3. Record Level Security: In this type of security we control data with a little bit more of a 'finesse'. We allow particular users to see an object, but we restrict the individual object records a user can see. A bit like virtual private database (VPD). In Oracle achieved with DBMS_RLS. I wonder how is this done in Salesforce? Salesforce is implemented on an Oracle database anyway. This is hot stuff. Everyone is interested in this nowadays, this means you can slice and dice the 'object' (table) horizontally! How is this done in Salesforce? Well the methods are four.

3.1 Organisational Wide Defaults (OWD): This is the baseline level of access to objects (tables ;-) ) a user has. OWD is defined for each standard or custom (your make) object in Salesforce. OWD in other words is the "baseline level of access that the most restricted user should have". Another name for OWD is "Sharing Model". That is, use OWD to lock-down objects to the most restrictive level.

3.2 Role Hierarchies: Role hierarchy, is the first way we can share access to records per se. OWD is a very 'lock-down' and 'restrictive' thing at the object level, whereas roles is the beginning of opening access to certain records via a hierarchy. The role hierarchy ensures that a manager will always have access to the same data as his or her employees. A polite way of saying your boss owns all your data. That is if you own 10 records in Salesforce, you own them, but your boss owns them as well. Typical, hierarchical record level access control coming down vertically, top to bottom and the 'Top' owns all! You can even assign access through hierarchies on an object-by-object basis as well.

3.3 Sharing Rules: Is when you start making exceptions, and confusing people. Not really! Sharing rules let us make automatic exceptions to Organisational Wide Defaults (OWD) for particular groups of users. As you realised in Role hierarchies section 3.2 above, the Role Hierarchies enable certain group of people to own lower level people's data. Low, down that is. Well Sharing Rules, don't do down, they do left and right. Did you get it? Basically saying that if there are 2 managers in the same hierarchy at the same level, how would they be able to see each others data? Enter Sharing Rules. Sharing Rules enable the access to records across levels in the hierarchy, by establishing certain groups which contain this same level managers, so that they can share their data. Something like Marketing wants to share its records with the IT department, with Role hierarchy this is not possible unless the departments are one 'under' the other and not across at the same level and next to each other in the hierarchy.

3.4 Manual Sharing: What manual sharing in few words is to 'give access to your record, to the guy you like'. No, not exactly but similar. With manual sharing you can grant read or read/write access on records that you own to any other user, role, or public group in Salesforce manually whenever you fancy and like. Although it isn't automatically like in all 3 cases above, manual sharing gives you the flexibility to share particular records. So manual sharing is when you can't do it in any of the 3 ways above. That is when you can't grant access to the record via OWD, Role hierarchies and Sharing Rules, you use Manual Sharing.

This was my attempt to give you a taste of what is data security in force.com. We started by letting you see the object, then playing with which fields of the object you can see, and lastly what records of the object you can see.

If you are interested to read more on this topic check 'Force.com Fundamentals' by Chris McGuire and Caroline Roth. A very Cloud book.