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