A reader, July 27, 2001 - 9:03 am UTC
A reader, July 27, 2001 - 9:09 am UTC
Class 1 teaching methodology
WL, July 28, 2001 - 12:28 pm UTC
This is the best answer for this type of the question.
Using XML to transfer data from SQL Server to Oracle
Satish, July 30, 2001 - 12:49 am UTC
But i still prefer a generic answer to this question , it can be any database to Oracle and as he mentioned some 100 companies are accessing this data. so how it can be done using XML?
The most efficient way.
AT, July 30, 2001 - 2:03 am UTC
This is the most effecient way to do a data transfer,
very clean indeed.
I wonder if a table link could be another way too.
will appreciate if it comes out with XML example
musa, July 30, 2001 - 3:20 pm UTC
will appreciate if it comes out with XML example
also SQL Server replication through ODBC
Paolo Bazzocchi, July 31, 2001 - 7:45 am UTC
It is quite easy to set up and there is no coding involved. We move real-time data from SQL server to Oracle and we never had a problem.
also SQL Server replication through ODBC
Paolo Bazzocchi, July 31, 2001 - 7:55 am UTC
It is quite easy to set up and there is no coding involved. We move real-time data from SQL server to Oracle and we never had a problem.
Nice Touch
Anthony, Lai Cheuk Tung, August 02, 2001 - 9:46 pm UTC
Pursue a simple way, the goal is how to get the job done, not how to apply tech. everywhere without cautious consideration.
Pure think
A reader, August 03, 2001 - 2:01 am UTC
a reader
juan pedro lopez, August 03, 2001 - 2:09 pm UTC
Parag
Parag Mehta, March 31, 2002 - 5:48 am UTC
Hi Tom :
As always , I am higly impressed byur ans. But it would be fairenough that , u would have given some sort of example. I am also facing same Problem. While seating at Oracle SQL Pormpt , I need to put join in MS SQL Server 2000 Table.
Can u please give me a simple example , ( with the hlp of CORE PL-SQL) if not then with JAVA , where i can access the tables (data) of SQL Server while seating at Oracle.
Secondly , One more requirement is to call SQL Server Stored procedure while seating at Oracle SQL* Prompt. How do i go about for same ?
Waiting for u reply with example.
Regards,
- Parag
March 31, 2002 - 9:08 am UTC
Parag
Parag, March 31, 2002 - 10:20 am UTC
Hi Tom :
I have gone through the link u have send to me. It's not usefull at all ( May be not to me) . Can u please send me a small example of same.
Waiting for Example rather then Link.
March 31, 2002 - 1:34 pm UTC
Is your keyboard broken? many of the Y's and O's seem to be missing.
An example of querying MS SQLServer data joined with Oracle data via a gateway:
select * from emp, dept where emp.deptno = dept.deptno;
(assumimg EMP is in MS Sqlserver, DEPT is in Oracle). Using a gateway, we make sql server look like Oracle, you can call stored procedures, do transactions, query the data, whatever you like.
There is no "example" of a gateway, you buy the product, install it, use it. Please read the links -- they do contain useful information.
since you don't like clicking on links and gathering data on your own, click on this link which gets you a step closer then the other one does:
</code>
http://www.oracle.com/gateways/gateway_bundles/index.html?open_system_gateways.html <code>
then follow the production information links to the FAQ, features, and getting technical.
Excellent
Chandra S. Reddy, April 01, 2002 - 4:48 am UTC
I think the first approach is good.
That is, sitting a Java inside Oracle server, Read and Insert data.
The performance could be increased dramatically.
Synchronize Data
Mohammed Osman, November 27, 2002 - 5:45 am UTC
I have been visiting this great site, since long and have learnt a lot from your valuable Advise.
I have a similar scenerio where I need to retrieve Records from Sql Server into Oracle9i Database, I have no knowledge of Java or Xml. Besides loading from Flat File using SQL Loader is there any other option available. I tried to create a Database Link for the ODBC String but could not succeed.
CREATE DATABASE LINK SQLSRV CONNECT TO ADMIN IDENTIFIED BY ADMIN USING 'Test'
Select * From Branches@SQLSRV
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name
CONN ADMIN/ADMIN@ODBC:Test
ERROR:
ORA-03121: no interface driver connected - function not performed
Would appreciate your respose.
November 27, 2002 - 7:39 am UTC
Generating XML file to Interface from Oracle
Bhavesh, March 25, 2003 - 11:15 am UTC
Tom,
I've simillar requirement and wondering if you have already provided the solution. I did not find it on the site though.
We are looking at generating XML file from Oracle to interface E-commerce website. Our code is complex pl/sql (not a single SQL statement) generating a flat file. Now we would like to generate XML instead of the flat file. I've looked at dbms_xml API's and it takes SQL statement as parameter. Is it possible to generate XML file from PL/SQL simillar to what utl_file does. What should be our approach ?
I would greatly appreciate if you provide some sample code or give us the direction. Thanks a lot for your help.
Bhavesh
March 25, 2003 - 12:05 pm UTC
Umm, you have complex code that results in a flat file.
flat files = rows and columns.
sounds like a query to me -- if you have to just put the rows and columns in a global temporary table and use that as the input to the XML stuff in the database.
XML Data Load performance Vs Text file
Sam, March 25, 2003 - 1:14 pm UTC
Hi Tom,
Which one has better data load performance, XML using dbms_xml api or text file uploaded through SQL Loader direct load?
Regards
Sam
March 25, 2003 - 8:14 pm UTC
apples and pears here. how could you even begin to compare a complex process with tons of benefits with a simple write?
google
Max, March 26, 2003 - 4:53 am UTC
hey, everybody
don`t you noticem that most of the answers were "go nad check out this or that link..."
is it ok?
please, use Google.
do not bother Mr. Kyte
A reader, March 04, 2004 - 10:31 am UTC
Transfer Data
sriram, April 02, 2009 - 7:09 am UTC
Tom ,
I have been asked to work on a PL/SQL program which generates an XML file of required set of data from Oracle Database A , and the thought is to use this XML to load the data into another Oracle Database B. I asked why do we use a webservice here as it is just a datatransfer from one database to another , so the answer was that the structuring of the data in both the databases were not the same and also there would be additional feeds coming into the database B. For this scenario do you feel that this method would be a best practice , please can you seggest a better methodology here?
Thanks
April 02, 2009 - 10:19 am UTC
I would use a dblink, SQL is pretty darn flexible (so flexible, it is generating your XML)
why would you
a) read data out
b) format it into xml
c) ship xml elsewhere
d) read/parse xml
e) write data into database
when you could
a) insert into yourstuff select * from theirstuff@remote;
XML - it is great when you are sharing data with someone else outside of your infrastructure. It is like two people that speak different languages - neither of which is english - using english to communicate with each other. That makes sense.
XML - it is not so great when you are sharing data with yourself. If you do not speak english as your first language, it is unlikely you think in english, you use a much higher baud rate language to think in - your native one.