Migration From Data Integrator To Java
May 6, 2013how to migration from ODI to java.. How to work on this type of migration.
View 1 Replieshow to migration from ODI to java.. How to work on this type of migration.
View 1 Repliesi had a problem with ODI 11g while i was loading some ";" delimited file.
The record is this one:
Header
PIATTAFORMA; FUNZIONE; OPERAZIONE; SUB_OPERAZIONE; DESC_FUNZIONE; SYSTEM; SUBSYSTEM; DES_OPERAZIONE; PROGRESSIVO; TIPOLOGIA; COMMIT; LOGGATA; MOTIVO_ESCLUSIONE
Data
UCAMP-PWS;CDBXF001;;;Inquiry su GEBA;CD;BX;;;Lettura;NO;NO;Funzione senza dati cliente
The field DES_OPERAZIONE is empty for that set of records, but is not a problem cause is a description, my target table is created with all VARCHAR2 except for one field that is Number (PROGRESSIVO in the example) and in this case is empty too, still not a problem cause is not some key or other.
What's happens to me is that if i have the field DES_OPERAZIONE empty, the record will be not loaded but without any message of error or warning. I used the NVL on the field to say if empty then 'ND' but doesn't work. The strange thing is that if i leave empty the field before for example for him doesn't matter, if i use NVL on the field before it works. On teh field DES_OPERAZIONE not !
We have our current production running on 9i and eventually want to migrate to 11g-r2. But the challenges are as follows:
9i production is running in San Francisco data center. 11g-r2 Production already setup up and running in Atlanta data center.
The database size is around 2 TB running on 9i. We are looking to transfer this to 11g-r2 and wondering, what options we have at our disposal: I was looking into EXP/IMP, but somebody said, dblinks will be much faster and reliable than EXP/IMP.
we're planning a data migration from an application (oracle-based) to another (also with oracle db).
the origin is a ca. 80 GB database. so lots of millions of records are to be migrated. (before loading records into the destination tables, they have to be transformed).
the current concept is to receive all origin data in xml files, load them in a staging area (an own migration scheme in oracle), transform and load them into the destination tables.
we have three days for the whole migration (including extract from origin database, transform, load, backup after completion...).
my question is, that a migration with xml-files is a good concept. i think xml processing is much slower than doing the same with csv files. my proposal to migrate an oracle dump (so we got the original data in our staging area) was declined.
is migration mass-data with xml files good or are there performance or other issues?
I had a table with columns as below
employee_id
payroll_pay_week1
payroll_pay_week2
payroll_pay_week3
payroll_pay_week26
I have created a new table, so that instead of having 26 columns for payment amount for each week, I want to have one column for pay amount and one column to represent the week as below
employee_id
payroll_pay
payroll_pay_week
how do I migrate the data from old table to the new table?
Send me sample data migration scripts to get knowledge on Data migration.
View 10 Replies View RelatedIs there any oracle utility to do remote migration of data or it is possible only using some third party software.
View 1 Replies View RelatedI need to migrate data from Mysql database to Oracle11g database.
a) is there any method available to import the all the sqls like table script,constraint scripts,data(insert ) script from Mysql.so that we can apply the sql directly to the oracle schema after making necessary changes(like datatype).
b) Is there any free tool available for the migration.
I have a long file in WORD as I try to load it in ORACLE quotes become periods, ex:
insert into mytab values ('myname, 26);
when i copy this and paste it in oRACLE (UNIX environment), it translates as insert into mytab values (.myname.,26)..does not recognize the quotes.
I tried copying from word to notepad to ORACLE same problem..
we have a requirement for migrating data real time from source db to target db as well as to a queue.
achieve this using any custom technique?
we tried exploring Streams with CDC but Streams being depricated and CDC removed in Oracle 12 c, we are kind of stuck.
I need to generate in report using PL/SQL code for counting the number of rows of all the tables from source and target database..The report should consist of following columns..
table name|source table row count|target table row count|mismatch|..provide me the PL/SQL code?
I got an assignment to create Oracle 11g db. I will be provided the full datapump export dump of an Oracle 10g db in linux. I need to import it to 11g Database in Windows. I have no information about the tablespaces, users etc I have created db with system,sysaux,undotbs temp and users tablespaces.
View 28 Replies View RelatedWe are planning to migrate data from an application called clintrace to another application called argus safety. Both the applications are related to pharmacovigilence safety operation. Both the applications functionality are similar. So both the database are having the same data though the table structures might be different. Both the database are oracle clintrace db is 9i and argus db is 11g.
View 8 Replies View RelatedWe have a data migration scripts written for oracle. Data is not huge but we are observing that the migration is faster in the development labs but is 5x slower in the production site.
The development Oracle setup is on Windows and Production setup on Solaris. I have attached the AWR generated for a period where migration was run for 3 hours and stopped due to slow performance.
Here is my initial analysis.
1) The first timed events is the DB CPU. Hence I feel the migration scripts can be modified to run in parallel so that they can finish faster. However here the question arises why it should run faster in development env if this is an issue.
2) I tried increasing the
a.large_pool_size set to 512M
b.sga_max_size set to 8G
c.sga_target set to 8G
from 0, 4G and 4G respectively.
I have attached the AWR and below are the etc/system contents for solaris settings.
* Begin MDD root info (do not edit)
rootdev:/pseudo/md@0:0,1,blk
* End MDD root info (do not edit)
set noexec_user_stack=1
set noexec_user_stack_log=1
* IBMdpo vpath_START (do not remove)
* default SCSI timeout is 60 seconds
* uncomment to change SCSI timeout * set sd:sd_io_time=0x1e
forceload: drv/vpathdd
* IBMdpo vpath_END (do not remove)
set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
P.S. The awr report is renamed to .txt from .html to be able to upload the file.
Is it possible to migrate everything (tables, indexes) from a unencrypted to encrypted tablespaces online i.e. while the database is being used (DML)?
View 3 Replies View RelatedHow to migrate Data from oracle to MS SQL Server or Vice Versa.
I came to know about 2 methods:
1) Using SQL Developer
2) USing ODBC.
I have Arabic data stored into below two encodings in oracle AL32UTF8 database
1 Million rows into WE8MSWIN1252
.5 million rows into AR8MSWIN1256
in all cases I like to convert 1 Million row of WE8MSWIN1252 into AR8MSWIN1256. I could convert the data encoding from 1252 to 1256 using SQLdeveloper. But no luck using oracle export/import utility (both exp and expdp)…. I’m thinking may be certain locale is required for export/import to work.
Also my company said SQL developer is free utility may not be supported by oracle so use export and import for this, I need to convert only one table.
Similar case
[URL]...........
I have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.
2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.
We should migrate our 10gR2 single-instance database with conventional file system to a two-node 11gR2 RAC on ASM (on same Windows Server platform…).
How can I migrate my production database using data pump? I have full data pump export from target but I don’t know how to import, whether the scheme after scheme, full import, do I need to first create manually tablespaces on destination, whether to exclude the index, constraint, statistics?
I am trying to insert data in one of the tables called as tstcntr_mstr in ibpslive instance by ibpslive user.
My source tables are on ncfiiidv instance.
Query is as follows:
insert into tstcntr_mstr
(select * from tstcntr_mstr@dlink_ncfmdv)
Error that I get is remote operations not permitted on object tables or user-defined type columns.
Table tstcntr_mstr@dlink_ncfmdv contains types.
the migration of the data.
It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For e.g.
Source Target
A B
B C
C D
D A
Last record forms a loop-I need to identify these records. My query is below-
SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;
I ran this query on 2 tables- one with 75000 records and the other with 25000 records. It works fine on the table with 75000 records completes within a minute but it does not complete on the other table. I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?
I want to retrieve values from a XML data stored on CLOB data type and store it in different table.
Retrieving data from Soap Envelope and Soap Body?
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
Data
</soap:Body>
<Account>ABCD1234</Account>
</soap:Envelope>
Below is the XML i have :
<?xml version="1.0" encoding="UTF-8" ?>
- <Transaction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.test.com/Support/Services/test1/2012" xsi:schemaLocation="http://schemas.test.com/Support/Services/test1/2012 Support.test1.v1.xsd">
- <Request>
<Memid>10</Memid>
<Actid>32</Actid>
[code].......
I want to extract the element values :
The below code will be useful when i have no namespace , what must be done in order to work with namespace and extract element value
v_string_xml :=
' //Request[' || TO_CHAR (counter_xml) || ']/Memid/text()';
v_ssp_table (v_ssp_table.COUNT).memid :=
p_xml_in.EXTRACT (v_string_xml).getnumberval ();
v_string_xml :=
[code].......
How to extract the data from XML using the xsd file. attached files.
Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>
i have to extract the value in between tags.
templatecode -> ORDCONF
templateversion ->1.0
Finally i have to load the data into table.
I've generated XML from oracle:
select dbms_xmlquery.getxml('select * from tbl_nm') from dual
<?xml version="1.0" encoding="ISO-8859-1"?>
<ROWSET>
<ROW num="1">
<NAME>admin</NAME>
<PASSWORD>a</PASSWORD>
[code]....
i tried to get this xml data in html table but the table was not displayed. When I changed the <ROW> tag into any other tag say <CD> the table was displayed.
I have to parse an XML and store the data in a table.
My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
[Code].....
The output is :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
There will be cases when I will get multiple values for the columns conversationid, command, value.
<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
[Code].....
In that case the output should be as :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2
Not sure how to go about XML parsing when I have multiple values.
I want to send data from my window based standalone application to my web based application.
View 1 Replies View Relatedhow to use dbms_xmlgen to take the results of a query and convert it into a XML formatted data. But if a column (clob) contains data in an XML format, is there a way to query that column and easily parse the data? I know that I could write loops that do string searches for each of the XML tags and nested tags.
View 9 Replies View RelatedOur company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'