Skip to main content

Loading Data from SQL Server to Oracle Database Using ODBC Connection

Loading Data from SQL Server to Oracle Database Using ODBC Connection




Step 1:


Creating an ODBC connection:



n  To create an ODBC connections go to ->Control Panel -> Administrative Tools.
n  Double Click on Data Sources (ODBC).
n  Choose System DSN tab.
n  Click Add button.










n  Choose SQL Server and click Finish button.














n  Choose name of Connection and select server.
n  Click Next>.














n  Enter sa users password.
n  Click Next >.











n  Click “Change the default database to:” option and select the database from which you want to fetch data to oracle.
n  Click Next >,









n  Click Test Data Source to test the connection.





















n If successful the following the system will show some window like the following.

n  Click OK to exit.


Step 2: Import – Using PL/SQL Developer Tool:

n  Connect to the Destination Oracle Database by PL/SQL Developer tool.
n  From “Tools” menu go to the ODBC Importer sub menu.



n  The window will look like the following.












n  On Data from ODBC tab – Select System DSN specified, User Name, Password and click Connect on The ODBC connection will show all the tables that your SQL Server Database has.

n  Now carefully select the source table from which data will be loaded into oracle db.


n  Now go to the Data to Oracle tab next to the Data from ODBC tab.
n  Select Owner and Table to which data from ODBC will be loaded.
n  Remember that both the SQL Server table and Oracle table structures need to be same.









n Now click IMPORT button to import data from SQL Server connected by ODBC connection to Oracle Database.




  • All data from the SQL Server to the ORACLE will be loaded.

-----------------------------------------------------------------------------------------------------------

Thanks for reading this article.

hAPPY tO hELP!!!





Labels:

Comments

Post a Comment

Popular posts from this blog

EXPDP/IMPDP Export/Import dumpfile to a Remote Server Using Network_Link.

EXPDP/IMPDP Export/Import dumpfile to a Remote Server Using Network_Link. Step 1:   First you have to create a TNS entry at destination database which will be used to connect to the remote target database. pumplink =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.171)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = Ultimus)     )   ) Step 2:   Connect to SQL plus: --Issue the following command to create db link on destination database: CREATE PUBLIC DATABASE LINK pumplink    connect to scott identified by tiger USING 'pumplink'; Step 3:   Issue the expdp command on the destination server using Network_link parameter: expdp scott/tiger directory= dumpdir logfile=impi_temp.log network_link= pumplink  schemas=scott dump...
5 comments
Read more

All Dictionary View Tables:

All Dictionary View Tables: TABLE_NAME COMMENTS ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_APPLY Details about each apply process that dequeues from the queue visible to the current user ALL_APPLY_CONFLICT_COLUMNS Details about conflict resolution on tables visible to the current user ALL_APPLY_DML_HANDLERS Details about the dml handler on tables visible to the current user ALL_APPLY_ENQUEUE Details about the apply enqueue action for user accessible rules where the destination queue exists and is visible to the user ALL_APPLY_ERROR Error transactions that were generated after dequeuing from the queue visible to the current user ALL_APPLY_EXECUTE Details about the apply execute action for all rules visible to the user ALL_APPLY_KEY_COLUMNS Alternative key columns for a STREAMS table visible to the current user ALL_APPLY_PARAME...
1 comment
Read more

Solution of problem: Resultset Exceeds the Maximum Size (100 MB)

Solution of problem: Resultset Exceeds the Maximum Size (100 MB) I was running a select statement in PL/SQL Developer. it was a short query but the data volume that the query was fetching was huge. But when ever i Click the button Fetch Last Page or press 'ALT+End' button a message box comes after a while saying: Then I started looking for the exact reason of this sort of problem in Google. When I realized there was no direct solution in the web, I started looking the PL/SQL Developer Software menu and found the ultimate solution. The reason of this problem is there is a parameter of maximum result set size in PL/SQL Developer Software which is by default set to 100 MB. To change this parameter you have to go to the following location: 1. Goto Edit Menu and click ' PL/SQL Beautifier Options '. A new window will open. 2. Click SQL Window of " Window Types ". 3. Now Change the value of "Maximum Result Set Size( 0 is unlimited)"  ...
6 comments
Read more