Heterogeneous Connectivity :: Create Interface Between Oracle And SQL Server
Dec 5, 2012
I am working on oracle and pl/sql since some time. so just a novice. I don't know much about sql server or transact sql, etc..I have a requirement to create an interface between Oracle and sql server.
we have a database on oracle and another on sql server. my requirement is to create an interface between oracle and sql server and see how can I update one (or more) tables (I am guessing using pl/sql) on sql server.
Oracle 11g on linuxdestination : ms sql server 2005 i need to create the db link from oracle to ms sql server to view the tables , is it posibble without configuring the gateways( as looks like it is licenced product)
we have microsoft sql server, oracle gateway server (11G) and database server (10.2.0.5) we already make the full setup, and it's working.
we create materialized view into oracle server to read a data from sql server, but the refresh of MV take long time so we are planing to create the materialized view into oracle gateway server.
create MV into oracle gateway server?i mean i don't want to download oracle database instance for only one MV.
I have installed unixodbc 2.3.1, postgres odbc driver (psqlodbc-07.03) and dg4odbc 11.2..On querying : select sysdate from dual@dblink_postgresql, the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found {01000} ORA-02063: preceding 2 lines from DBLINK_POSTGRES
Similar error appears in the trace file as well.
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' $ isql -v postgresql postgres postgres@2012 [01000][unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found [ISQL]ERROR: Could not SQLConnect
However the file is present in the location and has no permission related problems.
$ pwd /oracle/psqlodbc/lib $ ls -lrt total 2952 -rwxr-xr-x 1 oracle oinstall 663 Mar 25 15:28 psqlodbc.la -rw-r--r-- 1 oracle oinstall 731419 Mar 25 15:28 libpsqlodbc.a -rw-r--r-- 1 oracle oinstall 12215 Mar 25 22:11 win_md5.o -rw-r--r-- 1 oracle oinstall 18100 Mar 25 22:11 options.o [code]....
select from the postgre databse works just fine (however when i select data column alone it returns as date, if i select it together with som condition in where clause it returns varchar).
-- this runs ok select t."idbill", t."idpoint" from "crm"."bills2points"@dl_carecloud t ,"crm"."bills"@dl_carecloud brg where 1=1 and t."idbill" = brg."idbill" and t."idbill" = 9685; [code]....
I am trying to configure HS for Excel file. I am getting following error:
SQL> select table_name from all_tables@ODBCEXCEL; select table_name from all_tables@ODBCEXCEL * ERROR at line 1: ORA-28513: internal error in heterogeneous remote agent ORA-02063: preceding line from ODBCEXCELHeres my setup: [code]....
Oracle Corporation --- TUESDAY AUG 21 2012 11:17:46.685
Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with Oracle Database Gateway for ODBC
oracle server NLS_CHARACTERSET is UTF8 and mysql server Character set is UTF8, how should i configure the parameters HS_LANGUAGE(initsid.ora) and CHARSET(odbc.ini)? now i configure the CHARSET = UTF8 and HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15,but the sql query reslut like this:
SELECT "id","name" FROM "accounts"@LNMYSQL _______________________________________ xy-20120712-00016728 é士焱
Oracle 11gR2 Linux 6.x Sybase 12 (on Windows) unixODBC 3.2.x Sybase Client 12 ------------------------
Given the following files or structures that are important to the proper operation of the ODBC Gateway connecting to a remote Sybase database:
listener.ora tnsnames.ora initmydblink.ora ( my version of the initdg4odbc.ini ) dblink (not a file but the dblink in the Oracle database) odbc.ini odbcinst.ini
What files (and what properties within those files) may/must be changed if the remote Sybase db and system is different (i.e. the Sybase database source has been changed to something 'new')? For example, I can that the odbc.ini file is going to have to be alterer to point to the new database server IP address, database port, database name?
i have created the DB LINK from Oralce 11gR2 to MS Sql Server 2005,able to access the view of Sql Server from Oralce,but the Columns which have Datatype nvarchar(max) in Sql Server am not able to access through DB Link.
I am using oracle 11g express edition and MY Sql database How to establish database link between them using ODBC gateway. I am using ODBC gateway Mysqlodbc connector.
I was trying to connect to SQL server from Oracle using Oracle heterogeneous services (HS).But I found that I do not have HS installed on my system. It was mentioned in Oracle website that we need to run 'caths.sql' script under the path $ORACLE_HOME/rdbms/admin.
But I do not have any such folder(rdbms) in my $ORACLE_HOME.What does it mean? And how can I install HS alone on my system(Since I already have oracle)
We have a 2 node RAC installation on IBM AIX 5.3 with Oracle Standard Edition. We need to create a manual standby database (becoz Standard Edition does not allow Dataguard confign). Can we do this on a different platform (eg: Linux/Windows). This is to make use of existing servers for the standby environment.
I would like to develop a system with Oracle 9i as the database and PHP 4/5 as the front end. I am not able to establish the connectivity to the database. What should I do now ?
I have a requirement wherein I need to access the MS ACCESS database table from Oracle Client.I have created ODBC data source and have configured tnsnames.ora and listener.ora for the same. As a next step I am trying to configure HS init file for hsodbc connection but couldn't find the directory hs in $ORACLE_HOME (<ORACLE_HOME>hsadmininit******.ora). Any patch that needs to be applied for hsodbc connection in the server.
I am currently using Oracle 10.2.0.1 version, in a single PC (test server) and MS-Access (2003) in it.
I was trying to use the logic of HS ODBC connectivity, but when i try to select the table from the MS-Access i am getting the following error.
"ORA-12154: TNS:could not resolve the connect identifier specified".
to create the successful connection between the MS-ACCESS and ORACLE. I just need to read some data from MS-ACCESS on a monthly base from a particular table.
We have implemented PeopleSoft with an Oracle database. What I need to do is provide clients with access to information stored in that database. Some additional information may come from other external databases, however for right now I am just trying to crawl, not run.
I am working on getting table information from the PS provider, so that I can understand what information is available. I am also working with the clients to determine if there are pieces of information that they need access to which are not currently native to the PS implementation. I need to identify these for step 2 of the process. My superiors were looking at using an SOA architecture using SOAP to move the data. I am open to suggestions and since the project is in its infancy, I have time to alter perceptions before it is too late.
I am doing a research for our next project.MIGRATING oracle forms6i to java/apex/adfi've gather documentations to present to my boss.but she wants an actual(hands on) migration without purchasing yet.
application that i can test?Is FORMS to .XML to JAVA? is that possible ?where topic my message belongs to.(that's why i've put in GENERAL topic)
I have a problem in oracle forms 10g, as a following: Arabic language show as special character for example(????,!!!!!!,....),I use now charcterset AMERICAN_AMERICA.WE8ISO8859P1 ,I cannot change this charcterset on my database Because I am afraid that the old data may be lost
Note: I am use now oracle form 6i without no problem in Arabic language and when I intended to converted to oracle form 10g ,the language problem has come back, and I was try to change nls_lang to several charcterset charcterset(Arabic_Egypt.AR8MSWIN1256 ,......) but still show in special character character like (????,!!!!!!,....)
Oracle Restart 11.2.0.3 w/ASM. To improve redundancy we're planning to change the network configuration in our Oracle Restart environment from using 1 LAN interface to a bonded interface consisting of 2 interfaces.So the change will be from using "eth0" to using "bond0" consisting of "eth0" and "eth1". Are there any changes that must be done in the Oracle Restart configuration to reflect these changes?
We are using oracle10g database. Every time, we need to run the sql query and find answers for them. We are getting lot of request every day. I wanted to develop interface and i want ops team use the interface to pull the data.
Here are my thoughts...
1. using oracle APEX to develop interface.
2. Use the excel to write macro and get the answers for user request.
3. I can write a PLSQL to run the batch job and extract the data into excel and give it to ops team.
I am using OCI and instant_client (11.2.0.3.0) in my C++ program (on Windows 7) to connect remotely to 11G database. While doing a connection using server name as well as servername:port/service_name as connection strings, I am getting error of ORA-12504 TNS:listener was not given the SERVICE_NAME in CONNECT_DATA. According to instant client FAQ & white paper at [URL]..., no tnsnames.ora is required for instant client. Then why could I be getting this error? I ensured that only the instant client directory is in Path environment variable. There was a ODBC driver installed on this same host.
We would like to used Oracle secure backup using SAS interface on an IBM Autoloader Model TS-2900. Would it be possible to configure oracle secure backup using SAS?
In Oracle 10.2 installation there are OCI headers in rdbms/public directory: 10.2.0/rdbms/public/ocidem.h 10.2.0/rdbms/public/ocidfn.h 10.2.0/rdbms/public/ocikpr.h 10.2.0/rdbms/public/oci.h 10.2.0/rdbms/public/oci1.h ...
What should be installed additionally to have OCI headers in rdbms/public in Oracle 9.2.0?
I work with oracle 10g, on windows 7. sometime when i try to start my sqlplus in prompt command this message display.
C:UsersSony>sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly
But the solution people have proposed into the forum are:
1 - check the environment variable Oracle_home and modify if it ending by ""
2 - or try tho run my promt command as Administrator.
I try the both, but only the second works. while now i don't understand why it works when i start the promt command as Administrator and why it no works when i don't run it like administrator. Another thing is i am the only user of my computer, i have only one session (administor session).
