| Stored Procedure in migrating data [message #644066] |
Tue, 27 October 2015 14:23  |
 |
amy.wilson
Messages: 39
Registered: September 2015
Location: Www
|
Member |
|
|
Hi All,
We are working on Oracle 11.2.0.3.
We are migrating data from Siebel to SFDC.
In this process we have created database tables in SFDC. But some columns are missing and some columns are not matched in SFDC.
Now we would like to write a Stored procedure to migrate data from Siebel to SFDC.
But here my query is, how can we write a stored procedure which will convert the columns data and from SIEBEL to SFDC and how can we map the data and load the data without having insufficient columns and their respective data.?
As am aware we can load the data if we have similar number of columns and data types by using UTL files/collections. But am not aware how can we load the data in the above siutation
Can any one please advise.
Thanks,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Stored Procedure in migrating data [message #644152 is a reply to message #644149] |
Wed, 28 October 2015 10:24  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Does the varchar contain non-numeric data?
Does it contain data longer than 12 chars?
If the answer to both of those is no then a simple to_number is all you need.
If either is yes then someone needs to define rules for how to the convert the data. We can not help you with that, only some-one familiar with the systems in question can. We can potentially help with how to implement the rules once they are defined.
It still seems to be the case that you're asking your question in the wrong forum.
|
|
|
|
|
|
| Re: Stored Procedure in migrating data [message #644154 is a reply to message #644153] |
Wed, 28 October 2015 10:48 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can't stick a 27 character string in a number(12). At all. Ever.
So either you change the size/type of the number(12) so it can hold the data from the old system or you define some rules for data conversion.
|
|
|
|
| Re: Stored Procedure in migrating data [message #644185 is a reply to message #644153] |
Thu, 29 October 2015 07:00 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
amy.wilson wrote on Wed, 28 October 2015 10:32...
The data in teh column , some values are 16 characters and some are more than 27.
We have no rules why they defined varchar2 in one system and why it's number in another system. But our goal is to keep same data type and same data size in both the systems. and need to load the data successfully with out fail.
your suggestions would be appreciated.
No one is talking about rules of WHY the data is the way it is. It is what it is. No, the "rules" people are talking about are business rules about how you are going to convert, say, [siebel.empid varchar2(10)] to [sfdc.emipd number]. Let's say a typical siebel.empid has a value of 'AC-123-XY'. You will need to define a rule on what numeric value you want that to become when you put it in sfdc.empid. And it might not be as simple as 'strip out the non-numeric characters'. What if you have values 'AC-123-XY' and 'BD-123-YZ'? As was pointed out in your thread on this same subject on the OTN forum, there will be no substitute for the manual work of identifying EVERY column in BOTH systems, mapping them from one system to the other, and defining a local business rule for each. We can help with the technique, but only you can do the analysis and rule defintion.
|
|
|
|
| Re: Stored Procedure in migrating data [message #644203 is a reply to message #644185] |
Fri, 30 October 2015 03:39 |
 |
javon13
Messages: 17
Registered: October 2015
|
Junior Member |
|
|
Hi, besides business rules which EdStevens mentioned and which makes this migration non-trivial thing, it is good to put another question. How much data do you need to migrate and how fast. In general, there are faster ways how to migrate data. You can use insert..select statements and make all transformations there using just functions like case, decode, nvl. This approach is pretty fast.
If you need to use procedures I recommend use bulk collect altogether.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
